mysql alter能加入变量,ALTER TABLE脚本中的MySQL变量

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 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();

Thanks in advance.

解决方案

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.

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;