一.什么是乱穿马路?
例子:学生表(id,name,score),成绩表(id,score),在这种多对多的关系下,为了避免在多对多关系中创建交叉表 用学生表的scoreId 关联该学生的所有的成绩(可能是id,可能是score)使用逗号分隔的存放来解决。
二.乱穿马路带来的7个问题
又是一个例子:账户和产品的多对多关系。在jaywalking这种反模式中,我们用一个产品对应多个账户,在account_id(bigint unsigend)中用逗号存放多个账户id(将多个外键合并到一个字段项中)。
--插入两条语句做测试用
INSERT INTO Products ( product_id, product_name, account_id )
VALUES
( DEFAULT, 'Visual TurboBuilder', "12,34,65,87,98,09" );
INSERT INTO Products ( product_id, product_name, account_id )
VALUES
( DEFAULT, 'sibo', "34,65,87,98,09" );
注:为了将数据库结构做最小的改动,把Product.account_id变成varchar性。->牺牲了性能和数据完整性。
2.1.问题一:在查询指定合并外键的信息
eg:查询Produces表中账户为id=12的所有产品:
SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]';
带来的问题: 1.只能用正则匹配查询,不能使用=
,2.每种数据库正则匹配的模式都不同,sql语句也不能通用。3.没有办法使用合理的索引来提高性能。
2.2.问题二:[连表查询]指定产品的账户信息
-- 这条sql查不出来。。
SELECT *
FROM products AS p
JOIN Accounts AS a ON p.account_id REGEXP '[[:<:]]' || a.account_id || '[[:>:]]'
WHERE p.account_id = 1;
带来的问题: 1.sql不高级不美观,运行起来也耗时。2.因为连表查询还用到表达式,可能会毁掉任何一个索引。
2.3.问题三:使用[聚合函数]查询
SELECT product_id, LENGTH(account_id) - LENGTH(REPLACE(account_id, ',', '')) + 1
AS contacts_per_product
FROM Products;
带来的问题: 1.有些聚合函数的功能因为有匹配的存在,聚合不了,2.sql复杂繁琐,不利开发和调试。
2.4.问题四:做更新操作-更新指定产品的账号
-- oracle写法
UPDATE Products
SET account_id = account_id || ',' || '126'
WHERE product_id = '1'
带来的问题: 1.就算在末尾用“,”追加,字符串也不能让列表按顺序存储。2.每一次的更新都要提取出老的列表,再存储新的列表,做两次sql操作
2.5.问题五.验证product.account_id的合法性
INSERT INTO Products (product_id, product_name, account_id)
VALUES (DEFAULT, 'Visual TurboBuilder', '12,34,banana');
带来的问题: 1.id这种指定varchar类型,不好防止用户输入非数值型的数据,这样会让表数据越来越乱。
2.6.问题六.选择合适的分隔符
带来的问题: 我们用,号做分割,如果不是id这种类型,换做其他类型,字段信息中没有办法排除是否有逗号,推广到其他的符号,同样也没有办法100%的确保不会出现在条目中。
2.7. 列表长度的限制
带来的问题: 在一个固长的字段中,不可能无穷尽的存放字段,(假设varchar 30,每个条目是2个字符,也只能放15个条目。
三.解决办法
添加一张关联表,作为过渡!既能保证其灵活性,方便之后表字段的拓展,又能在数据库层保证数据的完整性。