使用相同查询语句，MySQL与R生成的cit_EP表行数不匹配问题求助

可能的原因 #

• 数据库连接不一致：R连接的MySQL实例、schema或原表，和你在Workbench中操作的不是同一个。比如R连测试库，Workbench连生产库，或者schema选错了。
• 过滤条件的实际执行差异：原表中earliest_filing_year或appln_auth字段的数据类型异常（比如年份是字符串而非数值），导致MySQL和R驱动对过滤条件的解析不同，R中没有过滤掉部分本应被排除的行。
• R驱动的大结果集拉取问题：使用dbGetQuery拉取超大规模结果集时，部分数据库驱动（如旧版RMySQL）可能因网络中断重连、内存溢出等问题，重复拉取部分行，导致dataframe行数增多。
• MySQL表创建后的隐式修改：你在Workbench中创建cit_EP表后，可能有其他操作（如定时任务、其他用户）对该表执行了删除或去重操作，导致行数减少。
• 事务隔离级别差异：MySQL和R连接的事务隔离级别不同，若两次查询之间原表有数据变更，会导致查询结果行数不一致。

快速验证步骤 #

先定位核心差异出在哪一步：

1. 在MySQL Workbench中执行计数查询：

   SELECT COUNT(*) FROM citations_patent_families
   WHERE earliest_filing_year >= 1978
     AND earliest_filing_year <= 2020
     AND appln_auth = 'EP';
   

2. 在R中执行相同的计数查询：

   dbGetQuery(con, "SELECT COUNT(*) FROM citations_patent_families
                    WHERE earliest_filing_year >= 1978
                      AND earliest_filing_year <= 2020
                      AND appln_auth = 'EP';")
   

3. 对比两个结果：
   • 如果计数一致：说明问题出在CREATE TABLE AS SELECT后的表修改，或R拉取数据时的重复问题。
   • 如果计数不一致：说明连接的数据库/表不同，或过滤条件的执行逻辑有差异。

针对性解决方案 #

1. 验证数据库连接一致性 #

• 检查R的连接代码con对应的数据库地址、端口、用户名、schema是否和Workbench完全一致。
• 在R和Workbench中分别执行SELECT COUNT(*) FROM citations_patent_families;，对比总条数是否相同。如果不同，说明连的不是同一个表。

2. 排查过滤条件执行差异 #

• 检查earliest_filing_year字段的数据类型：在MySQL中执行DESCRIBE citations_patent_families;，看该字段是INT还是VARCHAR。如果是字符串类型，修改查询语句显式转换为数值：

  SELECT * FROM citations_patent_families
  WHERE CAST(earliest_filing_year AS UNSIGNED) >= 1978
    AND CAST(earliest_filing_year AS UNSIGNED) <= 2020
    AND appln_auth = 'EP';
  

  同时在R中使用相同的带转换的查询语句，再对比行数。
• 检查appln_auth字段是否有NULL值：执行SELECT COUNT(*) FROM citations_patent_families WHERE appln_auth IS NULL;，确认是否有NULL行被R错误地包含进来。

3. 解决R驱动的重复拉取问题 #

• 改用分批次拉取的方式替代dbGetQuery，避免一次性加载超大结果集：

  # 发送查询请求
  res <- dbSendQuery(con, "SELECT * FROM citations_patent_families
                           WHERE earliest_filing_year >= 1978
                             AND earliest_filing_year <= 2020
                             AND appln_auth = 'EP';")
  # 初始化数据框
  cit_EP <- dbFetch(res, n = 100000)
  # 循环拉取剩余数据
  while (!dbHasCompleted(res)) {
    cit_EP <- rbind(cit_EP, dbFetch(res, n = 100000))
  }
  # 清理查询结果
  dbClearResult(res)
  

• 升级R的数据库驱动：将RMySQL或RMariaDB包升级到最新版本，修复旧版本的bug。

4. 检查MySQL表的创建与后续操作 #

• 在Workbench中创建cit_EP表后，立即执行SELECT COUNT(*) FROM cecilia.cit_EP;，看计数是否和原查询的COUNT一致。如果不一致，说明原表可能有损坏，执行CHECK TABLE citations_patent_families;检查表完整性。
• 确认是否有其他脚本或用户对cecilia.cit_EP表执行了删除、去重等操作。

5. 统一事务隔离级别 #

• 在R和Workbench中设置相同的事务隔离级别，避免因数据变更导致的差异：
  • MySQL中执行：

    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
    

  • R中执行：

    dbExecute(con, "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;")
    

  之后再分别执行查询和表创建操作。

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