在MYSQL 中使用存储过程调用Alert Table
在MYSQL 中使用存储过程的时候调用 Alter Table命令修改表中列的数据类型是,系统给报了一个错误,xxx.变量名
表不存在,原来是系统吧变量名当做字符串进行解析了。查询资料发现 ,在MySQL的存储过程中不允许调用ALTER TABLE 变量名,变量名只能被当成字符串处理。如果要在存储过程中对表进行修改,可以使用Dynamic SQL,可以构造一个动态的SQL语句,然后使用MySQL的PREPARE语句对其进行预处理,最后再使用EXECUTE语句执行这条动态SQL。
- 关键代码如下:
SET @query = CONCAT('ALTER TABLE ', tableName, ' MODIFY ', columnName,' TIMESTAMP DEFAULT CURRENT_TIMESTAMP', ';');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
- 完整代码如下:
-- 定义一个有参数的存储过,并传入一个参数表名,修改其中数据类型为datatime的
DROP PROCEDURE IF EXISTS UpdateType;
CREATE PROCEDURE UpdateType(IN tableName VARCHAR(300))
BEGIN
-- 定义变量
DECLARE columnName VARCHAR(300);
DECLARE columnType VARCHAR(300);
DECLARE done INT DEFAULT(0);
-- 创建游标,并存储数据
DECLARE cur_test CURSOR FOR
SELECT column_name, column_type FROM information_schema.columns WHERE table_name = tableName;
-- 游标中的内容执行完后将 done 设置为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- 打开游标
OPEN cur_test;
-- 执行循环
posLoop:LOOP
-- 判断是否结束循环
IF done=1 THEN
LEAVE posLoop;
END IF;
-- 取游标中的值
FETCH cur_test INTO columnName,columnType;
IF columnType='DATETIME' THEN
-- 修改对应表的数据类型
SET @query = CONCAT('ALTER TABLE ', tableName, ' MODIFY ', columnName,' TIMESTAMP DEFAULT CURRENT_TIMESTAMP', ';');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END LOOP posLoop;
-- 释放游标
CLOSE cur_test;
END
-- 定义一个无参数的存储过程并遍历数据库中所有的表
DROP PROCEDURE IF EXISTS UpdateDataType;
CREATE PROCEDURE UpdateDataType()
BEGIN
-- 定义变量
DECLARE tableName VARCHAR(300);
DECLARE done INT DEFAULT(0);
-- 创建游标,并存储数据
DECLARE cur_test CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_schema='demo';
-- 游标中的内容执行完后将 done 设置为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- 打开游标
OPEN cur_test;
-- 执行循环
posLoop:LOOP
-- 判断是否结束循环
IF done=1 THEN
LEAVE posLoop;
END IF;
-- 取游标中的值
FETCH cur_test INTO tableName;
-- 调用UpdateType 去修改对应表的数据类型
CALL UpdateType(tableName);
END LOOP posLoop;
-- 释放游标
CLOSE cur_test;
END
CALL UpdateDataType