使用R包RJDBC重命名MySQL select语句中的列

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

我正在使用RJDBC软件包连接到Windows 7机器上的R中的MySQL（Maria DB）数据库，我正在尝试像

select a as b
from table

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

但该列将始终在数据框中继续命名为“a”。

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).

这通常适用于RODBC和RMySQL，但不适用于RJDBC。不幸的是，我必须使用RJDBC，因为这是唯一一个对中文，希伯来语等字母编码没有问题的软件包（集合名称等似乎不适用于RODBC和RMySQL）。

Has anybody experienced this problem?

有人遇到过这个问题吗？

4 个解决方案

#1

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.

我遇到了同样令人沮丧的问题。有时AS关键字会产生预期效果，但有时则不会。我无法确定使其正常工作的条件。

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:

简答:(感谢Simon Urbanek（RJDBC的软件包维护者），Yev和Sebastien！请参阅完整答案。）您可以尝试的一件事是在连接字符串中使用？useOldAliasMetadataBehavior = true打开JDBC连接。例：

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)

This ended up working for me! See more details, including caveats, in the Long Answer.

这最终为我工作！在长答案中查看更多详细信息，包括警告。

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.

长答案：我尝试了各种各样的东西，包括创建视图，更改查询，使用JOIN语句，不使用JOIN语句，使用ORDER BY和GROUP BY语句等。我从来没有弄清楚为什么我的一些查询能够重命名列和其他人不是。

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

我联系了包维护者（Simon Urbanek。）以下是他所说的：

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.

在绝大多数情况下，这是JBDC驱动程序中的一个问题，因为除了调用驱动程序之外，RJDBC实际上没有什么可以做的。

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.

然后他建议我确保我有最新的MySQL JDBC驱动程序。我确实有最新的版本。然而，它让我想到“也许它是JDBC驱动程序的错误。”所以，我搜索谷歌：mysql jdbc驱动程序bug别名。

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.

此查询的最高结果是bugs.mysql.com上的条目。 Yev，使用MySQL 5.1.22，说当他从驱动程序版本5.0.4升级到5.1.5时，他的列别名停止工作。被问到这是不是一个bug。

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.

塞巴斯蒂安回答说：“不，这不是一个错误！这是所有后续版本驱动程序中记录的行为变化。”并建议使用？useOldAliasMetadataBehavior = true，引用JDBC驱动程序的文档。

Caveat Lector: The documentation for the JDBC driver states that

Caveat Lector：JDBC驱动程序的文档说明了这一点

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

useColumnNamesInFindColumn优先于useOldAliasMetadataBehavior，除非您需要它提供的与ResultSetMetadata相关的特定行为。

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?

我没有时间充分研究这意味着什么。换句话说，我不知道使用useOldAliasMetadataBehavior = true的所有后果是什么。使用风险由您自己承担。别人有更多信息吗？

#2

I don't know RJDBC, but in some cases when it is necessary to give permanent aliases to columns without renaming them, you can use VIEWs

我不知道RJDBC，但在某些情况下，当需要为列提供永久别名而不重命名它们时，您可以使用VIEW

CREATE OR REPLACE VIEW v_table AS
SELECT a AS b
FROM table

... and then ...

... 接着 ...

SELECT b FROM v_table

#3

There is a separate function in the ResultSetMetaData interface for retrieving the column label vs the column name:

ResultSetMetaData接口中有一个单独的函数用于检索列标签与列名称：

String getColumnLabel(int column) throws SQLException;

Gets the designated column's suggested title for use in printouts and displays. The suggested title is usually specified by the SQL AS clause. If a SQL AS is not specified, the value returned fromgetColumnLabel will be the same as the value returned by the getColumnName method.

获取指定列的建议标题，以便在打印输出和显示中使用。建议的标题通常由SQL AS子句指定。如果未指定SQL AS，则从getColumnLabel返回的值将与getColumnName方法返回的值相同。

Using getColumnLabel should resolve this issue (if not, check that your JDBC driver is following this spec).

使用getColumnLabel应解决此问题（如果没有，请检查您的JDBC驱动程序是否遵循此规范）。

e.g.

例如

ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while(rs.next()) {
    for (int i = 1; i < columnCount + 1; i++) {
        String label = rsmd.getColumnLabel(i);
        System.out.println(rs.getString(label));
    }   
}

#4

This is the work around we use for R and SAP HANA via RJDBC:

这是我们通过RJDBC用于R和SAP HANA的工作：

names(result)[1]<-"b"

名称（结果）[1] < - “b” 的

It's not the nicest work around, but since Aaron's solution does work for us, we went with this "solution".

这不是最好的工作，但由于Aaron的解决方案对我们有用，我们采用了这种“解决方案”。

#1