有时候在我们需求中，一条记录会对应多个
相同内容的列，比如电话号码：家庭电话、工作电话、手机号码等。那么就需要分四列来存储，而且后面需求可能还加入出差电话、传真号码这些不可预测的分类。

目标：存储多列属性

    像第2章一样，解决一个属性看上去只属于一张表，但是同时可能会有多个值。
    这一章，使用bug数据添加标签来举例。一个bug可以存在多个标签。

反模式：创建多个列

    第2章，我们解决了一列存储一个列表，这是不合适的。这次反模式采用一列存储一个值。

1
2
3
4
5
6
7
create table bug(
	bug_id serial primary key,
	description varchar(1000),
	tag1 varchar(20),
	tag2 varchar(20),
	tag3 varchar(20)
);
查询数据

    当你要根据给定标签(performance)所有bug时，你必须查询所有的三列，因为他可能存在这三列中任意一列。

1
2
3
4
select * from bug
where tag1 = 'performance' 
or tag2 = 'performance'
or tag3 = 'performance'

或者，使用in 的方式来查询

1
select * from bug where 'performance' in (tag1, tag2, tag3)
添加及删除值

    在这个设计中添加以及删除也是有问题的，单纯的使用update语句来更新一列是不安全的。举个例子，假如我们事先知道tag2是为null的，于是我们写出以下语句来更新

1
update bug set tag2 = 'performance' where bug_id = 1234;

    这条语句是不安全的，假如同时有两个线程执行这个一条语句，结果就是不正确的。或者你可以使用ifnull来避免这个问题。

1
2
update bug set tag2 = ifnull('performance', tag1)
where bug_id = 1234;

    或者，你可以写出更复杂的语句，但是它是非常耗时的。

1
2
3
4
5
6
update bug
set tag2 = case
		when 'performance' in (tag1, tag3) then tag2
		else coalesce(tag2, 'performance') 
	end
where bug_id = 1234;
确保唯一性

    在这个设计中，数据库是无法保证在一条记录中不存在相同的值的。

1
insert into bug(..., tag1, tag2, tag3) values(..., 'performance', 'printing', 'performance');
处理不断增长的值

    这个设计的另一个弱点就是三列并不够用，当你需要增加新一列时

1
alter table bug add column tag4 varchar(20);

    如果频繁使用以上语句会存在以下问题：

  • 重构一张表会锁住整张表，影响客户端访问。
  • 有些数据库会定义一张符合需求的新表，将现有的数据从旧表中导入新表，在丢弃旧表的方式来实现重构表结构，如果重构的表多，那么转换是相当耗时的。
  • 新增一列，你需要检查每一条已存在的sql，以保证这些sql能够支持新增的列。

如何识别反模式

    当出现以下情况时，可能是反模式

  1. 应该支持的动态列的数量是多少？
  2. 如何才能在SQL查询中同时查询多列？

合理使用反模式

    在默写情况下，一个属性可能有固定数量的候选值，并且他们的存储位置和顺序都是固定的。这样的话，可以使用反模式。

解决方案：创建从属表

    创建另外一张表，保存tag，其中有一列作为外键关联bug表，以上问题迎刃而解。

1
2
3
4
5
6
create table tag(
	bug_id bigint unsiged not null,
	tag varchar(20),
	primary key (bug_id, tag),
	foreign key (bug_id) references bug(bug_id)
);

总结

    将具有同样意义的值存储在同一列中。