Скачать или смотреть How to Select Rows in MySQL Where Column Contains Elements from a List

Discover the best practices for querying in MySQL, including efficient solutions for handling videos with multiple categories.
---
This video is based on the question https://stackoverflow.com/q/64898754/ asked by the user 'twcrnr' ( https://stackoverflow.com/u/14663791/ ) and on the answer https://stackoverflow.com/a/64898832/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) 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: MySQL Select row where column contains elements from a list

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.
---
How to Select Rows in MySQL Where Column Contains Elements from a List

When developing a database for a website that contains videos across multiple categories, you might encounter a common challenge: how to efficiently query rows where a column includes elements from a predefined list. If you've faced difficulties searching through categories—especially when each video can belong to multiple categories—this guide is for you. In this post, we'll explore potential solutions to better structure your query and database for optimal performance.

The Problem: Complex Queries

You might find yourself using MySQL queries with the LIKE operator in attempts to filter results by category. However, using LIKE can lead to complications, particularly if multiple categories share similar names or descriptions. This might produce inaccurate results that do not match your expectations, particularly in scenarios like:

Searching for 'cats_playing_with_balls'

Searching for 'dogs_playing_with_balls'

The crux of the problem is the data model—storing multiple categorical values in a single column makes queries inefficient and complicates your database design.

A Better Solution: Normalize Your Database

Step 1: Revamp Your Data Model

The most effective way to handle the categories for videos is to normalize your database. Instead of storing multiple category values in a single column, you should create separate tables that clearly define the relationships between videos and categories. Here’s how you can structure your tables:

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

By splitting the data into these tables, you create a more flexible and manageable structure that simplifies your queries.

Step 2: Efficient Querying

With a normalized data model, querying becomes straightforward and efficient. Here’s an example query to retrieve videos from a specific category:

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

This query leverages the relationships you established, ensuring accurate results without the pitfalls of LIKE searches.

Alternative for Existing Designs: FIND_IN_SET()

If you must work with an existing design where categories are stored as comma-separated values in a single column, you can use the FIND_IN_SET() function. This function finds a value in a comma-separated list, allowing you to filter your results, albeit less efficiently than the normalized model.

Here's how to use it:

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

While this may work temporarily, transitioning to a normalized data structure is recommended for long-term efficiency and manageability.

Conclusion

In summary, if you're seeking to select rows in MySQL where a column contains elements from a list, the path to success lies in having a well-structured database. By adopting a normalized schema, you streamline your queries and make data management far more effective. Whether you start with a normalized approach or migrate your existing models, these strategies will improve your database's reliability and performance significantly.

Taking the time to rethink and restructure can save you from a multitude of headaches in the future as your website evolves. Happy querying!