ストアドプロシージャのログを頑張って出力する

だいぶ力技ですができるのです〜

mysqlのストアドプロシージャは、SQLをログ出力できません。

出力されるのは、call proc_name(1, 2, 3); という呼び出し部分のみです。本当はプロシージャ内のSQLの内容を出力したい場合が多い。そこでちょっと考えました。

ログ出力するためのプロシージャ書けばいいじゃん

ということで書いてみます。

最初にロガー（プロシージャです）

SET NAMES UTF8;
delimiter //
-- ログ出力
DROP PROCEDURE IF EXISTS logger//
CREATE PROCEDURE logger(
  IN PROCEDURE_NAME TEXT,
  IN SQL_TEXT TEXT
)
BEGIN
DECLARE CNT INT;
CREATE TABLE IF NOT EXISTS PROCEDURE_LOG (
    NAME VARCHAR(100),
    QUERY TEXT,
    EXECUTE_DATE TIMESTAMP,
    KEY IDX1 (NAME, QUERY(255), EXECUTE_DATE)
) ENGINE=MYISAM DEFAULT CHARSET=UTF8;
-- 古いログを削除
SELECT COUNT(*) INTO CNT FROM PROCEDURE_LOG;
IF CNT >= 1000 THEN
    DELETE FROM PROCEDURE_LOG LIMIT 1;
END IF;
-- テーブルにログを記録
SET @sql = CONCAT('INSERT INTO PROCEDURE_LOG VALUES (', QUOTE(PROCEDURE_NAME), ',', QUOTE(SQL_TEXT), ', null)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
END
//
delimiter ;

使い方はこんな感じ。

〜省略〜
SET @sql = CONCAT('SELECT ID, NAME FROM TEST LIMIT ', offset, ', ', limit);
-- ストアドのSQLをログテーブルに記録
call logger('プロシージャ名', @sql);
〜省略〜

処理内容は、@sqlの一時変数にSQL文字列を設定しておき、存在しなかったらSTORED_LOGSテーブルを作成してSQL文字列をinsert、としているだけです。

注意点は、@sql を使い回さないようにしましょう。@変数は一時変数なので、loggerのトランザクション完了時に変数が破棄されてしまいます。

実践ハイパフォーマンスMySQL 第2版