Ruby mysql2在单个查询中有多个语句 [英] Ruby mysql2 multiple statements in single query

问题描述

我有一个问题，我要更新数据库中的数百万行，因此我不想将每个〜1000个语句的组合并到一个查询中，而不是单独更新每个行.

I have an issue where I'm updating millions of rows in my DB, so rather than updating each one individually I want to join groups of ~1000 statements into a single query.

我已经启用了MULTI_STATEMENTS

I have enabled MULTI_STATEMENTS like so

client = Mysql2::Client.new(:host => 'localhost', :database => 'mehdb', :username => "root", :password => "", :flags => Mysql2::Client::MULTI_STATEMENTS)

这是我正在运行的代码的示例

Here's an example of the code I'm running

sql = "SELECT id, x FROM pew WHERE x IS NULL LIMIT 1000"

results = db_read.query(sql)

while results.count > 0

  updates = ''

  results.each do |r|
    updates += "UPDATE pew SET x = 10 WHERE id = #{r['id']};"
  end

  db_write.query(updates) unless updates.empty?

  results = db_read.query(sql)
end

这项工作在第一次运行中就可以了，但是当它触发第二组更新时，我会收到此错误消息

This work's alright during the first run through but then when it fires off the second set of updates I get this error message

`query': Commands out of sync; you can't run this command now (Mysql2::Error)

有没有人遇到过这个?或对其他方法有何建议?

Has anyone come across this before? Or any advise on another approach?

推荐答案

对此问题的简短回答是，当启用MULTI_STATEMENTS时，mysql希望您处理查询结果.

Short answer to this problem is when MULTI_STATEMENTS are enabled mysql expects you to handle the result of your query.

一种快速的解决方法是在每组多个更新语句之后执行类似的操作

A quick fix is to do something similar to this after each set of multiple update statements

  while db_write.next_result
    db_write.store_result rescue ''
  end

这篇关于Ruby mysql2在单个查询中有多个语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！