动态更改表中的Mysql调用过程失败 [英] Mysql calling procedure failed when dynamically alter table in it
问题描述
我想根据表是否具有特定的列来动态更改表.
I want to alter my tables dynamically based on whether the table has specific column.
我的数据库名称是summer_cms
,其中有50多个表.
My database name is summer_cms
, and there are over 50 tables in it.
我想要的东西如下:
- 如果表中有名为
add_time
的列,那么我想在其中添加列add_user_id
. - 类似地,如果找到了
update_time
,我想在表中添加update_user_id
.
- If a table has a column named
add_time
, then I would like to add a columnadd_user_id
in it. - Similarly, I would like to add
update_user_id
in the table ifupdate_time
is found.
我知道在创建数据库模式的过程中应该把它记下来,但是我的数据库已经构建好了,我必须根据需要对其进行更改.
I know I should get it down in the process of creating the database schemas, but my database has been built and I have to alter it by need.
所以我创建了一个程序来做:
So I create a procedure to do it:
CREATE PROCEDURE ALTER_SUMMER_TABLE()
BEGIN
DECLARE tableName VARCHAR(64);
DECLARE exitence VARCHAR(64);
DECLARE ntable INT; # number of tables
DECLARE i INT; # index
SET i = 0;
# get the count of table
SELECT COUNT(DISTINCT(TABLE_NAME)) INTO ntable FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'summer_cms';
WHILE i < ntable DO
# select the specific table name into the variable of `tableName`.
SELECT TABLE_NAME INTO tableName
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'summer_cms'
AND COLUMN_NAME = 'add_time'
LIMIT 1 OFFSET i;
# alter table, but I get error in this clause.
ALTER TABLE tableName ADD COLUMN `add_user_id` INT NOT NULL DEFAULT 0 COMMENT 'add user id';
# check if the table has `update_time`
SELECT TABLE_NAME INTO exitence
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'summer_cms'
AND TABLE_NAME = tableName
AND COLUMN_NAME = 'update_time';
# add `update_user_id` if `update_time` be found.
IF exitence THEN
ALTER TABLE tableName ADD COLUMN `update_user_id` INT NOT NULL DEFAULT 0 COMMENT 'update user id';
END IF;
SET i = i + 1;
END WHILE;
END
但是调用此过程时出现错误.
But I got an error when I call this procedure.
Procedure execution failed
1146 - Table 'summer_cms.tableName' doesn't exist
有人能告诉我我想念什么或做错了什么吗?任何帮助将不胜感激.
Dose anyone could tell me what I was missing or wrong? Any help will be appreciated.
推荐答案
您可以对过程进行一些更改,以使其更加简化并解决一些问题.
There a a few alterations you can make to your procedure to make it more streamlined as well as getting round a few problems.
首先使用光标选择表名,而不是使用两个选择您要使用的表名.其次使用准备好的语句来允许您动态设置表名...
First using a cursor to select the table names rather than using the two selects your using. Secondly to use a prepared statement to allow you to dynamically set the table name...
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ALTER_SUMMER_TABLE`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tableName VARCHAR(64);
declare cur cursor for SELECT TABLE_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'summer_cms'
AND COLUMN_NAME = 'add_time';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur;
start_loop: loop
fetch cur into tableName;
if (done = 1 )THEN
LEAVE start_loop;
END IF;
SET @sql = CONCAT('ALTER TABLE ', tableName,' ADD COLUMN `add_user_id` INT NOT NULL DEFAULT 0 ');
PREPARE stmt FROM @sql;
EXECUTE stmt;
end loop;
close cur;
END$$
DELIMITER ;
您可以做一些调整-例如仅获取不存在该列的表名.
You could do a few tweaks - only fetch table names where the column doesn't already exist for example.
这篇关于动态更改表中的Mysql调用过程失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!