MySQL导入数据

MySQL导入数据

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

mysql > LOAD DATA [LOCAL] INFILE 'filename' INTO TABLE tablename [option]

option 可以是以下选项：

• FIELDS TERMINATED BY ‘string’（字段分隔符，默认为制表符’\t’）；
• FIELDS [OPTIONALLY] ENCLOSED BY ‘char’（字段引用符，如果加OPTIONALLY 选项则只用
  在char、varchar 和text 等字符型字段上。默认不使用引用符）；
• FIELDS ESCAPED BY ‘char’（转义字符，默认为’’）；
• LINES STARTING BY ‘string’（每行前都加此字符串，默认’’）；
• LINES TERMINATED BY ‘string’（行结束符，默认为’\n’）；
• IGNORE number LINES（忽略输入文件中的前n 行数据）；
• (col_name_or_user_var,…) （按照列出的字段顺序和字段数量加载数据）；
• SET col_name = expr,… 将列做一定的数值转换后再加载。

其中char表示此符号只能是单个字符，string表示可以是字符串。FILELD和LINES和前面SELECT …INTO OUTFILE…的含义完全相同，不同的是多了几个不同的选项，下面的例子将文件/tmp/emp.txt中的数据加载到表emp中：

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by
'"' ;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from emp;
+------+------+---------+
| id | name | content |
+------+------+---------+
| 2 | z1 | aa |
| 3 | z1 | aa |
| 4 | z1 | aa |
| 1 | z1 | aa |
+------+------+---------+
4 rows in set (0.00 sec)

如果不希望加载文件中的前两行，可以如下操作：

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by '"' ignore 2 lines;

Query OK, 2 rows affected (0.04 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from emp;
+------+------+---------+
| id | name | content |
+------+------+---------+
| 4 | z1 | aa |
| 1 | z1 | aa |
+------+------+---------+
2 rows in set (0.00 sec)

此时数据只加载了两行，对比一下数据文件，可以发现的确只加载了后两行：

mysql> system /tmp/emp.txt
sh: /tmp/emp.txt: Permission denied

mysql> system more /tmp/emp.txt
2,"z1","aa"
3,"z1","aa"
4,"z1","aa"
1,"z1","aa"

如果发现文件中的列顺序和表中的列顺序不符，或者只想加载部分列，可以在命令行中加上
列的顺序，如下例所示：

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by
'"' ignore 2 lines (id,content,name);
Query OK, 2 rows affected (0.05 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from emp;
+------+------+---------+
| id | name | content |
+------+------+---------+
| 4 | aa | z1 |
| 1 | aa | z1 |
+------+------+---------+
2 rows in set (0.00 sec)

可以发现，文件中第二列的内容放到了content 里面，第三列的内容放到了name 里面。
如果只想加载第一列，字段的列表里面可以只加第一列的名称：

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by
'"' ignore 2 lines (id);
Query OK, 2 rows affected, 2 warnings (0.04 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 2

mysql> select * from emp;
+------+------+---------+
| id | name | content |
+------+------+---------+
| 4 | NULL | NULL |
| 1 | NULL | NULL |
+------+------+---------+
2 rows in set (0.00 sec)

如果希望将id 列的内容+10 后再加载到表中，可以如下操作：

mysql> load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by
'"' set id=id+10;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from emp;
+------+------+---------+
| id | name | content |
+------+------+---------+
| 12 | z1 | aa |
| 13 | z1 | aa |
| 14 | z1 | aa |
| 11 | z1 | aa |
+------+------+---------+
4 rows in set (0.00 sec)