前言

最近在设计表结构时，发现自己总是不能一步到位，总是要增加字段，修改字段之类的，为了记录和方便升级，维护个增量脚本是一个不错的解决方案。

开始

表增量

在建表时，加上IF NOT EXISTS就行了，这样就可重复执行了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 用户表
CREATE TABLE IF NOT EXISTS `t_user` (
    `id` varchar(32) NOT NULL COMMENT '主键',
    `name` varchar(20) DEFAULT NULL COMMENT '昵称',
    `username` varchar(20) DEFAULT NULL COMMENT '用户名',
    `password` varchar(50) DEFAULT NULL COMMENT '密码',
    `head` varchar(32) DEFAULT NULL COMMENT '头像',
    `birthday` date DEFAULT NULL COMMENT '出生年月日',
    `height` float DEFAULT NULL COMMENT '身高',
    `weight` float DEFAULT NULL COMMENT '体重',
    `gender` char(1) DEFAULT NULL COMMENT '性别',
    `input_date` datetime DEFAULT NULL COMMENT '录入时间',
    `modify_date` datetime DEFAULT NULL COMMENT '修改时间',
    CONSTRAINT `pk_t_user_id` PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表，储存用户信息';

其他增量

对于其他增量，例如字段的增加/修改、索引的增加/修改，需要定义一个存储过程来进行判断

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
DROP PROCEDURE IF EXISTS schema_update; -- 删除已经存在的同名存储过程
CREATE PROCEDURE schema_update() BEGIN
-- ************开始**************

-- 为表添加 is_deleted 字段
IF not EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() and table_name = 't_user' and column_name = 'is_deleted') THEN
  ALTER TABLE `t_user` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志';
  ALTER TABLE `t_recipe` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志';
  ALTER TABLE `t_user_recipe` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志';
  ALTER TABLE `t_user_recipe_tag` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志';
  ALTER TABLE `t_food` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志';
  ALTER TABLE `t_food_price` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志';
  ALTER TABLE `t_recipe_food` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志';
  ALTER TABLE `t_recipe_cook_tool` ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志';
END IF;

-- 用户表 修改字段 头像
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() and table_name = 't_user' and column_name = 'head' and character_maximum_length = 20) THEN
  ALTER TABLE `t_user` MODIFY COLUMN `head` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '头像';
END IF;

-- 用户表 用户名 唯一索引
IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = DATABASE() and table_name = 't_user' AND index_name = 'uni_user_username') THEN
   alter table `t_user` add unique index `uni_user_username` (`username`);
END IF;

-- ************结束**************
END;
call schema_update();-- 运行该存储过程
drop PROCEDURE schema_update; -- 删除该存储过程


参考文章

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

mysql数据库升级脚本规范(可重复执行)