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