Solve the problem that one of MySQL string processing fields contains multiple IDs

If a field in a MySQL table contains multiple IDs, how can this problem be solved? The following describes how to solve this MySQL string problem, I hope it will help you learn MySQL strings.

1. Create a table

 
 

  
  
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. initialize data

 
 

  
  
insert Category(chiName) values ('fish'),('shrimp'),('crab'),('tiger');  
  
  
 
  
  
insert OpenRecord(cateIds) values('1,2');  
  
  
insert OpenRecord(cateIds) values('2,3');  
 
 

3. query the Category in OpenRecord where Id is 1.

# Incorrect method

 
 

  
  
select *   
  
  
    from Category  
  
  
    where (select INSTR(cateIds,cateId) from OpenRecord where opreId=1) 
 
 

# Correct Method

 
 

  
  
select *   
  
  
    from Category  
  
  
    where (select FIND_IN_SET(cateId,cateIds) from OpenRecord where opreId=1) 
 
 

When INSTR is used, when the ID is greater than 10, the data with ID 1 will be retrieved, and all the data with ID 1, 10, 11, 12 ...... will be taken out.

4. Problems with scaling.
Using FIND_IN_SET can solve the problem that IDs are separated. However, there are two other cases.

A. When IDs do not contain ",", but are separated by other symbols, for example, "| ". We have the following solutions:

 
 

  
  
select *   
  
  
    from Category  
  
  
    where (select FIND_IN_SET(cateId,REPLACE(cateIds,'|',',')) from OpenRecord where opreId=1)  
 
 

The above is how to handle the MySQL string problem.

MySql stored procedure with Parameters

How to view the three MySQL character sets

Provides you with an in-depth understanding of the default MySQL character set.

How does MySQL Delete foreign key definitions?

Four conditions for using the MySQL foreign key