使用R包RJDBC重命名MySQL select语句中的列 [英] Renaming columns in a MySQL select statement with R package RJDBC

问题描述

我正在使用RJDBC程序包连接到Windows 7计算机上R中的MySQL数据库(Maria DB)，并且正在尝试类似的语句

I am using the RJDBC package to connect to a MySQL (Maria DB) database in R on a Windows 7 machine and I am trying a statement like

select a as b
from table

，但该列在数据框中将始终继续命名为"a".

but the column will always continue to be named "a" in the data frame.

这通常可用于RODBC和RMySQL，但不适用于RJDBC.不幸的是，我必须使用RJDBC，因为这是唯一不会对中文，希伯来语等字母(设置名称等似乎与RODBC和RMySQL一起使用)的编码没有问题的软件包.

This works normally with RODBC and RMySQL but doesn't work with RJDBC. Unfortunately, I have to use RJDBC as this is the only package that has no problem with the encoding of chinese, hebrew and so on letters (set names and so on don't seem to work with RODBC and RMySQL).

有人遇到过这个问题吗?

Has anybody experienced this problem?

推荐答案

我遇到了同样令人沮丧的问题.有时AS关键字会达到预期的效果，而其他时候则没有.我无法确定使其正常运行的条件.

I have run into the same frustrating issue. Sometimes the AS keyword would have its intended effect, but other times it wouldn't. I was unable to identify the conditions to make it work correctly.

简短的回答:(感谢Simon Urbanek(RJDBC的软件包维护者)，Yev和Sebastien！请参见长期回答..)您可以尝试做的一件事是在连接字符串中使用?useOldAliasMetadataBehavior=true打开JDBC连接.示例:

Short Answer: (Thanks to Simon Urbanek (package maintainer for RJDBC), Yev, and Sebastien! See the Long Answer.) One thing that you may try is to open your JDBC connection using ?useOldAliasMetadataBehavior=true in your connection string. Example:

drv <- JDBC("com.mysql.jdbc.Driver", "C:/JDBC/mysql-connector-java-5.1.18-bin.jar", identifier.quote="`")
conn <- dbConnect(drv, "jdbc:mysql://server/schema?useOldAliasMetadataBehavior=true", "username", "password")
query <- "SELECT `a` AS `b` FROM table"
result <- dbGetQuery(conn, query)
dbDisconnect(conn)

这最终为我工作！在 Long Answers

长答案:我尝试了各种方法，包括制作视图，更改查询，使用JOIN语句，不使用JOIN语句，使用ORDER BY和GROUP BY语句等.弄清楚为什么我的某些查询能够重命名列，而其他查询却不能重命名.

Long Answer: I tried all sorts of stuff, including making views, changing queries, using JOIN statements, NOT using JOIN statements, using ORDER BY and GROUP BY statements, etc. I was never able to figure out why some of my queries were able to rename columns and others weren't.

我联系了包装维护人员(Simon Urbanek.)他说的是:

I contacted the package maintainer (Simon Urbanek.) Here is what he said:

在大多数情况下，这是JBDC驱动程序中的一个问题，因为除调用驱动程序外，RJDBC确实无能为力.

In the vast majority of cases this is an issue in the JBDC driver, because there is really not much RJDBC can do other than to call the driver.

然后，他建议我确保我具有用于MySQL的最新JDBC驱动程序.我确实有最新版本.但是，这让我开始思考也许这是JDBC驱动程序的错误".因此，我在Google上搜索了mysql jdbc driver bug alias.

He then recommended that I make sure I had the most recent JDBC driver for MySQL. I did have the most recent version. However, it got me thinking "maybe it IS a bug with the JDBC driver." So, I searched Google for: mysql jdbc driver bug alias.

此查询的最高结果是在bugs.mysql.com上的条目. Yev使用MySQL 5.1.22，说他从驱动程序版本5.0.4升级到5.1.5时，他的列别名停止工作.问它是否是错误.

The top result for this query was an entry at bugs.mysql.com. Yev, using MySQL 5.1.22, says that when he upgraded from driver version 5.0.4 to 5.1.5, his column aliases stopped working. Asked if it was a bug.

Sebastien回答:不，这不是错误！这是该驱动程序的所有后续版本中行为的书面记录."并建议使用?useOldAliasMetadataBehavior=true，并引用有关JDBC驱动程序的文档.

Sebastien replied, "No, it's not a bug! It's a documented change of behavior in all subsequent versions of the driver." and suggested using ?useOldAliasMetadataBehavior=true, citing documentation for the JDBC driver.

Caveat Lector::JDBC驱动程序的文档指出

Caveat Lector: The documentation for the JDBC driver states that

useColumnNamesInFindColumn优于useOldAliasMetadataBehavior，除非您需要它针对ResultSetMetadata提供的特定行为.

useColumnNamesInFindColumn is preferred over useOldAliasMetadataBehavior unless you need the specific behavior that it provides with respect to ResultSetMetadata.

我还没有时间去充分研究这意味着什么.换句话说，我不知道使用useOldAliasMetadataBehavior=true的所有后果.使用风险自负.其他人还有更多信息吗?

I haven't had the time to fully research what this means. In other words, I don't know what all of the ramifications are of using useOldAliasMetadataBehavior=true are. Use at your own risk. Does someone else have more information?

这篇关于使用R包RJDBC重命名MySQL select语句中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！