mysql查处所有表中的新数据,mysql – 对所有表执行查询并在新表中填入数据

我需要在那里得到结果表字段

– table_name,min_date,max_date

这是我的查询,我应该为所有表执行

SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate

FROM (SELECT short_date, type, value, count(*) as cnt

FROM testTable

GROUP BY short_date

HAVING COUNT(*) > 1) as Duplicates

然后我发现了如何获取所有表名

我是这样做的

SELECT TABLE_NAME as name FROM `information_schema`.`TABLES`

WHERE `TABLES`.`TABLE_SCHEMA` = 'test'

AND `TABLES`.`TABLE_NAME` LIKE 'test%'

但我不知道如何为所有表执行它并在新表中填入结果.

我试着这样做

DECLARE @DB_Name varchar(50)

DECLARE @Command varchar(100);

DECLARE database_cursor CURSOR FOR

SELECT name

FROM (SELECT TABLE_NAME as name FROM `information_schema`.`TABLES`

WHERE `TABLES`.`TABLE_SCHEMA` = 'test'

AND `TABLES`.`TABLE_NAME` LIKE 'test%') as TableNames

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @Command = 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate

FROM (SELECT short_date, type, value, count(*) as cnt

FROM ' + @DB_Name + '

WHERE type = ''test''

GROUP BY short_date, type, value

HAVING COUNT(*) > 1) as Duplicates'

EXEC sp_executesql @Command

FETCH NEXT FROM database_cursor INTO @DB_Name

END

CLOSE database_cursor

DEALLOCATE database_cursor

但我得到了这个错误

Syntax error or access violation: 1064 You have an error in your SQL

syntax; check the manual that corresponds to your MySQL server version

for the right syntax to use near ‘DECLARE @DB_Name varchar(50) DECLARE

@Command varchar(100)’ at line 1

UPD

CREATE PROCEDURE GetData()

BEGIN

DECLARE @DB_Name varchar(50), @Command varchar(100);

DECLARE database_cursor CURSOR FOR

SELECT name

FROM (SELECT TABLE_NAME as name FROM `information_schema`.`TABLES`

WHERE `TABLES`.`TABLE_SCHEMA` = 'test'

AND `TABLES`.`TABLE_NAME` LIKE 'test%_') as TableNames

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @Command = 'SELECT MIN(short_date) as FirstDuplicatedDate, MAX(short_date) as LastDuplicatedDate

FROM (SELECT short_date, type, value, count(*) as cnt

FROM ' + @DB_Name + '

WHERE type = ''test''

GROUP BY short_date, type, value

HAVING COUNT(*) > 1) as Duplicates'

EXEC sp_executesql @Command

FETCH NEXT FROM database_cursor INTO @DB_Name

END;

CLOSE database_cursor

DEALLOCATE database_cursor

END;

CALL GetData()

解决方法:

一开始就添加DELIMITER $$;添加DELIMITER;在END之后.

摆脱声明命令.而是使用不需要声明的@command.

添加SELECT @command;在SELECT @command之后：= …;这样我们就可以做一些调试了.

CLOSE和DEALLOCATE语句需要;终止他们.

测试将行耗尽到FETCH.

您真的需要查看一些存储过程的示例,尤其是那些带有游标的示例.

更新

呃,我甚至没有发现一半的语法错误.这可能有效(我无法分辨,因为我没有您的特定表或列.)：

DROP PROCEDURE IF EXISTS so42856538;

DELIMITER $$

CREATE PROCEDURE so42856538()

LANGUAGE SQL

MODIFIES SQL DATA

SQL SECURITY INVOKER

BEGIN

DECLARE _TableName varchar(64);

DECLARE _done INT DEFAULT FALSE;

DECLARE database_cursor CURSOR FOR

SELECT TABLE_NAME

FROM `information_schema`.`TABLES`

WHERE `TABLE_SCHEMA` = 'test'

AND `TABLE_NAME` LIKE 'test%';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

OPEN database_cursor;

curs_loop: LOOP

FETCH NEXT FROM database_cursor INTO _TableName;

IF _done THEN LEAVE curs_loop; END IF;

SET @Command := CONCAT(

'SELECT MIN(short_date) as FirstDuplicatedDate,

MAX(short_date) as LastDuplicatedDate

FROM ( SELECT short_date, type, value, count(*) as cnt

FROM ', _TableName, '

WHERE type = "test"

GROUP BY short_date, type, value

HAVING COUNT(*) > 1 ) as Duplicates'

);

SELECT _TableName, @command; -- Debugging (remove if it is clutter)

PREPARE _sql FROM @command;

EXECUTE _sql;

DEALLOCATE PREPARE _sql;

END LOOP;

CLOSE database_cursor;

END $$

DELIMITER ;

CALL so42856538;

标签：mysql,sql

来源： https://codeday.me/bug/20190522/1154374.html