如何不改表结构动态扩展字段？

小鱼儿 2022-10-11 03:41 5阅读 0赞

**点击关注公众号，Java干货****及时送达**![b85e43a1fa68b8024e9dd63528dac89a.png][]

*作者：李瑜宁  
来源：https://juejin.cn/post/6844903640990220302*

> 笔者的动态字段扩展解决方案主要针对 Mysql 5.7.8 以下版本，在 Mysql 5.7.8 已经新增 JSON Data Type，同样适用该方案，而且情况变得更加简单。

## 痛点 ##

**软件行业唯一不变的就是变化**，比如功能上线之后，客户或 PM 需要对已有的功能增加一些合理的需求，完成这些工作必须通过添加字段解决，或者某些功能的实现需要通过增加字段来降低实现的复杂性等等。

这些问题都会改动线上的数据库表结构，一旦改动就会导致锁表，会使所有的写入操作一直等待，直到表锁关闭，特别是对于数据量大的热点表，添加一个字段可能会因为锁表时间过长而导致部分请求超时，这可能会**对企业间接造成经济上的损失**。

## 解决方案 ##

**增加 json 格式的扩展字段。**

下面配合一些代码来描述这个解决方案，读者便于去理解。另外，MySQL 系列面试题和答案全部整理好了，微信搜索Java技术栈，在后台发送：面试，可以在线阅读。

[mysql 数据库脚本：][mysql]

DROP TABLE IF EXISTS `cs_dustbin`;
    CREATE TABLE IF NOT EXISTS `cs_dustbin` (
      `id` VARCHAR(45) NOT NULL COMMENT '主键自增id',
      `rfid_no` VARCHAR(20) NOT NULL COMMENT 'rfid 卡号',
      `state` INT(1) NOT NULL COMMENT '垃圾桶状态：0：已注销；1：未使用；2：待使用；3：已使用(绑定收集点)；',
      `user_id` INT NOT NULL COMMENT '登记人，负责录入垃圾桶的人',
      `type` INT(1) NOT NULL DEFAULT 1 COMMENT '垃圾桶类型：1：餐厨垃圾桶',
      `street_code` INT(11) DEFAULT NULL COMMENT '所在镇街 code，根据状态，这里的含义可能是领用镇街、退还镇街。',
      `create_time` DATETIME NOT NULL DEFAULT now() COMMENT '创建时间',
      `update_time` DATETIME NOT NULL DEFAULT now() COMMENT '更新时间',
      `ext` VARCHAR(1000) NOT NULL DEFAULT '{}' COMMENT '扩展字段',
      ...
      PRIMARY KEY (`id`))
    ENGINE = InnoDB
    COMMENT = '垃圾桶表';

[Java 代码：][mysql]

import com.alibaba.fastjson.JSON;
    import lombok.Data;
    
    import javax.validation.constraints.NotNull;
    import java.util.Date;
    import java.util.List;
    
    /**
     * 垃圾桶实体
     * Created by Blink on 6/28/2018 AD.
     *
     * @author Blink
     */
    @Data
    public class Dustbin {
    
        private String id;
    
        /**
         * rfid 卡号
         */
        @NotNull
        private String rfidNo;
    
        /**
         * 垃圾桶状态：0：已注销；1：未使用；2：待使用；3：已使用(绑定收集点)；
         * 对应 Dustbin.StateEnum 类
         */
        @NotNull
        private Integer state;
    
        /**
         * 录入垃圾桶的人员id
         */
        @NotNull
        private Long userId;
    
        /**
         * 垃圾桶类型：1：餐厨垃圾桶
         * DefaultValue: 1
         */
        @NotNull
        private Integer type;
    
        /**
         * 所在镇街 code
         * 根据状态，这里的含义可能是领用镇街、退还镇街
         */
        private Integer streetCode;
    
        /**
         * 创建时间
         * defaultValue : now()
         */
        @NotNull
        private Date createTime;
    
        /**
         * 更新时间
         */
        @NotNull
        private Date updateTime;
    
        /**
         * 扩展字段，详细数据查看 DustbinExt.java
         * DefaultValue: {}
         */
        private String ext;
    
        ...
    
        public DustbinExt getExtObject() {
            return JSON.parseObject(this.getExt(), DustbinExt.class);
        }
    
        public void setExtObject(DustbinExt ext) {
            this.ext = JSON.toJSONString(ext);
        }
    
        /**
         * 垃圾桶扩展属性
         * Created by Blink on 6/28/2018 AD.
         *
         * @author Blink
         */
        @Data
        public static class DustbinExt {
    
            /**
             * 所在镇街
             * 根据状态，这里的含义可能是领用镇街、退还镇街、绑定的镇街
             */
    
            private String street;
    
            /**
             * 客户（收集点）id，绑定收集点的时候需要填入
             * 根据目前的需求（2018-06-29），当收集点解绑的时候
             * 需要保存垃圾桶最新绑定收集点名称，所以在解绑垃圾桶的时候不会把这个信息删掉
             * 只有当绑定收集点的时候才把他覆盖
             */
            private Long customerId;
    
            /**
             * 客户（收集点）名称，绑定收集点的时候需要填入
             * 根据目前的需求（2018-06-29），当收集点解绑的时候
             * 需要保存垃圾桶最新绑定收集点名称，所以在解绑垃圾桶的时候不会把这个信息删掉
             * 只有当绑定收集点的时候才把他覆盖
             */
            private String customer;
    
            /**
             * 损坏部位
             * 1：桶盖；2：桶口；3：桶身；4：桶轴；5：桶底；6：桶轮；
             * 对应 DustbinDamagePartEnum 类
             */
            private List<Integer> parts;
        }
    
        ...
    }

[mysql 脚本可以看到扩展字段的信息：][mysql]

ext VARCHAR(1000) NOT NULL DEFAULT '{}' COMMENT '扩展字段'

[可以看到这么一段 Java 代码：][Java]

...
    
    /**
     * 扩展字段，详细字段查看 DustbinExt 类
     * DefaultValue: {}
     */
    private String ext;
    
    public DustbinExt getExtObject() {
        return JSON.parseObject(this.getExt(), DustbinExt.class);
    }
    
    public void setExtObject(DustbinExt ext) {
        this.ext = JSON.toJSONString(ext);
    }
    
    ...

[可以看到][Java] `ext` 字段就是用来存储 json 格式的数据，它可以动态地增加任何字段，甚至是对象，不需要通过 DDL（Data Definition Language） 去创建字段，非常适合用来解决上面提到的问题。

Java 代码在这里起到辅助性作用，通过定义一个内部类来管理扩展字段的属性，方便我们了解和管理扩展字段，提高代码的可读性和可维护性，java 这种方式也是笔者总结出来的较为优雅的做法（个人观点）。

## 局限性 ##

有经验的读者可能会提出，`ext` 字段在 Mysql 5.7.8 以下版本无法对扩展字段中的某一个或一部分字段建立索引，因为 Mysql 5.7.8 版本以下不支持（Mysql 5.7.8 支持为 Json Data Type 建立索引）。[*MySQL数据库开发的 36 条军规！*][MySQL_ 36]

没错，这是这个解决方案的一个局限性，在 Mysql 5.7.8 以下版本，我的建议是， **ext 扩展字段不要存储热点数据，只存储非热点数据**，这样就可以避免查询操作，降低维护 `ext` 字段带来的成本和风险，那如何识别新增字段是不是热点数据呢？

这个需要结合实际业务需求来判断，也可以询问对业务和技术更有经验的同事，便于读者更快得出结论。

## 终极版解决方案 ##

在一些极端的情况下，变化可能来得太快，而我们要的是减少变化带来的成本和风险，所以在表设计之初可以根据自身经验，或者找更有经验的人寻求帮助，预估一下需要预留多少个备用字段，再配合扩展字段，基本上可以把改变（添加字段）表结构的次数降至一个非常少的次数。

## 总结 ##

在特殊情况下，通过扩展字段 + 预留字段基本上可以做到动态扩展字段，又不会影响为热点数据建立索引的情况，这样我们得到了一个非常灵活的表结构，便于我们应对未来的变化，但是请注意，要维护好我们的实体，包括里面的每一个字段，敬畏每一行代码。

最后，关注公众号Java技术栈，在后台回复：面试，可以获取我整理的 MySQL 系列面试题和答案，非常齐全。

![396d89207a9d308617e7dce89d708ece.gif][]

![321b30874b6d7c0a8f345d7af795447b.png][]

[![826768ee8d7c622a93b3c7de01f0397d.png][]][826768ee8d7c622a93b3c7de01f0397d.png 1]

[![a1557d967b93c9de47d21a06837a8261.png][]][a1557d967b93c9de47d21a06837a8261.png 1]

[![33e0bcd3528e3546a08b24509870533e.png][]][33e0bcd3528e3546a08b24509870533e.png 1]

[![f52c70519a2908c852ef4e3c5003ef4f.png][]][f52c70519a2908c852ef4e3c5003ef4f.png 1]

[![7ca9b9bcdf73369a62368ffa20f85a2a.png][]][7ca9b9bcdf73369a62368ffa20f85a2a.png 1]

**关注Java技术栈看更多干货**

![a38de6764947ee6cbd08754f182c6026.png][]

![f896c61db87a9f1ad81104fd9c7dc4a6.gif][]

获取 Spring Boot 实战笔记！

[b85e43a1fa68b8024e9dd63528dac89a.png]: /images/20221005/240e59fbe7c048ecbc321ad909355b20.png
[mysql]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb50cefddc2747ebf647ce65467630ba9f010b1558f9bd5e5156700bcdc17baf788ad0c94a86&idx=2&mid=2247535563&scene=21&sn=2f28d811947f61f9690336b433831c19#wechat_redirect
[Java]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb50cc31dc274527c7a10d055d8c01cee03e4e3944ea85ef831d7986e28710e9467e44954eb2&idx=2&mid=2247534855&scene=21&sn=c9b956c95ee63704f2362ad382fe412c#wechat_redirect
[MySQL_ 36]: http://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&chksm=eb538febdc2406fd70748604c500eeaea584306c137cb2020ab5a72187c9359926015e00a46d&idx=2&mid=2247486173&scene=21&sn=0603756b36279b5f5f036151af85f9fd#wechat_redirect
[396d89207a9d308617e7dce89d708ece.gif]: /images/20221005/55ea190a492f45608ce8d9389c27297f.png
[321b30874b6d7c0a8f345d7af795447b.png]: /images/20221005/1fce6cf6f7b747a1ba784d0bb62b6408.png
[826768ee8d7c622a93b3c7de01f0397d.png]: /images/20221005/39ea3ea1f2be4c7b8a9a70013fe86318.png
[826768ee8d7c622a93b3c7de01f0397d.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247535009&scene=21&sn=5a0ac485739f9ee7be02bbe938ff0eb3#wechat_redirect
[a1557d967b93c9de47d21a06837a8261.png]: /images/20221005/d22ef14e9185497ebd55f8a02ef064ae.png
[a1557d967b93c9de47d21a06837a8261.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247534855&scene=21&sn=fdaf7ea70fca4597d3cc4e18f5ada334#wechat_redirect
[33e0bcd3528e3546a08b24509870533e.png]: /images/20221005/cac7ac48befd444f9dc9e21d57c386a3.png
[33e0bcd3528e3546a08b24509870533e.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247530418&scene=21&sn=e6a0138c7c2f9a9ff6a8b4c878386211#wechat_redirect
[f52c70519a2908c852ef4e3c5003ef4f.png]: /images/20221005/1dff783cabae4f0bbeb87961a46b3c2b.png
[f52c70519a2908c852ef4e3c5003ef4f.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247535062&scene=21&sn=c34b69e41525dbcb3c473c6986daa801#wechat_redirect
[7ca9b9bcdf73369a62368ffa20f85a2a.png]: /images/20221005/a2f2702cba034b09af526d0d6aca8e77.png
[7ca9b9bcdf73369a62368ffa20f85a2a.png 1]: https://mp.weixin.qq.com/s?__biz=MzI3ODcxMzQzMw%3D%3D&idx=1&mid=2247535563&scene=21&sn=b2a1de775b95890690e3295aa7e6b253#wechat_redirect
[a38de6764947ee6cbd08754f182c6026.png]: /images/20221005/19f408a91cf840d59bc2810937fa8a90.png
[f896c61db87a9f1ad81104fd9c7dc4a6.gif]: /images/20221005/40dcf201fd8f4b11ad3e4a98f120bf3e.png