mysql升级表结构的存储过程
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 filedSELECT count(*) INTO m_count FROM information_schema.COLUMNSWHERE TABLE_SCHEMA = db_nameAND TABLE_NAME = table_nameAND 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','DATETIME','not null');