技术分享 | load data导致主键丢失的神秘问题

ywz888

发布于 2022-8-29 15:39

浏览

0收藏

● 1、发现问题
● 2、复现问题
● 3、检查导入文件
● 4、问题原因
● 5、处理问题
● 6、总结

1、发现问题

在一次数据迁移的任务中，小玲将源端数据库中数据导出为CSV文件，然后通过 load data 导入数据到MySQL，结果惊奇地发现id字段丢失了，就像这个样子：

mysql> select * from t2;
+----+-------+---------------------+
| id | col1  | col2                |
+----+-------+---------------------+
                 |
            || TfdESTA
              |TESTA
          |4 | TEfdfdSTA
 | 5 | TEST5 | TESfddfdsfdsfdsfTA
      |TEST6 | TESffdfdfddTA
+----+-------+---------------------+
6 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

目标数据库版本与表结构如下：

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)


mysql> show create table t2;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `col1` varchar(69) DEFAULT NULL,
  `col2` varchar(79) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

小玲冷静一下之后，通过以下语句验证了主键id并没有真的丢失，似乎只是出现了某种显示错误：

mysql> select * from t2 where id=1;
+----+-------+------+
| id | col1  | col2 |
+----+-------+------+
  |1 | TEST1 | TA
+----+-------+------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.

于是小玲决定排查整个操作流程，搞清楚问题的原因。

2、复现问题

首先创建一个表t1

mysql> CREATE TABLE `t1` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `col1` varchar(60)  DEFAULT NULL,
    ->   `col2` varchar(70) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.01 sec)1.
2.
3.
4.
5.
6.
7.

在windows环境下，通过记事本准备一个新的测试文件t1.txt，在linux环境下查看t1.txt内容如下：

great@great-PC:~/Downloads/windows$ cat t1.txt
test1,test1
test2,test2
test3,test3
test4,test4
test5,test5
test6,test61.
2.
3.
4.
5.
6.
7.

进行load，并查看数据。发现并没有错乱。

mysql> load data infile "/home/great/Downloads/windows/t1.txt" \
       into table t1 FIELDS TERMINATED BY ',' (col1,col2);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t1;
+----+-------+--------+
| id | col1  | col2   |
+----+-------+--------+
 | 1 | test1 | test1
 | 2 | test2 | test2
 | 3 | test3 | test3
 | 4 | test4 | test4
 | 5 | test5 | test5
 | 6 | test6 | test6
+----+-------+--------+
6 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

但是有点奇怪的就是右侧的边线不见了，手动插入一个数据，再查询看下数据情况。

mysql> insert into t1(col1,col2) values('test7','test7');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+-------+--------+
| id | col1  | col2   |
+----+-------+--------+
 | 1 | test1 | test1
 | 2 | test2 | test2
 | 3 | test3 | test3
 | 4 | test4 | test4
 | 5 | test5 | test5
 | 6 | test6 | test6
|  7 | test7 | test7  |
+----+-------+--------+
7 rows in set (0.00 sec)

mysql> select * from t1 where id=7;
+----+-------+-------+
| id | col1  | col2  |
+----+-------+-------+
|  7 | test7 | test7 |
+----+-------+-------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.

解析一下binlog日志

...

### INSERT INTO `test`.`t1`
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test6' /* VARSTRING(240) meta=240 nullable=1 is_null=0 */
###   @3='test6\r' /* VARSTRING(280) meta=280 nullable=1 is_null=0 */
# at 1223
#210923 11:19:09 server id 12345  end_log_pos 1250      Xid = 271
COMMIT/*!*/;

...

### INSERT INTO `test`.`t1`
### SET
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test7' /* VARSTRING(240) meta=240 nullable=1 is_null=0 */
###   @3='test7' /* VARSTRING(280) meta=280 nullable=1 is_null=0 */
# at 1500
#210923 11:19:15 server id 12345  end_log_pos 1527      Xid = 272
COMMIT/*!*/;1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.

可以明显看出来实际插入txt的数据test6的时候并不是test6,而是test\r。而我们手动插入的test7，则确实为test7。

再查看下实际插入的数据的16进制值。

mysql> select id,hex(col2) from t1;
+----+--------------+
| id | hex(col2)    |
+----+--------------+
|  1 | 74657374310D |
|  2 | 74657374320D |
|  3 | 74657374330D |
|  4 | 74657374340D |
|  5 | 74657374350D |
|  6 | 74657374360D |
|  8 | 7465737437   |
+----+--------------+
7 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

load 进来的数据后面比手动插入的正常数据多了0D。

mysql> select  hex('\r') from t1;
+-----------+
| hex('\r') |
+-----------+
| 0D        |
| 0D        |
| 0D        |
| 0D        |
| 0D        |
| 0D        |
| 0D        |
+-----------+
7 rows in set (0.00 sec)


mysql> select  unhex('0D') from t1;
+-------------+
| unhex('0D') |
+-------------+
           |
           |
           |
           |
           |
           |
           |
+-------------+
7 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

到这里基本上就很明显了，是由于txt文件中的符号导致的。而不同于开头查询的结果显示那么不规范的原因，是因为col2的length都是5。导入length不同的数据，就可以明显看出差异。

mysql> load data infile "/home/great/Downloads/windows/oracle_objects.txt" \
       into table t1 FIELDS TERMINATED BY ',' (col1,col2);
Query OK, 2088 rows affected (0.02 sec)
Records: 2088  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from t1   limit 30  ;
+----+-----------------------------+------------+
| id | col1                        | col2       |
+----+-----------------------------+------------+
     | test1                       | test1
     | test2                       | test2
     | test3                       | test3
     | test4                       | test4
     | test5                       | test5
     | test6                       | test6
|  8 | test7                       | test7      |
     | A                           | TABLE
     | A1                          | TABLE
     | AAA                         | TABLE
     | ABC123                      | TABLE
     | ABCDEF                      | TABLE
     | ACTIVE_ALARMS               | TABLE
 |15 | ADDAUTH                     | PROCEDURE
 |16 | ADDROLEAUTH                 | PROCEDURE
     | AGENT_AVAIL_PRIV            | TABLE
     | AGE_STAT_FORBIDTIMELOGIN    | TABLE
     | AGE_STAT_NOTFORBIDTIMELOGIN | TABLE
     | AGE_STAT_QUERYLARGE         | TABLE
     | APP_SCHEDULE_INFO           | TABLE
 |22 | AP_CLEARMUTEXROLL           | PROCEDURE
 |23 | AP_CLEARROLEBYSTSNUM        | PROCEDURE
 |24 | AP_CLEARROLEBYTEL           | PROCEDURE
 |25 | AP_CLEARSUPERROLL           | PROCEDURE
 |26 | AP_CREATE_RDDLV_FILE        | PROCEDURE
 |27 | AP_DEALACTLOG_OPERIP        | PROCEDURE
 |28 | AP_LOC_CRMBI_MSGSEND_LOG    | PROCEDURE
 |29 | AP_MENDCLICK                | PROCEDURE
 |30 | AP_PUB_UNLOCK_VERIFYCODE    | PROCEDURE
 |31 | AP_SETDBUSERANDPASS         | PROCEDURE
+----+-----------------------------+------------+
30 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

3、检查导入文件

确认是导入的文件问题，我们查看下这个文件的16进制的情况。

这里使用的是vscode插件hexdump，结果如下

 Offset: 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
00000000: 74 65 73 74 31 2C 74 65 73 74 31 0D 0A 74 65 73    test1,test1..tes
00000010: 74 32 2C 74 65 73 74 32 0D 0A 74 65 73 74 33 2C    t2,test2..test3,
00000020: 74 65 73 74 33 0D 0A 74 65 73 74 34 2C 74 65 73    test3..test4,tes
00000030: 74 34 0D 0A 74 65 73 74 35 2C 74 65 73 74 35 0D    t4..test5,test5.
00000040: 0A 74 65 73 74 36 2C 74 65 73 74 36 0D 0A          .test6,test6..1.
2.
3.
4.
5.
6.

观察可以看到除了0D还有0A

mysql> select  hex('\n') ;
+-----------+
| hex('\n') |
+-----------+
| 0A        |
+-----------+
1 row in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.

可以得知，导入的txt文件中，每一行的末尾是 \r\n 作为换行的。

在不同的系统中，对于换行符有着不同的表示方式。

以下来自维基百科

应用软件以及操作系统对于换行字符的表示方式：

以ASCII为基础的或兼容的字符集使用分别LF（Line feed，U+000A）或CR（Carriage Return，U>+000D）或CR+LF；下面列出各系统换行字符编码的列表

LF：在Unix或Unix兼容系统（GNU/Linux，AIX，Xenix，Mac OS X，...）、BeOS、Amiga、RISC OS

CR+LF：DOS（MS-DOS、PC-DOS等）、微软视窗操作系统（Microsoft Windows）、大部分非Unix的系统

CR：Apple II家族，Mac OS至版本9

4、问题原因

由上可知，是由于txt文本中，每一行的末尾使用 \r\n 作为换行，而linux系统使用 \n 作为换行，因此 \r 作为一个字符被插入到表中。

\r 在mysql中被处理为　A carriage return character，因此会出现文章头的情况，查询结果不规范。

5、处理问题

在使用load_data导入数据的时候，可以使用　lines terminated by '\r\n' 来告诉mysql，\r\n 是整个作为换行符来使用的。

这样来重新load一下。

mysql> show master status;
+---------------+----------+--------------+------------------+---------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                           |
+---------------+----------+--------------+------------------+---------------------------------------------+
| binlog.000013 |      192 |              |                  | b5457ec7-f50a-11eb-ac22-2cf05daaf63e:1-6182 |
+---------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> load data infile "/home/great/Downloads/windows/t1.txt" \
       into table t1 fields terminated by ','lines terminated by '\r\n'(col1,col2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t1;
+----+-------+-------+
| id | col1  | col2  |
+----+-------+-------+
|  1 | test1 | test1 |
|  2 | test2 | test2 |
|  3 | test3 | test3 |
|  4 | test4 | test4 |
|  5 | test5 | test5 |
|  6 | test6 | test6 |
+----+-------+-------+
6 rows in set (0.00 sec)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.

解析下二进制日志看下实际插入的情况

...
### INSERT INTO `test`.`t1`
### SET
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test6' /* VARSTRING(240) meta=240 nullable=1 is_null=0 */
###   @3='test6' /* VARSTRING(280) meta=280 nullable=1 is_null=0 */
# at 532
#210923 14:39:18 server id 12345  end_log_pos 559       Xid = 385
COMMIT/*!*/;
...1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

插入的数值正确。

6、总结

整个问题是由于linux系统和windows系统的换行符不一致导致的。再导入时候需要思考不同系统之间的文件是否存在处理差异，避免导致不可预知的后果。

[参考文档]

1.String Literals(https://dev.mysql.com/doc/refman/8.0/en/string-literals.html)

2.LOAD DATA Statement(https://dev.mysql.com/doc/refman/8.0/en/load-data.html)

Enjoy GreatSQL :)

文章转载自公众号：GreatSQL社区

分类

其他

标签

GreatSQL

已于2022-8-29 15:39:36修改

51CTO

51CTO博客

51CTO学堂

技术分享 | load data导致主键丢失的神秘问题

订阅鸿蒙技术特刊，精选内容抢先看