WINDOWS上的MySQL LOAD DATA LOCAL INFILE无法正常工作?

尝试:

文件 Z:\Path\To\MySQL\Files\my_file.csv::

1,“a string”
2,“a string containing a , comma”
3,“a string containing a " quote”
4,"a string containing a “, quote and comma”
MySQL命令行:

Z:>mysql
Enter password: **************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.11 MySQL Community Server - GPL

Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> DROP TABLE IF EXISTS _.my_table;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> CREATE TABLE IF NOT EXISTS _.my_table (
-> col0 INT NOT NULL PRIMARY KEY,
-> col1 VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.45 sec)

mysql> SHOW VARIABLES WHERE variable_name = ‘secure_file_priv’;
±-----------------±------------------------+
| Variable_name | Value |
±-----------------±------------------------+
| secure_file_priv | Z:\Path\To\MySQL\Files\ |
±-----------------±------------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE ‘Z:\Path\To\MySQL\Files\my_file.csv’
-> INTO TABLE _.my_table
-> FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’
-> LINES TERMINATED BY ‘\r\n’;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

mysql> SELECT @@GLOBAL.local_infile;
±------------------------+
| @@GLOBAL.local_infile |
±------------------------+
| 0 |
±------------------------+
1 row in set (0.00 sec)

mysql> SET @@GLOBAL.local_infile := 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.local_infile;
±------------------------+
| @@GLOBAL.local_infile |
±------------------------+
| 1 |
±------------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE ‘Z:\Path\To\MySQL\Files\my_file.csv’
-> INTO TABLE _.my_table
-> FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’
-> LINES TERMINATED BY ‘\r\n’;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

mysql> SELECT col0, col1
-> FROM _.my_table;
Empty set (0.00 sec)

mysql> exit
Bye

Z:>mysql --local-infile=1
Enter password: **************
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.11 MySQL Community Server - GPL

Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SELECT @@GLOBAL.local_infile;
±------------------------+
| @@GLOBAL.local_infile |
±------------------------+
| 1 |
±------------------------+
1 row in set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE ‘Z:\Path\To\MySQL\Files\my_file.csv’
-> INTO TABLE _.my_table
-> FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘"’
-> LINES TERMINATED BY ‘\r\n’;
Query OK, 4 rows affected (0.19 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> SELECT col0, col1
-> FROM _.my_table;
±-----±-----------------------------------------+
| col0 | col1 |
±-----±-----------------------------------------+
| 1 | a string |
| 2 | a string containing a , comma |
| 3 | a string containing a " quote |
| 4 | a string containing a ", quote and comma |
±-----±-----------------------------------------+
4 rows in set (0.00 sec)

注意字符集编码问题

转载自 https://cloud.tencent.com/developer/ask/172334/answer/274611

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值