Py学习  »  DATABASE

MySQL LOAD DATA 语句详解

老叶茶馆 • 4 年前 • 246 次点击  

导读

作者:沃趣-罗小波

沃趣科技高级数据库技术专家,主要负责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. REPLACEIGNORE

 

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. FIELDSLINES注意

 

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导出数据为文本的语法如下:

  1. 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命令,语法如下:

  1. 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 语句的一些主从一致性测试》



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

扫码添加助教


END









扫码加入MySQL技术Q群

(群号:529671799)

   



Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/47317
 
246 次点击