[大数据]Mysql 根据if条件判断是否执行sql语句

需求例如：

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();

参考：https://segmentfault.com/a/1190000014973591