How to use MySQL's FIND_IN_SET function to find whether a certain value exists in a list of strings

How to use MySQL's FIND_IN_SET function to find whether a certain value exists in a string list

Introduction:
MySQL is a commonly used relational database management system that provides many powerful functions and operators to manipulate data. Among them, the FIND_IN_SET function is a commonly used string search function that can be used to determine whether a value exists in a comma-separated string list. This article will introduce in detail how to use the FIND_IN_SET function and provide relevant code examples.

FIND_IN_SET function syntax:
FIND_IN_SET(str, strlist)

• str: ​​the string or expression to be found;
• strlist: comma Delimited list of strings.

The FIND_IN_SET function returns an integer value representing the position where the value is found in the string list. If the value is not found, 0 is returned.

Code example:
Suppose we have a table named students, which contains information about the courses selected by each student. The table structure is as follows:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    courses VARCHAR(100)
);

Now, we want to find whether a specific course is included in the courses field, which can be achieved using the FIND_IN_SET function.

First, insert some test data:

INSERT INTO students (name, courses)
VALUES ('小明', '语文,数学,英语'),
       ('小红', '数学,化学,物理'),
       ('小刚', '英语,历史,地理');

Next, we can use the following code to query whether a student has taken a specific course:

SET @course = '数学';
SELECT id, name
FROM students
WHERE FIND_IN_SET(@course, courses) > 0;

In In the above code, the course name to be found is assigned to the variable @course. Then, in the SELECT statement, use the FIND_IN_SET function to determine whether the variable @course exists in the courses field. If the returned value is greater than 0, it means the course was found and the student's id and name will be returned.

After running the above code, we will get the following results:

+----+--------+
| id | name   |
+----+--------+
| 1  | 小明   |
| 2  | 小红   |
+----+--------+

As can be seen from the results, the students who took the mathematics course include Xiao Ming and Xiao Hong.

Summary:
This article describes how to use MySQL's FIND_IN_SET function to find whether a certain value exists in a string list. Through this function, we can easily determine whether a course is taken by a student. By mastering the use of the FIND_IN_SET function, you can operate string list data in the MySQL database more flexibly and efficiently.

Please note that when using the FIND_IN_SET function, you need to ensure that the parameter types passed in are correct to avoid unexpected results.

The above is the detailed content of How to use MySQL's FIND_IN_SET function to find whether a certain value exists in a list of strings. For more information, please follow other related articles on the PHP Chinese website!