Mysql scrip 用于删除所有行，其中每个现有表中都有一个零

Question

>我有一个事件检查 3 个表以在质量低于 50 时删除一行，但现在我想更改它，以便它自动检查同一数据库中的每个表，而不需要总是像下面的代码一样重复相同的行

DELIMITER |
/* vervang ALTER naar CREATE voor een nieuw event*/
ALTER EVENT removezero
    ON SCHEDULE EVERY 1 Hour STARTS '2017-10-30 10:00:00' ON COMPLETION PRESERVE ENABLE
    DO
        Begin
        DELETE FROM kepserverex.plc1 WHERE BelgischePLC_PLC_1_WasCyclus_QUALITY < 50;
        DELETE FROM kepserverex.plc2 WHERE BelgischePLC_PLC_2_WasCyclus_QUALITY < 50;
        DELETE FROM kepserverex.plc3 WHERE BelgischePLC_PLC_3_WasCyclus_QUALITY < 50;
        END|
DELIMITER ;

Answer 1

检查一下，它将为您提供需要执行的所有查询，然后创建一个程序来执行它。

SELECT GROUP_CONCAT(t.query SEPARATOR '; ')
FROM
(
    SELECT CONCAT('DELETE FROM ', a.table_name,
                  ' WHERE BelgischePLC_PLC_1_WasCyclus_QUALITY < 50;') AS query,
        '1' AS id
    FROM information_schema.tables a
    WHERE a.table_schema = 'kepserverex'
) t
GROUP BY t.id

编辑：正如@P.Salmon评论的那样，如果你有太多的桌子，你可能会达到goupe_concat的最大长度。您可以通过在配置文件中更改它或仅使用此查询来使其乞求SET SESSION group_concat_max_len = 1000000; 或逐行打印结果

USE INFORMATION_SCHEMA;
SELECT CONCAT("DELETE FROM `", TABLE_SCHEMA,"`.`", TABLE_NAME, "` WHERE BelgischePLC_PLC_1_WasCyclus_QUALITY < 50;") 
AS MySQLCMD FROM TABLES 
WHERE TABLE_SCHEMA = "kepserverex";

注意：没有Group_concat的那个返回行，因此您必须使用 Cursor 遍历一组行

将结果存储到 sql 文件中，然后执行它;我在文件名中使用了小时，因为您说每小时都会执行该事件，否则如果您想使用相同的名称或告诉 mysql 覆盖它，您将不得不删除该文件

SELECT GROUP_CONCAT(t.query SEPARATOR '; ') INTO OUTFILE '/var/lib/mysql-files/hour(now())alterstatements.sql'
FROM
(
    SELECT CONCAT('DELETE FROM ', a.table_name,
                  ' WHERE BelgischePLC_PLC_1_WasCyclus_QUALITY < 50') AS query,
        '1' AS id
    FROM information_schema.tables a
    WHERE a.table_schema = 'db_lists'
) t
GROUP BY t.id;
Source '/var/lib/mysql-files/hour(now())alterstatements.sql';

Answer 2

在此示例中，我设置了一个过程来说明如何执行此操作，但您应该能够将过程中的代码直接拖放到事件中。

truncate table plc1;
truncate table plc2;
truncate table plc3;
insert into plc1 (BelgischePLC_PLC_1_WasCyclus_QUALITY) values (10),(40),(50);
insert into plc2 (BelgischePLC_PLC_2_WasCyclus_QUALITY) values (10),(40),(50);
insert into plc3 (BelgischePLC_PLC_3_WasCyclus_QUALITY) values (10),(40),(50);
select 'p1 before', p.* from plc1 p;
select 'p2 before', p.* from plc2 p;
select 'p3 before', p.* from plc3 p;
drop procedure if exists t;
delimiter $$
create procedure t ()
begin
set @stmt = null;
#Parse information schema table and columns to build a big string
set @str = (select group_concat(str)
from
(
select concat('delete from ' , it.table_schema , '.', it.TABLE_NAME , ' where ' , ic.column_name , ' < 50;') str
from information_schema.`TABLES` it
join information_schema.columns ic on ic.table_name = it.table_name
where it.table_name like 'plc%'
        and ic.column_name like '%plc%'
) s
);
set @str = replace(@str,';,',';'); #tidy up
#parse the big string and execute prepared statement
while length(@str) > 0 do
set @stmt = substring(@str,1,instr(@str,';'));
set @str = replace(@str, @stmt,'');
#select 'stmt ', @stmt;
prepare stmt from @stmt;
execute stmt;
deallocate prepare stmt;
end while;
end $$
delimiter ;
call t();
select 'p1 after', p.* from plc1 p;
select 'p2 after', p.* from plc2 p;
select 'p3 after', p.* from plc3 p;

结果

MariaDB [sandbox]> select 'p1 after', p.* from plc1 p;
+----------+----+--------------------------------------+
| p1 after | id | BelgischePLC_PLC_1_WasCyclus_QUALITY |
+----------+----+--------------------------------------+
| p1 after |  3 |                                   50 |
+----------+----+--------------------------------------+
1 row in set (0.00 sec)
MariaDB [sandbox]> select 'p2 after', p.* from plc2 p;
+----------+----+--------------------------------------+
| p2 after | id | BelgischePLC_PLC_2_WasCyclus_QUALITY |
+----------+----+--------------------------------------+
| p2 after |  3 |                                   50 |
+----------+----+--------------------------------------+
1 row in set (0.00 sec)
MariaDB [sandbox]> select 'p3 after', p.* from plc3 p;
+----------+----+--------------------------------------+
| p3 after | id | BelgischePLC_PLC_3_WasCyclus_QUALITY |
+----------+----+--------------------------------------+
| p3 after |  3 |                                   50 |
+----------+----+--------------------------------------+
1 row in set (0.00 sec)