blocks|key|2830548|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2830549|文件：Z:\Path\To\MySQL\Files\my_file.csv|offset|length|style|CODE|2830550|1,"a+string"
2,"a+string+containing+a+,+comma"
3,"a+string+containing+a+\"+quote"
4,"a+string+containing+a+\",+quote+and+comma"|code-block|syntax|javascript|2830551|MySQL命令行：|2830552|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+(c)+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';
%2B------------------%2B-------------------------%2B
%7C+Variable_name++++%7C+Value+++++++++++++++++++%7C
%2B------------------%2B-------------------------%2B
%7C+secure_file_priv+%7C+Z:\Path\To\MySQL\Files\+%7C
%2B------------------%2B-------------------------%2B
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`;
%2B-------------------------%2B
%7C+@@GLOBAL.`local_infile`+%7C
%2B-------------------------%2B
%7C+++++++++++++++++++++++0+%7C
%2B-------------------------%2B
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`;
%2B-------------------------%2B
%7C+@@GLOBAL.`local_infile`+%7C
%2B-------------------------%2B
%7C+++++++++++++++++++++++1+%7C
%2B-------------------------%2B
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+(c)+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`;
%2B-------------------------%2B
%7C+@@GLOBAL.`local_infile`+%7C
%2B-------------------------%2B
%7C+++++++++++++++++++++++1+%7C
%2B-------------------------%2B
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`;
%2B------%2B------------------------------------------%2B
%7C+col0+%7C+col1+++++++++++++++++++++++++++++++++++++%7C
%2B------%2B------------------------------------------%2B
%7C++++1+%7C+a+string+++++++++++++++++++++++++++++++++%7C
%7C++++2+%7C+a+string+containing+a+,+comma++++++++++++%7C
%7C++++3+%7C+a+string+containing+a+"+quote++++++++++++%7C
%7C++++4+%7C+a+string+containing+a+",+quote+and+comma+%7C
%2B------%2B------------------------------------------%2B
4+rows+in+set+(0.00+sec)|2830553|entityMap^0|0|3|Y|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|W|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|Y|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Try:

File: <code>Z:\Path\To\MySQL\Files\my_file.csv</code>:

<pre class="lang-bsh prettyprint-override"><code>1,"a string"
2,"a string containing a , comma"
3,"a string containing a \" quote"
4,"a string containing a \", quote and comma"
</code></pre>

MySQL Command-Line:

<pre class="lang-sql prettyprint-override"><code>Z:\&gt;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 (c) 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&gt; DROP TABLE IF EXISTS `_`.`my_table`;
Query OK, 0 rows affected, 1 warning (0.03 sec)

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

mysql&gt; 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&gt; LOAD DATA LOCAL INFILE 'Z:\\Path\\To\\MySQL\\Files\\my_file.csv'
 -&gt; INTO TABLE `_`.`my_table`
 -&gt; FIELDS TERMINATED BY ',' ENCLOSED BY '"'
 -&gt; LINES TERMINATED BY '\r\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

mysql&gt; SELECT @@GLOBAL.`local_infile`;
+-------------------------+
| @@GLOBAL.`local_infile` |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)

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

mysql&gt; SELECT @@GLOBAL.`local_infile`;
+-------------------------+
| @@GLOBAL.`local_infile` |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

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

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

mysql&gt; exit
Bye

Z:\&gt;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 (c) 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&gt; SELECT @@GLOBAL.`local_infile`;
+-------------------------+
| @@GLOBAL.`local_infile` |
+-------------------------+
| 1 |
+-------------------------+
1 row in set (0.00 sec)

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

mysql&gt; SELECT `col0`, `col1`
 -&gt; 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)
</code></pre>

blocks|key|2830599|text|截至2019年1月，这是MySQL工作台8.0.12的一个未解决的错误。请参阅：https://bugs.mysql.com/bug.php?id=91891|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2830600|但是，如果"local_infile“全局变量设置为1，则通过命令行界面执行相同的命令应该是可行的：|2830601|2830602|+SET+@@GLOBAL.local_infile=1;|blockquote|2830603|2830604|entityMap|0|LINK|mutability|MUTABLE|url|https://bugs.mysql.com/bug.php?id=91891^0|14|13|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]|$1|G|3|H|5|I|7|Y|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

As of January 2019, this is an unresolved bug with MySQL Workbench 8.0.12. See: <a href="https://bugs.mysql.com/bug.php?id=91891" rel="nofollow noreferrer">https://bugs.mysql.com/bug.php?id=91891</a> .
 
Executing the same command through the CLI should work, however, provided the "local_infile" global variable has been set to 1:

<blockquote>
 SET @@GLOBAL.local_infile=1;
</blockquote>

blocks|key|94196|text|在搜索之后我也遇到了同样的问题，我通过从查询中删除local关键字来解决这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|94197|SET+GLOBAL+local_infile+=+1;
SHOW+VARIABLES+LIKE+'local_infile';|code-block|syntax|javascript|94198|查询应返回:+ON|94199|查询应如下所示：|94200|load+data+infile+'C:\\CA_DRU_proj_2010-2060.csv'
into+table+pop_proj
fields+terminated+by+','
enclosed+by+'"'
lines+terminated+by+'\n'
ignore+1+lines;|94201|您应该注意到，我从此处删除了关键字，现在它可以正常工作。|offset|length|style|BOLD|94202|entityMap^0|0|0|0|0|0|E|E|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|Z|8|@$O|10|P|11|Q|R]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

I have the same problem after a search, I solve the problem by removing the local keyword from the query.

<pre><code>SET GLOBAL local_infile = 1;
SHOW VARIABLES LIKE 'local_infile';
</code></pre>

The query should return: ON

The query should look like this:

<pre><code>load data infile 'C:\\CA_DRU_proj_2010-2060.csv'
into table pop_proj
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
ignore 1 lines;
</code></pre>

You should be noticed that I removed the local keyword from here and now it works fine.

blocks|key|91816|text|我一直在处理一个类似的问题，在Windows上使用MySQL+8.0.19。通过不使用本地关键字并使用Windows服务管理器(在浏览器任务栏上键入服务)，右键单击MySQL80+->selecting属性，然后在登录选项卡上，将登录身份:值更改为本地系统帐户，我能够解决此问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|91817|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I've been dealing with a similar problem, using MySQL 8.0.19 on Windows.
I was able to resolve by NOT using the LOCAL keyword AND 
using the Windows Services manager (type Services on the explorer taskbar), right clicking on MySQL80 ->selecting Properties, then on the Log On tab, changing the Log On As: value to Local System Account.

blocks|key|2830741|text|Windows10用户:值得重申的是，确认Windows使用哪个帐户来运行MySQL服务是多么重要。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2830742|这个问题在Windows10的MySQL+8.0.21中仍然存在。使用LOAD+DATA+INFILE+(和而不是+LOAD+DATA+INFILE+LOCAL，这需要额外的文件传输，即使您在同一桌面上运行客户端和服务器)，我开始收到以下错误：|offset|length|style|CODE|BOLD|2830743|not+found+(OS+errno+13+-+Permission+denied)|2830744|出于某种原因，MySQL决定选择“网络服务”，可能是因为我将安装从默认的C:驱动器移到了D:驱动器上更快的物理驱动器上。|2830745|NO:|2830746|📷|atomic|2830747|​|2830748|最初的安装已经设置了Local，我选择了"Local+System+Account“的单选按钮，这就为我解决了这个问题。|2830749|是：|2830750|2830751|2830752|mySQL+documentation鼓励在Windows上将mySQL作为Windows服务运行，但它没有给出指定哪个帐户作为该服务的登录帐户的建议。|2830753|和其他许多人一样，我浏览了所有选项(local_infile等)。但直到我见到the+answer+from+CesarinG，我才检查服务登录，现在我欠他一杯啤酒或咖啡。|2830754|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/1bd39e592d33a413304179c725787d11.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/9d8d50452f23d3afdb7f3a576116e190.png|2|LINK|MUTABLE|url|https://dev.mysql.com/doc/refman/8.0/en/windows-start-service.html|3|https://stackoverflow.com/a/60406945/5839677^0|0|Z|G|1M|M|1I|3|0|0|17|0|0|0|3|0|0|1|0|0|0|0|0|2|0|0|1|1|0|0|0|J|2|0|13|O|3|0^^$0|@$1|2|3|4|5|6|7|1L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1M|8|@$D|1N|E|1O|F|G]|$D|1P|E|1Q|F|G]|$D|1R|E|1S|F|H]]|9|@]|A|$]]|$1|I|3|J|5|6|7|1T|8|@$D|1U|E|1V|F|G]]|9|@]|A|$]]|$1|K|3|L|5|6|7|1W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1X|8|@$D|1Y|E|1Z|F|H]]|9|@]|A|$]]|$1|O|3|P|5|Q|7|20|8|@]|9|@$D|21|E|22|1|23]]|A|$]]|$1|R|3|S|5|6|7|24|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|25|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|26|8|@$D|27|E|28|F|H]]|9|@]|A|$]]|$1|X|3|P|5|Q|7|29|8|@]|9|@$D|2A|E|2B|1|2C]]|A|$]]|$1|Y|3|S|5|6|7|2D|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|2E|8|@]|9|@$D|2F|E|2G|1|2H]]|A|$]]|$1|11|3|12|5|6|7|2I|8|@]|9|@$D|2J|E|2K|1|2L]]|A|$]]|$1|13|3|-4|5|6|7|2M|8|@]|9|@]|A|$]]]|14|$15|$5|16|17|18|A|$19|1A|1B|-4]]|1C|$5|16|17|18|A|$19|1D|1B|-4]]|1E|$5|1F|17|1G|A|$1H|1I]]|1J|$5|1F|17|1G|A|$1H|1K]]]]

Windows 10 users: it is worth repeating how important it is to confirm which account Windows is using to run the MySQL service.
This problem continued in MySQL 8.0.21 in Windows 10. With <code>LOAD DATA INFILE</code> (and not <code>LOAD DATA INFILE LOCAL</code>, which requires an additional file transfer even if you're running client and server on the same desktop) I started receiving this error:
<code>not found (OS errno 13 - Permission denied)</code>
For some reason, MySQL decided to select &quot;Network Services&quot;, possibly because I moved the installation from the default C: drive to a faster physical drive on D:
NO: 
<a href="https://i.stack.imgur.com/VPPYy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VPPYy.png" alt="No. Wrong. Gives not found (OS errno 13 - Permission denied error " /></a>
The original installation had set up Local I selected the radio button for &quot;Local System Account&quot; and that solved it for me.
YES: 
<a href="https://i.stack.imgur.com/1RvHk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1RvHk.png" alt="Original and correct configuration" /></a>
The <a href="https://dev.mysql.com/doc/refman/8.0/en/windows-start-service.html" rel="nofollow noreferrer">mySQL documentation</a> encourages running mySQL on Windows as a Windows Service, but it gives no recommendation as to which account to specify as the log on for the service.
As many others have, I went through all the options (local_infile etc.) but did not check the service logon until I saw <a href="https://stackoverflow.com/a/60406945/5839677">the answer from CesarinG</a>, to whom I now owe a beer or coffee.

I am using a windows with MySQL 8.0 installed.

I have checked the solutions already posted on stackoverflow but these had no immediate results.

I have set the global variable with <code>SET GLOBAL local_infile = 1;</code>
This options seems to be enable now but MySQL keeps throwing the following error:

Error Code: 

<blockquote>
 <ol start="1148">
 <li>The used command is not allowed with this MySQL version</li>
 </ol>
</blockquote>

Can anyone help me out to remedy this behavior ?

MySQL LOAD DATA LOCAL INFILE on WINDOWS not WORKING

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

我使用的是安装了MySQL 8.0的windows。我已经检查了stackoverflow上已经发布的解决方案，但这些解决方案没有立即得到结果。我已经用SET GLOBAL local_infile = 1;设置了全局变量，这个选项现在似乎是启用的，但是MySQL一直抛出以下错误：错误代码：此MySQL version...

问WINDOWS上MySQL LOAD DATA LOCAL INFILE无效
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问WINDOWS上MySQL LOAD DATA LOCAL INFILE无效EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问WINDOWS上MySQL LOAD DATA LOCAL INFILE无效
EN