生成多个查询字符串，并通过一个

Question

表结构执行一个：

CREATE TABLE IF NOT EXISTS mysql.`my_autoinc` (
    `table_schema` VARCHAR(64) NOT NULL, 
    `table_name` VARCHAR(64) NOT NULL, 
    `auto_increment` INT(11) UNSIGNED NULL DEFAULT NULL, PRIMARY KEY (`table_schema`, `table_name`) 
) ENGINE=InnoDB; 

问题1：在任何DB命名table1或table2所有表的 名单。

REPLACE INTO mysql.`my_autoinc` 
SELECT table_schema, table_name, NULL AS `auto_increment` 
FROM information_schema.tables 
WHERE table_name IN ("table1", "table2"); 

查询1可以生成

table_schema | table_name | auto_increment 
=============================================== 
client_1  | table1  | NULL 
client_1  | table2  | NULL 
client_2  | table1  | NULL 
client_3  | table1  | NULL 

问题2：查询字符串的 列表。

SELECT CONCAT(
    'REPLACE INTO my_autoinc ', 
    'SELECT "',table_schema,'", "',table_name,'", MAX(Id) FROM ', 
    '(' 
     'SELECT MAX(Id) AS Id FROM ', table_schema, '.', table_name, 
     ' UNION ', 
     'SELECT MAX(Id) AS Id FROM ', table_schema, '_history.', table_name, '_history', 
    ') t' 
) AS 'queries' 
FROM my_autoinc; 

当被执行由查询2生成的查询的列表。

table_schema | table_name | auto_increment 
=============================================== 
client_1  | table1  | 99 
client_1  | table2  | 60 
client_2  | table1  | 299 
client_3  | table1  | 399 

我已经尝试过：GROUP_CONCAT但字符串的级联长度超过1000所以，不能执行该长度的查询。

更新：我不能做multiple statements in a prepare。

需要解决方案：要执行查询生成的查询2 查询2一个接一个。

Answer 1

你可以从GROUP_CONCAT

-- for the session 
SET SESSION group_concat_max_len = 1000000; 

-- or global 
SET GLOBAL group_concat_max_len = 1000000; 

Answer 2

设置最大lenght你可以用这样一个存储过程做到这一点，然后调用它：

DELIMITER $$ 
CREATE PROCEDURE doAllThings() 
BEGIN 
    DECLARE cursor_VAL VARCHAR(2000); 
    DECLARE done INT DEFAULT FALSE; 

    DECLARE cursor_i CURSOR FOR 
    SELECT CONCAT('REPLACE INTO mysql.my_autoinc ','SELECT "',table_schema,'", "',TABLE_NAME,'", MAX(Id) FROM ', '(SELECT MAX(Id) AS Id FROM ', table_schema, '.',TABLE_NAME,' UNION ','SELECT MAX(Id) AS Id FROM ', table_schema, '_history.', TABLE_NAME, '_history',') t') AS 'queries' FROM mysql.my_autoinc; 

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; 

    OPEN cursor_i; 
    read_loop: LOOP 
    FETCH cursor_i INTO cursor_VAL; 
    IF done THEN 
     LEAVE read_loop; 
    END IF; 

    SET @SQL := queries; 
    PREPARE stmt FROM @SQL; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;  

    END LOOP; 
    CLOSE cursor_i; 
END$$ 
DELIMITER ; 


-- call it 
call doAllThings();