RODBC连接MySQL使用总结-细节梳理

2018-11-18更新

ODBC即开放数据库连接，open database connectivity，提供一组对数据库访问的标准API

安装MySQL-odbc数据源，32位可直接安装(没有依赖项)，64位需要先安装vc_redist.x64文件。安装文件列表如下：

mysql-connector-odbc-5.2.7-win32

mysql-connector-odbc-8.0.13-winx64.msi

vc_redist.x64.exe

设置odbc connection character set utf-8，这样读MySQL数据库中的中文数据时，显示正常

library("RODBC")
#DBMSencoding-命名DBMS返回的编码字符串(默认值是R语言环境编码),返回handle
con=odbcConnect("mysqlodbc64",uid="root",pwd="**********",DBMSencoding="utf8",case="nochange")
odbcGetInfo(con)	#ODBC连接信息(客户端+服务器的细节信息)


#将SQL查询提交ODBC数据库,返回检索结果
#query-任意有效的SQL语句
sqlQuery(con,query,errors=TRUE,...,rows_at_time)


#写入表
sqlSave(channel, dat, tablename = NULL, append = FALSE, rownames = TRUE, colnames = FALSE, verbose = FALSE, safer = TRUE, addPK = FALSE, typeInfo, varTypes, fast = TRUE, test = FALSE, nastring = NULL)
#更新已存在行的表(用的比较少,主要通过索引列-匹配确定要更新的行)
sqlUpdate(channel, dat, tablename = NULL, index = NULL, verbose = FALSE, test = FALSE, nastring = NULL, fast = TRUE)
#dat-a data frame
 tablename-character,数据库表名如果缺失则使用dat名称
 index-character,要使用的索引列名称
 append-数据是否应追加到现有表
 verbose-是否输出log信息(可判断error位置)
 rownames-逻辑值(是否将R中数据框的行名称保存为数据库中表的第一列)或字符(一个给出所需列名的字符串,即命名第一列)
 colnames-逻辑值,是否将列名保存为表的第一行
 addPK-逻辑值,是否将rownames指定为主键(若存在)
#组合索引列
sqlUpdate(con, writedata, tablename ="表名",index=c("ORG_NEW_ID","YEAR_MONTH"))


#sqlTables(),在ODBC连接上列出表
sqlTables(con)
sqlFetch(con,sqtable="表名",colnames=FALSE,rownames=FALSE)


sqlClear(channel, sqtable, errors = TRUE)	#删除表所有行
sqlDrop(channel, sqtable, errors = TRUE)	#删除表


#关闭连接
odbcClose(con)

当使用RODBC的sqlSave将data.frame插入MySQL数据库中出现

Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test,  : 
missing columns in 'data'
设置sqlSave的fast=FALSE