数据库脚本可重复执行的思考

通常在项目升级迭代过程中会有许多操作数据库或是同项目发布需要执行的脚本，当一个全新的项目初始化部署时，可以不需要考虑脚本的重复执行问题，只需要初始脚本即可，但是随着不断的迭代，伴随着越来越多的脚本累积，就可能带来更多的问题数据，最终导致几个版本下来，预期的数据可能和我们的设想所不一致，或是因为脚本的重复执行，变更了（或是重新初始化）用户已经设定好的数据，这时候，脚本的可重复执行就尤为重要。

脚本重复执行需要考虑到的几个问题点：

1. 多次执行是否会报错
2. 多次执行是否会造成数据丢失/增加
3. 多次执行是否会覆盖已有数据（初始化不需要考虑）

在迭代部署过程种常碰到的脚本类型

• 新增表
• 添加/修改字段
• 修改字段名称
• 修改字段类型
• 新增字典类型数据
• 新增配置类型数据
• 多脚本执行前后的重复数据

以下针对上述几点做实例演示

例表

CREATE TABLE `sys_parameter` (
    `param_code` varchar(64) NOT NULL COMMENT  '参数代码'，
    `param_value` varchar(64) NOT NULL COMMENT  '参数内容'，
    PRIMARY KEY (`param_code`)
)

新增数据表

在新增数据表时，经常在create之前习惯加上：

DROP TABLE IF EXISTS `sys_parameter`;

语意很简单，如果表存在，那么就删掉。但是如果是迭代版本中仍然执行了此脚本，那么会造成什么问题？
也许这个表用户已经使用，并且存在了大量的数据，那么这句话将直接导致数据被清空，造成严重损失。
在新增表的时候，应当是表存在那么就不可以再次新建，可以使用类似以下脚本：

CREATE TABLE IF NOT EXISTS `sys_parameter` (
    `param_code` varchar(64) NOT NULL COMMENT  '参数代码'，
    `param_value` varchar(64) NOT NULL COMMENT  '参数内容'，
    PRIMARY KEY (`param_code`)
)

区别在于create时判断表是否已经存在，如果存在就不再创建

添加修改字段

添加及修改字段同样都需要判断字段是否已存在或是已经被修改过了，不加以判断，则会执行报错，可以写一个简单的存储过程：

DROP PROCEDURE IF EXISTS alter_sys_parameter;
delimiter $$
CREATE PROCEDURE alter_sys_parameter()
BEGIN
    SELECT (SELECT DATABASE()) INTO @databaseName;
    IF EXISTS (
            SELECT 1 FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = @databaseName
            AND TABLE_NAME = `sys_parameter`
        )  AND NOT EXISTS (
            SELECT 1 FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @databaseName
            AND TABLE_NAME = `sys_parameter`
            AND COLUMN_NAME = `param_code`
        ) THEN
    ALTER TABLE  sys_parameter ADD COLUMN `param_code` varchar(64) NOT NULL COMMENT '参数代码';
    END IF;
END $$
delimiter;
CELL alter_sys_parameter();
DROP PROCEDURE IF EXISTS alter_sys_parameter;

存储过程的主要用途在于判断表和字段是否已经存在，字段不存在的情况下进行add

修改字段名称和修改字段类型

两者同属于对某一个字段进行操作，处理方式类似

DROP PROCEDURE IF EXISTS alter_sys_parameter;
delimiter $$
CREATE PROCEDURE alter_sys_parameter()
BEGIN
    SELECT (SELECT DATABASE()) INTO @databaseName;
    IF EXISTS (
            SELECT 1 FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @databaseName
            AND TABLE_NAME = `sys_parameter`
            AND COLUMN_NAME = `param_code_up`
        ) THEN
    ALTER TABLE  sys_parameter MODIFY COLUMN `param_code_up` varchar(64) NOT NULL COMMENT '参数代码';
    END IF;
END $$
delimiter;
CELL alter_sys_parameter();
DROP PROCEDURE IF EXISTS alter_sys_parameter;

处理方式相对简单，修改字段名需要判断新的字段名是否存在，而修改字段类型或是其他只需要判断当前字段是否存在

新增字典类型数据

字典类型的数据相对来说较为稳定，基本设定一次不会发生变化，处理方式也相对简单

DELETE FROM sys_parameter WHERE param_code = 'test_code';
INSERT INTO sys_parameter (param_code, param_value) VALUES ('test_code', 'test_value');

删除重新插入即可

新增配置类型数据

配置类型数据和字段类似，不同点在于配置内容会交由用户自己更改，这时候再采用字典的处理方式，就会将用户设置的内容覆盖，严重的甚至会发生“生产事故”
处理方式需要变通一下，但是万变不离其宗，如果数据存在，则不修改

INSERT INTO sys_parameter (param_code, param_value) 
  SELECT  'test_code', 'test_value'  WHERE NOT EXISTS (
    SELECT 1 FROM sys_parameter WHERE param_code = 'test_code' AND param_value= 'test_value');

将需要插入的值作为select查询出来，在查询的时候做判断，如果存在就不会再次插入了

多脚本执行前后的重复数据

通过以上方式添加新的配置参数，当后续的脚本修改对应数据的param_code之后，再次执行插入语句仍然会正常执行，那么继续执行后续修改脚本同样修改param_code，如果param_code字段为主键，那么将会报错，这样即使一个脚本完成了可重复执行，但是不能避免多脚本重复执行造成不必要的麻烦

INSERT INTO sys_parameter (param_code, param_value) 
  SELECT  'test_code', 'test_value'  WHERE NOT EXISTS (
    SELECT 1 FROM sys_parameter WHERE param_code = 'test_code' AND param_value= 'test_value');

UPDATE sys_parameter SET param_code="test_value_tmpl" WHERE param_code="test_value";

上述两句重复执行将会报错，主键冲突

更正方法，在修改数据之前确认一下是否库里同时含有param_code="test_value_tmpl" 和param_code="test_value"的数据，如果同时存在则删除param_code="test_value"的数据

DELETE p1 FROM sys_parameter p1 
LEFT JOIN sys_parameter p2 
ON P1.module_code = p2.module_code 
WHERE p1.param_code="test_value" AND p2.param_code="test_value_tmpl";