Mysql 根据if条件判断是否执行sql语句

最新推荐文章于 2023-09-17 21:09:40 发布

原创最新推荐文章于 2023-09-17 21:09:40 发布 · 5.3k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql #数据库

该博客介绍了如何使用SQL语句检查并修改数据库表结构。通过创建存储过程，检查device表是否缺少subType字段，并在缺失时进行添加。同时，检查是否存在server_kid_num表，若不存在则创建并插入初始数据。

需求例如：

if device 表中没有subType字段 ALTER TABLE device ADD subType varchar(20)
DEFAULT NULL; end

if server_kid_num表不存在
CREATE TABLE IF NOT EXISTS server_kid_num (
id varchar(33) NOT NULL,
num varchar(33) DEFAULT NULL,
used varchar(33) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `server_kid_num` VALUES ('10', '10', '0');
INSERT INTO `server_kid_num` VALUES ('11', '11', '0'); end

sql：

DELIMITER //
-- 如果存在存储过程（sql函数）FUN20180516则将其删除
DROP PROCEDURE IF EXISTS `FUNTEST` //
CREATE PROCEDURE `FUNTEST` ()
BEGIN
--     声明变量 int hasDb
    DECLARE hasDevice INT;
    DECLARE hasServerKidNum INT;
    DECLARE hasDbType INT;
    
    SELECT count(*) INTO hasDevice FROM information_schema.tables WHERE (table_schema = 'schema') AND (table_name= 'device');
    IF hasDevice = 1 THEN
        SELECT count(*) INTO hasDbType FROM information_schema.columns WHERE (table_schema = 'schema') and (table_name= 'device') and (column_name = 'subType');
        IF hasDbType = 0 THEN
            ALTER TABLE device ADD `subType` varchar(20) DEFAULT NULL;
        end if;
    END IF;
    
    SELECT count(*) INTO hasServerKidNum FROM information_schema.tables WHERE (table_schema = 'schema') AND (table_name= 'server_kid_num');
    IF hasServerKidNum = 0 THEN
        CREATE TABLE  IF NOT EXISTS `server_kid_num` (
		  `id` varchar(33) NOT NULL,
		  `num` varchar(33) DEFAULT NULL,
		  `used` varchar(33) DEFAULT NULL,
		  PRIMARY KEY (`id`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		
		INSERT INTO `server_kid_num` VALUES ('10', '10', '0');
		INSERT INTO `server_kid_num` VALUES ('11', '11', '0');
    END IF;
END //
DELIMITER ;
call FUNTEST();