mysql データベースで空の列を見つける方法

はじめに

mysqlデータベースで、データの無いすべてのテーブルからすべてのカラムを見つけなければならない問題があります。
この問題を解決する方法はいろいろあるが、mysqlが提供するツールだけで解決した。
例えばpythonのような外部スクリプトを使えばもっと簡単に解決できるかもしれないが、システムの制約上sqlしか使えない。
良いチャレンジだ。

解決方法

空の列を見つけるクエリを作成する

• すべての行を取得
• クエリを発生
• 生成されたクエリを1つずつ実行するプロシージャ手順を作成
• データ表示

クエリ作成

すべての列を取得

mysqlですべての列はこのクエリで取得できます：

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

クエリを発生

テーブル列に空(NULL)があるかどうかを調べるには、次のようなクエリが必要です：

SELECT 
    'table_name', 
    'column_name'
FROM 
    DUAL
WHERE 
    (
        SELECT COUNT(*) 
        FROM `table_name`
    ) = 
    (
        SELECT COUNT(*) 
        FROM `table_name` 
        WHERE `column_name` IS NULL
    )

これを基に、データベース内のすべてのカラムに対して同様のクエリを作成することができます。

SELECT CONCAT(
  'SELECT \'', TABLE_NAME, 
  '\' AS table_name, \'', COLUMN_NAME, 
  '\' AS column_name FROM DUAL WHERE (SELECT COUNT(*) FROM `', 
  TABLE_SCHEMA, '`.`', TABLE_NAME, '`) = (SELECT COUNT(*) FROM `', 
  TABLE_SCHEMA, '`.`', TABLE_NAME, '` WHERE `', COLUMN_NAME, '` IS NULL)'
) AS query_to_run
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'xxxxxxxxxx'; -- スキーマ名に置き換えてください

ちょっと読みにくいですが、クエリ文字列を生成しました。

発生されたクエリを実行

小さなデータベースでも数百のカラムがあるかもしれないので、一つ一つ手作業で実行するのは非常に面倒です。

テーブル作成

最初にデータを保存するためにテーブルを作成

CREATE TEMPORARY TABLE Results (
  table_name VARCHAR(64),
  column_name VARCHAR(64)
);

手順を削除

最後に、すべてのクエリ結果をループして実行し、先に作成したテーブルにデータを挿入するプロシージャーが必要です。

DELIMITER $$
CREATE PROCEDURE RunGeneratedQueries()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE query_to_run TEXT;
  DECLARE cur CURSOR FOR 
    SELECT CONCAT(
      'INSERT INTO Results SELECT \'', TABLE_NAME, 
      '\' AS table_name, \'', COLUMN_NAME, 
      '\' AS column_name FROM DUAL WHERE (SELECT COUNT(*) FROM `', 
      TABLE_SCHEMA, '`.`', TABLE_NAME, '`) = (SELECT COUNT(*) FROM `', 
      TABLE_SCHEMA, '`.`', TABLE_NAME, '` WHERE `', COLUMN_NAME, '` IS NULL)'
    ) AS query_to_run
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'xxxxxxxxxx'; -- スキーマ名に置き換えてください
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO query_to_run;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SET @s = query_to_run;
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END$$
DELIMITER ;

結果

select * from Results

はデータベース内のすべてのNULL列をリストアップされます。
結果が空の場合は、未使用の列がないことを意味します。

まとめ

空欄を見つけるのは意外に難しかった。
他のデータベースにはもっと簡単な方法があるのだろう。
何か良い方法をご存知でしたら、コメント欄にお書きください。