Solution for a MySQL field containing multiple IDs

Source: Internet
Author: User

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

 
 
  1. create table Category  
  2. (  
  3.    cateId                         int(5)                         not null AUTO_INCREMENT,  
  4.    chiName                        varchar(80),  
  5.    primary key (cateId)  
  6. );  
  7.  
  8. drop table if exists OpenRecord;  
  9. create table OpenRecord  
  10. (  
  11.    opreId                         int(5)                         not null AUTO_INCREMENT,  
  12.    cateIds                        varchar(80),  
  13.    primary key (opreId)                      
  14. );  
  15.  

2. initialize data

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

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

# Incorrect method

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

# Correct Method

 
 
  1. select *   
  2.     from Category  
  3.     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:

 
 
  1. select *   
  2.     from Category  
  3.     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)

 
 
  1. DELIMITER $$  
  2.  
  3. DROP FUNCTION IF EXISTS fun_instr$$  
  4.  
  5. CREATE FUNCTION fun_instr(ns VARCHAR(100),s TEXT,isplit CHAR(1))  
  6.     RETURNS INT(8)  
  7.     BEGIN  
  8.  
  9.         DECLARE strPosition INT(8);  
  10.  
  11.         SET strPosition = FIND_IN_SET(ins,REPLACE(s,split,','));  
  12.         RETURN strPosition;  
  13.     END$$  
  14.  
  15. DELIMITER ;  
  16.  

# Usage

 
 
  1. select *   
  2.     from Category  
  3.     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:

 
 
  1. DELIMITER $
  2.  
  3. Drop function if exists fun_custom_instr $
  4.  
  5. Create function fun_custom_instr (ins VARCHAR (255), s TEXT, split VARCHAR (10 ))
  6. Returns int (8)
  7. BEGIN
  8. DECLARE splitlen INT (2 );
  9. DECLARE strPosition INT (8 );
  10. SET splitLen = LENGTH (split );
  11. SET strPosition = 0;
  12. # The characters in the first segment are equal
  13. IF s = ins THEN
  14. RETURN 1;
  15. End if;
  16. # The characters in the middle segment are equal
  17. While instr (s, split)> 0 DO
  18. SET strPositionstrPosition = strPosition + 1;
  19. If left (s, INSTR (s, split)-1) = ins THEN
  20. RETURN strPosition;
  21. End if;
  22. SET s = SUBSTRING (s, INSTR (s, split) + splitLen );
  23. End while;
  24. # The most character segment is equal
  25. IF s = ins THEN
  26. RETURN strPosition + 1;
  27. End if;
  28.  
  29. RETURN 0;
  30. END $
  31.  
  32. DELIMITER;
  33.  

# Usage

 
 
  1. select *   
  2.     from Category  
  3.     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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.