在MYSQL中,新增、修改、删除列时不能进行IF EXISTS判断,IF语句只能出现在存储过程当中,故博主用存储过程的方法新增、修改、删除列,修改列名称。
Mysql没有直接的语法可以在增加列前进行判断该列是否存在,需要写一个存储过程完成同样任务,
sql语句:
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)');
Mysql没有直接的语法可以在增加列前进行判断该列是否存在,需要写一个存储过程完成同样任务,
下面例子是:在T_DT_HOMEWORK表中增加一列RECOMMEND_RECORD列 :
create procedure add_col_homework() BEGIN
IF EXISTS (SELECT column_name FROM information_schema.columns WHERE column_name = 'T_DT_HOMEWORK' AND column_name = 'RECOMMEND_RECORD')
THEN
ALTER TABLE `T_DT_HOMEWORK` DROP COLUMN `RECOMMEND_RECORD`;
END IF;
ALTER TABLE `T_DT_HOMEWORK` ADD COLUMN `RECOMMEND_RECORD` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `RECOMMEND_ORG_CODE`;
END;
drop procedure if exists add_col_homework;
SELECT
COLUMN_NAME,
DATA_TYPE,
IS_NULLABLE,
COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_name'
AND table_schema = 'database_name' ;
SELECT
COUNT(COLUMN_NAME)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_name'
AND table_schema = 'database_name'
AND COLUMN_NAME = 'column_name';