登录
首页 >  数据库 >  MySQL

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

来源:SegmentFault

时间:2023-01-14 10:25:32 363浏览 收藏

亲爱的编程学习爱好者,如果你点开了这篇文章,说明你对《MySQL主表存储以逗号分隔id时的增删改查操作》很感兴趣。本篇文章就来给大家详细解析一下,主要介绍一下MySQL、MySQL优化,希望所有认真读完的童鞋们,都有实质性的提高。

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

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

今天带大家了解了MySQL、MySQL优化的相关知识,希望对你有所帮助;关于数据库的技术知识我们会一点点深入介绍,欢迎大家关注golang学习网公众号,一起学习编程~

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表