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)