MySQL LOAD DATA 语句详解

最新推荐文章于 2024-03-18 11:53:28 发布
最新推荐文章于 2024-03-18 11:53:28 发布
阅读量6k 收藏 3
点赞数 1
原文链接：https://ke.qq.com/course/410349?tuin=3a95bf5d
版权

导读

作者：沃趣-罗小波

沃趣科技高级数据库技术专家，主要负责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' 记录结束符，即换行符

示例

$ mkdir /data/backup/	
$ chown mysql.mysql /data/backup -R	
$ mysqldump -uadmin -ppassword -h 10.10.30.241 --single-transaction --master-data=2 --triggers --routines --events xiaoboluo -T /data/backup/	
mysqldump: [Warning] Using a password on the command line interface can be insecure.	
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.\	
 If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.	
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;	
SET @@SESSION.SQL_LOG_BIN= 0;	
--	
-- GTID state at the beginning of the backup	
--	
SET @@GLOBAL.GTID_PURGED='2016f827-2d98-11e7-bb1e-00163e407cfb:1-114';	
--	
-- Position to start replication or point-in-time recovery from	
--	
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=21737;  #使用-T选项时，--master-data=2打印的binlog pos信息会直接打印在标准输出上	
--	
-- Dumping events for database 'xiaoboluo'	
--	
--	
-- Dumping routines for database 'xiaoboluo'	
--	
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;	
$ ll /data/backup/  #可以看到mysqldump -T选项批量导出的表数据除了文本文件之外，还同时备份了表结构	
total 32	
-rw-r--r-- 1 root  root  1526 May  3 22:45 test2.sql	
-rw-rw-rw- 1 mysql mysql  286 May  3 22:45 test2.txt	
-rw-r--r-- 1 root  root  1549 May  3 22:45 test3.sql	
-rw-rw-rw- 1 mysql mysql  194 May  3 22:45 test3.txt	
-rw-r--r-- 1 root  root  1600 May  3 22:45 test4.sql	
-rw-rw-rw- 1 mysql mysql  314 May  3 22:45 test4.txt	
-rw-r--r-- 1 mysql mysql 1493 May  3 22:45 test.sql	
-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参数可以是如下选项

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

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

admin@localhost : (none) 11:08:58> use xiaoboluo	
Database changed	
admin@localhost : xiaoboluo 11:09:00> show tables;	
+---------------------+	
| Tables_in_xiaoboluo |	
+---------------------+	
| test                |	
| test2               |	
| test3               |	
| test4               |	
+---------------------+	
4 rows in set (0.00 sec)	
admin@localhost : xiaoboluo 11:09:01> select * from test;	
+----+------+-------+	
| id | test | test2 |	
+----+------+-------+	
|  2 | 1    | 2     |	
|  4 | 2    | NULL  |	
|  6 | null | NULL  |	
|  8 | 4    | NULL  |	
| 10 |         | NULL  |	
| 12 | \\t  | NULL  |	
| 14 | t    | NULL  |	
| 16 | \t   | NULL  |	
| 18 | t    | NULL  |	
| 20 | NULL | NULL  |	
| 22 | "t   | NULL  |	
+----+------+-------+	
11 rows in set (0.00 sec)	
admin@localhost : xiaoboluo 11:09:11> system cat /data/backup/test.txt;	
2    1   2	
4    2   \N	
6    null    \N	
8    4   \N	
10    \       \N	
12    \\\\t   \N	
14    t   \N	
16    \\t \N	
18    t   \N	
20    \N  \N	
22    "t  \N	
admin@localhost : xiaoboluo 11:12:08> select * from test2;	
+----+------+-------+---------------------+	
| id | test | test2 | dt                  |	
+----+------+-------+---------------------+	
|  2 | 1    | 2     | 2017-05-02 18:47:03 |	
|  4 | 2    | NULL  | 2017-05-02 18:47:03 |	
|  6 | null | NULL  | 2017-05-02 18:47:03 |	
|  8 | 4    | NULL  | 2017-05-02 18:47:03 |	
| 10 |         | NULL  | 2017-05-02 18:47:03 |	
| 12 | \\t  | NULL  | 2017-05-02 18:47:03 |	
| 14 | t    | NULL  | 2017-05-02 18:47:03 |	
| 16 | \t   | NULL  | 2017-05-02 18:47:03 |	
| 18 | t    | NULL  | 2017-05-02 18:47:03 |	
| 20 | NULL | NULL  | 2017-05-02 18:47:03 |	
+----+------+-------+---------------------+	
10 rows in set (0.00 sec)	
admin@localhost : xiaoboluo 11:12:15> system cat /data/backup/test2.txt;	
2    1   2   2017-05-02 18:47:03	
4    2   \N  2017-05-02 18:47:03	
6    null    \N  2017-05-02 18:47:03	
8    4   \N  2017-05-02 18:47:03	
10    \       \N  2017-05-02 18:47:03	
12    \\\\t   \N  2017-05-02 18:47:03	
14    t   \N  2017-05-02 18:47:03	
16    \\t \N  2017-05-02 18:47:03	
18    t   \N  2017-05-02 18:47:03	
20    \N  \N  2017-05-02 18:47:03	
admin@localhost : xiaoboluo 11:12:27> truncate test2;	
Query OK, 0 rows affected (0.00 sec)	
admin@localhost : xiaoboluo 11:12:32> select * from test3;	
+----+------------------------------------------+--------+-------+	
| id | test                                     | test2  | test3 |	
+----+------------------------------------------+--------+-------+	
|  2 | a string                                 | 100.20 | null  |	
|  4 | a string containing a , comma            | 102.20 | NULL  |	
|  6 | a string containing a " quote            | 102.20 | NULL  |	
|  8 | a string containing a ", quote and comma | 102.20 | NULL  |	
| 10 | \t                                       | 102.20 | NULL  |	
| 14 | \t                                       | 102.20 | NULL  |	
+----+------------------------------------------+--------+-------+	
6 rows in set (0.00 sec)	
admin@localhost : xiaoboluo 11:12:44> system cat /data/backup/test3.txt;	
2    a string    100.20  null	
4    a string containing a , comma   102.20  \N	
6    a string containing a " quote   102.20  \N	
8    a string containing a ", quote and comma    102.20  \N	
10    \\t 102.20  \N	
14    \\t 102.20  \N	
admin@localhost : xiaoboluo 11:12:59> truncate test3;	
Query OK, 0 rows affected (0.01 sec)	
admin@localhost : xiaoboluo 11:13:03> select * from test4;	
+----+------------------------------------------+--------+-------+---------------------+	
| id | test                                     | test2  | test3 | test4               |	
+----+------------------------------------------+--------+-------+---------------------+	
|  2 | a string                                 | 100.20 | null  | 2017-05-03 18:41:02 |	
|  4 | a string containing a , comma            | 102.20 | NULL  | 2017-05-03 18:41:02 |	
|  6 | a string containing a " quote            | 102.20 | NULL  | 2017-05-03 18:41:02 |	
|  8 | a string containing a ", quote and comma | 102.20 | NULL  | 2017-05-03 18:41:02 |	
| 10 | \t                                       | 102.20 | NULL  | 2017-05-03 18:41:02 |	
| 14 | \t                                       | 102.20 | NULL  | 2017-05-03 18:41:02 |	
+----+------------------------------------------+--------+-------+---------------------+	
6 rows in set (0.00 sec)	
admin@localhost : xiaoboluo 11:13:15> system cat /data/backup/test4.txt;	
2    a string    100.20  null    2017-05-03 18:41:02	
4    a string containing a , comma   102.20  \N  2017-05-03 18:41:02	
6    a string containing a " quote   102.20  \N  2017-05-03 18:41:02	
8    a string containing a ", quote and comma    102.20  \N  2017-05-03 18:41:02	
10    \\t 102.20  \N  2017-05-03 18:41:02	
14    \\t 102.20  \N  2017-05-03 18:41:02	
admin@localhost : xiaoboluo 11:13:24> truncate test4;	
Query OK, 0 rows affected (0.01 sec)	
admin@localhost : xiaoboluo 11:13:28> flush logs;	
Query OK, 0 rows affected (0.01 sec)
2.2.2.1. 单表导入

  • 使用mysqlimport命令导入单张表

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

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

$ mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000006	
.....	
BEGIN	
/*!*/;	
# at 344	
#170503 23:15:29 server id 3306241  end_log_pos 443 CRC32 0x4c1c8e8a      Rows_query	
# LOAD DATA   INFILE '/data/backup/test.txt' INTO TABLE `test` IGNORE 0 LINES  #mysqlimport内部调用的load data语句在这里	
# at 443	
#170503 23:15:29 server id 3306241  end_log_pos 501 CRC32 0x1ddc6d53      Table_map: `xiaoboluo`.`test` mapped to number 304	
# at 501	
#170503 23:15:29 server id 3306241  end_log_pos 631 CRC32 0xa8c4beab      Write_rows: table id 304 flags: STMT_END_F	
### INSERT INTO `xiaoboluo`.`test`  #由于binlog_format=row，所以写到binlog中时内部把load data语句转换为了row格式	
### SET	
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=10 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='\x09' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=12 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='\x5c\x5ct' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=14 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=16 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='\x5ct' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=18 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=20 /* INT meta=0 nullable=0 is_null=0 */	
###   @2=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
### INSERT INTO `xiaoboluo`.`test`	
### SET	
###   @1=22 /* INT meta=0 nullable=0 is_null=0 */	
###   @2='"t' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */	
###   @3=NULL /* VARSTRING(300) meta=300 nullable=1 is_null=1 */	
# at 631	
#170503 23:15:29 server id 3306241  end_log_pos 662 CRC32 0x0cd1a6ae      Xid = 756	
COMMIT/*!*/;	
......
2.2.2.2.多表导入

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

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

  • 使用mysqlimport导入多表

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

  • 校验数据

admin@localhost : xiaoboluo 11:35:15> select * from test;	
+----+------+-------+	
| id | test | test2 |	
+----+------+-------+	
|  2 | 1    | 2     |	
|  4 | 2    | NULL  |	
|  6 | null | NULL  |	
|  8 | 4    | NULL  |	
| 10 |         | NULL  |	
| 12 | \\t  | NULL  |	
| 14 | t    | NULL  |	
| 16 | \t   | NULL  |	
| 18 | t    | NULL  |	
| 20 | NULL | NULL  |	
| 22 | "t   | NULL  |	
+----+------+-------+	
11 rows in set (0.00 sec)	
admin@localhost : xiaoboluo 11:40:31> select * from test2;	
+----+------+-------+---------------------+	
| id | test | test2 | dt                  |	
+----+------+-------+---------------------+	
|  2 | 1    | 2     | 2017-05-02 18:47:03 |	
|  4 | 2    | NULL  | 2017-05-02 18:47:03 |	
|  6 | null | NULL  | 2017-05-02 18:47:03 |	
|  8 | 4    | NULL  | 2017-05-02 18:47:03 |	
| 10 |         | NULL  | 2017-05-02 18:47:03 |	
| 12 | \\t  | NULL  | 2017-05-02 18:47:03 |	
| 14 | t    | NULL  | 2017-05-02 18:47:03 |	
| 16 | \t   | NULL  | 2017-05-02 18:47:03 |	
| 18 | t    | NULL  | 2017-05-02 18:47:03 |	
| 20 | NULL | NULL  | 2017-05-02 18:47:03 |	
+----+------+-------+---------------------+	
10 rows in set (0.00 sec)	
admin@localhost : xiaoboluo 11:40:33> select * from test3;	
+----+------------------------------------------+--------+-------+	
| id | test                                     | test2  | test3 |	
+----+------------------------------------------+--------+-------+	
|  2 | a string                                 | 100.20 | null  |	
|  4 | a string containing a , comma            | 102.20 | NULL  |	
|  6 | a string containing a " quote            | 102.20 | NULL  |	
|  8 | a string containing a ", quote and comma | 102.20 | NULL  |	
| 10 | \t                                       | 102.20 | NULL  |	
| 14 | \t                                       | 102.20 | NULL  |	
+----+------------------------------------------+--------+-------+	
6 rows in set (0.00 sec)	
admin@localhost : xiaoboluo 11:40:34> select * from test4;	
+----+------------------------------------------+--------+-------+---------------------+	
| id | test                                     | test2  | test3 | test4               |	
+----+------------------------------------------+--------+-------+---------------------+	
|  2 | a string                                 | 100.20 | null  | 2017-05-03 18:41:02 |	
|  4 | a string containing a , comma            | 102.20 | NULL  | 2017-05-03 18:41:02 |	
|  6 | a string containing a " quote            | 102.20 | NULL  | 2017-05-03 18:41:02 |	
|  8 | a string containing a ", quote and comma | 102.20 | NULL  | 2017-05-03 18:41:02 |	
| 10 | \t                                       | 102.20 | NULL  | 2017-05-03 18:41:02 |	
| 14 | \t                                       | 102.20 | NULL  | 2017-05-03 18:41:02 |	
+----+------------------------------------------+--------+-------+---------------------+	
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

老叶茶馆_
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
mysql中的Load data的使用方法
12-15
测试把txt文件导入至mysql数据库中： table： txt文件：D:/data.txt （txt文件下载） txt中使用 ‘\N’ 描述null值。 导入数据： 代码如下: load data local infile ‘D:/data.txt’ into table pet lines terminated by ‘\r\n’ ignore 1 lines; 应用mysql版本： 您可能感兴趣的文章:Java利用MYSQL LOAD DATA LOCAL INFILE实现大批量导入数据到MySQLMySQL中由load data语
MySQL- 'load data infile'语句
weixin_33975951的博客
03-03 1207
基本语法： load data [low_priority] [local] infile 'file_name txt' [replace | ignore] into table tbl_name [fields [terminated by't'] [OPTIONALLY] enclosed by ''] [escaped by'\' ]] [lines...
MySQL的load data
最新发布
2401_83591620的博客
03-18 312
即使已经设置local_infile的值为1，进入MySQL前的权限不足也是无法执行load data的，会出现如下错误。有的参考是mysql --local-infile -u root -p，我试了不太行，不确定是否版本问题。fields terminated by后接的是字段值的分隔符。解决：退出数据库之后，加--local-infile权限再进入。by后接的是记录值（一个record）的分割符。
mysql load data
Pineapple
08-09 468
load data local infile 'E:/amounthis1.txt' ignore into table amount1 fields terminated by ',' enclosed by '\'' lines terminated by '\n' (cycleTime,areaCode,tileCode,A1,B1,A2,B2,A3,B3,A4,B4,sensitiveTo...
mysql之load data
37度2
10-12 3890
在使用LOAD DATA到MySQL的时候，有2种情况： （1）在远程客户端（需要添加选项：–local-infile=1）导入远程客户端文本到MySQL，需指定LOCAL（默认就是ignore）,加ignore选项会放弃数据，加replace选项会更新数据，都不会出现唯一性约束问题。 （2）在本地服务器导入本地服务器文本到MySQL，不指定LOACL，出现唯一性约束冲突，会失
mysql的load data_【MySQL】load data语句详解（一）
weixin_39884492的博客
01-18 5468
作者 罗小波沃趣科技高级MySQL数据库工程师load data语句详解背景数据库版本：MySQL 5.7.18服务器信息：本地到处导入在10.10.30.241上演示，local远程导入在10.10.30.250上演示数据库参数配置：*双一，secure_file_priv=''，log-bin，binlog_format=row，隔离级别RC，sql_mode='ONLY_FULL_GROU...
MySQL load语句详细介绍
09-10
主要介绍了MySQL load语句详细介绍,本文讲解了load的基本语法、文件的路径、配置选项、STARTING LINES选项、TERMINATED LINES选项等内容,需要的朋友可以参考下
MySQL中由load data语句引起死锁的解决案例
01-19
这是一个类似数据分析的项目，数据完全通过LOAD DATA语句导入一个InnoDB表中。为方便描述，表结构简化为如下： Create table tb(id int primary key auto_increment, c int not null) engine=innodb; 导入数据的...
mysql Load Data InFile 的用法
09-11
Load Data InFile是用于批量向数据表中导入记录。
mysql中Load Data记录换行问题的解决方法
01-21
问题是这样的：表persons有两个字段: id和name文本文档persons.txt中内容（其中每行字段之间用tab分割）：1 Bush2 Carter3 Bush在mysql命令行下使用 load data local infile “persons.txt” into table persons ...
mysql 的load data infile
09-11
前些日子在开发一个舆情监测系统，需要在一个操作过程中往数据表里插入大量的数据，为了改变以往生硬地逐条数据插入的笨办法，也为了提高执行效率，决定用load data infile来执行数据插入。
mysql load data infile 的用法(40w数据 用了3-5秒导进mysql)
09-10
测试数据的时候，生成txt文件应该快点，再用这种方式导入到mysql 速度上快点。40w数据 用了3-5秒导进mysql，牛逼毁了
MySql日期查询语句详解
09-10
在mysql中对时间日期操作的函数有很多，有时我们就希望直接通过sql查询出指定日期的数据
mysql遇到load data导入文件数据出现1290错误的解决方案
12-16
　执行 “load data infile 文本数据路径 into table tab_load_data” 弹出错误。 #load data (载入数据)语法,要载入数据如下： 1 张三 男 江西 1 2 李四 男 四川 2 3 王五 男 上海 1 4 赵六 女 湖北 3 5 ...
mysql LOAD语句批量录入数据
12-15
语法：LOAD DATA [LOCAL] INFILE ‘file_name.txt’ [REPLACE | IGNORE] INTO TABLE tbl_name LOAD DATA INFILE语句从一个文本文件中以很高的速度读入一个表中。如果指定LOCAL关键词，从客户主机读文件。如果LOCAL没...
MySQL数据库从loaddata引发的死锁说起
01-31
这是一个类似数据分析的项目，数据完全通过LOADDATA语句导入一个InnoDB表中。为方便描述，表结构简化为如下：Createtabletb(idintprimarykey auto_increment,cintnotnull)engine=innodb;导入数据的语句对应为产生...
MySQL Load data
hashcon
05-18 4610
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED
mysql load data
l460602540的专栏
09-15 942
mysql提供了包文件中的数据插入到表中； LOADDATA [LOW_PRIORITY] [LOCAL] INFILE 'file_name.txt' [REPLACE | IGNORE] INTO TABLE tbl_name [FIELDS [TERMINATED BY '\t'] [OPTIONALLY] ENCLOSED BY ''] [ESCAPED BY '\\' ]]
LOAD DATA INFILE句法
也想快乐的生活
05-15 1525
LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE file_name.txt [REPLACE | IGNORE]    INTO TABLE tbl_name    [FIELDS        [TERMINATED BY /t]        [OPTIONALLY] ENCLOSED BY ]        [ESCAPED BY // ]] 
mysql load data infile 语句如何编写
06-12
`LOAD DATA INFILE` 语句可以用来从文件中加载数据到 MySQL 数据库中。以下是一个基本的 `LOAD DATA INFILE` 语句的示例： ``` LOAD DATA INFILE 'file_path/file_name.txt' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; ``` 在这个示例中，`file_path/file_name.txt` 是要导入的文件路径，`table_name` 是要导入到的表名，`FIELDS TERMINATED BY ','` 表示字段之间的分隔符是逗号，`LINES TERMINATED BY '\n'` 表示行之间的分隔符是换行符。 如果你的文件中包含列名行，你可以使用 `IGNORE 1 LINES` 来忽略第一行，例如： ``` LOAD DATA INFILE 'file_path/file_name.txt' INTO TABLE table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES; ``` 这将忽略文件中的第一行，然后将其余的行插入到 `table_name` 表中。 注意，使用 `LOAD DATA INFILE` 导入数据需要确保文件路径正确和文件权限设置正确，否则可能会出现导入失败的情况。同时，也需要根据导入数据的具体情况来设置适当的分隔符和行分隔符。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值