mysql动态新增字段

使用PREPARE预处理语句动态新增字段，先判断表的字段是否存在，如果存在不新增，反之新增。

-- 1. 动态新增字段(储存过程)

; -- 结束符号

DROP procedure if EXISTS sp_add_column;

/* 
动态新增字段，如果存在则不新增
tablename 表名
columnname 字段
add_sql 新增字段的语句
*/

create procedure sp_add_column(in tablename varchar(50),in columnname varchar(50), in add_sql VARCHAR(500)) 
BEGIN

-- MySQL判断字段是否存在
set @is_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE TABLE_NAME = tablename AND COLUMN_NAME = columnname);

if @is_exists = 0 THEN	
set @temp_sql = add_sql;
PREPARE stmt from @temp_sql; 
EXECUTE stmt;
-- 执行
DEALLOCATE PREPARE stmt; 
-- select add_sql;	

END IF;

-- SELECT @tablename,@columnname,@is_exists, add_sql;

end 
; -- 结束符号

CREATE TABLE if not EXISTS demo( id int(11) , PRIMARY KEY(id));

set @tablename = 'demo',@columnname = 'id3';
set @add_sql = CONCAT('ALTER TABLE ',@tablename,' add ',@columnname,' int(10) AFTER id; ');
-- select @add_sql;
call sp_add_column(@tablename,@columnname,@add_sql);
DESC demo;
-- DROP procedure if EXISTS sp_add_column;

　　

 

-- 2. 动态新增字段(sql)

CREATE TABLE if not EXISTS demo( id int(11) , PRIMARY KEY(id));

set @tablename = 'demo',@columnname = 'id4';
set @add_sql = CONCAT('ALTER TABLE ',@tablename,' add ',@columnname,' int(10) AFTER id; ');
-- MySQL判断字段是否存在
set @is_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname);
set @temp_sql = (select case @is_exists WHEN 1 THEN ';' ELSE @add_sql END);

-- select @temp_sql;	

PREPARE stmt from @temp_sql; 
EXECUTE stmt;
-- 执行
DEALLOCATE PREPARE stmt; 
DESC demo;