mysqlアップグレードテーブル構造のストレージ・プロシージャ
1022 ワード
DROP PROCEDURE IF EXISTS add_field;
CREATE PROCEDURE add_field(db_name VARCHAR(50),table_name VARCHAR(50),field_name VARCHAR(50),data_type VARCHAR(50),allow_null VARCHAR(50))
BEGIN
DECLARE m_count int DEFAULT 0;
DECLARE v_sql VARCHAR(500);
SET v_sql= CONCAT_WS( ' ','alter table ',table_name,' add ',field_name,data_type,allow_null);
#whether the table has this filed
SELECT count(*) INTO m_count
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = db_name
AND TABLE_NAME = table_name
AND COLUMN_NAME = field_name;
#if does't has this filed,then add it
IF(m_count=0)THEN
SET @sqlcmd = v_sql;
PREPARE stmt FROM @sqlcmd;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END;
call add_field('dbname','tablename','fieldname','datatype','not null');