需求例如:
if device 表中没有subType字段 ALTER TABLE device ADD
subType
varchar(20)
DEFAULT NULL; endif server_kid_num表不存在
CREATE TABLE IF NOT EXISTSserver_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