MySQLで全てのテーブルからキーワード検索する

はじめに

最近仕事させて頂いている現場でER図が存在せず、foreign key設定もされておらず、DB構成の理解に時間がかかりました。

画面上で入力した値がどこに設定されているかすぐ検索するためにDB内の全検索プログラムが見当たらなかったので作りました。（MySQL8.0動作確認済）
カーソルの使用経験があまりなかったのでちょうど良い題材でした。

データベースから全検索

drop procedure if exists searchValueInAllTables;

delimiter //
create procedure searchValueInAllTables(IN table_schima_name varchar(255), IN keyword varchar(255))
begin
    declare v_table varchar(255);
    declare v_column varchar(255);
    declare done int default 0;
    declare cur cursor for
        select table_name, column_name from information_schema.columns where TABLE_SCHEMA=table_schima_name;
    declare continue handler for sqlstate '02000' set done=1; -- SQLSTATE 値 '02000' で「データなし」状況時にhandleし、done=1を設定する。その後のアクション「継続」をcontinueで指定
    set @keyword = keyword;
    drop table if exists WORK_DATA;
    create temporary table WORK_DATA( -- temporaryを付けると一時テーブルとして作成できる。セッションを抜けると自動でDropされる
        table_name varchar(255),
        column_name varchar(255),
        hit_count int
    );
    open cur;
        repeat
            fetch cur into v_table, v_column;
            if not done then
                set @sql_search=CONCAT('SELECT COUNT(*) INTO @cnt FROM ' , v_table, ' WHERE ', v_column, ' LIKE ?;');
                prepare stmt from @sql_search;
                execute stmt using @keyword; -- パラメータ値はユーザー変数でのみ提供可能
                if (@cnt > 0) then
                    set @sql_add_result=CONCAT('INSERT INTO WORK_DATA VALUES(?, ?, ?)');
                    prepare stmt_add_result from @sql_add_result;
                    SET @v_table = v_table;
                    SET @v_column = v_column;
                    execute stmt_add_result using @v_table, @v_column, @cnt;
                end if;
            end if;
        until done end repeat; -- untilの後は終了条件。
    close cur;
    select * from WORK_DATA;
end
//
delimiter ;

-- 全テーブル検索
call searchValueInAllTables('db_name', '%search_keyword%');

テーブル内の全検索

drop procedure if exists searchRowInTable;

delimiter //
create procedure searchRowInTable(IN i_table_name varchar(255), IN i_keyword varchar(255))
begin
    declare v_column varchar(255);
    declare done int default 0;
    declare row_num int default 0;
    declare cur cursor for
        select column_name from information_schema.columns where table_name=i_table_name;
    declare continue handler for sqlstate '02000' set done=1;
    
    set @sql_search=CONCAT('SELECT * FROM ', i_table_name, ' WHERE ');
    open cur;
        repeat
            fetch cur into v_column;
            if not done then
                if (row_num > 0) then
                    set @sql_search=CONCAT(@sql_search, ' OR');
                end if;
                set @sql_search=CONCAT(@sql_search, ' ', v_column, ' LIKE "', i_keyword, '"');
            end if;
            set row_num = row_num + 1;
        until done end repeat;
    close cur;
    prepare sql_stmt from @sql_search;
    execute sql_stmt;
end
//
delimiter ;

-- テーブル内全検索
call searchRowInTable("table_name", "%search_keyword%");

作成時に詰まったポイント

MySQL内の変数の扱いがよくわからずエラー解消しにくかった。（stmtのusingでは@valはOKだけどvalはNGなど。 以下2種類の変数があるということで理解できました。

• ローカル変数：　cursor内でdeclareで宣言したものや、selectした値
• ユーザー定義変数：　@から始まるもの（set @var=1だけでなく@var:=1で代入も可能）

GitHubはこちら

https://github.com/somurieengieer/SqlUtility

• ← Previous Post
• Next Post →