Mysql检查列是否存在并新增、修改、删除列

时间 2019-12-05 标签 mysql 检查列是否存在新增修改删除列

在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)');

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;

sql语句：

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';