mysql - 将一个表中的分隔值中的子字符串与第二个表中的记录匹配，并构建连接

Question

我最近将旧 CRM 中的记录作为两个表转储到 Navicat (MySQL) 数据库中。

__________________
| table_1:       |
|----------------|
| id             |
| delimited_col  |
------------------

__________________
| table_2:       |
|----------------|
| id             |
| name           |
------------------

信息：

• 表格1：
  • table_1.delimited_col 由以“;”分隔的串联名称组成

    前记录：“凯文；南希；詹姆斯；巴特；德莱尼”

• 表 2：
  • table_2.name 由单个名称组成

    前唱片：“凯文”

我的目标：

• 创建一个连接表 (table_3)，它检查 table_1.delimited_col 中的每条记录以查看它们是否包含 table_2.name 中的值。
  • 如果 table_1.delimited 中的记录包含在 table_2.name 中找到的子字符串，则将 table_1 的 id 中的记录和 table_2 的 id 中的匹配记录添加到连接 table_3。

__________________
| join_table:    |
|----------------|
| id             |
| table_1_id     |
| table_2_id     |
------------------

我一直在努力解决这个问题，并希望得到任何帮助或建议！

标签： mysqlsqlstringcsvjoin

Answer 1

您可以使用字符串方法find_in_set()：

select t1.id table_1_id, t2.id table_2_id
from table_1 t1
inner join table_2 t2 on find_in_set(t2.name, replace(t1.delimited_col, ';', ','))

但更好的是，您应该真正修复您的数据模型并将分隔列表中的每个值存储在单独的表行中。将 csv 数据存储在关系数据库中并不是一个好习惯，而且可能会在很多方面伤害到您。这是一篇著名的 SO 帖子，详细介绍了该主题 - 实际上，许多与分隔列表相关的问题都作为 SO 上此帖子的副本而关闭。

如果要使用查询结果创建新表，则：

create table table_3(id int auto_increment, table_1_id, table_2_id);

insert into table_3 (table_1_id, table_2_id)
select t1.id, t2.id
from table_1 t1
inner join table_2 t2 on find_in_set(t2.name, replace(t1.delimited_col, ';', ','));