mysql 存储过程更改表结构_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 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');

分享到：

2013-07-24 09:26

浏览 566

分类:数据库

评论