MySQL数据库判断包含年度月份的表是否存在、字段是否存在，不存在则创建

最新推荐文章于 2025-06-07 15:54:52 发布

原创最新推荐文章于 2025-06-07 15:54:52 发布 · 397 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

说明

接上篇，此篇描述MySQL的写法

存储过程

DROP PROCEDURE IF EXISTS auditchange;
CREATE PROCEDURE auditchange() 
BEGIN
DECLARE VCOUNT1 INT;
DECLARE EXISTFLAG INT;
DECLARE audittable varchar(20);
DECLARE sqlstring varchar(200);
SET audittable = CONCAT('gspaudit', substring(curdate()+0, 3, 4));
SET sqlstring = CONCAT('alter table ', audittable, ' add column seclevel VARCHAR(256)');
	SELECT COUNT(1) INTO VCOUNT1 FROM information_schema.tables WHERE table_name = audittable;
	IF (VCOUNT1 > 0) 
    THEN
      SELECT COUNT(1) INTO EXISTFLAG FROM information_schema.columns WHERE column_name='seclevel' AND table_name=audittable;
        IF EXISTFLAG = 0 THEN  
           SET @SQL1 = sqlstring;
           PREPARE stmt1 FROM @SQL1;       
           EXECUTE stmt1;
        END IF;
	END IF;
END;
CALL auditchange();