【MySQL】升级增量脚本的编写

升级现场的数据库脚本，因为不同现场的版本不一致，所以如果是要升级，需要找到对应的版本，然后一个一个增量脚本升级，但是有的时候有一些现场是直接修改的，不一定是满足某个版本的实际情况，所以想了一个办法，推出升级的所有增量脚本。

总的思路就是如果不存在就添加。具体涉及到：表的新建、字段的新建和修改和基础数据的插入。

表的新建

判断一张表是否存在，不存在就新建，这个比较简单，如下：

CREATE TABLE IF NOT EXISTS T_TEST  (
  `id` varchar(64)  NOT NULL COMMENT '主键ID',
  `code` varchar(64)  NOT NULL COMMENT '编码',
  `name` varchar(64)  NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

就是相较于以前普通的

CREATE TABLE T_TEST (
  `id` varchar(64)  NOT NULL COMMENT '主键ID',
  `code` varchar(64)  NOT NULL COMMENT '编码',
  `name` varchar(64)  NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

增加了IF NOT EXIST的判断。

还有很多的脚本是先DROP TABLE删除表后添加，但是有的现场可能已经创建了表并且在使用，所以不能直接DROP，于是就是先判断再创表是最合适的。

字段的新建和修改

字段的修改比较简单，就是

ALTER TABLE t_test MODIFY COLUMN CODE varchar(32) NULL COMMENT ‘编码';

因为修改字段不会跟原来的有冲突，只需要放在创建表的后面就可以了。

字段的新建也需要判断字段是否存在，这个需要通过存储过程完成

DROP PROCEDURE IF EXISTS add_col_homework; -- 删除已经存在的同名存储过程
CREATE PROCEDURE add_col_homework() BEGIN
-- 检查字段是否存在，不存在就新增
IF not EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 't_test' and column_name = 'mark') THEN
    ALTER TABLE t_test ADD COLUMN `mark` varchar(128) NULL COMMENT '备注';
END IF;
END;

call add_col_homework();-- 运行该存储过程

drop PROCEDURE add_col_homework; -- 删除该存储过程

这个直接放到脚本中跑会报ERROR 1064 (42000)的错误。因为;是sql执行的结束符，会导致sql执行器错误判断存储过程的执行流程，所以需要改一下

-- 声明结束符为$$
DELIMITER $$
-- 删除已经存在的同名存储过程
DROP PROCEDURE IF EXISTS add_col_homework
$$
CREATE PROCEDURE add_col_homework() BEGIN
-- 检查字段是否存在，不存在就新增
IF not EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 't_test' and column_name = 'mark') THEN
    ALTER TABLE t_test ADD COLUMN `mark` varchar(128) NULL COMMENT '备注';
END IF;
END;
$$
-- 重新声明结束符为;
DELIMITER ;


call add_col_homework();-- 运行该存储过程


drop PROCEDURE add_col_homework; -- 删除该存储过程

这个坑找了很久才发现问题，这样字段的添加也完成了。

初始数据的插入

数据的插入就是要判断表的唯一索引对应的数据是否存在

INSERT INTO t_test(id,code,name,mark)
SELECT 'cfb9cf5e461346bf8e9f9883e96e1364', 'paul', '王大强', '不是隔壁老王，安全' FROM DUAL WHERE NOT EXISTS(SELECT * FROM
t_test WHERE id = 'cfb9cf5e461346bf8e9f9883e96e1364');

脚本集成

集成上面的内容，一个可以安全重复执行的脚本如下

-- 新建表
CREATE TABLE IF NOT EXISTS T_TEST  (
  `id` varchar(64)  NOT NULL COMMENT '主键ID',
  `code` varchar(64)  NOT NULL COMMENT '编码',
  `name` varchar(64)  NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- 修改字段
ALTER TABLE t_test MODIFY COLUMN CODE varchar(32) NULL COMMENT '编码';

-- 新建字段
-- 声明结束符为$$
DELIMITER $$
-- 删除已经存在的同名存储过程
DROP PROCEDURE IF EXISTS add_col_homework
$$
CREATE PROCEDURE add_col_homework() BEGIN
-- 检查字段是否存在，不存在就新增
IF not EXISTS (SELECT column_name FROM information_schema.columns WHERE table_name = 't_test' and column_name = 'mark') THEN
    ALTER TABLE t_test ADD COLUMN `mark` varchar(128) NULL COMMENT '备注';
END IF;
END;
$$
-- 重新声明结束符为;
DELIMITER ;


call add_col_homework();-- 运行该存储过程


drop PROCEDURE add_col_homework; -- 删除该存储过程

-- 插入数据
INSERT INTO t_test(id,code,name,mark)
SELECT 'cfb9cf5e461346bf8e9f9883e96e1364', 'paul', '王大强', '不是隔壁老王，安全' FROM DUAL WHERE NOT EXISTS(SELECT * FROM
t_test WHERE id = 'cfb9cf5e461346bf8e9f9883e96e1364');