尝试将R与MySQL配合使用:此MySQL版本不允许使用used命令 [英] Trying to use R with MySQL: The used command is not allowed with this MySQL version

问题描述

我正在尝试将数据集导入MySQL中的表中.我正在使用MySQL 8.0.11和R Studio的1.1.453版本.我正在使用RMySQL软件包.

I am trying import a data set into a table in MySQL. I am using MySQL 8.0.11 and version 1.1.453 of R Studio. I am using the RMySQL package.

有问题的行是:

dbWriteTable(ajppDb, value = EvanData, row.names = FALSE, name = "summaryfile2015", append = TRUE ) 

然后我得到以下答复:

.local(conn，statement，...)中的错误: 无法运行语句:此MySQL版本不允许使用命令

Error in .local(conn, statement, ...) : could not run statement: The used command is not allowed with this MySQL version

此行在MySQL 5.7版中对我有效，但在较新版本中无效，因此我不确定发生了什么更改或我做错了什么.有人知道我如何使用R导入数据吗?

This line worked for me in version 5.7 of MySQL but not for the newer version so I'm not sure what has changed or what I am doing wrong. Does anyone know how I can import my data using R?

推荐答案

以下步骤应修复R中的dbWritetable()错误:

The following steps should fix the dbWritetable() error in R:

1. 通过键入"MySQL -u user -p*"登录MySQL终端(如果设置了密码，则输入用户密码).

1. Login MySQL terminal by typing "MySQL -u user -p*" (followed by user password if you set one).

在MySQL终端命令中键入"SET GLOBAL local_infile = true;".

Type "SET GLOBAL local_infile = true;" in the MySQL terminal command.

最后，在终端中键入"SHOW GLOBAL VARIABLES LIKE 'local_infile';"，并在命令行输出中检查ON状态.

Lastly, type "SHOW GLOBAL VARIABLES LIKE 'local_infile';" into the terminal and check the command line output for the ON status.

我不确定为什么数据库功能从MySQL 5.6失败到8.0，但是，"local_infile"使用户能够从本地源访问数据加载---此解决方案应适用于所有数据库干扰堆栈( R，Python等)！

I'm not sure why the database function fails from MySQL 5.6 to 8.0, however, "local_infile" enables user access to data loads from local sources--- this solution should work for all database interference stacks (R, Python, etc)!

这篇关于尝试将R与MySQL配合使用:此MySQL版本不允许使用used命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！