The author’s dynamic field extension solution is mainly for Mysql 5.7.8 and below. JSON Data Type has been added in Mysql 5.7.8, which also applies to this solution, and the situation is much simpler.

Pain points

The only constant in the software industry is change. For example, after functions are launched, customers or PM need to add some reasonable requirements for existing functions, which must be solved by adding fields, or the realization of some functions needs to reduce the complexity of implementation by adding fields, etc. These problems will change online database table structure, once changes will lead to lock table, will make all the write operation has been waiting for, until the table lock close, especially for the hot spot of the large amount of data table, add a field may lead to part of the request for lock table time is too long timeout, which may indirectly caused economic losses to the enterprise.

The solution

Added extended fields in JSON format.

The following code describes the solution for the reader to understand.

Mysql database script:

DROP TABLE IF EXISTS `cs_dustbin`;
CREATE TABLE IF NOT EXISTS `cs_dustbin` (
  `id` VARCHAR(45) NOT NULL COMMENT 'Primary key increment ID'.`rfid_no` VARCHAR(20) NOT NULL COMMENT 'the rfid card number'.`state` INT(1) NOT NULL COMMENT 'Trash bin status: 0: logged out; 1: unused. 2: to be used; 3: used (bound collection point) '.`user_id` INT NOT NULL COMMENT 'The registrant, the person who puts it in the trash.'.`type` INT(1) NOT NULL DEFAULT 1 COMMENT 'Trash bin Type: 1: Kitchen trash bin'.`street_code` INT(11) DEFAULT NULL COMMENT Depending on the status, this can mean claim street, return street. '.`create_time` DATETIME NOT NULL DEFAULT now(a)COMMENT 'Creation time'.`update_time` DATETIME NOT NULL DEFAULT now(a)COMMENT 'Update Time'.`ext` VARCHAR(1000) NOT NULL DEFAULT '{}' COMMENT 'Extended field'. PRIMARYKEY (`id`))
ENGINE = InnoDB
COMMENT = 'Trash can List';

Copy the code

Java code:

import com.alibaba.fastjson.JSON;
import lombok.Data;

import javax.validation.constraints.NotNull;
import java.util.Date;
import java.util.List;

/** * Garbage bin entities * Created by Blink on 6/28/2018 AD. **@author Blink
 */
@Data
public class Dustbin {

    private String id;

    /** * RFID card number */
    @NotNull
    private String rfidNo;

    /** * Bin status: 0: deregistered; 1: unused. 2: to be used; 3: used (bound collection point) * Corresponds to dustbin. StateEnum class */
    @NotNull
    private Integer state;

    /** * Id of the garbage collector */
    @NotNull
    private Long userId;

    /** * Garbage bin type: 1: kitchen garbage bin * DefaultValue: 1 */
    @NotNull
    private Integer type;

    /** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** *
    private Integer streetCode;

    /** * defaultValue: now() */
    @NotNull
    private Date createTime;

    /** * update time */
    @NotNull
    private Date updateTime;

    /** * Extended field, detailed data see dustbinext.java * DefaultValue: {} */
    privateString ext; .public DustbinExt getExtObject(a) {
        return JSON.parseObject(this.getExt(), DustbinExt.class);
    }

    public void setExtObject(DustbinExt ext) {
        this.ext = JSON.toJSONString(ext);
    }

    /** * Garbage bin extension properties * Created by Blink on 6/28/2018 AD. **@author Blink
     */
    @Data
    public static class DustbinExt {

        /** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** **

        private String street;

        * According to the current requirement (2018-06-29), when the collection point is unbound *, the name of the latest binding collection point of the garbage can needs to be saved. So this information will not be deleted when untying the garbage bin * only overwrites it when binding the collection point */
        private Long customerId;

        * According to the current requirement (2018-06-29), when the collection point is unbound *, the name of the latest binding collection point of the garbage can needs to be saved. So this information will not be deleted when untying the garbage bin * only overwrites it when binding the collection point */
        private String customer;

        /** ** damaged parts * 1: barrel cover; 2: barrel mouth; 3: barrel body; 4: barrel shaft; 5: bottom of the barrel; 6: barrel wheel; * corresponding to DustbinDamagePartEnum class */
        privateList<Integer> parts; }... }Copy the code

Mysql script can see information about extended fields:

ext VARCHAR(1000) NOT NULL DEFAULT '{}' COMMENT 'Extended field'
Copy the code

You can see the following Java code:

./** * Extended field, detailed field see DustbinExt class * DefaultValue: {} */
    private String ext;

    public DustbinExt getExtObject(a) {
        return JSON.parseObject(this.getExt(), DustbinExt.class);
    }

    public void setExtObject(DustbinExt ext) {
        this.ext = JSON.toJSONString(ext); }...Copy the code

As you can see, ext fields are used to store jSON-formatted Data. They can dynamically add any fields, even objects, without the need for DDL (Data Definition Language) to create a field, which is perfect for solving the above problem.

Java code plays a supporting role here. By defining an internal class to manage the attributes of the extended fields, we can easily understand and manage the extended fields, and improve the readability and maintainability of the code. The Java approach is also summarized by the author as a more elegant approach (personal opinion).

limitations

Experienced readers may point out that ext cannot index one or some of the extended fields in Mysql 5.7.8 or later because Mysql 5.7.8 or later does not support indexes for Json Data types.

Yes, this is a limitation in this solution, in Mysql 5.7.8 version below, my advice is to ext extension field don’t store hot data, data storage not only hot, so you can avoid query operation, reduce the cost and risk brought by maintaining the ext field, hot data that how to identify the new field? This needs to be judged based on actual business requirements. You can also ask colleagues who are more experienced in business and technology, so that readers can draw conclusions more quickly.

Ultimate solution

In some extreme cases, the change may come too fast, and what we want is to reduce the cost and risk brought by the change. Therefore, at the beginning of table design, we can estimate how many spare fields need to be reserved according to our own experience or seek help from more experienced people, and then coordinate with the extended fields. You can basically reduce the number of times you change (add fields) the table structure to a very small number.

conclusion

Under special circumstances, by extending the field + reserved fields can basically achieve the dynamic extension field, and will not affect for hot data indexing, so that we got a very flexible table structure, to facilitate our response to the change of the future, but please note that to safeguard our entity, including the inside of the each field, the fear of every line of code.