技术分享 | MySQL 在批量插入时捕捉错误信息 - 技术分享 - 新闻资讯 - 爱可生

技术分享 | MySQL 在批量插入时捕捉错误信息

发布时间:2021-01-13 浏览次数:268

关键字:数据库运维服务数据库运维管理数据库灾备

背景

本篇文章来源于今天客户问的一个问题。

问题大概意思是:我正在从 Oracle 迁移到 MySQL,数据已经转换为单纯的 INSERT 语句。由于语句很多,每次导入的时候不知道怎么定位到错误的语句。 如果 INSERT 语句少也就罢了,我可以手工看,不过 INSERT 语句很多,我怎么定位到是哪些语句出错了,我好改正呢?总不能每次遇到的错误的时候改一下,再重新运行继续改正吧?有没有简单点的方法。

其实 MySQL 自身就有错误诊断区域,如果能好好利用,则事半功倍。


演示

下面我来简单说下怎么使用错误诊断区域

比如说我要插入的表结构为 n3,保存错误信息的日志表为 error_log 两个表结构如下:

  1. -- tables definition.

  2. [ytt]>create table n3 (id int not null, id2 int generated always as ((mod(id,10))));

  3. Query OK, 0 rows affected (0.04 sec)


  4. [ytt]>create table error_log (sqltext text, error_no int unsigned, error_message text);

  5. Query OK, 0 rows affected (0.04 sec)

假设插入的语句,为了演示,我这里仅仅简单写了 8 条语句。

-- 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 的错误代码很多,不过总体归为三类:

  • sqlwarning SQLSTATE 代码开始为 '01'


  • not found SQLSTATE 代码开始为 '02'


  • sqlexception SQLSTATE 代码开始非 '00','01','02' 的所有错误代码。

为了简单方便,我们写这些代码到存储过程里。以下为示例存储过程。

  1. -- stored routines body.

  2. drop procedure if exists sp_insert_simple;

  3. delimiter ||

  4. create procedure sp_insert_simple()

  5. l1:begin

  6.  DECLARE i,j TINYINT DEFAULT 1;   -- loop counter.

  7.  DECLARE v_errcount,v_errno INT DEFAULT 0; -- error count and error number.

  8.  DECLARE v_msg TEXT; -- error details.

  9.  declare v_sql json; -- store statements list.

  10.  declare v_sql_keys varchar(100); -- array index.

  11.  declare v_sql_length int unsigned; -- array length.


  12.  -- Handler declare.

  13.  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND  -- exception in mysql routines.

  14.  l2:BEGIN

  15.    get stacked diagnostics v_errcount = number;

  16.    set j = 1;

  17.    WHILE j <= v_errcount

  18.    do

  19.      GET stacked DIAGNOSTICS CONDITION j  v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;

  20.      -- record error messages into table.

  21.      INSERT INTO error_log(sqltext,error_no,error_message) VALUES (@sqltext, v_errno,v_msg);

  22.      SET j = j + 1;

  23.    END WHILE;

  24.  end;

  25.  -- sample statements array.

  26.  set v_sql = '{

  27.        "a1": "INSERT INTO n3 (id) VALUES(100)",

  28.        "a2": "INSERT INTO n3 (id) VALUES(''test'')",

  29.        "a3": "INSERT INTO n3 (id) VALUES(''test123'')",

  30.        "a4": "INSERT INTO n3 (id) VALUES(''123test'')",

  31.        "a5": "INSERT INTO n3 (id) VALUES(200)",

  32.        "a6": "INSERT INTO n3 (id) VALUES(500)",

  33.        "a7": "INSERT INTO n3 (id) VALUES(null)",

  34.        "a8": "INSERT INTO n3 (id) VALUES(10000000000000)"

  35. }';

  36.  set i = 1;

  37.  set v_sql_length = json_length(v_sql);

  38.  while i <=v_sql_length  do

  39.    set v_sql_keys = concat('$.a',i);

  40.    set @sqltext = replace(json_extract(v_sql,v_sql_keys),'"','');

  41.    prepare s1 from @sqltext;

  42.    execute s1;

  43.    set i = i + 1;

  44.  end while;

  45.  drop prepare s1;

  46.  -- invoke procedure.

  47.  -- call sp_insert_simple;

  48. end;

  49. ||

  50. delimiter ;

我们来调用这个存储过程看下结果。

  1. [(none)]>use ytt

  2. Reading table information for completion of table and column names

  3. You can turn off this feature to get a quicker startup with -A

  4. Database changed


  5. [ytt]>call sp_insert_simple;

  6. Query OK, 0 rows affected (0.05 sec)

表N3的结果。

[ytt]>select  * from n3;+-----+------+| id  | id2  |+-----+------+| 100 |    0 || 200 |    0 || 500 |    0 |+-----+------+3 rows in set (0.00 sec)

错误日志记录了所有错误的语句。

[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 等外部语言来说,将会更简单,思路差不多。

关键字:数据库运维服务数据库运维管理数据库灾备

相关推荐

产品试用 产品试用
400-820-6580 免费电话