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

mysql - 一个字段存储多个id时的查询
 
一、说明
    当在一张表的某个字段中存储了另外一张表的多个id时常用查询

二、准备

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

[sql] view plain copy
 
-- 类别表ddl  
create table `t_categorys` (  
  `c_id` int(11) not null,  
  `c_name` varchar(255) not null,  
  primary key (`c_id`)  
)   
[sql] view plain copy
 
-- 主题表ddl  
create table `t_topic` (  
  `t_id` int(11) not null,  
  `t_name` varchar(255) not null,  
  `t_categorys` varchar(128) not null,  
  primary key (`t_id`)  
)  
[sql] view plain copy
 
-- 准备类别数据  
insert into `t_categorys` (`c_id`, `c_name`) values ('1', 'struts');  
insert into `t_categorys` (`c_id`, `c_name`) values ('2', 'spring');  
insert into `t_categorys` (`c_id`, `c_name`) values ('3', 'ibatis');  
insert into `t_categorys` (`c_id`, `c_name`) values ('4', 'hibernate');  
[sql] view plain copy
 
-- 准备主题数据  
insert into `xilen_dev`.`t_topic` (`t_id`, `t_name`, `t_categorys`) values ('1', 'ssi整合示例', '1,2,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
 
-- 查询类别包含了ibatis(id=3)的主题记录  
select * from t_topic where find_in_set(3, t_categorys)  
        结果：
            

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

        sql：

[sql] view plain copy
 
-- 查询主题 ssh整合示例(id=2) 包含的类别记录  
select * from t_categorys 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
 
-- 查询主题表时将类别的id转换为类别的name  
select t.t_id as topicid,   
    (select group_concat(c.c_name) from t_categorys c where find_in_set(c.c_id,t.t_categorys)) as categoryname       
from t_topic t  
        结果：
            

    4、补充

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

[sql] view plain copy
 
-- 非 , 作为分隔符时，先replace函数替换它  
select * from t_topic where find_in_set(3, replace(t_categorys,'|',','))