Скачать или смотреть How to Select Rows with a Specific Value in a MySQL Column Containing Multiple Values

Learn how to effectively query rows in MySQL where a column contains multiple values, by using `find_in_set()`.
---
This video is based on the question https://stackoverflow.com/q/66817207/ asked by the user 'Rajdeep Das' ( https://stackoverflow.com/u/13111089/ ) and on the answer https://stackoverflow.com/a/66817225/ provided by the user 'Gordon Linoff' ( https://stackoverflow.com/u/1144035/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: I want to select rows which has a particular value in the column but the column can contain multiple values

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Selecting Rows with a Specific Value in a MySQL Column

When working with databases, particularly in MySQL, you may encounter situations where a column contains multiple comma-separated values within a single row. This raises the question: How can we select rows that contain a particular value in such a column? In this guide, we’ll take a deep dive into a common problem when querying MySQL databases and explore a practical solution using built-in functions.

The Problem

Consider the following data structure:

col_1col_20ab,bc,cd1bc,xy2zz,xx3ab4cc5ef,kk,okIn this table, the col_2 column contains multiple values stored as a single string. You might want to select rows that include ab among these values. In this example, the 0th and 3rd rows should be included in the selection because both contain the value ab in col_2.
The question then is: Is there an SQL query that can achieve this?

The Ideal Solution

1. Data Model Consideration

Before diving into the query, it is crucial to point out that storing multiple values in a string is not a best practice in database design. Ideally, each col_1 and col_2 combination should have its own row. This way, querying becomes straightforward and efficient without needing to handle string manipulations.

However, we often find ourselves dealing with existing databases that may not follow these best practices. In such cases, we rely on MySQL’s functions to help us work around these issues.

2. Using find_in_set() Function

MySQL offers a built-in function called find_in_set() that can be used for this exact scenario. This function is designed to find a specified string within a comma-separated list of strings. Here's how you can use it:

[[See Video to Reveal this Text or Code Snippet]]

In this query:

Replace your_table_name with the name of your actual table.

The condition find_in_set('ab', col_2) > 0 checks if ab exists in the col_2 column.

3. Understanding the Outcome

Applying this query to the example data, the result will return the rows that contain the value ab in the col_2 field. You will get both the 0th row (ab,bc,cd) and the 3rd row (ab) as a result of your selection.

Conclusion

While using find_in_set() is a convenient temporary solution for querying columns that contains multiple values, it’s advisable to consider restructuring your data model in the long run. Proper normalization will not only improve query performance but also simplify data management in the future.

By being mindful of these best practices, your database will be much more efficient and easier to work with.

If you find yourself constantly needing to manipulate data in this way, it might be worth considering a redesign of your database schema for greater long-term benefits.