mysql in(百条名单),这样结构的mysql表，如何快速update

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