SQL反模式02-乱穿马路 | 并发笔记

什么是“乱穿马路”

在数据库设计中，多对多的情况经常成为我们的需求，比如一个产品有多个负责人，一个人可以负责多个产品。程序员通常使用逗号分隔的列表来避免在多对多的关系中创建交叉表，书中将这种设计模式叫做“乱穿马路”

目的（存储多值属性）

背景：每个产品对应一个联系人，一个联系人可能对应多个产品，因此我们在产品和账号之间是一个多对一的关系。我们设计product表如下：

create table product(
	product_id SERIAL PRIMARY KEY,
	product_name VARCHAR(50),
	account_id BIGINT UNSIGEND,
	FOREIGN KEY (account_id) REFERENCES account(account_id)
);

但是随着需求变更，一个产品需要对应多个联系人，此时product表的一行数据必须存储多个联系人

反模式（格式化数据，用逗号分隔列表）

为了将数据库表结构改动控制到最小，你决定将account_id的类型改成VARCHAR，每个账号id之间用逗号分隔，这样就可以存储多个联系人

create table product(
	product_id SERIAL PRIMARY KEY,
	product_name VARCHAR(50),
	account_id VARCHAR(100)
);

这样看似可行，但是你要承受以下伴随的问题

1.查询指定账号的产品

不能使用SQL语法中的等号操作符，只能使用like 或者正则表达式。

1	SELECT * FROM PRODUCT WHERE account_id REGEXP '[[:<:]]12[[:>:]]';

模式匹配可能会返回错误的结果，而且，此时索引将不可用，查询效率不能保证。

2.查询指定产品的账号

如果你想使用关联查询account表，也是极其耗时的，如下：

SELECT * FROM PRODUCT AS p 
JOIN ACCOUNT AS a 
ON a.account_id REGEXP '[[:<:]]'+a.account_id+'[[:>:]]'
WHERE p.product_id = 1;

以上语句必须扫描两种表，创建一个交叉结果集，然后使用正则匹配每一行联合的数据，而它不能使用任何索引

3.执行聚合查询

使用COUNT(),SUM(),AVG()等函数时也极其困难，你不得不间接的得出结果，比如实现COUNT()

1 2	SELECT product_id,LENGTH(account_id)-LENGTH(REPLACE(account_id,',',''))+1 as contacts_per_product FROM product;

这类办法看起来很帅（使用它的长度减去替换后’,’的长度，得到’,’的个数。），但是有些聚合函数根本不能使用这些技巧来完成，何况这类解决办法需要花费大量时间

4.更新产品的账号

更新产品的联系人，使用追加的方式，也不能保证按顺序存储

1 2	UPDATE product SET account_id=account_id+','+3 WHERE product_id=1;

删除产品某个联系人，你必须先查出老的列表，然后使用java等语言删除联系人后，才能将更新后的列表存储

5.选择合适的分隔符

如果存储一个字符串列表而不是数字列表，条目中可能会包含分隔符，那么你该如何选择分隔符，你能确保你选择的分隔符永远不会出现在条目中吗？

6.列表长度限制

你能在一个VARCHAR(30)的结构存储多少数据？用mysql举例，如果每个条目长度为6，你只能存储4个条目，你能确定到底多少的长度够用吗？

如何识别反模式

如果你在开发中说过下面这些话，那么很有可能在使用“乱穿马路”

列表最多支持存放多少数据？
这个问题在选择Varchar列的最大长度时被提及
你知道怎么使用正则提取数据吗？
需要使用正则表达式来提取数据，这可能是一种提示，意味着你应该把这些数据分开存储。
哪些字符按不会出现在任何一个条目中？

合理使用反模式

如果应用程序接收的源数据是有逗号分隔的格式，而你只需要存储和使用它们并且不对其做任何修改，完全没有必要分开其中的值。
个人补充：如果这个要存储的列表，你只是保存和单纯的查询出来，不做修改，不需要关联查询的话，我觉得完全可以这样做。你甚至还可以保存一个json数组

解决办法(创建一张交叉表)

创建一张交叉表，使用规范性设计数据，能使你的代码更加灵活，以上所说的，不能使用索引，不能使用聚合函数等，都能得到解决