Mysql存储过程在sql查询中使用游标获取变量

Question

我定义了以下过程。

create procedure deleteData()
   begin
     DECLARE no_tbls INT;
     DECLARE tbl VARCHAR(64);
     DECLARE tbls_cr CURSOR for SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema='db';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_tbls=1;
    OPEN tbls_cr;
    SET no_tbls=0;
    while no_tbls=0 do
      fetch tbls_cr into tbl;
      select tbl.updated_at from tbl limit 1;
    end while;
    close tbls_cr;
  end

运行此过程后，我收到错误db.tbl不存在。所以我正在寻找是否有一种方法可以在另一个查询中使用游标获取的对象。我正在做所有这些繁琐的事情的问题是我想使用特定的 where 子句从数据库的所有表中删除数据。注意:所有表都有一个带有日期格式的 updated_at 列。(我是 MySQL 存储过程的新手)。

Answer 1

在普通的 select 语句中，不能用变量代替 from 子句中的表名，mysql 将在数据库中查找名为 tbl 的表。

您需要使用字符串连接和 prepared statements动态创建并执行sql语句:

mysql> USE test;

mysql> CREATE TABLE t1 (a INT NOT NULL);

mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';

mysql> SET @s = CONCAT('SELECT * FROM ',@table);

mysql> PREPARE stmt3 FROM @s;

mysql> EXECUTE stmt3;

mysql> DEALLOCATE PREPARE stmt3;

准备好的语句也适用于存储过程，上面的示例演示了如何通过连接字符串文字和变量来创建 sql 语句，准备语句，执行它，然后从内存中释放准备好的语句。