从r插入mysql

我说得对。我不得不将“”改为“null”,将na改为null,然后在insert中使用ifelse语句。这样地:

ch <- DBI::dbConnect(MySQL())
dbSendQuery(ch, 'set character set "utf8"')
dbSendQuery(ch, 'SET NAMES utf8')
test[test == ""] <- "NULL"
test[is.na(test)] <- "NULL"
for (i in 1:nrow(test)) {
  query <- paste0("INSERT INTO trade_data VALUES('0', '", test[i, 1], "', ",
                  ifelse(test[i, 2] == "NULL", test[i, 2], paste0("'", test[i, 2], "'")), ", ", 
                  ifelse(test[i, 3] == "NULL", test[i, 3], paste0("'", test[i, 3], "'")), ", ",
                  # test[i, 3],", ", 
                  test[i, 4], ", ",
                  test[i, 5], ", ",
                  test[i, 6], ", ", test[i, 7] , ", ",
                  test[i, 8], ", ", test[i, 9] , ", ",
                  test[i, 10], ", ", test[i, 11] , ", '",
                  test[i, 12], "')")
  dbSendQuery(ch, query)
}
DBI::dbDisconnect(ch)

如果您愿意将na改为0,那么最好的选择是执行以下操作。

test[is.na(test)] <- 0

这将替换data.frame中的所有NAS test 0。您也可以这样做,如果您愿意的话,也可以改为字符串“null”。

test[is.na(test)] <- 'NULL'

如果只希望替换列,可以执行以下操作:

test$col3[is.na(test$col3)] <- 0

考虑为运行sql的任何应用程序层(如r)设置参数化的编程行业标准。使用这种方法,您可以避免任何字符串插值或混乱的引号附件的需要。r的dbi标准有几种方法,其中之一是 sqlInterpolate :

# PREPARED STATEMENT (NO DATA) QMARKS REQUIRED BUT NAMES CAN CHANGE
sql <- "INSERT INTO trade_data (Col1, Col2, Col3, col4) 
        VALUES (?param1, ?param2, ?param3, ?param4)"

ch <- DBI::dbConnect(MySQL())
dbSendQuery(ch, 'set character set "utf8"')
dbSendQuery(ch, 'SET NAMES utf8')

for (i in 1:nrow(test)) {
  # BIND PARAMS
  query <- sqlInterpolate(conn, sql, param1 = "0", param2 = test[i, 1], 
                          param3 = test[i, 2], param4 = test[i, 3])
  # EXECUTE QUERY
  dbSendQuery(ch, query)
}