mysql判断字段是否存在不存在则添加

mysql 判断字段是否存在, 不存在则添加

Fuly1024

3790人浏览 · 2022-11-15 19:25:42

Fuly1024 · 2022-11-15 19:25:42 发布

表存在则删除
DROP TABLE IF EXISTS oil_enterprise;

字段存在则添加原著在这 : https://blog.csdn.net/lihaiyong92/article/details/109642946

-- 存在 pro_AddColumn 则删除
DROP PROCEDURE IF EXISTS pro_AddColumn;
DELIMITER $$
-- 创建 pro_AddColumn
CREATE PROCEDURE pro_AddColumn(
	IN tablename VARCHAR(50),
	IN colname VARCHAR(50),
	IN coltype VARCHAR(20),
	IN coldesc VARCHAR(50) )

BEGIN
	if not exists(select 1 from information_schema.`COLUMNS` where table_name=tablename and column_name=colname) then
		set @sqlStr = CONCAT('alter table ',  tablename, ' add ', colname, ' ',coltype,'  null', ' COMMENT ',  coldesc);
    prepare sqlStr from @sqlStr;
    execute sqlStr;
	end if;
END $$
DELIMITER $$


-- 表名, 字段名, 字段类型, 注释
CALL pro_AddColumn('demo1', 'audit_user_id2', 'BIGINT', '\'审核员id\'');
-- CALL pro_AddColumn( 'demo1', 'audit_user_name', 'VARCHAR(20)', '\'审核员姓名\'');
-- 执行完删除存储过程
DROP PROCEDURE pro_AddColumn;