|
1.创建数据文件:/angel/student.txt
[root@CentOS8 ~]# cat /angel/student.txt
1,zhangsan,1997-01-01
2,lisi,2008-09-10
2.加载数据文件到表中:
mysql> LOAD DATA LOCAL INFILE '/angel/student.txt' INTO TABLE student fields terminated by ',' ;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
3.查询结果:
mysql> select * from student;
+------+----------+------------+
| id | name | birth |
+------+----------+------------+
| 1 | zhangsan | 1997-01-01 |
| 2 | lisi | 2008-09-10 |
+------+----------+------------+
2 rows in set (0.00 sec)
mysql>
注意:
1.
报错信息:ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
解决方案:提示很明显,客户端服务端都要开启加载本地数据的功能。
找到/etc/my.cnf 文件进行编辑
[mysqld]
loose-local-infile=1
[client]
loose-local-infile=1
更改配置文件后重新加载或者重启:[root@CentOS8 init.d]# ./mysql.server restart
2.
您可以根据需要在LOAD DATA语句中显式指定列值分隔符和行尾标记,但是默认值为制表符和换行符。
例如在WINDOWS平台上:
LOAD DATA LOCAL INFILE '/angel/student.txt' INTO TABLE student LINES TERMINATED BY '\r\n';
MacOS 需要用:
LOAD DATA LOCAL INFILE '/angel/student.txt' INTO TABLE student LINES TERMINATED BY '\r';
|
|