前段时间学习MYSQL,发现MYSQL与SQL2008有些语法区别,在MYSQL中,新增、修改、删除列时不能进行IF EXISTS判断,IF语句只能出现在存储过程当中,故博主用存储过程的方法新增、修改、删除列,修改列名称。
DROP PROCEDURE IF EXISTS Pro_ColumnWork;
DELIMITER$$
-- 1表示新增列,2表示修改列类型,3表示修改列名称,4表示删除列
CREATE PROCEDURE Pro_ColumnWork(TableName VARCHAR(50),ColumnName VARCHAR(50),CType INT,SqlStr VARCHAR(4000))
BEGIN
DECLARE Rows1 INT;
SET Rows1=0;
SELECT COUNT(*) INTO Rows1 FROM INFORMATION_SCHEMA.Columns
WHERE table_schema= DATABASE() AND table_name=TableName AND column_name=ColumnName;
-- 新增列
IF (CType=1 AND Rows1<=0) THEN
SET SqlStr := CONCAT( 'ALTER TABLE ',TableName,' ADD COLUMN ',ColumnName,' ',SqlStr);
-- 修改列类型
ELSEIF (CType=2 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' MODIFY ',ColumnName,' ',SqlStr);
-- 修改列名称
ELSEIF (CType=3 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' CHANGE ',ColumnName,' ',SqlStr);
-- 删除列
ELSEIF (CType=4 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE ',TableName,' DROP COLUMN ',ColumnName);
ELSE SET SqlStr :='';
END IF;
-- 执行命令
IF (SqlStr<>'') THEN
SET @SQL1 = SqlStr;
PREPARE stmt1 FROM @SQL1;
EXECUTE stmt1;
END IF;
END$$
DELIMITER ;
-- CALL Pro_ColumnWork ('BaseInfo','Name2',4,'VARCHAR(50)');
-- CALL Pro_ColumnWork ('BaseInfo','Abc',4,'VARCHAR(30)');