第一种:EAV(实体-属性-值)模型
模型建表示例:
表:entities (实体)主表
id | name |
---|---|
1 | 小明 |
2 | 小李 |
表:attributes (属性) 自定义字段
id | field_name | remark |
---|---|---|
1 | age | 年龄 |
2 | sex | 性别 |
3 | nation | 民族 |
表:values (值)
id | entity_id | attribute_id | value |
---|---|---|---|
1 | 1 | 1 | 27 |
2 | 1 | 2 | 男 |
3 | 1 | 3 | 汉族 |
4 | 2 | 1 | 22 |
5 | 2 | 2 | 男 |
6 | 2 | 3 | 汉族 |
此方式优点时可以无限添加自定义字段,并且可以给自定义字段额外添加字段(如attibutes表的remark字段),很灵活,不影响主表;缺点是需要连表查询,影响查询速度。
第二种:预定义字段
建表时预先定义一些额外的字段,示例:
表:user
id | name | extend1 | extend2 | extend3 |
---|---|---|---|---|
1 | 小明 | 27 | 男 | 汉族 |
2 | 小李 | 22 | 男 | 汉族 |
此方法优点是添加字段表不受影响;缺点是不能额外再扩展字段,并且造成空间的浪费,不够灵活。
第三种:使用json列
建表时添加一列,数据类型设置为json,MYSQL 5.7 之后引入了JSON类型,可以使用JSON函数搜索数据,示例:
表:user
id | name | data |
---|---|---|
1 | 小明 | {“age”:25,“sex”:“男”,“nation”:“汉族”} |
2 | 小李 | {“age”:22,“sex”:“男”,“nation”:“汉族”} |
查询年龄大于22岁的用户:
SELECT * FROM user1 WHERE data
->‘$.age’ >= 22;
查询民族为汉族的用户
SELECT * FROM user1 WHERE data
->‘$.nation’ = “汉族”;
此方式优点时只需要一个字段存储就可以无限添加自定义字段,并且还能执行一些简单的查询操作;缺点是涉及到一些复杂查询就无法使用了。
第四种:动态列
动态增删字段,直接在原表的基础上添加列。
此方式优点是操作简单,对查询有利;缺点是影响数据写入性能,数据量逐渐变大时增加字段需要的时间较长。
第五种:垂直分表
将自定义字段放在另外一张新建的表里作为字段补充,示例:
表:user
id | name |
---|---|
1 | 小明 |
2 | 小李 |
补充表:user_extra
id | age | sex | nation |
---|---|---|---|
1 | 25 | 男 | 汉族 |
2 | 22 | 男 | 汉族 |