[MySQL] よく使うけどたまに忘れるお役立ちクエリのメモ

全テーブルのレコード数

SELECT table_name, table_rows FROM information_schema.TABLES

列情報の取得

SELECT table_name, column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '[DB名]' ORDER BY TABLE_NAME;

実行中の書き込みクエリ

SELECT `time`, `info` FROM information_schema.PROCESSLIST WHERE `state` = 'update';

UNIX TIMEへ変換

SELECT unix_timestamp(now());
SELECT unix_timestamp('2010-01-01 11:22:33');

datetime型へ変換

SELECT from_unixtime(1292120553);
SELECT from_unixtime(unix_timestamp());