MySQL lOAD DATA详解

2021/9/26 19:10:51

本文主要是介绍MySQL lOAD DATA详解,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

load data
参考文档:
https://dev.mysql.com/doc/refman/8.0/en/load-data.html

 

LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT}
[, col_name={expr | DEFAULT}] ...]


这一堆参数还不少。

 

LOCAL:是否导入本地电脑文本文件,
导入本地电脑文件:一定要启用 local_infile 参数,否则会报错。
导入非本地电脑文件:用户一定要 FILE 权限,secure_file_priv参数值如果不为空,则文件一定要在这个目录中,如果为空,则该文件只需服务器可读。

小插曲,我本地使用mysql8.0。23客户端,在一切条件符合的情况下,LOAD DATA数据报错.
mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 ;
ERROR 2068 (HY000): LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
排错一圈,才发现踩了mysql8的一个bug,
https://bugs.mysql.com/bug.php?id=91872
解决方法:
在client端配置文件中加入
[client]
loose-local-infile = 1
或者在使用mysql命令行时,指定 loose-local-infile = 1 连接数据库
mysql --local-infile=1 -uroot -p123456 -P3306 -h1.1.1.1

 

 

[REPLACE | IGNORE]:如遇到唯一冲突重复处理机制
REPLACE:覆盖写。
IGNORE:忽略。
如果没有指定REPLACE, IGNORE或者LOCAL,当发生错误时,会报错,并且文本余下部分不会被执行。

示例:
mysql> load data infile '/root/1.csv' into table ceshi.t1 ;
ERROR 1265 (01000): Data truncated for column 'id' at row 2

提示:如果要在加载数据中忽略外键约束,需要在Load data 数据之前执行SET foreign_key_checks = 0

 


如果没有指定 FIELDS 或 LINES 子句,则默认值如下
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''
提示:在 WINDOWS 系统中,想要正确的读文件需要配置 LINES TERMINATED BY '\r\n',因为WINDOWS系统通常使用两个字符做为终止符。

CHARACTER SET charset_name
设置导入内容的字符集,默认采用character_set_database系统变量值字符集导入内容。
提示:
这里我踩了一个坑,我本地使用CRT连接数据库,不知为何客户端字符集是latain1了,文本中包含中文,如果以默认方式导入会出现乱码。一般情况下,不需要指定CHARACTER SET

示例:
root# cat 1.csv
1,chai
2,测试

mysql> show variables like '%character%'
-> ;
+--------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql57/share/charsets/ |
+--------------------------+------------------------------------+
8 rows in set (0.07 sec)

mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\';
Query OK, 2 rows affected (0.15 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
| 1 | chai |
| 2 | ?? |
+----+------+
2 rows in set (0.06 sec)

set names utf8;
再查询就正常了
mysql> select * from t1;
+----+----------------+
| id | name |
+----+----------------+
| 1 | chai |
| 2 | 测试 |

 


FIELDS TERMINATED BY:指定两列之间分隔符,默认是\t ,也就是跳格,但大多时候生成的文本文件都是','逗号,所以在导入数据时,需要显式指定。

示例:
root# cat 1.csv
1,chai
2,测试

mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',';
Query OK, 2 rows affected (0.20 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+--------+
| id | name |
+----+--------+
| 1 | chai |
| 2 | 测试 |
+----+--------+
2 rows in set (0.09 sec)

 

 


ENCLOSED BY:去掉字符串中包裹的符号

示例:

root #cat 1.csv
1,chai
2,测试
3,""chayicha"
4,"chayige"

如果以之前的参数导入,则结果如下,里边的引号也会写入进去。

mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',';
Query OK, 4 rows affected (0.16 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+-------------+
| id | name |
+----+-------------+
| 1 | chai |
| 2 | 测试 |
| 3 | ""chayicha" |
| 4 | "chayige" |
+----+-------------+
4 rows in set (0.05 sec)


##加入 ENCLOSED BY '"' 参数后,在导入时字符左右两则的双引号被删掉了。

mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Query OK, 4 rows affected (0.13 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+-----------+
| id | name |
+----+-----------+
| 1 | chai |
| 2 | 测试 |
| 3 | "chayicha |
| 4 | chayige |
+----+-----------+

 


ESCAPED BY:设置转义字符,默认为\ 。

示例:
root#cat 1.csv
1,chai
2,测试
3,"\tchayicha"
4,wo\\a\\b\\c\tchayige

mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\';
Query OK, 4 rows affected (0.13 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+------------------+
| id | name |
+----+------------------+
| 1 | chai |
| 2 | 测试 |
| 3 | chayicha |
| 4 | wo\a\b\c chayige |
+----+------------------+

 

 


LINES STARTING BY:忽略一个公共前缀,如示例,只有以 cha 开头的记录正确写入到了数据库,这个参数应该不常用

示例:
root#cat 1.csv
cha1,chai
2,测试
cha3,"yicha"
4,chayige

mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES STARTING BY 'cha';
Query OK, 3 rows affected, 2 warnings (0.14 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 2

mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 1 | chai |
| 3 | yicha |
| 0 | NULL |
+----+-------+

 

 

LINES TERMINATED BY 'string':分行符,一般情况下遇到回行即分行 (\r\n)

示例:
演示一次遇到句号(。)即换行符
root#cat 2.csv
a,chai。2,测试。3,chayicha。

mysql> load data local infile '/Users/zhigangchai/2.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '。';
Query OK, 4 rows affected, 4 warnings (0.15 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 4

mysql> select * from t1;
+----+----------+
| id | name |
+----+----------+
| 0 | chai |
| 2 | 测试 |
| 3 | chayicha |
| 0 | NULL |
+----+----------+
4 rows in set (0.07 sec)

 


IGNORE number {LINES | ROWS}:跳过开始的多少行才进行导入,如果文本中有字段名,可以跳过第一行.

示例:
root# cat 1.csv
1,chai
2,测试
3,"yicha"
4,chayige

mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE
1 LINES;
Query OK, 3 rows affected (0.13 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+---------+
| id | name |
+----+---------+
| 2 | 测试 |
| 3 | yicha |
| 4 | chayige |
+----+---------+

 

 

[(col_name_or_user_var [, col_name_or_user_var] ...)]:手动指定要插入的列

示例:
root# cat 1.csv
1,chai
2,测试
3,"yicha"
4,chayige

mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE
1 LINES(id,name);
Query OK, 3 rows affected (0.16 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 2 | 测试 | NULL |
| 3 | yicha | NULL |
| 4 | chayige | NULL |
+----+---------+------+
3 rows in set (0.09 sec)

 


[SET col_name={expr | DEFAULT} [, col_name={expr | DEFAULT}] ...]:在加载数据时做一些计算或更新一些其它字段值。

示例:
root# cat 1.csv
1,chai
2,测试
3,"yicha"
4,chayige

#在写入数据时,更新age字段列
mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE
-> 1 LINES(id,name) set age=10;
Query OK, 3 rows affected (0.13 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 2 | 测试 | 10 |
| 3 | yicha | 10 |
| 4 | chayige | 10 |
+----+---------+------+
3 rows in set (0.07 sec)

#在写入数据时对数据做二次逻辑处理
mysql> load data local infile '/Users/zhigangchai/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE
-> 1 LINES(id,@name) set name=concat(@name,1);
Query OK, 3 rows affected (0.14 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | 测试1 | NULL |
| 3 | yicha1 | NULL |
| 4 | chayige1 | NULL |
+----+----------+------+
3 rows in set (0.07 sec)

 



这篇关于MySQL lOAD DATA详解的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程