MySQL string function FIND_IN_SET details

FIND_IN_SET is a string function in MySQL that finds the position of a string in a comma-separated list of strings. It returns an integer representing the location of the found string (counting from 1), or 0 if the string is not in the list. This function is often used to handle fields that are stored as comma-separated strings, although this design is generally not recommended (it is better to use the advantages of a relational database, such as creating many-to-many relational tables).

grammar

FIND_IN_SET(str, strlist)

str: The string to look for.

strlist: A list of comma-separated strings.

Give an example

Suppose you have a table named user that contains a list of hobbies, which store the user's hobbies, separated by commas.

SELECT * FROM users WHERE FIND_IN_SET('eat', hobbies) > 0;

Matters needing attention

Performance: Using FIND_IN_SET on large data sets can cause performance problems because it requires progressive scanning and parsing of comma-separated strings.

Indexes: FIND_IN_SET cannot be used on indexes, which means that queries may not be able to use indexes to speed up data retrieval.

Data normalization: It is better to normalize this multi-valued field, for example by creating a new user_hobbies table with the user_id and hobby fields, each hobby of each user taking up a row. This allows you to use standard SQL join operations, and you can leverage indexes to improve query performance.

In general, while FIND_IN_SET can be useful in some cases, it's best to avoid comma-separated strings to store multiple values when designing a database, and instead take a more normalized approach.