批量更新mysql数据库数据

任务：

数据库有多篇文档（file），每篇文档有多个段落（para），每个段落有多个句子（sentence），现在是要为250万个句子标注句子位置信息（第n句第m句话标为m-1）

方法一：

使用python脚本，对于每句话用update语句进行数据更新，用时1天多

sen_index = 0
last_para = 0
last_file_id = 0
for i,a_data in data:
    para = a_data['para']
    file_id = a_data['file_id']
    sentence_id = a_data['id']
    if para == last_para and file_id == last_file_id:
        sen_index += 1
    else:
        sen_index = 0
        last_para = para
        last_file_id = file_id
    执行update的SQL，更新sentence_id对应的sen_index 

方法二：导出数据再导入

1、将数据导出至txt，用时约为60s

（注：Excel 的行列数不能超过 1,048,576 行和 16,384 列的限制。）

2、使用python对数据进行编辑，几秒搞定

f = open('res.csv','r')
text = f.read()
f.close()
data = text.split('\n')

sen_index = 0
last_para = 0
last_file_id = 0
res_data = [data[0]]
for a_data in data[1:]:
    cols = a_data.split(',')
    if len(cols) < 4:
        break
    para = cols[2]
    file_id = cols[1]
    s_id = cols[0]
    if para == last_para and file_id == last_file_id:
        sen_index += 1
    else:
        sen_index = 0
        last_para = para
        last_file_id = file_id
    cols[3] = str(sen_index)
    res_data.append(','.join(cols))

f = open('dst.csv', 'w')
f.write('\n'.join(res_data))
f.close()

3、再将数据导入到数据库中，大约需要450秒

方法三：使用update case when 语句

#接方法二，循环体最后一句话改为res_data.append(cols)
from pymysql import *
batch = 10000
conn = connect(host='localhost', port=3306, database='db', user='root',
                   password='root', charset='utf8')

res_data = res_data[1:]
for i in range(len(res_data)//batch+1):
    sub_data = res_data[i*batch:(i+1)*batch]
    if len(sub_data) <= 0:
        break
    condition = ' '.join([f'when {a_data[0]} then {a_data[3]}' for a_data in sub_data])
    where = ','.join([a_data[0] for a_data in sub_data])
    sql = f'update table set sen_index = (case id {condition} end) where id in ({where})'
    cs1 = conn.cursor()
    cs1.execute(sql)
    conn.commit()
    cs1.close()

conn.close()

运行这段代码需要大约1800s

注：update语句后面必须跟where，这是因为不加where会锁定全表，降低查询效率（更新1000条数据需要100s，加上where需要0.13s），甚至还会让系统误认为发生了死锁。

参考资料：https://www.cnblogs.com/ldj3/p/9288187.html

下面方法不可行：

更新语句，id必须连续，用时大约1000s，结果只能更新所有为0的行

因为更新时会先产生临时表，更新临时表的数据时不会使原表数据立即生效，只有等所有数据更新完成后才会更新原表数据

UPDATE ner_sentence_temp t1
INNER JOIN ner_sentence_temp t2 ON t1.id - 1 = t2.id
SET t1.para_index = (
	CASE
	WHEN t1.para_order = t2.para_order
	AND t1.file_id = t2.file_id THEN
		t2.para_index + 1
	ELSE
		0
	END
)