Generate multiple query string and execute one by one

Question

Table Structure:

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;

Query 1: List of all tables named table1 or table2 in any DB.

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

Query 1 may generate

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

Query 2: List of query string.

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;

When the list of query generated by the Query 2 was executed.

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

I already tried: GROUP_CONCAT but the concatenated length of the string is more than 1000. So, can't execute a query of that length.

Update: I can't do multiple statements in a prepare.

Solution Required: To execute queries generated by Query 2 one by one.

score 0 · Answer 1 · answered Dec 30 '15 at 10:33

0

you can set the max lenght from GROUP_CONCAT

-- for the session
SET SESSION group_concat_max_len = 1000000;

-- or global
SET GLOBAL group_concat_max_len = 1000000;

answered Dec 30 '15 at 10:33

Bernd Buffen

13,762
2
22
34

But, I can't prepare multiple statement. http://stackoverflow.com/a/11635064/1753174 – Balakrishnan Dec 30 '15 at 11:14

score 0 · Answer 2 · answered Dec 30 '15 at 13:06

You can do it with a stored procedure like this and then call it:

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();

Generate multiple query string and execute one by one

2 Answers2