mysql拆分用逗号拼接的字段做关联_mysql 多表关联时逗号拼接的字段

有两张表A，B，主表诡异的设计， 将用户表的id用逗号分隔做字段保存。
而现在想要看每个用户的信息，就得把id逗号拼接做拆分。

模拟了一张order表

模拟了一张order_user表

1. 把逗号拼接的id做拆分做一张临时表出来

		select a.ID, substring_index(substring_index(a.users, ',', b.help_topic_id + 1), ',', -1) users
	    from `order` a join
						mysql.help_topic b
						on b.help_topic_id < (length(a.users) - length(replace(a.users, ',', '')) + 1)
		order by a.ID
1
2
3
4
5

2. 拆分后的临时表和用户表做关联 获取用户信息

select temp.*, user.user_name, user.email
from (
			 select a.ID, substring_index(substring_index(a.users, ',', b.help_topic_id + 1), ',', -1) users
			 from `order` a
							join
						mysql.help_topic b
						on b.help_topic_id < (length(a.users) - length(replace(a.users, ',', '')) + 1)
			 order by a.ID
		 ) temp left join order_user user on temp.users = user.id
order by temp.id
1
2
3
4
5
6
7
8
9
10

3. 或者用group by ，继续使用逗号拼接成一个字段（具体看需求）

select temp.id, group_concat(temp.users), group_concat(user.user_name) user_name, group_concat(user.email) email
from (
			 select a.ID, substring_index(substring_index(a.users, ',', b.help_topic_id + 1), ',', -1) users
			 from `order` a
							join
						mysql.help_topic b
						on b.help_topic_id < (length(a.users) - length(replace(a.users, ',', '')) + 1)
			 order by a.ID
		 ) temp left join order_user user on temp.users = user.id
              group by temp.id
order by temp.id
1
2
3
4
5
6
7
8
9
10
11

————————————
发现还可以用find_in_set() 函数

该函数能将逗号分隔的字符串分解成set的形式

以用户的id在逗号分隔的id用户id表为查询条件
find_in_set(user.id, `order`.users)

select temp.*, user.user_name, user.email from
`order` temp left join order_user user on find_in_set(user.id, temp.users)
order by temp.id
1
2
3