devbox
从前慢现在也慢
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL LOAD DATA 语句详解

作者：从前慢现在也慢 | 2024-04-29 19:11:36

赞

踩

mysql load data

导读

作者：沃趣-罗小波

沃趣科技高级数据库技术专家，主要负责MySQL RDS产品的原型与架构设计。熟悉MySQL体系结构，Innodb存储引擎，喜好专研开源技术，追求完美

概要：
对于MySQL 的LOAD DATA语句来说，它使用的频率并不高，因为它的适用场景比较窄。但是，由于它能够加载按照指定分隔符分割的纯文本数据，通过MySQL Server发行软件包中提供的mysqlimport工具还能实现批量导入纯文本数据。所以，一些童鞋可能会将其用于数据库软件跨大版本升级或者数据的跨平台流转等场景中。不过，该语句存在着众多的子选项，加上不常用，常常容易忘记，正巧，笔者曾经完整地研究过这些内容，现在，我们将其整理出来分享给大家。

一、原文目录

MySQL LOAD DATA 语句详解

 

1、语法解析

 

1.1. 必选子句或关键字

 

1.2. 可选子句或关键字

 

1.2.1. LOW_PRIORITY关键字

 

1.2.2. LOCAL关键字

 

1.2.2.1. 使用与不使用local关键字的流程

 

1.2.2.2. 使用local关键字的错误处理

 

1.2.3. REPLACE与IGNORE关键字

 

1.2.4. PARTITION子句

 

1.2.5. CHARACTER SET charset_name子句

 

1.2.6. FIELDS（与COLUMNS关键字相同）和LINES子句

 

1.2.6.1. FIELDS关键字及其子句详解

 

1.2.6.2. LINES 关键字及其子句详解

 

1.2.6.3. FIELDS和LINES注意事项

 

1.2.7. IGNORE number {LINES |ROWS}子句

 

1.2.8.(col_name_or_user_var,...)指定字段名称的子句

 

1.2.8. SET col_name = expr,...子句

 

2、批量导出和批量导入

 

2.1. 使用mysqldump批量导出

 

2.2. 使用mysqimport批量导出

 

2.2.1. mysqlimport语法及其参数说明

 

2.2.2. mysqlimport用法演示示例

 

2.2.2.1. 单表导入

 

2.2.2.2.多表导入

 

3、总结

 

二、原文节选

2、批量导出和批量导入

  • 使用mysqldump批量导出生成表的txt文件，并使用mysqlimport批量导入表的txt文件到数据库中

2.1. 使用mysqldump批量导出

  • 使用mysqldump导出数据为文本的语法如下：

mysqldump -u username -p'xxx' -T target_dir db_name tb_name [option];

  • 其中option参数是以下几种可选参数：

--fields-terminated-by 'string' 字段分隔符

--fields-enclosed-by 'char' 字段引用符

--fields-optionally-enclosed-by 'char' 字段引用符，只在char,varchar,text等字段类型上生效

--fields-escaped-by 'char' 转义字符

--lines-terminated-by 'string' 记录结束符，即换行符

示例

  1. $ mkdir /data/backup/	
  2. $ chown mysql.mysql /data/backup -R	
  3. $ mysqldump -uadmin -ppassword -h 10.10.30.241 --single-transaction --master-data=2 --triggers --routines --events xiaoboluo -T /data/backup/	
  4. mysqldump: [Warning] Using a password on the command line interface can be insecure.	
  5. Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database.\	
  6.  If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.	
  7. SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;	
  8. SET @@SESSION.SQL_LOG_BIN= 0;	
  9. --	
  10. -- GTID state at the beginning of the backup	
  11. --	
  12. SET @@GLOBAL.GTID_PURGED='2016f827-2d98-11e7-bb1e-00163e407cfb:1-114';	
  13. --	
  14. -- Position to start replication or point-in-time recovery from	
  15. --	
  16. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=21737;  #使用-T选项时，--master-data=2打印的binlog pos信息会直接打印在标准输出上	
  17. --	
  18. -- Dumping events for database 'xiaoboluo'	
  19. --	
  20. --	
  21. -- Dumping routines for database 'xiaoboluo'	
  22. --	
  23. SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;	
  24. $ ll /data/backup/  #可以看到mysqldump -T选项批量导出的表数据除了文本文件之外，还同时备份了表结构	
  25. total 32	
  26. -rw-r--r-- 1 root  root  1526 May  3 22:45 test2.sql	
  27. -rw-rw-rw- 1 mysql mysql  286 May  3 22:45 test2.txt	
  28. -rw-r--r-- 1 root  root  1549 May  3 22:45 test3.sql	
  29. -rw-rw-rw- 1 mysql mysql  194 May  3 22:45 test3.txt	
  30. -rw-r--r-- 1 root  root  1600 May  3 22:45 test4.sql	
  31. -rw-rw-rw- 1 mysql mysql  314 May  3 22:45 test4.txt	
  32. -rw-r--r-- 1 mysql mysql 1493 May  3 22:45 test.sql	
  33. -rw-rw-rw- 1 mysql mysql   95 May  3 22:45 test.txt
2.2. 使用mysqimport批量导出
2.2.1. mysqlimport语法及其参数说明

  • mysqlimport实用程序加载数据文件时，它通过向服务器发送LOAD DATA INFILE语句来实现(它实际是客户端提供了load data infile语句的一个命令行接口)，可以使用--local选项使mysqlimport从客户端主机（而不是mysql server主机）读取数据文件。如果客户端和服务器支持压缩协议，则可以指定--compress选项以在慢速网络中获得更好的性能。

  • 使用mysqlimport命令，语法如下：

mysqlimport -uroot -p 'xxx' [--local] db_name order_tab.txt [iption]

  • 其中，option参数可以是如下选项

  1. --fields-terminated-by=name   指定字段分隔符	
  2. --fields-enclosed-by=name  指定字段引用符	
  3. --fields-optionally-enclosed-by=name   指定字段引用符，但只在char、varchar、text字段上使用引用符	
  4. --fields-escaped-by=name   指定转义字符	
  5. --lines-terminated-by=name   指定行记录结束符（换行符）	
  6. --ignore-liens=number   忽略前几行	
  7. --low-priority  碰到有其他线程update操作操作的表与导入操作表相同时，延迟执行导入操作	
  8. -i, --ignore  如果碰到唯一键冲突就忽略冲突行导入	
  9. -r, --replace  如果碰到唯一键冲突就覆盖冲突行导入	
  10. -L, --local  从客户端主机加载数据文本文件	
  11. -C, --compress  在C/S模型之间使用压缩传输数据	
  12. -c, --columns=name  指定需要导入哪些列，与load data语句中一样需要指定表定义中真实的列名，有多个列名时使用逗号分隔	
  13. --default-character-set=name  设置使用该选项指定的字符集来解析文本文件中的内容	
  14. -h, --host  指定导入server的主机IP	
  15. -p, --password[=name]  指定导入server的用户密码	
  16. -P, --port=#  指定导入server的监听端口	
  17. --use-threads=#  指定多少个线程并发执行load data语句（实测单表时指定多线程时要比单线程要快，由于数据量小，测试出来的差别并不大，官方并没有说明是基于什么级别的并发，\	
  18. 只写了一句：Load files in parallel using N threads，推测可能是基于类似mydumper的并发，但是多表导入时指定多线程就明显比单线程要快很多）	
  19. -u, --user=name  指定导入server的用户名	
  20. -d, --delete  指定导入操作之前先把表清空（实测重复导入时加了这个选项之后可以正常执行，，通过解析binlog发现，发现binlog中记录的第二次和第一次导入的语句完全相同是，\	
  21. 第二次导入时如果发现表中有冲突数据，就先执行的不带where条件的delete，所有表先delete掉，然后再执行load data语句导入数据，另外，当与replace一起使用时，忽略replace选项）
2.2.2. mysqlimport用法演示示例

  • 先执行清理server中表的数据

  1. admin@localhost : (none) 11:08:58> use xiaoboluo	
  2. Database changed	
  3. admin@localhost : xiaoboluo 11:09:00> show tables;	
  4. +---------------------+	
  5. | Tables_in_xiaoboluo |	
  6. +---------------------+	
  7. | test                |	
  8. | test2               |	
  9. | test3               |	
  10. | test4               |	
  11. +---------------------+	
  12. 4 rows in set (0.00 sec)	
  13. admin@localhost : xiaoboluo 11:09:01> select * from test;	
  14. +----+------+-------+	
  15. | id | test | test2 |	
  16. +----+------+-------+	
  17. |  2 | 1    | 2     |	
  18. |  4 | 2    | NULL  |	
  19. |  6 | null | NULL  |	
  20. |  8 | 4    | NULL  |	
  21. | 10 |         | NULL  |	
  22. | 12 | \\t  | NULL  |	
  23. | 14 | t    | NULL  |	
  24. | 16 | \t   | NULL  |	
  25. | 18 | t    | NULL  |	
  26. | 20 | NULL | NULL  |	
  27. | 22 | "t   | NULL  |	
  28. +----+------+-------+	
  29. 11 rows in set (0.00 sec)	
  30. admin@localhost : xiaoboluo 11:09:11> system cat /data/backup/test.txt;	
  31. 2    1   2	
  32. 4    2   \N	
  33. 6    null    \N	
  34. 8    4   \N	
  35. 10    \       \N	
  36. 12    \\\\t   \N	
  37. 14    t   \N	
  38. 16    \\t \N	
  39. 18    t   \N	
  40. 20    \N  \N	
  41. 22    "t  \N	
  42. admin@localhost : xiaoboluo 11:12:08> select * from test2;	
  43. +----+------+-------+---------------------+	
  44. | id | test | test2 | dt                  |	
  45. +----+------+-------+---------------------+	
  46. |  2 | 1    | 2     | 2017-05-02 18:47:03 |	
  47. |  4 | 2    | NULL  | 2017-05-02 18:47:03 |	
  48. |  6 | null | NULL  | 2017-05-02 18:47:03 |	
  49. |  8 | 4    | NULL  | 2017-05-02 18:47:03 |	
  50. | 10 |         | NULL  | 2017-05-02 18:47:03 |	
  51. | 12 | \\t  | NULL  | 2017-05-02 18:47:03 |	
  52. | 14 | t    | NULL  | 2017-05-02 18:47:03 |	
  53. | 16 | \t   | NULL  | 2017-05-02 18:47:03 |	
  54. | 18 | t    | NULL  | 2017-05-02 18:47:03 |	
  55. | 20 | NULL | NULL  | 2017-05-02 18:47:03 |	
  56. +----+------+-------+---------------------+	
  57. 10 rows in set (0.00 sec)	
  58. admin@localhost : xiaoboluo 11:12:15> system cat /data/backup/test2.txt;	
  59. 2    1   2   2017-05-02 18:47:03	
  60. 4    2   \N  2017-05-02 18:47:03	
  61. 6    null    \N  2017-05-02 18:47:03	
  62. 8    4   \N  2017-05-02 18:47:03	
  63. 10    \       \N  2017-05-02 18:47:03	
  64. 12    \\\\t   \N  2017-05-02 18:47:03	
  65. 14    t   \N  2017-05-02 18:47:03	
  66. 16    \\t \N  2017-05-02 18:47:03	
  67. 18    t   \N  2017-05-02 18:47:03	
  68. 20    \N  \N  2017-05-02 18:47:03	
  69. admin@localhost : xiaoboluo 11:12:27> truncate test2;	
  70. Query OK, 0 rows affected (0.00 sec)	
  71. admin@localhost : xiaoboluo 11:12:32> select * from test3;	
  72. +----+------------------------------------------+--------+-------+	
  73. | id | test                                     | test2  | test3 |	
  74. +----+------------------------------------------+--------+-------+	
  75. |  2 | a string                                 | 100.20 | null  |	
  76. |  4 | a string containing a , comma            | 102.20 | NULL  |	
  77. |  6 | a string containing a " quote            | 102.20 | NULL  |	
  78. |  8 | a string containing a ", quote and comma | 102.20 | NULL  |	
  79. | 10 | \t                                       | 102.20 | NULL  |	
  80. | 14 | \t                                       | 102.20 | NULL  |	
  81. +----+------------------------------------------+--------+-------+	
  82. 6 rows in set (0.00 sec)	
  83. admin@localhost : xiaoboluo 11:12:44> system cat /data/backup/test3.txt;	
  84. 2    a string    100.20  null	
  85. 4    a string containing a , comma   102.20  \N	
  86. 6    a string containing a " quote   102.20  \N	
  87. 8    a string containing a ", quote and comma    102.20  \N	
  88. 10    \\t 102.20  \N	
  89. 14    \\t 102.20  \N	
  90. admin@localhost : xiaoboluo 11:12:59> truncate test3;	
  91. Query OK, 0 rows affected (0.01 sec)	
  92. admin@localhost : xiaoboluo 11:13:03> select * from test4;	
  93. +----+------------------------------------------+--------+-------+---------------------+	
  94. | id | test                                     | test2  | test3 | test4               |	
  95. +----+------------------------------------------+--------+-------+---------------------+	
  96. |  2 | a string                                 | 100.20 | null  | 2017-05-03 18:41:02 |	
  97. |  4 | a string containing a , comma            | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  98. |  6 | a string containing a " quote            | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  99. |  8 | a string containing a ", quote and comma | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  100. | 10 | \t                                       | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  101. | 14 | \t                                       | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  102. +----+------------------------------------------+--------+-------+---------------------+	
  103. 6 rows in set (0.00 sec)	
  104. admin@localhost : xiaoboluo 11:13:15> system cat /data/backup/test4.txt;	
  105. 2    a string    100.20  null    2017-05-03 18:41:02	
  106. 4    a string containing a , comma   102.20  \N  2017-05-03 18:41:02	
  107. 6    a string containing a " quote   102.20  \N  2017-05-03 18:41:02	
  108. 8    a string containing a ", quote and comma    102.20  \N  2017-05-03 18:41:02	
  109. 10    \\t 102.20  \N  2017-05-03 18:41:02	
  110. 14    \\t 102.20  \N  2017-05-03 18:41:02	
  111. admin@localhost : xiaoboluo 11:13:24> truncate test4;	
  112. Query OK, 0 rows affected (0.01 sec)	
  113. admin@localhost : xiaoboluo 11:13:28> flush logs;	
  114. Query OK, 0 rows affected (0.01 sec)
2.2.2.1. 单表导入

  • 使用mysqlimport命令导入单张表

  1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 ~]# mysqlimport -uadmin -ppassword -h10.10.30.241 xiaoboluo /data/backup/test.txt	
  2. mysqlimport: [Warning] Using a password on the command line interface can be insecure.	
  3. xiaoboluo.test: Records: 11  Deleted: 0  Skipped: 0  Warnings: 0	
  4. # 查看数据库中的数据	
  5. admin@localhost : xiaoboluo 11:13:42> select * from test;	
  6. +----+------+-------+	
  7. | id | test | test2 |	
  8. +----+------+-------+	
  9. |  2 | 1    | 2     |	
  10. |  4 | 2    | NULL  |	
  11. |  6 | null | NULL  |	
  12. |  8 | 4    | NULL  |	
  13. | 10 |         | NULL  |	
  14. | 12 | \\t  | NULL  |	
  15. | 14 | t    | NULL  |	
  16. | 16 | \t   | NULL  |	
  17. | 18 | t    | NULL  |	
  18. | 20 | NULL | NULL  |	
  19. | 22 | "t   | NULL  |	
  20. +----+------+-------+	
  21. 11 rows in set (0.00 sec)

  • 解析binlog查看里边如何记录的

  1. $ mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000006	
  2. .....	
  3. BEGIN	
  4. /*!*/;	
  5. # at 344	
  6. #170503 23:15:29 server id 3306241  end_log_pos 443 CRC32 0x4c1c8e8a      Rows_query	
  7. # LOAD DATA   INFILE '/data/backup/test.txt' INTO TABLE `test` IGNORE 0 LINES  #mysqlimport内部调用的load data语句在这里	
  8. # at 443	
  9. #170503 23:15:29 server id 3306241  end_log_pos 501 CRC32 0x1ddc6d53      Table_map: `xiaoboluo`.`test` mapped to number 304	
  10. # at 501	
  11. #170503 23:15:29 server id 3306241  end_log_pos 631 CRC32 0xa8c4beab      Write_rows: table id 304 flags: STMT_END_F	
  12. ### INSERT INTO `xiaoboluo`.`test`  #由于binlog_format=row，所以写到binlog中时内部把load data语句转换为了row格式	
  13. ### SET	
  14. ###   @1=2 /* INT meta=0 nullable=0 is_null=0 */	
  15. ###   @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  16. ###   @3='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  17. ### INSERT INTO `xiaoboluo`.`test`	
  18. ### SET	
  19. ###   @1=4 /* INT meta=0 nullable=0 is_null=0 */	
  20. ###   @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  21. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  22. ### INSERT INTO `xiaoboluo`.`test`	
  23. ### SET	
  24. ###   @1=6 /* INT meta=0 nullable=0 is_null=0 */	
  25. ###   @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  26. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  27. ### INSERT INTO `xiaoboluo`.`test`	
  28. ### SET	
  29. ###   @1=8 /* INT meta=0 nullable=0 is_null=0 */	
  30. ###   @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  31. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  32. ### INSERT INTO `xiaoboluo`.`test`	
  33. ### SET	
  34. ###   @1=10 /* INT meta=0 nullable=0 is_null=0 */	
  35. ###   @2='\x09' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  36. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  37. ### INSERT INTO `xiaoboluo`.`test`	
  38. ### SET	
  39. ###   @1=12 /* INT meta=0 nullable=0 is_null=0 */	
  40. ###   @2='\x5c\x5ct' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  41. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  42. ### INSERT INTO `xiaoboluo`.`test`	
  43. ### SET	
  44. ###   @1=14 /* INT meta=0 nullable=0 is_null=0 */	
  45. ###   @2='t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  46. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  47. ### INSERT INTO `xiaoboluo`.`test`	
  48. ### SET	
  49. ###   @1=16 /* INT meta=0 nullable=0 is_null=0 */	
  50. ###   @2='\x5ct' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  51. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  52. ### INSERT INTO `xiaoboluo`.`test`	
  53. ### SET	
  54. ###   @1=18 /* INT meta=0 nullable=0 is_null=0 */	
  55. ###   @2='t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  56. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  57. ### INSERT INTO `xiaoboluo`.`test`	
  58. ### SET	
  59. ###   @1=20 /* INT meta=0 nullable=0 is_null=0 */	
  60. ###   @2=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  61. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  62. ### INSERT INTO `xiaoboluo`.`test`	
  63. ### SET	
  64. ###   @1=22 /* INT meta=0 nullable=0 is_null=0 */	
  65. ###   @2='"t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
  66. ###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
  67. # at 631	
  68. #170503 23:15:29 server id 3306241  end_log_pos 662 CRC32 0x0cd1a6ae      Xid = 756	
  69. COMMIT/*!*/;	
  70. ......
2.2.2.2.多表导入

  • 清理掉test表，并刷新一下binlog

  1. admin@localhost : xiaoboluo 11:32:19> truncate test;	
  2. Query OK, 0 rows affected (0.01 sec)	
  3. admin@localhost : xiaoboluo 11:35:09> flush logs;	
  4. Query OK, 0 rows affected (0.01 sec)

  • 使用mysqlimport导入多表

  1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# time mysqlimport -uadmin -ppassword -h10.10.30.241 --replace xiaoboluo /data/backup/*.txt	
  2. mysqlimport: [Warning] Using a password on the command line interface can be insecure.	
  3. xiaoboluo.test2: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0	
  4. xiaoboluo.test3: Records: 6  Deleted: 0  Skipped: 0  Warnings: 0	
  5. xiaoboluo.test4: Records: 6  Deleted: 0  Skipped: 0  Warnings: 0	
  6. xiaoboluo.test: Records: 11  Deleted: 0  Skipped: 0  Warnings: 0	
  7. real    0m0.014s	
  8. user    0m0.002s	
  9. sys    0m0.002s	
  10. # 多表导入时可以使用参数--use-threads指定多个线程，明显比单线程导入速度要快	
  11. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# time mysqlimport -uadmin -ppassword -h10.10.30.241 --replace --use-threads=8 xiaoboluo /data/backup/*.txt	
  12. mysqlimport: [Warning] Using a password on the command line interface can be insecure.	
  13. xiaoboluo.test3: Records: 6  Deleted: 0  Skipped: 0  Warnings: 0	
  14. xiaoboluo.test2: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0	
  15. xiaoboluo.test: Records: 11  Deleted: 0  Skipped: 0  Warnings: 0	
  16. xiaoboluo.test4: Records: 6  Deleted: 0  Skipped: 0  Warnings: 0	
  17. real    0m0.007s	
  18. user    0m0.006s	
  19. sys    0m0.002s

  • 校验数据

  1. admin@localhost : xiaoboluo 11:35:15> select * from test;	
  2. +----+------+-------+	
  3. | id | test | test2 |	
  4. +----+------+-------+	
  5. |  2 | 1    | 2     |	
  6. |  4 | 2    | NULL  |	
  7. |  6 | null | NULL  |	
  8. |  8 | 4    | NULL  |	
  9. | 10 |         | NULL  |	
  10. | 12 | \\t  | NULL  |	
  11. | 14 | t    | NULL  |	
  12. | 16 | \t   | NULL  |	
  13. | 18 | t    | NULL  |	
  14. | 20 | NULL | NULL  |	
  15. | 22 | "t   | NULL  |	
  16. +----+------+-------+	
  17. 11 rows in set (0.00 sec)	
  18. admin@localhost : xiaoboluo 11:40:31> select * from test2;	
  19. +----+------+-------+---------------------+	
  20. | id | test | test2 | dt                  |	
  21. +----+------+-------+---------------------+	
  22. |  2 | 1    | 2     | 2017-05-02 18:47:03 |	
  23. |  4 | 2    | NULL  | 2017-05-02 18:47:03 |	
  24. |  6 | null | NULL  | 2017-05-02 18:47:03 |	
  25. |  8 | 4    | NULL  | 2017-05-02 18:47:03 |	
  26. | 10 |         | NULL  | 2017-05-02 18:47:03 |	
  27. | 12 | \\t  | NULL  | 2017-05-02 18:47:03 |	
  28. | 14 | t    | NULL  | 2017-05-02 18:47:03 |	
  29. | 16 | \t   | NULL  | 2017-05-02 18:47:03 |	
  30. | 18 | t    | NULL  | 2017-05-02 18:47:03 |	
  31. | 20 | NULL | NULL  | 2017-05-02 18:47:03 |	
  32. +----+------+-------+---------------------+	
  33. 10 rows in set (0.00 sec)	
  34. admin@localhost : xiaoboluo 11:40:33> select * from test3;	
  35. +----+------------------------------------------+--------+-------+	
  36. | id | test                                     | test2  | test3 |	
  37. +----+------------------------------------------+--------+-------+	
  38. |  2 | a string                                 | 100.20 | null  |	
  39. |  4 | a string containing a , comma            | 102.20 | NULL  |	
  40. |  6 | a string containing a " quote            | 102.20 | NULL  |	
  41. |  8 | a string containing a ", quote and comma | 102.20 | NULL  |	
  42. | 10 | \t                                       | 102.20 | NULL  |	
  43. | 14 | \t                                       | 102.20 | NULL  |	
  44. +----+------------------------------------------+--------+-------+	
  45. 6 rows in set (0.00 sec)	
  46. admin@localhost : xiaoboluo 11:40:34> select * from test4;	
  47. +----+------------------------------------------+--------+-------+---------------------+	
  48. | id | test                                     | test2  | test3 | test4               |	
  49. +----+------------------------------------------+--------+-------+---------------------+	
  50. |  2 | a string                                 | 100.20 | null  | 2017-05-03 18:41:02 |	
  51. |  4 | a string containing a , comma            | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  52. |  6 | a string containing a " quote            | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  53. |  8 | a string containing a ", quote and comma | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  54. | 10 | \t                                       | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  55. | 14 | \t                                       | 102.20 | NULL  | 2017-05-03 18:41:02 |	
  56. +----+------------------------------------------+--------+-------+---------------------+	
  57. 6 rows in set (0.00 sec)

解析binlog查看（由于内容较多，这里就不贴出来了，binlog解析的多表导入操作在binlog中记录的就是一个表一个load data语句）

3、总结

关于使用local子句与不使用local子句的时候的差异

  • 如果load data语句使用了local子句，则客户端使用TCP远程连接mysql server时，没有file权限仍然能够导入文本文件，这个时候是非常危险的，因为local子句的内部原理是从客户端的主机读取文本文件并传送到server端的/tmp目录并保存为一个临时文件，再执行load data语句的。另外，要使用local子句，还需要看server端启动是否关闭了local_infile选项（如果不指定该选项，则服务端默认为ON），mysql client连接时是否关闭了local_infile选项（如果不指定该选项，则客户端默认为ON），local_infile在server或client端任意一端关闭都不能使用local子句，会报错误：ERROR 1148 (42000): The used command is not allowed with this MySQL version

  • 如果load data语句不使用local子句，则这个时候用户必须要有file权限才能够执行导入文本文件（并且只能够导入server端的本地文本文件），如果没有file权限，可能报没有file权限的错误，也可能报错：ERROR 1045 (28000): Access denied for user 'test'@'%' (using password: YES)

  • 如果不想这么麻烦（因为要限制客户端使用local子句在没有file权限的时候使用load data语句，需要在server端使用local_infile=OFF来关闭，不使用local子句时，如果用户没有file权限，那很显然不能够使用load data语句，但是如果还想限制由具有file权限的用户怎么办？），可以使用参数secure_file_priv=null，设置为null时，全面禁止使用load data语句（不管使用local子句还是不使用都不允许执行load data语句）

强调一点：在mysql的主备复制架构中，load data语句被认为是不安全的，要使得load data语句安全地进行复制，在binlog_format=mixed格式下会转为row格式记录，在binlog_format=statement时执行 load data语句不会发出警告，而是内部通过一些列的流程来处理。具体是如何处理的呢，请看下回分解《MySQL LOAD DATA 语句的一些主从一致性测试》

因文章篇幅较长，公众号无法全部摘选，若想了解全文，可以添加助教获取完整文章电子版。

扫码添加助教

640?wx_fmt=png

END

640?wx_fmt=png

640?wx_fmt=png

640?wx_fmt=gif

640?wx_fmt=gif

扫码加入MySQL技术Q群

（群号：529671799）

   

640?wx_fmt=jpeg

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号