MySQL 导入数据文件

1. 介绍

使用如下指令导入本地文件到数据库表：

-- 中文路径使用 \\ 分隔
load data local infile '文件路径' into table `表名` fields terminated by ',' lines terminated by '\n';

2. 示例

建表:

create table test_load_data_file (
    id int,
    name varchar(100),
    gender char,
    age int
);

数据文件( D:\\1.txt )，内容如下：

1,张三,男,18
2,小红,女,17
3,小明,男,20

命令行:

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p --local-infile
Enter password: ******


mysql> show tables;
+---------------------+
| Tables_in_my_db_xxx |
+---------------------+
| test_load_data_file |
+---------------------+


mysql> load data local infile 'D:\\1.txt' into table `test_load_data_file` fields terminated by ',' lines terminated by '\n';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select * from test_load_data_file;
+------+------+--------+------+
| id   | name | gender | age  |
+------+------+--------+------+
|    1 | 张三 | 男     |   18 |
|    2 | 小红 | 女     |   17 |
|    3 | 小明 | 男     |   20 |
+------+------+--------+------+
3 rows in set (0.00 sec)

3. 参考

• 命令窗口load data local infile向MySQL数据库导入数据报错Error 3948和Error 2068，已解决 (opens new window)