mysql以id_MySQL主表存储以逗号分隔id时的增删改查操作

案例1：查询系统用户(993318396439445506)关联的产品关联的应用

[sys_user] [sys_user_prod] [product] [app]

uid-------------user_id /---id----------products

/

prod_id-------/

难点： app表通过一个字段products维护所有关联的产品id的集合。如：2 或 4,11,12

实现：

SELECT `id`,`uuid`,`name`,`token`,`time`,`products` FROM app

where CONCAT(',',products,',') REGEXP

(

SELECT CONCAT(

',{1}(',

GROUP_CONCAT(prod_id SEPARATOR '|') ,

'),{1}'

)

FROM sys_user_prod WHERE user_id = '993318396439445506'

)

order by id;

片段理解1：

假设products=1,17 --> CONCAT(',',products,',') --> ,1,17,

片段理解2：

SELECT CONCAT(

',{1}(',

GROUP_CONCAT(prod_id SEPARATOR '|') ,

'),{1}'

)

FROM sys_user_prod WHERE user_id = '993318396439445506'

拆分来理解：

SELECT prod_id FROM sys_user_prod WHERE user_id = '993318396439445506';

以上sql会产生多行，所以使用group_concat函数将多上拼接起来：1|17|19

SELECT group_concat(prod_id SEPARATOR '|') FROM sys_user_prod WHERE user_id = '993318396439445506';

最后使用concat拼接处正则表达式：,{1}(1|17|19),{1}

SELECT concat(',{1}(', group_concat(prod_id SEPARATOR '|') ,'),{1}') FROM sys_user_prod WHERE user_id = '993318396439445506';

所以，where子句后面的，实际内容是这样的：

SELECT `id`,`uuid`,`name`,`token`,`time`,`products` FROM app

where CONCAT(',',products,',') REGEXP ',{1}(1|17|19),{1}' order by id;

案例2：查询终端用户(36)关联的设备

场景：“点击某个终端用户，显示该用户所有设备

[user] [device]

id---------\ /-----id

x

devices----/ \-----users

难点：多对多

实现1(不推荐)：

SELECT * FROM device WHERE concat(',',id,',') REGEXP

(SELECT CONCAT(',{1}(', REPLACE(devices,',','|'), '),{1}') FROM `user` WHERE id = 36);

首先看看用户表的devices, result --> 3,6

select * from user where id = 36;

逐步拼接正则匹配条件, result --> 3|6

SELECT REPLACE(devices,',','|') FROM `user` WHERE id = 38;

逐步拼接正则匹配条件, result --> ,{1}(3|6),{1}

SELECT CONCAT(',{1}(', REPLACE(devices,',','|'), '),{1}') FROM `user` WHERE id = 36;

所以，where子句后面的，实际内容是这样的：

SELECT * FROM device WHERE concat(',',id,',') REGEXP ',{1}(3|6),{1}';

实现2(推荐)：

SELECT * FROM device WHERE id REGEXP

(SELECT CONCAT('^(', REPLACE(devices,',','|'), ')$') FROM `user` WHERE id = 36);

实际内容是这样的：

SELECT * FROM device WHERE id REGEXP '^(3|6)$';

实现3(强烈推荐)：

SELECT * FROM device WHERE FIND_IN_SET(id, (SELECT devices FROM `user` WHERE id = 36) );

案例3：查询设备(3)下面所有终端用户

场景：显示某个设备下关联的所有终端用户的信息

[user] [device]

id---------\ /-----id

x

devices----/ \-----users

实现1(不推荐)：

SELECT

d.id as device_id,

d.name as device_name,

d.users as device_users,

u.`id` AS user_id,

u.`name` AS user_name,

u.`phone` AS user_phone,

u.`wechat` AS user_wechat,

u.`devices` AS user_devices

FROM device d, `user` u

WHERE d.`id` = 3

AND u.`id` REGEXP CONCAT('^(',REPLACE(d.`users`, ',', '|'),')$');

实现2(推荐)：

SELECT

d.id as device_id,

d.name as device_name,

d.users as device_users,

u.`id` AS user_id,

u.`name` AS user_name,

u.`phone` AS user_phone,

u.`wechat` AS user_wechat,

u.`devices` AS user_devices

FROM device d, `user` u

WHERE d.`id` = 3 AND FIND_IN_SET(u.`id`,d.`users`);

案例4：删除产品(7)下面关联的服务(25)

场景：删除产品下面某个服务，需要在产品表中删除对应的服务id

[product] [service] [service_property]

id /--id /----id

/ /

services---/ properties----/

实现：

UPDATE product SET services =

SUBSTR(

REPLACE(

CONCAT(',',services), CONCAT(',',25), ''

), 2

)

WHERE id = 7;

理解，从查询开始理解，避免删除错了：

select id,name,services from product where id = 7;

现在知道services的值是24,25,26,27

思路是先用concat将 "24,25,26,27" 变成 ",24,25,26,27"

再用replace将 ",24,25,26,27" 中的 ",25" 替换成空字符串 ''，处理后字符串变成",24,26,27"

由于最前面多了一个逗号，所以最后用substr来去掉那个多余的逗号

select substr(

replace(

concat(',',services),concat(',',25),''

),2

) from product where id = 7;

案例5：产品(7)下面添加关联的服务(33)

场景：添加产品下面某个服务，需要在产品表中添加对应的服务id

[product] [service] [service_property]

id /--id /----id

/ /

services---/ properties----/

实现：

UPDATE product SET services = CONCAT(services,',',33) WHERE id = 7;

理解，从查询开始理解，避免添加错了：

select CONCAT(services,',',33) from product WHERE id = 7;

案例6：删除服务(5)下面所有关联的属性

场景：删除产品下面某个服务，需要先删除服务下面关联的属性

[product] [service] [service_property]

id /--id /----id

/ /

services---/ properties----/

实现1(不推荐)：

DELETE FROM service_property WHERE id

REGEXP CONCAT( '^(', REPLACE( (SELECT properties FROM service WHERE id = 5), ',', '|' ), ')$');

理解：从查询开始理解，避免删除错了：

result --> 13,14,15

select properties from service where id = 5;

替换 result --> 13|14|15

select REPLACE( (SELECT properties FROM service WHERE id = 5), ',', '|' );

拼接成正则 result --> ^(13|14|15)$

select CONCAT( '^(', REPLACE( (SELECT properties FROM service WHERE id = 5), ',', '|' ), ')$');

最后就可以用来匹配属性id：

select id,name from service_property where id

REGEXP CONCAT( '^(',

REPLACE( (SELECT properties FROM service WHERE id = 2), ',', '|' ),

')$');

实现2(强烈推荐)：

select id,name from service_property where

FIND_IN_SET(id,(SELECT properties FROM service WHERE id = 6));