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