Solution for a MySQL field containing multiple IDs

What should I do if a MySQL field contains multiple IDs? The following describes how to process a single MySQL field containing multiple IDs.

The following is a complete example of a MySQL field containing multiple IDs.

1. Create a table

 
 

  
  
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)  
 
 

In this case, there are many tables. We can also write it as a function. MYSQL 5)

 
 

  
  
DELIMITER $$  
  
  
 
  
  
DROP FUNCTION IF EXISTS fun_instr$$  
  
  
 
  
  
CREATE FUNCTION fun_instr(ns VARCHAR(100),s TEXT,isplit CHAR(1))  
  
  
    RETURNS INT(8)  
  
  
    BEGIN  
  
  
 
  
  
        DECLARE strPosition INT(8);  
  
  
 
  
  
        SET strPosition = FIND_IN_SET(ins,REPLACE(s,split,','));  
  
  
        RETURN strPosition;  
  
  
    END$$  
  
  
 
  
  
DELIMITER ;  
  
  
 
 
 

# Usage

 
 

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

B. When an ID contains "," but is separated by other symbols, for example, "| ". The above method does not work. We have the following solutions:

 
 

  
  
DELIMITER $
  
  
 
  
  
Drop function if exists fun_custom_instr $
  
  
 
  
  
Create function fun_custom_instr (ins VARCHAR (255), s TEXT, split VARCHAR (10 ))
  
  
Returns int (8)
  
  
BEGIN
  
  
DECLARE splitlen INT (2 );
  
  
DECLARE strPosition INT (8 );
  
  
SET splitLen = LENGTH (split );
  
  
SET strPosition = 0;
  
  
# The characters in the first segment are equal
  
  
IF s = ins THEN
  
  
RETURN 1;
  
  
End if;
  
  
# The characters in the middle segment are equal
  
  
While instr (s, split)> 0 DO
  
  
SET strPositionstrPosition = strPosition + 1;
  
  
If left (s, INSTR (s, split)-1) = ins THEN
  
  
RETURN strPosition;
  
  
End if;
  
  
SET s = SUBSTRING (s, INSTR (s, split) + splitLen );
  
  
End while;
  
  
# The most character segment is equal
  
  
IF s = ins THEN
  
  
RETURN strPosition + 1;
  
  
End if;
  
  
 
  
  
RETURN 0;
  
  
END $
  
  
 
  
  
DELIMITER;
  
  
 
 
 

# Usage

 
 

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

5. Summary
Although the above methods can solve our problem, the speed is slow when the data volume is large. The complete solution is to design the database according to the database paradigm.
The current space is basically no longer a problem, and the hardware is already very cheap.

 

MySQL string truncation Function Method

MySQL multiple condition judgment example

MySQL string processing

How to query duplicate fields in a MySQL large table

Mysql query case