1、批量更新表中某个字段,如果表比较大,每条记录都执行一条update,1s执行10条数据,10万条数据就要1W秒,3个多小时。
2、可以用case when 来实现,模板如下
UPDATE categories SET
display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3);
ID 是条件字段,WHEN 相当于where,THEN 表示要更新的字段设置成的值。
如果有多个字段要更新,模板如下
UPDATE categories
SET dingdan = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)
手动串接字符太麻烦,可以用python,每串接2000条,执行一次sql,1分钟内解决问题:
sql = " SELECT `id`,c_id,SUM(`total_price` - `reduction`) AS price FROM `orders` WHERE STATUS IN (9,11) AND total_price >0 GROUP BY c_id HAVING price > 0 ORDER BY c_id"
datas = bt.getData(sql)
error = 0
count = 0
totalcount = 0
id = []
sql = "UPDATE customer SET consumption_amount = consumption_amount + CASE id "
sqlwhen = ""
sqlend = "END WHERE id in "
sqllen = 2000
with open("sqlupdatenew.sql",'w') as f:
print(len(datas))
for data in datas:
if count <= sqllen:
sqlwhen += "WHEN " + str(data[1]) + " THEN " + str(data[2]) + " "
count += 1
id.append(data[1])
if count == sqllen or totalcount == len(datas) -1:
mysql = sql + sqlwhen + sqlend + str(tuple(id))
bt.executesql(mysql)
temp = sql + sqlwhen + sqlend + str(tuple(id)) + ";\n"
f.write(temp)
print(totalcount)
count = 0
sqlwhen = ""
id.clear()
totalcount += 1
bt.closemysql()