使用RMySQL连接AWS MySQL执行CTE语句报错求助

报错信息 #

Error in .local(conn, statement, ...) : 
  could not run statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE db.table
WITH CTE_1 AS (
SELECT variable
' at line 3

环境信息 #

• MySQL版本：8.0.32（AWS云端及本地实例）
• R版本：4.2.2
• RStudio版本：2022.12.0
• 尝试过的数据库驱动：RMySQL、RMariaDB

可正常运行的SQL语句（MySQL Workbench中） #

CREATE TABLE dbname.census_summary
WITH CTE_census AS (
SELECT state
      ,SUM(population) population
      ,SUM(CASE WHEN sex = "Male" THEN population ELSE 0 END) male_pop
      ,SUM(CASE WHEN sex = "Female" THEN population ELSE 0 END) female_pop
FROM dbname.census_data
GROUP BY state
),
CTE_covid AS (
SELECT state
      ,SUM(confirmed_cases) covid_cases
      ,SUM(deaths) covid_deaths
FROM dbname.covid_data
GROUP BY state
)
SELECT *
FROM CTE_census AS a
LEFT JOIN CTE_covid AS b
ON a.state = b.state

R中执行代码 #

library(RMySQL)
library(DBI)
library(readr)

con <- RMySQL::dbConnect(RMySQL::MySQL(),
                 host = "hostname",
                 user = "username",
                 password = "pass",
                 dbname = "dbname",
                 port = 3306)

query_exec <- dbGetQuery(con, statement = read_file('sql_file.sql'))

问题原因及解决方案 #

1. 缺少AS关键字（核心原因） #

MySQL的CREATE TABLE ... SELECT语法要求在表名后添加AS关键字，Workbench可能会自动兼容无AS的写法，但数据库驱动（RMySQL/RMariaDB）会严格解析语法。

修改后的SQL语句：

CREATE TABLE dbname.census_summary AS
WITH CTE_census AS (
SELECT state
      ,SUM(population) population
      ,SUM(CASE WHEN sex = "Male" THEN population ELSE 0 END) male_pop
      ,SUM(CASE WHEN sex = "Female" THEN population ELSE 0 END) female_pop
FROM dbname.census_data
GROUP BY state
),
CTE_covid AS (
SELECT state
      ,SUM(confirmed_cases) covid_cases
      ,SUM(deaths) covid_deaths
FROM dbname.covid_data
GROUP BY state
)
SELECT *
FROM CTE_census AS a
LEFT JOIN CTE_covid AS b
ON a.state = b.state

2. 检查SQL文件的换行符/特殊字符 #

read_file读取的SQL文件如果包含Windows换行符（\r\n）或其他隐藏特殊字符，可能导致驱动解析错误。可以尝试：

• 将SQL语句直接嵌入R代码的字符串中测试：

  query <- "CREATE TABLE dbname.census_summary AS
  WITH CTE_census AS (
  SELECT state
        ,SUM(population) population
        ,SUM(CASE WHEN sex = 'Male' THEN population ELSE 0 END) male_pop
        ,SUM(CASE WHEN sex = 'Female' THEN population ELSE 0 END) female_pop
  FROM dbname.census_data
  GROUP BY state
  ),
  CTE_covid AS (
  SELECT state
        ,SUM(confirmed_cases) covid_cases
        ,SUM(deaths) covid_deaths
  FROM dbname.covid_data
  GROUP BY state
  )
  SELECT *
  FROM CTE_census AS a
  LEFT JOIN CTE_covid AS b
  ON a.state = b.state"
  query_exec <- dbGetQuery(con, statement = query)
  

• 将SQL文件保存为Unix换行符（\n）格式。

3. 字符串引号兼容问题 #

MySQL默认推荐使用单引号包裹字符串，虽然部分环境允许双引号，但为避免解析差异，建议将SQL中的双引号替换为单引号（如sex = 'Male'）。

内容的提问来源于stack exchange，提问作者Juan Esteban Vaccaro Silvio