背景描述：
使用MySQL 8.0 Command Line Cline 命令行将CSV文件导入table中，win10系统。
方式：
1、查看local_infile权限
mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile' ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)
mysql> SET GLOBAL local_infile = true;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile'
-> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
2、找到mysql中欲导入数据的table
mysql> SHOW database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> SHOW databases;
+--------------------+
| Database |
+--------------------+
| data |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> use data
Database changed
mysql> SHOW tables;
+----------------+
| Tables_in_data |
+----------------+
| company |
| dataanalyst |
| orderinfo |
| userinfo |
+----------------+
4 rows in set (0.00 sec)
3、找到secure_file_priv文件夹（因为这事安全文件夹，所以只有这个文件夹中的文件才能上传）
mysql> SHOW variables like '%secure%' ;
+--------------------------+------------------------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------------------------+
| require_secure_transport | OFF |
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+--------------------------+------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
4、将csv文件放入secure_file_priv 所指向的文件夹
（以我为例，需要将csv文件放入C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\中）
注：数据要符合要求才可以
5、执行导入命令
mysql> load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/user_info_utf.csv'
-> into table userinfo
-> fields terminated by ',' ;
Query OK, 32079 rows affected (0.26 sec)
Records: 32079 Deleted: 0 Skipped: 0 Warnings: 0
6、成功。
微信公众号：
公众号ID： AppleGossip
简书：https://www.jianshu.com/u/505e89457641
网友评论