某些业务表出于历史原因或性能原因,都使用了违反第一范式的设计模式。即同一个列中存储了多个属性值。如下表中的 bill_ids 所示:
gmt_create | bill_ids |
---|---|
2019-08-12 00:00:00 | 209755,209756,209757 |
…… | …… |
这种情况下,可以考虑将该列根据分隔符进行分割,形成多个列。如下表所示:
gmt_create | bill_id1 | bill_id2 | bill_id3 |
---|---|---|---|
2019-08-12 00:00:00 | 209755 | 209756 | 209757 |
…… | …… | …… | …… |
可以使用MySQL中的字符串拆分函数实现,函数说明如下:
SUBSTRING_INDEX(str,delim,count)
-- str: 被分割的字符串; delim: 分隔符; count: 分割符出现的次数
举个栗子:
对于字符串 “209755,209756,209757” ,设置delim为 “,”,count为1,就会返回 “209755”;其它参数不变,count为2,就会返回 “209755,209756”;其它参数不变,count为-1,就会返回 “209757”。
最后,具体实现如下:
select gmt_create
,(select substring_index(substring_index(bill_ids,',',1),',',-1)) bill_id1
,(select substring_index(substring_index(bill_ids,',',2),',',-1)) bill_id2
,(select substring_index(substring_index(bill_ids,',',3),',',-1)) bill_id3
from lt_repayment;
注意:
1.这里默认 bill_ids 这个字段都是三个值组成的集合,若不知道要分割的字段究竟有几个值(如可能某些行就1个值,某些有6个),可以考虑根据具有最多值的数量来选择使用多少条(select substring_index(substring_index(bill_ids,’,’,第几个值),’,’,-1))语句。但是会有个问题:
ids 2 1,2,3,4 1,3 上表分割后的结果是
id1 id2 id3 id4 2 2 2 2 1 2 3 4 1 3 3 3 更合理的结果应该是
id1 id2 id3 id4 2 null null null 1 2 3 4 1 3 null null ;
2.分割后取数的性能较差;
3.数据表设计时还是需要遵从第一范式,即任何属性都是原子性的,不可再分。