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