php - How to perform a range search for an age group field in a format similar to 20-35 in MYSQL?

Question

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

Answer 1

According to what you said, if 30-55 matches 25-34, it can be like this

select * from tbl where left(age,2)<=34 and right(age,2)>=25;

Answer 2

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

Answer 3

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

Answer 4

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?

Answer 5

A lot of people who don’t understand the built-in functions of MySQL give random guidance to others

Answer 6

The source of the problem is that the database design is unreasonable

Answer 7

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?

1. Set A and set C have intersection

2. Set A is a subset of set C

3. 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.

Answer 8

Your table structure cannot achieve the function you mentioned