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

Question

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

id	key1	value1	key2	value2	key3	value3	key4	value4
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，不是整个记录）。
比如上述的例子处理后得到：

id	key1	value1	key2	value2	key3	value3	key4	value4
1	Null	Null	南	0.513	西	0.961	北	0.756
2	南	0.962	Null	Null	北	0.625	东	0.533
3	西	0.628	北	0.268	东	0.156	Null	Null
4	Null	Null	东	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)
... 

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

Answer 1

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

Answer 2

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

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

Answer 3

select * from key_value_table;

1.*

SELECT
    a.*, CASE NAME
WHEN key1 THEN
    'key1'
WHEN key2 THEN
    'key2'
WHEN key3 THEN
    'key3'
WHEN key4 THEN
    'key4'
ELSE
    NULL
END AS key_yws,
 CASE NAME
WHEN key1 THEN
    'value1'
WHEN key2 THEN
    'value2'
WHEN key3 THEN
    'value3'
WHEN key4 THEN
    'value4'
ELSE
    NULL
END AS value_yws
FROM
    (
        SELECT
            a. NAME,
            b.key1,
            b.key2,
            b.key3,
            b.key4,
            b.id
        FROM
            black_list_table a,
            key_value_table b
        WHERE
            a.`name` = b.key1
        OR a.`name` = b.key2
        OR a.`name` = b.key3
        OR 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_text
FROM
    (
        SELECT
            a.*, CASE NAME
        WHEN key1 THEN
            'key1'
        WHEN key2 THEN
            'key2'
        WHEN key3 THEN
            'key3'
        WHEN key4 THEN
            'key4'
        ELSE
            NULL
        END AS key_yws,
        CASE NAME
    WHEN key1 THEN
        'value1'
    WHEN key2 THEN
        'value2'
    WHEN key3 THEN
        'value3'
    WHEN key4 THEN
        'value4'
    ELSE
        NULL
    END AS value_yws
    FROM
        (
            SELECT
                a. NAME,
                b.key1,
                b.key2,
                b.key3,
                b.key4,
                b.id
            FROM
                black_list_table a,
                key_value_table b
            WHERE
                a.`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 ()
BEGIN

DECLARE 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) DO

SET @my_sq2 = my_sql;

PREPARE s1
FROM
    @my_sq2;

EXECUTE s1;

DEALLOCATE PREPARE s1;

FETCH cur INTO my_sql;


END
WHILE;

CLOSE cur;


END;

4.*

call my_procedure()

5.*