官方文档

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/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.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/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/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/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/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/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/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/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/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/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/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/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)
  

  最后贴一个成功案例

  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字段不赋值，因为表头已建好