mysql - mySQL:使用条件计算多个表中的唯一键
问题描述
我有一堆表('table001'、'table002' 等),其中包含大量行和几列。
列是“id”(键)、“mode”(“ui”或“cmd”)和日期。
我想找出所有表中存在多少个唯一的 'id',其模式设置为 'ui' 并且其日期小于 30 天前。
这是我当前的查询:
SELECT COUNT(*) FROM
(
SELECT id FROM table001 WHERE mode="ui" AND date > (NOW() - INTERVAL 30 DAY)
UNION
SELECT id FROM table002 WHERE mode="ui" AND date > (NOW() - INTERVAL 30 DAY)
UNION
SELECT id FROM table003 WHERE mode="ui" AND date > (NOW() - INTERVAL 30 DAY)
UNION
[...etc etc for 30 tables]
) as t
这是这样做的正确方法吗?
解决方案
如果调用了所有 pk id,id
那么您可以运行下面的存储过程,而无需静态键入所有表:
DELIMITER $$
CREATE PROCEDURE eval(IN dynamic_statement TEXT)
BEGIN
SET @dynamic_statement := dynamic_statement;
PREPARE prepared_statement FROM @dynamic_statement;
EXECUTE prepared_statement;
DEALLOCATE PREPARE prepared_statement;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE sp_unique_id_count_across_all_tables()
BEGIN
DECLARE var_key_fieldname VARCHAR(64) DEFAULT 'id';
DECLARE var_tables_count INTEGER UNSIGNED;
DECLARE var_index INTEGER UNSIGNED DEFAULT 1;
DECLARE var_tablename_of_index VARCHAR(64);
DROP TEMPORARY TABLE IF EXISTS tmp_tables_with_id;
CREATE TEMPORARY TABLE tmp_tables_with_id
SELECT
# *
(@cnt := @cnt + 1) AS idx,
TABLE_NAME AS tablename
FROM information_schema.columns
CROSS JOIN (SELECT @cnt := 0) A
WHERE table_schema=SCHEMA()
AND (COLUMN_NAME COLLATE utf8_unicode_ci) = (var_key_fieldname COLLATE utf8_unicode_ci)
;
SET var_tables_count := (SELECT COUNT(0) FROM tmp_tables_with_id);
DROP TEMPORARY TABLE IF EXISTS tmp_all_ids;
CREATE TEMPORARY TABLE tmp_all_ids(
id INTEGER NOT NULL UNIQUE
);
WHILE (var_index <= var_tables_count) DO
SET var_tablename_of_index := (
SELECT tablename
FROM tmp_tables_with_id
WHERE idx = var_index
);
CALL eval(CONCAT('
INSERT IGNORE INTO tmp_all_ids(id)
SELECT
DISTINCT t.', var_key_fieldname, '
FROM ', var_tablename_of_index,' t
-- ON DUPLICATE KEY UPDATE id = t.', var_key_fieldname,'
;
'));
SET var_index := var_index + 1;
END WHILE;
SELECT COUNT(0) FROM tmp_all_ids;
END$$
DELIMITER;
DROP PROCEDURE IF EXISTS eval;
DROP PROCEDURE IF EXISTS sp_unique_id_count_across_all_tables;
CALL sp_unique_id_count_across_all_tables();