欢送来到 GreatSQL社区分享的MySQL技术文章，如有疑难或想学习的内容，能够在下方评论区留言，看到后会进行解答
- GreatSQL社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
- 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)指标数据库版本与表构造如下：
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 |+-------+----------------------------------------------------------------------------------小玲沉着一下之后，通过以下语句验证了主键id并没有真的失落，仿佛只是呈现了某种显示谬误：
mysql> select * from t2 where id=1;+----+-------+------+| id | col1 | col2 |+----+-------+------+ |1 | TEST1 | TA+----+-------+------+1 row in set (0.00 sec)于是小玲决定排查整个操作流程，搞清楚问题的起因。
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)在windows环境下，通过记事本筹备一个新的测试文件t1.txt，在linux环境下查看t1.txt内容如下：
great@great-PC:~/Downloads/windows$ cat t1.txttest1,test1test2,test2test3,test3test4,test4test5,test5test6,test6进行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: 0mysql> 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)然而有点奇怪的就是右侧的边线不见了，手动插入一个数据，再查问看下数据状况。
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)解析一下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 = 271COMMIT/*!*/;...### 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 = 272COMMIT/*!*/;能够显著看进去理论插入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)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)到这里基本上就很显著了，是因为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: 0mysql> 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)3、查看导入文件
确认是导入的文件问题，咱们查看下这个文件的16进制的状况。
这里应用的是vscode插件hexdump，后果如下
Offset: 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F00000000: 74 65 73 74 31 2C 74 65 73 74 31 0D 0A 74 65 73 test1,test1..tes00000010: 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,tes00000030: 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..察看能够看到除了0D还有0A
mysql> select hex('\n') ;+-----------+| hex('\n') |+-----------+| 0A |+-----------+1 row in set (0.00 sec)能够得悉，导入的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: 0mysql> 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)解析下二进制日志看下理论插入的状况
...### 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 = 385COMMIT/*!*/;...插入的数值正确。
6、总结
整个问题是因为linux零碎和windows零碎的换行符不统一导致的。再导入时候须要思考不同零碎之间的文件是否存在解决差别，防止导致不可预知的结果。
[参考文档]
1.String Literals(https://dev.mysql.com/doc/ref...)
2.LOAD DATA Statement(https://dev.mysql.com/doc/ref...)
Enjoy GreatSQL :)
文章举荐：
技术分享 | MGR最佳实际(MGR Best Practice)
https://mp.weixin.qq.com/s/66...
技术分享 | 万里数据库MGR Bug修复之路
https://mp.weixin.qq.com/s/Ia...
Macos零碎编译percona及局部函数在Macos零碎上运算差别
https://mp.weixin.qq.com/s/jA...
技术分享 | 利用systemd治理MySQL单机多实例
https://mp.weixin.qq.com/s/iJ...
产品 | GreatSQL，打造更好的MGR生态
https://mp.weixin.qq.com/s/By...
产品 | GreatSQL MGR优化参考
https://mp.weixin.qq.com/s/5m...
对于 GreatSQL
GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
微信&QQ群：
可搜寻增加GreatSQL社区助手微信好友，发送验证信息“加群”退出GreatSQL/MGR交换微信群
QQ群：533341697
微信小助手：wanlidbc
本文由博客一文多发平台 OpenWrite 公布！