mysql一列多值多属性设计-乱穿马路

乱穿马路

需求:每个产品都可能有多个联系人

简单方案:将单值存储改为逗号分隔存储多值

常用逗号分隔的列表来避免在多对多关系中创建交叉表,这是一种反模式,称为乱穿马路(Jaywalking),因为可以避免十字路口

目标:存储多值属性

如何在一列中存储一系列相关数据的集合

一个账号对应多个产品

每个产品对应一个联系人

但每个产品可能对应多个联系人

反模式:格式化的逗号分隔列表

在这里插入图片描述

查询指定账号的产品

查询异常困难,不能再用等号
在这里插入图片描述

不得不借助模式匹配

困难出错且无法带来性能的优势

查询指定产品的账号

在这里插入图片描述

多表连接也不合适

执行聚合查询

在这里插入图片描述

很糟糕

缺陷:

  1. 困难出错且无法带来性能的优势
  2. 多表连接也不合适
  3. 聚合查询很糟糕
  4. 更新过于麻烦
  5. 验证麻烦
  6. 选择合适的分隔符,存储的是字符串使用逗号就不合适,甚至使用任何可能出现的新字符都不合适
  7. 列表长度限制

识别

  1. 列表最多支持存放多少数据
  2. 如何分词查询
  3. 哪个字符不会出现在任何一个列表中

合理使用

可能会需要,也可能没必要使用这样的单独项。如果数据源是这样的值,并且只做存储不做修改就每必要分开。

谨慎使用,尽可能用规范化的,更加灵活可变

解决方案:创建一张交叉表

在这里插入图片描述
将account_id存储在单独的一张表中而不是products表,每个独立的account_id都可占据一行

展现了products和accounts的多对多
在这里插入图片描述

问题解决

  1. 通过账号查询产品和反查,使用连接查询,由于能使用索引更加高效,且更加简单

    select p.* from products as p join constracts as c on(p.product_id = c.product_id) where c.account = 34;

  2. 聚合查询

    select product_id,count(*) from constracts group by product_id

  3. 更新指定产品的相关联系人

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y5aAQhmV-1608896591993)(乱穿马路.assets/image-20201221214154487.png)]

  4. 验证产品id

    约束数据类型

  5. 不用分隔符

  6. 没有长度限制

  7. 用到索引,效率更改,添加额外属性,比如记录一个联系人被加入产品的具体日期,或产品的第一、第二联系人

区域被添加到活动的日期,活动的主要区域、次级区域等

每个值都应该存储在各自的行与列中。

创建交叉表

  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: 可以使用 MySQL 的 SUBSTRING_INDEX 函数来将一列拆分成多。该函数可以基于指定的分隔符将字符串划分为多个子字符串,并且可以选择返回其中的某一个子字符串。 例如,假设有如下一列数据: ``` id | name ---|--------- 1 | John,Doe 2 | Jane,Smith ``` 我们可以使用 SUBSTRING_INDEX 函数将 name 拆分成两,分别存储 first_name 和 last_name: ``` SELECT id, SUBSTRING_INDEX(name, ',', 1) AS first_name, SUBSTRING_INDEX(name, ',', -1) AS last_name FROM my_table; ``` 执行上述 SQL 后,我们会得到如下结果: ``` id | first_name | last_name ---|------------|---------- 1 | John | Doe 2 | Jane | Smith ``` 在上述 SQL 中,SUBSTRING_INDEX 函数的第一个参数为待划分的字符串,第二个参数为分隔符,第三个参数为返回的子字符串索引。在本例中,第二个参数为逗号,表示按照逗号进行划分;第三个参数为 1 和 -1,分别表示返回第一个和最后一个子字符串。 ### 回答2: 在MySQL中,可以使用多种方法将一列拆分成多。以下是两种常用的方法: 1. 使用SUBSTRING函数和其他字符串函数 使用SUBSTRING函数可以从一列的值中提取子字符串,并将其作为多个进行显示。例如,如果有一个名为full_name的,其中包含了名字和姓氏,可以使用以下SQL语句将其拆分成两个名为first_name和last_name: ``` SELECT SUBSTRING(full_name, 1, LOCATE(' ', full_name) - 1) AS first_name, SUBSTRING(full_name, LOCATE(' ', full_name) + 1) AS last_name FROM table_name; ``` 这将返回一个结果集,其中包含了两,分别为名字和姓氏。 2. 使用正则表达式和SUBSTRING_INDEX函数 MySQL还提供了SUBSTRING_INDEX函数,可以将字符串根据指定的分隔符拆分成多个子字符串,并提取特定位置上的子串。结合正则表达式,可以更灵活地进行拆分。例如,如果有一个名为full_address的,其中包含了街道、城市和州的信息,可以使用以下SQL语句将其拆分成三个名为street、city和state: ``` SELECT REGEXP_SUBSTR(full_address, '^[^,]+') AS street, REGEXP_SUBSTR(SUBSTRING_INDEX(full_address, ',', -2), '[^,]+') AS city, TRIM(SUBSTRING_INDEX(full_address, ',', -1)) AS state FROM table_name; ``` 这将返回一个结果集,其中包含了三,分别为街道、城市和州。 以上是两种常用的方法,可以根据具体情况选择适合的方法将一列拆分成多。 ### 回答3: 在MySQL中,如果需要将一列拆分成多,可以通过使用字符串函数和相应的SQL语句来实现。 首先,我们需要使用字符串函数将原始中的数值分解成多个部分,并将其存储到新的中。比如,如果我们有一个包含日期和时间的,例如"2022-01-01 12:00:00",我们可以使用函数SUBSTRING将日期和时间分开并保存到两个不同的中。 下面是一个使用SUBSTRING函数的示例: SELECT col1, SUBSTRING(col1, 1, 10) AS date_col, SUBSTRING(col1, 12) AS time_col FROM table_name; 这将返回一个结果集,其中包含原始以及分解后的日期和时间。 另外,如果原始中的值是由特定的分隔符进行分割的,我们还可以使用函数SUBSTRING_INDEX来提取分隔符之间的部分,并将其存储到不同的中。 以下是使用SUBSTRING_INDEX函数的示例: SELECT col1, SUBSTRING_INDEX(col1, ',', 1) AS col2, SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', 2), ',', -1) AS col3, SUBSTRING_INDEX(SUBSTRING_INDEX(col1, ',', 3), ',', -1) AS col4 FROM table_name; 这将返回一个结果集,其中包含原始以及根据逗号进行分割后的第1、2和3部分。 需要注意的是,拆分的方式和具体操作取决于中的数据结构和分隔符,你需要根据实际情况选择合适的函数和语句来实现的拆分。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值