我正在尝试编写 MySQL 脚本，删除一些由模式选择的表，但我的程序没有编译。有人可以请教一下它有什么问题吗？

delimiter #
drop procedure if exists drop_audit_tables #
create procedure drop_audit_tables()
begin
  declare done int default false;
  declare cmd varchar(4000);
  declare cmds cursor for select 'drop table [' + table_name + ']' from information_schema.tables where table_name like '%_audit';
  declare continue handler for not found set done = true;
  open cmds;
  tLoop: loop
    fetch cmds into cmd;
    if done then
      leave tLoop;
    end if;
    PREPARE STMT FROM cmd;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
  end loop tLoop;
  close cmds;
end #

错误信息:

[42000][1064] 您的 SQL 语法有误；查看与您的 MySQL 服务器版本对应的手册，了解在 'cmd; 附近使用的正确语法。执行STMT；取消分配准备 STMT；结束循环 tLoop；在第 13 行关闭 cm'

最佳答案

你可以避开光标:

mysql> DROP TABLE IF EXISTS `one_audit`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `two_audit`;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS `three_audit`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `one_audit`(`a` INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `two_audit`(`a` INT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `three_audit`(`a` INT);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @`drop_tables` := (
    ->   SELECT
    ->     CONCAT('DROP TABLE IF EXISTS ',
    ->     GROUP_CONCAT(CONCAT('`', `TABLE_NAME`, '`') SEPARATOR ', '))
    ->   FROM
    ->     `information_schema`.`TABLES`
    ->   WHERE
    ->     `TABLE_SCHEMA` = DATABASE() AND
    ->     `TABLE_TYPE` = 'BASE TABLE' AND
    ->     `TABLE_NAME` LIKE '%_audit'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @`drop_tables`;
+--------------------------------------------------------------+
| @`drop_tables`                                               |
+--------------------------------------------------------------+
| DROP TABLE IF EXISTS `one_audit`, `three_audit`, `two_audit` |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE `exec` FROM @`drop_tables`;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE `exec`;
Query OK, 0 rows affected (0.00 sec)

mysql> DEALLOCATE PREPARE `exec`;
Query OK, 0 rows affected (0.00 sec)

你必须小心系统变量 group_concat_max_len .

更新

使用光标:

DELIMITER #

DROP PROCEDURE IF EXISTS `drop_audit_tables`#

CREATE PROCEDURE `drop_audit_tables`()
BEGIN
  DECLARE `done` BOOL DEFAULT 0;
  DECLARE `cmd` VARCHAR(4000);
  DECLARE `cmds` CURSOR FOR
  SELECT
    CONCAT('DROP TABLE IF EXISTS `', `TABLE_NAME`, '`')
  FROM
    `information_schema`.`TABLES`
  WHERE
    `TABLE_SCHEMA` = DATABASE() AND
    `TABLE_TYPE` = 'BASE TABLE' AND
    `TABLE_NAME` LIKE '%_audit';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET `done` := 1;

  OPEN `cmds`;

  `tLoop`: LOOP
    FETCH `cmds` INTO `cmd`;
    IF `done` THEN
      CLOSE `cmds`;
      LEAVE `tLoop`;
    END IF;
    SET @`cmd` := `cmd`;
    PREPARE `STMT` FROM @`cmd`;
    EXECUTE `STMT`;
    DEALLOCATE PREPARE `STMT`;
  END LOOP `tLoop`;

  SET @`cmd` := NULL;
END#

CALL `drop_audit_tables`#

DELIMITER ;

14.5.1 PREPARE Syntax

PREPARE stmt_name FROM preparable_stmt

...

preparable_stmt is either a string literal or a user variable that contains the text of the SQL statement.

...

关于mysql动态查询执行，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36626962/