python-mysqldb - 这样结构的mysql表，如何快速update

问题描述

碰到一个类似这样的问题，请各位朋友帮助一下。比如我的mysql数据表如下：

idkey1value1key2value2key3value3key4value4 1冬0.138南0.513西0.961北0.756 2南0.962喜0.258北0.625东0.533 3西0.628北0.268东0.156难0.697 4杯0.628东0.894南0.625西0.268 ...........................

还有一个黑名单list：list = [’冬’, ’难’, ’喜’, ’杯’]通过SQL update更新数据，若表中某一条记录的key*字段的值在黑名单list中，则将其和其对应的value变为Null（注意只是该key和对应的value，不是整个记录）。比如上述的例子处理后得到：

idkey1value1key2value2key3value3key4value4 1NullNull南0.513西0.961北0.756 2南0.962NullNull北0.625东0.533 3西0.628北0.268东0.156NullNull 4NullNull东0.894南0.625西0.268 ...........................

那么问题来了：假如1.有一百万条记录（id：1 ~ 1,000,000）2.有十个key-value对（key1,value1 ~ key10,value10）3.黑名单list有十万个词汇如何可以快速的更新整个数据表？

我的笨方法是：

...for key in list: for i in range(1,11):sql = 'UPDATE table_name SET key1=Null, value1=Null WHERE key%d=’%s’' % (i, key)cursor.execute(sql)...

但是这个速度很慢，想请问如何能更快速的更新整个数据表？

问题解答

回答1：

给个我之前提的类似问题链接给你，希望能给你点帮助https://segmentfault.com/q/10...

回答2：

黑名单的数据存到数据库表中，如：表名black_list, 字段名称为name, 并在name上创建索引

分为十个sql语句执行：update tset key1 = null, value1 = nullwhere exists (select 1 from black_list where name = key1);update tset key2 = null, value2 = nullwhere exists (select 1 from black_list where name = key2);以此类推更新到key10, value10字段。

回答3：

select * from key_value_table;

1.*

SELECT a.*, CASE NAMEWHEN key1 THEN ’key1’WHEN key2 THEN ’key2’WHEN key3 THEN ’key3’WHEN key4 THEN ’key4’ELSE NULLEND AS key_yws, CASE NAMEWHEN key1 THEN ’value1’WHEN key2 THEN ’value2’WHEN key3 THEN ’value3’WHEN key4 THEN ’value4’ELSE NULLEND AS value_ywsFROM (SELECT a. NAME, b.key1, b.key2, b.key3, b.key4, b.idFROM black_list_table a, key_value_table bWHERE a.`name` = b.key1OR a.`name` = b.key2OR a.`name` = b.key3OR a.`name` = b.key4 ) a

create index idx_key_value_table_key on key_value_table(key1,key2,key3,key4);

2.*

CREATE TABLE exec_sql AS SELECT concat(’update key_value_table set ’,key_yws,'=null, ',value_yws,’=null where id=’,id ) AS sql_textFROM (SELECT a.*, CASE NAMEWHEN key1 THEN ’key1’WHEN key2 THEN ’key2’WHEN key3 THEN ’key3’WHEN key4 THEN ’key4’ELSE NULLEND AS key_yws,CASE NAME WHEN key1 THEN’value1’ WHEN key2 THEN’value2’ WHEN key3 THEN’value3’ WHEN key4 THEN’value4’ ELSENULL END AS value_yws FROM( SELECTa. NAME,b.key1,b.key2,b.key3,b.key4,b.id FROMblack_list_table a,key_value_table b WHEREa.`name` = b.key1 OR a.`name` = b.key2 OR a.`name` = b.key3 OR a.`name` = b.key4) a ) tmp2

3.*

CREATE PROCEDURE my_procedure ()BEGINDECLARE my_sql VARCHAR (200);DECLARE my_sq2 VARCHAR (200);DECLARE STOP INT DEFAULT 0;DECLARE cur CURSOR FOR (SELECT sql_text FROM exec_sql);DECLARE CONTINUE HANDLER FOR SQLSTATE ’02000’SET STOP = NULL;OPEN cur;FETCH cur INTO my_sql;WHILE (STOP IS NOT NULL) DOSET @my_sq2 = my_sql;PREPARE s1FROM @my_sq2;EXECUTE s1;DEALLOCATE PREPARE s1;FETCH cur INTO my_sql;ENDWHILE;CLOSE cur;END;

4.*

call my_procedure()

5.*