官方文档
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_infile 参数，否则会报错。
导入非本地电脑文件：用户一定要 FILE 权限，secure_file_priv参数值如果不为空，则文件一定要在这个目录中，如果为空，则该文件只需服务器可读。
我本地使用mysql8.0。23客户端，在一切条件符合的情况下，LOAD DATA数据报错.mysql> load data local infile '/Users/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[mysqld]local_infile=1然后再在使用mysql命令行时，指定 loose-local-infile = 1 连接数据库
mysql --local-infile=1 -uroot -p123456 -P3306 -h1.1.1.1REPLACE：覆盖写。
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_database系统变量值字符集导入内容。
提示：
这里我踩了一个坑，我本地使用CRT连接数据库，不知为何客户端字符集是latain1了，文本中包含中文，如果以默认方式导入会出现乱码。一般情况下，不需要指定CHARACTER SET
示例：
root# cat 1.csv1,chai2,测试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/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: 0mysql> 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 | 测试 |默认是\t ，也就是跳格，但大多时候生成的文本文件都是’,'逗号，所以在导入数据时，需要显式指定。
示例：
root# cat 1.csv1,chai2,测试mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',';Query OK, 2 rows affected (0.20 sec)Records: 2 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from t1;+----+--------+| id | name |+----+--------+| 1 | chai || 2 | 测试 |+----+--------+2 rows in set (0.09 sec)示例：
root #cat 1.csv1,chai2,测试3,""chayicha"4,"chayige"如果以之前的参数导入，则结果如下,里边的引号也会写入进去。
mysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',';Query OK, 4 rows affected (0.16 sec)Records: 4 Deleted: 0 Skipped: 0 Warnings: 0mysql> 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/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: 0mysql> select * from t1;+----+-----------+| id | name |+----+-----------+| 1 | chai || 2 | 测试 || 3 | "chayicha || 4 | chayige |+----+-----------+示例：
root#cat 1.csv1,chai2,测试3,"\tchayicha"4,wo\\a\\b\\c\tchayigemysql> load data local infile '/Users/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: 0mysql> select * from t1;+----+------------------+| id | name |+----+------------------+| 1 | chai || 2 | 测试 || 3 | chayicha || 4 | wo\a\b\c chayige |+----+------------------+如示例，只有以 cha 开头的记录正确写入到了数据库，这个参数应该不常用
示例：
root#cat 1.csvcha1,chai2,测试cha3,"yicha"4,chayigemysql> load data local infile '/Users/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: 2mysql> select * from t1;+----+-------+| id | name |+----+-------+| 1 | chai || 3 | yicha || 0 | NULL |+----+-------+一般情况下遇到回行即分行 (\r\n)
示例：
演示一次遇到句号(。)即换行符root#cat 2.csva,chai。2,测试。3,chayicha。mysql> load data local infile '/Users/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: 4mysql> select * from t1;+----+----------+| id | name |+----+----------+| 0 | chai || 2 | 测试 || 3 | chayicha || 0 | NULL |+----+----------+4 rows in set (0.07 sec)如果文本中有字段名，可以跳过第一行.
示例：
root# cat 1.csv1,chai2,测试3,"yicha"4,chayigemysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE1 LINES;Query OK, 3 rows affected (0.13 sec)Records: 3 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from t1;+----+---------+| id | name |+----+---------+| 2 | 测试 || 3 | yicha || 4 | chayige |+----+---------+示例：
root# cat 1.csv1,chai2,测试3,"yicha"4,chayigemysql> load data local infile '/Users/1.csv' into table ceshi.t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE1 LINES(id,name);Query OK, 3 rows affected (0.16 sec)Records: 3 Deleted: 0 Skipped: 0 Warnings: 0mysql> select * from t1;+----+---------+------+| id | name | age |+----+---------+------+| 2 | 测试 | NULL || 3 | yicha | NULL || 4 | chayige | NULL |+----+---------+------+3 rows in set (0.09 sec)示例：
root# cat 1.csv1,chai2,测试3,"yicha"4,chayige#在写入数据时，更新age字段列
mysql> load data local infile '/Users/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: 0mysql> 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/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: 0mysql> select * from t1;+----+----------+------+| id | name | age |+----+----------+------+| 2 | 测试1 | NULL || 3 | yicha1 | NULL || 4 | chayige1 | NULL |+----+----------+------+3 rows in set (0.07 sec)最后贴一个成功案例
load data local infile 'D:\\34178517.csv' into table `finance_new` fields terminated by ',' enclosed by '"' lines terminated by '\n'ignore 1 lines(@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字段不赋值，因为表头已建好来源地址：https://blog.csdn.net/u011783999/article/details/130140884
