如何查询R中sqlSave创建的数据库表的列数据类型

问题根因 #

查询无结果的核心原因通常为以下三类：

• sqlSave 创建表时会自动适配目标数据库的大小写规则，多数场景下会将未加引号的表名/列名转换为全小写或全大写，你查询时写的 'XYZ' 和实际库中的表名不匹配
• 表默认创建在数据库的默认Schema下（例如SQL Server的 dbo、PostgreSQL的 public），查询时未指定Schema过滤条件
• 部分数据库的 INFORMATION_SCHEMA 视图需要当前账号有对应表的元数据查看权限，权限不足也会返回空结果

方法1：直接在R端获取列类型（最便捷） #

无需编写SQL，直接通过RODBC包的sqlColumns函数查询sqlSave创建的表结构即可，代码如下：

# 直接查询XYZ表的所有列元数据，包含数据类型
col_meta <- sqlColumns(dbConnect, "XYZ")
# 筛选a列的类型
col_meta[col_meta$COLUMN_NAME == "a", "TYPE_NAME"]

该函数会自动适配数据库的大小写、Schema规则，返回结果和实际表结构完全一致。

方法2：修正SQL查询语句 #

首先查询全表确认实际表名和所属Schema：

-- 先查当前账号可见的所有表，确认XYZ的实际名称和所属Schema
SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE UPPER(TABLE_NAME) = 'XYZ'

拿到实际的TABLE_SCHEMA和TABLE_NAME后再查询列类型，例如查到表实际名为xyz、所属Schema为dbo，就用以下语句：

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'dbo' 
  AND TABLE_NAME = 'xyz' 
  AND UPPER(COLUMN_NAME) = 'A'

如果是MySQL数据库，还需要补充库名过滤条件：

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = '你的实际数据库名'
  AND TABLE_NAME = 'xyz' 
  AND COLUMN_NAME = 'a'

额外优化建议 #

后续使用sqlSave时建议明确指定表名字符串、配置列类型映射避免隐式转换：

# 表名用字符串包裹避免R变量解析问题，指定varType手动定义列类型
sqlSave(dbConnect, dataKR[,c("a","b","c")], 
        tablename = "XYZ", 
        safer = FALSE,
        varType = c(a = "INT", b = "VARCHAR(255)", c = "DECIMAL(10,2)"))

这样创建的表名、列类型完全可控，不会出现隐式转换导致的元数据查询问题。

内容的提问来源于stack exchange，提问作者user16656588