find_in_set - 请教 mysql查询

Question

mysql 表中字段option_tag 存储的值格式（分号）为： 4:539;8:543;4:545;8:549
通过 find_in_set() 可以查找到以逗号分隔的字符串。
以分号分隔的 字符串 有什么函数直接能查询到 option 含有 539的所有值？

Answer 1

sql statement:
SELECT * FROM table
WHERE locate(':539;', CONCAT(options_tag, ';')) > 0

The logic of using like is not rigorous. For example, 4:2539 will also be matched by like, but it is not the desired result.

From the design perspective, if it is a complex data type, it is recommended to save it in json format. The higher version of mysql directly has built-in json parsing and query functions.

Answer 2

Just use like or regular rules.
However, it is not recommended to put logic in mysql, which will cause excessive pressure on the database engine and is extremely unsafe.
The correct approach should be to take out the value of the entire field, use it as a string and then use a language such as php python nodejs to process it.

Answer 3

SELECT * FROM table
WHERE options_tag LIKE '%539%';

As @小草哥 said, this is a design flaw. This seems to be a one-to-many or many-to-many relationship. It should not be stored in a field. Not only is the query efficiency low (the field is processed in SQL The operation may make the index of this field unusable), and the query is not flexible.

Answer 4

like match

Answer 5

like :539

Answer 6

I say this is a design flaw, does anyone agree?

Answer 7

like similar matching

Answer 8

Blurry
Query
Research