MySQL 快速导入数据指令load Data 详解

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

样例

LOADDATA[LOW_PRIORITY | CONCURRENT][LOCAL]INFILE'file_name'[REPLACE|IGNORE]INTOTABLE tbl_name
[PARTITION(partition_name [, partition_name]...)][CHARACTERSET charset_name][{FIELDS|COLUMNS}
[TERMINATEDBY'string'][[OPTIONALLY]ENCLOSEDBY'char'][ESCAPEDBY'char']][LINES[STARTINGBY'string'][TERMINATEDBY'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客户端，在一切条件符合的情况下，LOADDATA数据报错.
mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 ;
ERROR 2068(HY000): LOADDATALOCALINFILEfile request rejected due to restrictions on access.

排错一圈，才发现踩了mysql8的一个bug，
https://bugs.mysql.com/bug.php?id=91872

解决方法：在client端配置文件中加入

[client]
loose-local-infile=1[mysqld]
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>loaddatainfile'/root/1.csv'intotable ceshi.t1 ;
ERROR 1265(01000): Data truncated forcolumn'id' at row2

提示：如果要在加载数据中忽略外键约束，需要在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/|+--------------------------+------------------------------------+8rowsinset(0.07 sec)

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 FIELDSTERMINATEDBY','ENCLOSEDBY''ESCAPEDBY'\\';
Query OK,2rows affected (0.15 sec)
Records: 2 Deleted: 0 Skipped: 0Warnings: 0

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

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

FIELDS TERMINATED BY：指定两列之间分隔符，

默认是\t ，也就是跳格，但大多时候生成的文本文件都是’,'逗号，所以在导入数据时，需要显式指定。

示例：

root# cat 1.csv1,chai
2,测试

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 FIELDSTERMINATEDBY',';
Query OK,2rows affected (0.20 sec)
Records: 2 Deleted: 0 Skipped: 0Warnings: 0

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

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

示例：

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

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

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 FIELDSTERMINATEDBY',';
Query OK,4rows affected (0.16 sec)
Records: 4 Deleted: 0 Skipped: 0Warnings: 0

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

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

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 
FIELDSTERMINATEDBY','ENCLOSEDBY'"';
Query OK,4rows affected (0.13 sec)
Records: 4 Deleted: 0 Skipped: 0Warnings: 0

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

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

示例：

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

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 
FIELDSTERMINATEDBY','ENCLOSEDBY'"'ESCAPEDBY'\\';
Query OK,4rows affected (0.13 sec)
Records: 4 Deleted: 0 Skipped: 0Warnings: 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>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 
FIELDSTERMINATEDBY','ENCLOSEDBY'"'ESCAPEDBY'\\'LINESSTARTINGBY'cha';
Query OK,3rows affected,2warnings(0.14 sec)
Records: 3 Deleted: 0 Skipped: 0Warnings: 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>loaddatalocalinfile'/Users/2.csv'intotable ceshi.t1 
FIELDSTERMINATEDBY','ENCLOSEDBY'"'ESCAPEDBY'\\'LINESTERMINATEDBY'。';
Query OK,4rows affected,4warnings(0.15 sec)
Records: 4 Deleted: 0 Skipped: 0Warnings: 4

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

IGNORE number {LINES | ROWS}:跳过开始的多少行才进行导入，

如果文本中有字段名，可以跳过第一行.

示例：

root# cat 1.csv1,chai
2,测试
3,"yicha"4,chayige

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 FIELDSTERMINATEDBY','ENCLOSEDBY'"'ESCAPEDBY'\\'IGNORE1LINES;
Query OK,3rows affected (0.13 sec)
Records: 3 Deleted: 0 Skipped: 0Warnings: 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.csv1,chai
2,测试
3,"yicha"4,chayige

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 FIELDSTERMINATEDBY','ENCLOSEDBY'"'ESCAPEDBY'\\'IGNORE1LINES(id,name);
Query OK,3rows affected (0.16 sec)
Records: 3 Deleted: 0 Skipped: 0Warnings: 0

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

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

示例：

root# cat 1.csv1,chai
2,测试
3,"yicha"4,chayige

#在写入数据时，更新age字段列

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 FIELDSTERMINATEDBY','ENCLOSEDBY'"'ESCAPEDBY'\\'IGNORE->1LINES(id,name)set age=10;
Query OK,3rows affected (0.13 sec)
Records: 3 Deleted: 0 Skipped: 0Warnings: 0

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

#在写入数据时对数据做二次逻辑处理

mysql>loaddatalocalinfile'/Users/1.csv'intotable ceshi.t1 FIELDSTERMINATEDBY','ENCLOSEDBY'"'ESCAPEDBY'\\'IGNORE->1LINES(id,@name)set name=concat(@name,1);
Query OK,3rows affected (0.14 sec)
Records: 3 Deleted: 0 Skipped: 0Warnings: 0

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

最后贴一个成功案例

loaddatalocalinfile'D:\\34178517.csv'intotable`finance_new`fieldsterminatedby','enclosedby'"'linesterminatedby'\n'ignore1lines(@id,`uuid`,`buyer_type`,`buyer_account`,`vendor_code`,`vendor_name`,`vendor_short_name`,`policy_no`,`order_id`,`biz_id`,`parent_biz_id`,`biz_line`,`biz_type`);# 忽略第一行和id字段不赋值，因为表头已建好

标签： mysql 数据库

本文转载自: https://blog.csdn.net/u011783999/article/details/130140884
版权归原作者 huihttp 所有，如有侵权，请联系我们删除。

MySQL 快速导入数据指令load Data 详解

样例

LOCAL:是否导入本地电脑文本文件，

[REPLACE | IGNORE]：如遇到唯一冲突重复处理机制

CHARACTER SET charset_name

FIELDS TERMINATED BY：指定两列之间分隔符，

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

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

LINES STARTING BY：忽略一个公共前缀,

LINES 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}] …]:在加载数据时做一些计算或更新一些其它字段值。

发表评论

“MySQL 快速导入数据指令load Data 详解”的评论:

关于作者

overfit同步小助手

相关阅读

文章导航