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

摘要：案例查询系统用户关联的产品关联的应用难点表通过一个字段维护所有关联的产品的集合。

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

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

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

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

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

场景：删除产品下面某个服务，需要在产品表中删除对应的服务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;

场景：添加产品下面某个服务，需要在产品表中添加对应的服务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;

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

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