一个字段存储多个ID时的查询

MySQL - 一个字段存储多个ID时的查询

 

一、说明

    当在一张表的某个字段中存储了另外一张表的多个ID时常用查询

二、准备

    假设有一博客，每一个主题允许有多个类别。数据库设计时建了类别和主题两张表，主题对类别的关联设置在主题表中。

[sql]  view plain  copy

 

1. -- 类别表DDL  
2. CREATE TABLE `T_CATEGORYS` (  
3.   `C_ID` int(11) NOT NULL,  
4.   `C_NAME` varchar(255) NOT NULL,  
5.   PRIMARY KEY (`C_ID`)  
6. )   

[sql]  view plain  copy

 

1. -- 主题表DDL  
2. CREATE TABLE `T_TOPIC` (  
3.   `T_ID` int(11) NOT NULL,  
4.   `T_NAME` varchar(255) NOT NULL,  
5.   `T_CATEGORYS` varchar(128) NOT NULL,  
6.   PRIMARY KEY (`T_ID`)  
7. )  

[sql]  view plain  copy

 

1. -- 准备类别数据  
2. INSERT INTO `T_CATEGORY` (`C_ID`, `C_NAME`) VALUES ('1', 'Struts');  
3. INSERT INTO `T_CATEGORY` (`C_ID`, `C_NAME`) VALUES ('2', 'Spring');  
4. INSERT INTO `T_CATEGORY` (`C_ID`, `C_NAME`) VALUES ('3', 'iBatis');  
5. INSERT INTO `T_CATEGORY` (`C_ID`, `C_NAME`) VALUES ('4', 'Hibernate');  

[sql]  view plain  copy

 

1. -- 准备主题数据  
2. INSERT INTO `xilen_dev`.`T_TOPIC` (`T_ID`, `T_NAME`, `T_CATEGORYS`) VALUES ('1', 'SSI整合示例', '1,2,3');  
3. INSERT INTO `xilen_dev`.`T_TOPIC` (`T_ID`, `T_NAME`, `T_CATEGORYS`) VALUES ('2', 'SSH整合示例', '1,2,4');  

三、查询

    1、查询多ID的字段中包含某个ID的记录
        SQL：

[sql]  view plain  copy

 

1. -- 查询类别包含了iBatis(id=3)的主题记录  
2. SELECT * FROM T_TOPIC WHERE FIND_IN_SET(3, T_CATEGORYS)  

        结果：

            

    2、查询多ID字段中这些ID所代表的记录

        SQL：

[sql]  view plain  copy

 

1. -- 查询主题 SSH整合示例(id=2) 包含的类别记录  
2. SELECT * FROM T_CATEGORY c WHERE (SELECT FIND_IN_SET(c.C_ID,t.T_CATEGORYS) from T_TOPIC t where t.T_ID=2)  

        结果：

            

    3、查询时将多ID的字段的ID转换为对应的标识

        SQL：

[sql]  view plain  copy

 

1. -- 查询主题表时将类别的ID转换为类别的NAME  
2. SELECT t.T_ID AS topicId,   
3.     (SELECT GROUP_CONCAT(c.C_NAME) FROM T_CATEGORY c WHERE FIND_IN_SET(c.C_ID,t.T_CATEGORYS)) AS categoryName       
4. FROM T_TOPIC t  

        结果：

            

    4、补充

        FIND_IN_SET函数默认是以符号 , 作为分割符的，如果多ID字段使用的这个默认的分隔符，而是以如 | 的符号作为分隔符，那么：

[sql]  view plain  copy

 

1. -- 非 , 作为分隔符时，先REPLACE函数替换它  
2. SELECT * FROM T_TOPIC WHERE FIND_IN_SET(3, REPLACE(T_CATEGORYS,'|',','))