由于ADD COLUMN IF NOT EXISTS这个语法是PostgreSQL中的特性,并不是MySQL的标准语法,于是用下面的方法添加列。
现在要对TABLE_NAME及COLUMN_NAME作为参数传递,求修改后的写法。
DELIMITER //
CREATE PROCEDURE AddMobileColumn()
BEGIN
DECLARE column_exists INT;
SET @query = CONCAT('SELECT COUNT(*) INTO @column_exists FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''Student'' AND COLUMN_NAME = ''mobile''');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
IF @column_exists = 0 THEN
ALTER TABLE Student ADD COLUMN mobile VARCHAR(50) DEFAULT NULL;
END IF;
END;//
DELIMITER ;
CALL AddMobileColumn();