問題描述

碰到一個(gè)類似這樣的問題，請各位朋友幫助一下。比如我的mysql數(shù)據(jù)表如下：

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 ...........................

還有一個(gè)黑名單list：list = [’冬’, ’難’, ’喜’, ’杯’]通過SQL update更新數(shù)據(jù)，若表中某一條記錄的key*字段的值在黑名單list中，則將其和其對應(yīng)的value變?yōu)镹ull（注意只是該key和對應(yīng)的value，不是整個(gè)記錄）。比如上述的例子處理后得到：

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.有十個(gè)key-value對（key1,value1 ~ key10,value10）3.黑名單list有十萬個(gè)詞匯如何可以快速的更新整個(gè)數(shù)據(jù)表？

我的笨方法是：

...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)...

但是這個(gè)速度很慢，想請問如何能更快速的更新整個(gè)數(shù)據(jù)表？

問題解答

回答1：

給個(gè)我之前提的類似問題鏈接給你，希望能給你點(diǎn)幫助https://segmentfault.com/q/10...

回答2：

黑名單的數(shù)據(jù)存到數(shù)據(jù)庫表中，如：表名black_list, 字段名稱為name, 并在name上創(chuàng)建索引

分為十個(gè)sql語句執(zhí)行：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.*