There is a field age in mysql, which stores the age group. The format is as shown in the figure. If it is 0-0, it is unlimited
The current requirement is to search based on age group. For example, I want to find all data between the ages of 25-34.
Then the problem comes. If you use and in where, you will get very few results, almost none. If you use or, the results will become more, but it is obvious that the results are not accurate. How can I search it accurately
Thank you for your answers. The method I am currently using is
SELECT age FROM
jobs
WHERE ((substring_index(age,'-',1) >= 16 OR substring_index(age,'-',-1) = 0 ) OR (substring_index(age,'-',-1) <= 24)) AND !(substring_index(age,'-',1) = 0 AND substring_index(age,'-',-1) = 0) ;
It can barely be used, but something feels weird
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
According to what you said, if 30-55 matches 25-34, it can be like this
This is difficult to judge. Does 30-55 meet the conditions of 25-34 you want? Or does it have to be within 25-34? It depends on your specific requirements
I am very curious as to why the data table structure is designed like this. Why is it not storing age? If the table structure is designed like this, why do we need to fetch data between 25-34 years old? Your age is just a specified range, not a specific value. You should redesign your table structure and don’t make mistakes by mistake
1. If you save the age group like this, should you create another key to distinguish the age span?
2. The screenshot also shows span designs such as 1-5, 2-6, and 3-4. If the user chooses 3, which one should he choose?
A lot of people who don’t understand the built-in functions of MySQL give random guidance to others
The source of the problem is that the database design is unreasonable
It is recommended that you use abstract thinking to think back: think of the information stored in the age field in the table as set A, and think of the conditions in where as set C. What situation should your final result meet?
Set A and set C have intersection
Set A is a subset of set C
Set C is itself of set C
On this basis, your current table structure is definitely not suitable. If the amount of data is not large, you can clean the data offline and split the age field into lower limit and upper limit, and then use the collection filtering method. That’s a lot.
Your table structure cannot achieve the function you mentioned