一个不愿透露姓名的Netscape工程师曾经将一个指针的地址当成字符串传给了JavaScript，随后再回传给C，节省了30秒。 -- 布雷克·罗斯
假设你正在为缺陷跟踪程序开发一种功能，将某个用户指定为某个产品的主要联系人。你最初的设计只允许每个产品拥有一个联系人，然而，就像你猜的那样，需求可能会变为支持“每个产品有多个联系人”。
此时，将数据库中原来存储单一用户标识的字段改成使用逗号分隔的用户标识列表，似乎是一个很简单且合理的解决方案。
很快，你的老板跑来问你：“工程部在往他们的项目中添加相关人员时发现最多只能添加5个人，如果想要继续添加更多的人，程序就会报错，这是怎么回事？”
你点头说道：“是的，只能在一个项目中列出这么多人，就是这么设计的。”你觉得这是一件再正常不过的事情了。
但老板似乎并不这么认为，他需要一个更加明确的解释。“好吧，5到10个，可能再多存几个，那取决于这些账号创建时间的早晚。”老板感觉非常吃惊，于是你继续说道：“我使用逗号分隔的列表来存储项目的账号ID，而这个ID列表的长度不能超过字符串的最大长度值。账号ID越短，列表中的账号ID就越多。因此，账号创建较早的人，他们的ID不大于99，这些账号ID更短。”
老板皱起眉头，你觉得你又要加班到很晚了。
程序员通常使用逗号分隔的列表来避免在多对多的关系中创建交叉表，我将这种设计方式定义为一种反模式，称为乱穿马路（Jaywalking），因为乱穿马路也是避免过十字路口的一种方式。
2.1 目标：存储多值属性
设计一个单值表列是非常简单的：你可以选择一个SQL的内置数据类型，以该类型来存储这个表项的数据，比如整型、日期类型或者字符串。但是如何才能做到在一列中存储一系列相关数据的集合呢？
在我们的缺陷跟踪数据库的例子中，我们在Products表中使用一个整型的列来关联产品和对应的联系人。每个账号可能对应很多产品，每个产品又引用了一个联系人，因此我们在产品和账号之间有一个多对一的关系。
Jaywalking/obj/create.sql
CREATE TABLE Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(1000),
account_id BIGINT UNSIGNED, -- . . .
FOREIGN KEY (account_id) REFERENCES Accounts(account_id) );
INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder', 12);
随着项目日趋成熟，你意识到一个产品可能会有多个联系人。除了多对一的关系之外，我们还需要支持产品到账号的一对多的关系。Products表中的一行数据必须要能够存储多个联系人。
2.2 反模式：格式化的逗号分隔列表
为了将对数据库表结构的改动控制在最小范围内，你决定将account_id的类型修改成VARCHAR，这样就可以列出该列中的多个账号ID，每个账号ID之间用逗号分隔。
Jaywalking/anti/create.sql
CREATE TABLE Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(1000),
account_id VARCHAR(100), -- comma-separated list
-- . . .
);
这样的设计似乎可行，因为你没有创建额外的表或者列，而仅仅改变了一个字段的数据类 型。然而，我们来看一下这样设计所必须承受的性能问题和数据完整性问题。
2.2.1 查询指定账号的产品
如果所有的外键都合并在一个单元格内，查询会变得异常困难。你将不能再使用等号，相反，不得不对某类模式使用测试。比如，MySQL下可以写一些如下的表达式来查询所有账号ID为12的产品：
Jaywalking/anti/regexp.sql
SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]';
模式匹配的表达式可能会返回错误结果，并且无法享受索引带来的性能优势。由于模式匹配表达式的语法在不同品牌的数据库中是不同的，因此你的SQL代码并不是平台中立的。
2.2.2 查询指定产品的账号
同样地，使用逗号分隔的列表来做多表联结查询定位一行数据也是极不优雅和耗时的。
Jaywalking/anti/regexp.sql
SELECT * FROM Products AS p JOIN Accounts AS a
ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]'
WHERE p.product_id = 123;
联合两张表并使用如上的一句表达式将毁掉任何使用索引的可能。这样的查询必须扫描两张表，创建一个交叉结果集，然后使用正则表达式遍历每一行联合后的数据进行匹配。
2.2.3 执行聚合查询
聚合查询使用SQL内置的聚合函数，如COUNT()、SUM()、AVG()。然而，这些函数是针对分组行而设计的，并不是为了逗号分隔的列表。你不得不借助于如下的一些方法：
Jaywalking/anti/count.sql
SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1 AS contacts_per_product FROM Products;
这类方法可能看上去很高明，但并不清晰。这种类型的解决方案需要花费很长的时间来开发并且不方便调试。何况有些聚合查询根本无法使用这些技巧来完成。
2.2.4 更新指定产品的账号
你可以用字符串拼接的方式在列表尾端增加一个新的ID，但这并不能使列表按顺序存储。
UPDATE Products SET account_id = account_id || ',' || 56 WHERE product_id = 123;
从列表中删除一个条目，必须执行两条SQL查询语句：第一条提取老的列表，第二条存储更新后的列表。
Jaywalking/anti/remove.php
<?php
$stmt = $pdo->query("SELECT account_id FROM Products WHERE product_id = 123");
$row = $stmt->fetch();
$contact_list = $row['account_id']; // change list in PHP code
$value_to_remove = "34";
$contact_list = split(",", $contact_list);
$key_to_remove = array_search($value_to_remove, $contact_list);
unset($contact_list[$key_to_remove]);
$contact_list = join(",", $contact_list);
$stmt = $pdo->prepare("UPDATE Products SET account_id = ? WHERE product_id = 123");
$stmt->execute(array($contact_list));
仅仅为了从列表中删除一个账号就要写如此多的代码。
2.2.5 验证产品ID
用什么来防止用户在ID中输入诸如“banana”（香蕉）这样的非法字段？
Jaywalking/anti/banana.sql
INSERT INTO Products (product_id, product_name, account_id) VALUES (DEFAULT, 'Visual TurboBuilder', '12,34,banana');
用户总能找到办法输入他们想输入的东西，然后你的数据库就会变得很乱。上面这样的情况并不一定是一个数据库错误，但相对应的数据会变得毫无价值。
2.2.6 选择合适的分隔符
如果存储一个字符串列表而不是数字列表，列表中的某些条目可能会包含分隔符。使用逗号作为分隔符可能会有问题。当然，你到时候可以再换一个字符，但你能确保这个新字符永远不会出现在条目中吗？
2.2.7 列表长度限制
你能在一个VARCHAR(30)的结构中存多少数据呢？这依赖于每个条目的长度。如果每个条目只有2个字符长，那你能存10个条目（包括逗号）。但如果每个条目的长度为6，你就只能存4个了。
Jaywalking/anti/length.sql
UPDATE Products SET account_id = '10,14,18,22,26,30,34,38,42,46' WHERE product_id = 123;
UPDATE Products SET account_id = '101418,222630,343842,467790' WHERE product_id = 123;
你怎能确定VARCHAR(30)能够支持你未来所需的最长列表呢？多长才够长？你可以自己尝试着去和老板或者客户解释这么限制的原因。
2.3 如何识别反模式
如果你的项目团队说过下面这些话，那么这很有可能就是在项目中使用了“乱穿马路”设计模式的线索。
- “列表最多支持存放多少数据？”这个问题在选择VARCHAR列的最大长度时被提及。
- “你知道在SQL中如何做分词查找吗？”如果你用了正则表达式来提取字符串中的组成部分，这可能是一种提示，意味着你应该把这些数据分开存储。
- “哪些字符不会出现在任何一个列表条目中？”你想要使用一个不会令人困惑的分割符号，但你也应该明白任何字符都有可能在某天出现在字段中的某个值内。
2.4 合理使用反模式
出于性能优化的考量，可能在数据库的结构中需要使用反规范化的设计。将列表存储为以逗号分隔的字符串就是反规范化的一个实例。
应用程序可能会需要逗号分隔的这种存储格式，也可能没必要获取列表中的单独项。同样，如果应用程序接收的源数据是有逗号分隔的格式，而你只需要存储和使用它们并且不对其做任何修改，完全没必要分开其中的值。
你需要谨慎使用反规范化的数据库设计。尽可能地使用规范化的数据库设计，因为那样的设计能让你的产品代码更灵活，并且也能在数据库层保持数据完整性。
2.5 解决方案：创建一张交叉表
将account_id存储在一张单独的表中，而不是存储在Products表中，从而每个独立的account值都可以占据一行。这张新表称为Contacts，实现了Products和Accounts的多对多关系。
Jaywalking/soln/create.sql
CREATE TABLE Contacts (
product_id BIGINT UNSIGNED NOT NULL,
account_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (product_id, account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id),
FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);
INSERT INTO Contacts (product_id, accont_id) VALUES (123, 12), (123, 34), (345, 23), (567, 12), (567, 34);
当一张表有指向另外两张表的外键时，我们称这种表为一张交叉表1，它实现了两张表之间的多对多关系。这意味着每个产品都可以通过交叉表和多个账号关联；同样地，一个账号也可以通过交叉表和多个产品关联。参考图2-1。
1 有人用“联合表”、“多对多表”、“映射表”或其他术语来描述这种表，表述方式不同而已，其本质都是相同的。——译者注
2.5.1 通过账号查询产品和反过来查询
要查询指定账号的产品的所有属性，使用Products和Contacts表的联结查询是再简单不过的了：
Jaywalking/soln/join.sql
SELECT p.* FROM Products AS p JOIN Contacts AS c ON (p.account_id = c.account_id) WHERE c.account_id = 34;
有些人拒绝使用联结查询，他们认为那样很低效。然而，与2.2节中提出的方法相比，这个查询更好地使用了索引。
查询账号的细节也非常地简单，也方便优化。这里使用索引提高了联结查询的效率，而不是使用深奥的正则表达式：
Jaywalking/soln/join.sql
SELECT a.* FROM Accounts AS a JOIN Contacts AS c ON (a.account_id = c.account_id) WHERE c.product_id = 123;
2.5.2 执行聚合查询
下面的例子返回每个产品相关的账号数量：
Jaywalking/soln/group.sql
SELECT product_id, COUNT(*) AS accounts_per_product FROM Contacts GROUP BY product_id;
获取每个账号相关的产品数量也很简单：
Jaywalking/soln/group.sql
SELECT account_id, COUNT(*) AS products_per_account FROM Contacts GROUP BY account_id;
生成其他更加复杂的报表也是可行的，比如找到相关账号最多的产品：
Jaywalking/soln/group.sql
SELECT c.product_id, c.accounts_per_product FROM (
SELECT product_id, COUNT(*) AS accounts_per_product
FROM Contacts
GROUP BY product_id
) AS c
HAVING c.accounts_per_product = MAX(c.accounts_per_product)
2.5.3 更新指定产品的相关联系人
你可以通过添加或者删除交叉表中的行来简单地修改字段中的条目。在Contacts表中，每条产品记录都是分开存储在不同的行中的，因而可以添加或删除。
Jaywalking/soln/remove.sql
INSERT INTO Contacts (product_id, account_id) VALUES (456, 34);
DELETE FROM Contacts WHERE product_id = 456 AND account_id = 34;
2.5.4 验证产品ID
你可以以另一张表中的合法数据为标准，使用外键来验证数据。通过声明Contacts. account_id引用Accounts.account_id，就能依靠数据库自身的约束来强制引用的完整性，以确保交叉表中只包含确凿存在的账号ID。
你还可以使用SQL的数据类型来约束条目。例如，设定字段中的条目应该是INTEGER或者DATE类型的，就可以确信所有条目都是合法的数据（不会是乱七八糟的像“banana”一样的数据）。
2.5.5 选择分隔符
你用不到分隔符了，因为数据都分开存储在不同的行中。即使条目中出现了逗号或者任何你可能想用做分隔符的其他字符，也不会有任何问题。
2.5.6 列表长度限制
至此，每个条目都位于交叉表中的独立的行内，列表的长度限制就变成了一张表可以实际存放的行数。如果可以限制条目总数，你应该在程序中加强对条目数量的使用，而不是统计列表的总体长度。
2.5.7 其他使用交叉表的好处
为Contacts.account_id做索引的查询效率比用逗号分隔列表中分串高效得多。在许多数据库中，声明某一列为外键会隐式地为该列创建索引（实际情况以相关文档为准）。
你还可以在交叉表中添加其他属性。比如，可以记录一个联系人被加入产品的具体日期，或产品的第一联系人及第二联系人。这些都是在逗号分隔的列表中无法做到的。
每个值都应该存储在各自的行与列中。
下一节：树就是树，你还需要考虑些什么呢？ -- 罗纳德·里根