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));