ALTER TABLE脚本中的MySQL变量 [英] MySQL variables in ALTER TABLE script
问题描述
Hello以下过程将必须将所有约束从一个表移动到另一个表,但是我在约束应该删除的时候遇到一些困难。
Hello The following procedure will have to move all constraints from one table to the other however I am having some difficulties at the point where the constraint should be deleted.
问题:如何在下面行中使用变量
The problem: how do I use variables in the following line
ALTER TABLE var_referenced_table_name DROP FOREIGN KEY var_constraint_name;
当我使用时,我收到以下错误
when I use as is, I receive the following error
Error Code: 1146. Table 'oaf_businesslink_dev.var_referenced_table_name' doesn't exist
MySQL无法识别 var_referenced_table_name
和 var_constraint_name
作为变量。
MySQL does not recognise var_referenced_table_name
and var_constraint_name
as variables.
DELIMITER //
DROP PROCEDURE IF EXISTS AlterConstraints//
CREATE PROCEDURE AlterConstraints()
BEGIN
DECLARE schema_name VARCHAR(60) DEFAULT 'oaf_businesslink_dev';
DECLARE table_name VARCHAR(60) DEFAULT 'wp_systemuser';
DECLARE finished INTEGER DEFAULT 0;
DECLARE total INTEGER DEFAULT 0;
DECLARE var_constraint_name VARCHAR(60) DEFAULT '';
DECLARE var_table_name VARCHAR(60) DEFAULT '';
DECLARE var_column_name VARCHAR(60) DEFAULT '';
DECLARE var_referenced_table_name VARCHAR(60) DEFAULT '';
DECLARE var_referenced_column_name VARCHAR(60) DEFAULT '';
DECLARE cur_constraints CURSOR FOR SELECT constraint_Name, table_name,column_name,referenced_table_name,referenced_column_name
FROM information_schema.key_column_usage
WHERE constraint_schema = schema_name
AND referenced_table_name = table_name
AND table_name IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN cur_constraints;
get_constraint:
LOOP FETCH cur_constraints
INTO var_constraint_name
,var_table_name
,var_column_name
,var_referenced_table_name
,var_referenced_column_name;
IF finished THEN
LEAVE get_constraint;
END IF;
/* Get Constraint Count */
SET total = total + 1;
/* Remove Constraint */
IF EXISTS(SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_NAME = var_constraint_name AND TABLE_NAME = var_referenced_table_name AND TABLE_SCHEMA = schema_name)
THEN
/*
* Error Code: 1146. Table 'oaf_businesslink_dev.var_referenced_table_name' doesn't exist
*/
ALTER TABLE var_referenced_table_name DROP FOREIGN KEY var_constraint_name;
END IF;
/* Change Datatype to BIGINT */
/* Recreate Constraint to new table */
END
LOOP get_constraint;
CLOSE cur_constraints;
SELECT total;
END
//
DELIMITER ;
CALL AlterConstraints();
提前感谢。
推荐答案
使用变量作为列名和表,最好将 DECLARE
作为字符串,然后执行该字符串通过 准备的语句
。
With the use of variables as column names and tables, it would be best to DECLARE
a query as a "string" and then execute that string via a Prepared Statement
.
这可以通过两种方式完成: CONCAT()
构建完整的字符串或使用 PREPARE
和参数:
This can be done in two ways, either by CONCAT()
to build the full string or by using PREPARE
with arguments:
SET @query = CONCAT('ALTER TABLE ', var_referenced_table_name, ' DROP FOREIGN KEY ', var_constraint_name, ';');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
这篇关于ALTER TABLE脚本中的MySQL变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!