技術共有|MySQLは一括挿入時にエラー情報をキャプチャする
作者:楊涛
背景
今日の記事は、今日お客様が質問した質問に由来します.
私はOracleからMySQLに移行しています.データは単純なINSERT文に変換されています.文が多いため、インポートするたびにエラーの文にどのようにナビゲートするか分かりません.もしINSERT文が少なくてもいいなら、私は手で見ることができますが、INSERT文はたくさんあります.私はどの文が間違っているのか、どうやって位置付けますか.私は修正しますか.エラーが発生するたびに修正することはできません.再実行して修正を続けましょうか.簡単な方法はありませんか.
実はMySQL自体にエラー診断エリアがあり、うまく利用できれば、仕事は半分になります.エラー診断領域の使い方を簡単に説明します.
たとえば、私が挿入するテーブル構造はn 3、エラー情報を保存するログテーブルはerror_logの2つのテーブル構造は以下の通りです.
挿入した文を想定して、プレゼンテーションのために、ここでは8つの文を簡単に書いただけです.
MySQLのエラーコードは多いですが、全体的に3つに分類されています. sqlwarning SQLSTATEコードは'01' に始まります. not found SQLSTATEコードは'02' に開始する. sqlexception SQLSTATEコードは、'00','01','02'以外のすべてのエラーコードを開始します.
簡単で便利なように、これらのコードをストレージプロセスに書きます.ストレージ・プロシージャの例を次に示します.
このストレージ・プロシージャを呼び出して結果を見てみましょう.
表N 3の結果.
エラー・ログには、すべてのエラーの文が記録されます.
実はこの問題はPythonやPHPなどの外部言語で言えば、もっと簡単で、考え方が悪くありません.
背景
今日の記事は、今日お客様が質問した質問に由来します.
私はOracleからMySQLに移行しています.データは単純なINSERT文に変換されています.文が多いため、インポートするたびにエラーの文にどのようにナビゲートするか分かりません.もしINSERT文が少なくてもいいなら、私は手で見ることができますが、INSERT文はたくさんあります.私はどの文が間違っているのか、どうやって位置付けますか.私は修正しますか.エラーが発生するたびに修正することはできません.再実行して修正を続けましょうか.簡単な方法はありませんか.
実はMySQL自体にエラー診断エリアがあり、うまく利用できれば、仕事は半分になります.エラー診断領域の使い方を簡単に説明します.
たとえば、私が挿入するテーブル構造はn 3、エラー情報を保存するログテーブルはerror_logの2つのテーブル構造は以下の通りです.
mysql
-- tables definition.
[ytt]>create table n3 (id int not null, id2 int generated always as ((mod(id,10))));
Query OK, 0 rows affected (0.04 sec)
[ytt]>create table error_log (sqltext text, error_no int unsigned, error_message text);
Query OK, 0 rows affected (0.04 sec)
挿入した文を想定して、プレゼンテーションのために、ここでは8つの文を簡単に書いただけです.
mysql
-- statements body.
set @a1 = "INSERT INTO n3 (id) VALUES(100)";
set @a2 = "INSERT INTO n3 (id) VALUES('test')";
set @a3 = "INSERT INTO n3 (id) VALUES('test123')";
set @a4 = "INSERT INTO n3 (id) VALUES('123test')";
set @a5 = "INSERT INTO n3 (id) VALUES(200)";
set @a6 = "INSERT INTO n3 (id) VALUES(500)";
set @a7 = "INSERT INTO n3 (id) VALUES(null)";
set @a8 = "INSERT INTO n3 (id) VALUES(10000000000000)";
MySQLのエラーコードは多いですが、全体的に3つに分類されています.
簡単で便利なように、これらのコードをストレージプロセスに書きます.ストレージ・プロシージャの例を次に示します.
mysql
-- stored routines body.
drop procedure if exists sp_insert_simple;
delimiter ||
create procedure sp_insert_simple()
l1:begin
DECLARE i,j TINYINT DEFAULT 1; -- loop counter.
DECLARE v_errcount,v_errno INT DEFAULT 0; -- error count and error number.
DECLARE v_msg TEXT; -- error details.
declare v_sql json; -- store statements list.
declare v_sql_keys varchar(100); -- array index.
declare v_sql_length int unsigned; -- array length.
-- Handler declare.
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND -- exception in mysql routines.
l2:BEGIN
get stacked diagnostics v_errcount = number;
set j = 1;
WHILE j <= v_errcount
do
GET stacked DIAGNOSTICS CONDITION j v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
-- record error messages into table.
INSERT INTO error_log(sqltext,error_no,error_message) VALUES (@sqltext, v_errno,v_msg);
SET j = j + 1;
END WHILE;
end;
-- sample statements array.
set v_sql = '{
"a1": "INSERT INTO n3 (id) VALUES(100)",
"a2": "INSERT INTO n3 (id) VALUES(''test'')",
"a3": "INSERT INTO n3 (id) VALUES(''test123'')",
"a4": "INSERT INTO n3 (id) VALUES(''123test'')",
"a5": "INSERT INTO n3 (id) VALUES(200)",
"a6": "INSERT INTO n3 (id) VALUES(500)",
"a7": "INSERT INTO n3 (id) VALUES(null)",
"a8": "INSERT INTO n3 (id) VALUES(10000000000000)"
}';
set i = 1;
set v_sql_length = json_length(v_sql);
while i <=v_sql_length do
set v_sql_keys = concat('$.a',i);
set @sqltext = replace(json_extract(v_sql,v_sql_keys),'"','');
prepare s1 from @sqltext;
execute s1;
set i = i + 1;
end while;
drop prepare s1;
-- invoke procedure.
-- call sp_insert_simple;
end;
||
delimiter ;
このストレージ・プロシージャを呼び出して結果を見てみましょう.
mysql
[(none)]>use ytt
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
[ytt]>call sp_insert_simple;
Query OK, 0 rows affected (0.05 sec)
表N 3の結果.
mysql
[ytt]>select * from n3;
+-----+------+
| id | id2 |
+-----+------+
| 100 | 0 |
| 200 | 0 |
| 500 | 0 |
+-----+------+
3 rows in set (0.00 sec)
エラー・ログには、すべてのエラーの文が記録されます.
mysql
[ytt]>select * from error_log;
+--------------------------------------------+----------+-------------------------------------------------------------+
| sqltext | error_no | error_message |
+--------------------------------------------+----------+-------------------------------------------------------------+
| INSERT INTO n3 (id) VALUES('test') | 1366 | Incorrect integer value: 'test' for column 'id' at row 1 |
| INSERT INTO n3 (id) VALUES('test123') | 1366 | Incorrect integer value: 'test123' for column 'id' at row 1 |
| INSERT INTO n3 (id) VALUES('123test') | 1265 | Data truncated for column 'id' at row 1 |
| INSERT INTO n3 (id) VALUES(null) | 1048 | Column 'id' cannot be null |
| INSERT INTO n3 (id) VALUES(10000000000000) | 1264 | Out of range value for column 'id' at row 1 |
+--------------------------------------------+----------+-------------------------------------------------------------+
5 rows in set (0.00 sec)
実はこの問題はPythonやPHPなどの外部言語で言えば、もっと簡単で、考え方が悪くありません.