1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
DELIMITER $$
CREATE DEFINER=`user`@`localhost` PROCEDURE `multiDBqueryRun_V1`(IN `query` TEXT, IN `table_name_var` VARCHAR(255), IN `columns_used_var` TEXT, IN `where_text_var` TEXT, IN `separator_value_var` VARCHAR(255)) COMMENT 'Query: SingleDB → MultiDB (All DBs) + run it' NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER BEGIN
SET @TABLE_NAME = table_name_var;
SET @WHERE_TEXT = where_text_var;
SET @COLUMNS_USED = columns_used_var;
SET @MULTIDB_QUERY = CONCAT('SELECT "$MULTIDB" FROM `$MULTIDB`.', @TABLE_NAME, @WHERE_TEXT);
-- EXECUTION --
CREATE TEMPORARY TABLE `MULTIDB_TEMP_DB_TBL_COLS` AS
SELECT * FROM (
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA NOT IN('mysql', 'test', 'tmp', 'information_schema', 'sys', 'performance_schema') AND
TABLE_NAME = @TABLE_NAME AND
FIND_IN_SET(COLUMN_NAME, @COLUMNS_USED)
) tbl
GROUP BY
TABLE_SCHEMA,
TABLE_NAME;
SELECT GROUP_CONCAT(REPLACE(@MULTIDB_QUERY, '$MULTIDB', CONCAT('', TABLE_SCHEMA, '')) SEPARATOR "\nUNION ALL\n")
INTO @stmt_sql
FROM `MULTIDB_TEMP_DB_TBL_COLS`;
PREPARE stmt FROM @stmt_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
|
