1、新建表
- drop table if exists Category;
- create table Category
- (
- cateId int(5) not null AUTO_INCREMENT,
- chiName varchar(80),
- primary key (cateId)
- );
- drop table if exists OpenRecord;
- create table OpenRecord
- (
- opreId int(5) not null AUTO_INCREMENT,
- cateIds varchar(80),
- primary key (opreId)
- );
2、初始化数据
- insert Category(chiName) values ('fish'),('shrimp'),('crab'),('tiger');
- insert OpenRecord(cateIds) values('1,2');
- insert OpenRecord(cateIds) values('2,3');
#正确的方法
- select *
- from Category
- where (select FIND_IN_SET(cateId,cateIds) from OpenRecord where opreId=1)
4、扩展会出现的问题。
用FIND_IN_SET可以解决ID是用","号隔开的问题。然而会有另外的两种情况。A、当ID不包含",",但是用别的符号分开时,如用"|"。我们有如下的解决办法
- select *
- from Category
- where (select FIND_IN_SET(cateId,REPLACE(cateIds,'|',',')) from OpenRecord where opreId=1)