多列属性

目的

存储多值属性

一个属性看上去虽然只属于一张表，但同时可能会有多个值。此时你也许会想到，使用逗号分割存储多个值，这样非常不友好；将多个值合并在一起并用逗号分割导致难以对数据进行校验，难以读取或者改变单个值，同时也对聚合公式（诸如统计不同值的数量）非常不友好

我们举例说明本章主题需求：我们现在有一个bug系统的数据库，我们想在bug上添加标签，用于bug的分类；bug的常见分类有

第三方系统（打印系统、邮件系统）的问题引起的我们bug系统的问题（bug的标签为performance）
业务功能问题导致系统崩溃（bug的标签为crash）
用户体验的bug （bug的标签为cosmetic）

功能要求：打标签的功能必须支持一个bug可以打多个标签，标签并不会相互排斥

反模式

创建多个列

CREATE TABLE Bugs(
bug_id SERIAL PRIMARY  KEY ,
description VARCHAR (1000),
tag1 VARCHAR (20),
tag2 VARCHAR (20),
tag3 VARCHAR (20)
)

当你将一个标签指定一个bug记录时，必须将这个标签存放于这三个列中一个。其他未使用的列将保持空的状态如：

UPDATE Bugs SET tag2='crash' WHERE bug_id=123

这种方式每个列只存储一个标签看上去很自然，但是这种属性设计，很简单的任务就都变得很复杂了。接下来我们从几个方面来看一下，这种设计带来的问题

1.查询数据问题

当根据一个给定的标签查询所有的bug的时候，你必须搜索tag1,tag2,tag3这三列，因为这个标签字符串可能存放于这三列中的任何一列

SELECT  * FROM Bugs WHERE tag1='crash' OR tag2='crash' OR tag3 = 'crash'

还有可能查找同时标记为crach和performance的bug，要完成这样的查询如：

SELECT  * FROM Bugs WHERE (tag1='crash' OR tag2='crash' OR tag3 = 'crash') AND (tag1='performance' OR tag2='performance' OR tag3 = 'performance')

通过上述例子可知：在多个列中查找一个值的语法是冗长乏味的。

2.添加及删除值问题

在这个的列的集合中添加以及删除一个值也是有问题的。单纯的使用UPDATE语句更新一列的值是不安全的，因为你不知道你的数据到底存储到tag1、tag2、tag3 哪一列中，你不得不将整行数据读取到前端程序来分析。

SELECT * FROM Bugs WHERE bug_id=123

假如，我们获取的数据tag2这一列为空，于是我们可以写如下SQL

UPDATE Bugs SET tag2='crash' WHERE bug_id=123

这么做的话，就面临多步骤操作间数据同步的问题，那么如何解决？使用NULLIF()函数，把两条SQL，用一个SQL处理了

--上述的SQL意思是将‘crash’标签加到第一个空列中，如果3列都不为空，这条SQL将什么也不做，新的标签将不会被插入，同时这个SQL非常耗时
UPDATE Bugs SET tag1 = NULLIF (tag1,'crash'), tag2 = NULLIF (tag2,'crash'),tag3 = NULLIF (tag3,'crash') WHERE bug_id=123

基于上述写的SQL的问题，我们可以使用case when 语句处理

UPDATE Bugs SET 
tag1 = CASE WHEN 'crash' IN (tag2,tag3) THEN tag1 ELSE COALESCE(tag1,'crash') END ,
tag2 = CASE WHEN 'crash' IN (tag1,tag3) THEN tag1 ELSE COALESCE(tag2,'crash') END ,
tag3 = CASE WHEN 'crash' IN (tag2,tag1) THEN tag1 ELSE COALESCE(tag3,'crash') END 
WHERE bug_id = 123

3.唯一性问题

你可能并不希望同一个标签的值存储在多个列中，如 bug_id=123 的tag1=‘crash’ 和 tag2=‘crash’ 数据库并不好处理这种问题

4.列不断增长问题

随着需求的增长，三列已经不够用了，需要增加列，但是又没有办法预估增加多少列。我们可以使用如下SQL语句为表增加字段

ALTER TABLE Bugs ADD COLUMN tag4 VARCHAR(20)

虽然这条sql简单，但是数据库操作的时候开销还是比较大的

并发量大，为已经存在数据的表增加字段可能导致会导致锁住整张表
有些数据库是定义一张符合需求的表，然后将现在表中的数据复制到新定义的表中，再丢弃旧表的方式。如果表中的数据很多，复制很耗时
在多列中增加一列之后，你必须检查你之前的业务逻辑的sql，修改这些sql支持新增加的列。

如何识别反模式

如果在项目中你听到如下的声音，有可能你的同时就使用了反模式

我们应该支持的标签数量最大值是多少，你需要决定为标签这样的多值属性定义多少列
我要怎么才能在SQL查询中同时搜索多列

合理使用反模式

解决方案

创建从属表

最好的解决办法就是创建一张从属表，仅使用一列来存储多值属性。将多个值存在多行中而不是多列中。同时，在从属表中定义一个外键，将这个值与Bugs表中主记录关联起来

CREATE TABLE Tags(
bug_id BIGINT UNSINGED NOT NULL,
tag VARCHAR (20) NOT NULL ,
PRIMARY KEY (bug_id,tag),
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
)

查询变简单了

INSERT INTO Tags (bug_id,tag) VALUES (123,'crash'),(123,'performance')

查找一个给定的标签的相关的所有的bug就变得非常简单

SELECT * FROM Bugs JOIN Tags USING (bug_id) WHERE tag = 'crash'

查找和两个标签相关的bug

SELECT * FROM Bugs JOIN Tags AS tag1 USING (bug_id) JOIN Tags AS tag2 USING (bug_id) WHERE tag1.tag = 'crash' AND tag2.tag='performance'

添加删除简单了

创建从属表后，添加和删除，不在需要逐行检查是否有空列可以添加记录了。

插入语句：

INSERT INTO Tags (bug_id,tag) VALUES (123,'crash'),(123,'performance')

删除语句：

DELETE FROM Tags WHERE bug_id = 123 AND tag='crash'

唯一性也解决了

主键的约束能够保证不会有重复记录出现，一个给定的标签只能和一个给定的Bug关联一次，如果尝试重复插入，SQL会报错。

列数据限制没有了

每个Bug不再限制只能打三个标签，不像在Bugs表中只能增加三个tagN的列，现在只要有需要就可以一直加新的标签

08.多列属性