MySQL FIND_IN_SET Function Guide: Usage, Tips & Alternatives

1. Introduction: Common Cases Where FIND_IN_SET is Needed

The Savior When Struggling with Tag Searches

Common Usage Scenarios

• When you want to extract a specific value from “tags” or “categories” saved in comma-separated format
• When you want to use values input in CSV format in the admin screen, etc., as search conditions
• When you want to perform flexible filtering on meta information in CMS like WordPress
• When you want to process without modifying an existing table where multiple selection items are consolidated into one column

2. What is the FIND_IN_SET Function? [Basic Syntax and Return Value]

Basic Syntax

FIND_IN_SET(search_value, comma-separated_string)

• Search Value: The string to search for
• Comma-Separated String: The comma-separated list to search in

Usage Example

SELECT FIND_IN_SET('python', 'php,python,sql');

SELECT FIND_IN_SET('ruby', 'php,python,sql');
-- Result: 0

SELECT FIND_IN_SET(NULL, 'php,python,sql');
-- Result: NULL

Return Value Specifications

Note: 0 Means “Does Not Exist”

3. Practical Example 1: Basic Usage [Simple SELECT Statement]

Preparation of the Example Table

Example: Searching for Users with “python”

SELECT * FROM user_tags
WHERE FIND_IN_SET('python', tags);

Exact String Matching is the Key

Example of Search Using Variables in SQL

SET @skill = 'python';

SELECT * FROM user_tags
WHERE FIND_IN_SET(@skill, tags);

4. Practical Example 2: Handling Dynamic Searches (Variables and Form Integration)

Dynamic Search Using Variables in SQL

-- Store the tag to search for in a variable
SET @target_tag = 'python';

-- Dynamic search with FIND_IN_SET
SELECT * FROM user_tags
WHERE FIND_IN_SET(@target_tag, tags);

Integration with Applications: PHP Case

<?php
$tag = $_GET['tag']; // Example: Form input "python"

// SQL generation (prepared statements are recommended)
$sql = "SELECT * FROM user_tags WHERE FIND_IN_SET(?, tags)";

$stmt = $pdo->prepare($sql);
$stmt->execute([$tag]);
$results = $stmt->fetchAll();
?>

Application in WordPress: Tag Search in Custom Fields

global $wpdb;
$tag = 'python';

$sql = $wpdb->prepare(
  "SELECT * FROM {$wpdb->prefix}postmeta WHERE meta_key = %s AND FIND_IN_SET(%s, meta_value)",
  '_user_tags', $tag
);
$results = $wpdb->get_results($sql);

Caution: Be Careful with Spaces and Full-Width Commas

• Remove spaces with the TRIM() function
• Normalize the comma format (full-width to half-width)
• Input validation on the application side

5. Advanced Techniques for FIND_IN_SET [GROUP_CONCAT, Subqueries, JOIN]

Combining with GROUP_CONCAT

SELECT *
FROM user_tags
WHERE FIND_IN_SET('python', (
  SELECT GROUP_CONCAT(tag_name)
  FROM master_tags
));

Retrieving Values Dynamically with Subqueries for Searching

SELECT *
FROM user_tags
WHERE FIND_IN_SET(
  'python',
  (SELECT setting_value FROM search_conditions WHERE id = 1)
);

Comparison with JOIN: JOIN is Superior in Normalized Structures

• users table
• tags table
• user_tag_relation table (intermediate table holding user_id and tag_id)

SELECT users.*
FROM users
JOIN user_tag_relation ON users.id = user_tag_relation.user_id
JOIN tags ON user_tag_relation.tag_id = tags.id
WHERE tags.name = 'python';

Which Technique Should You Choose?

6. Pitfalls and Precautions for FIND_IN_SET [Performance and Design Aspects]

Performance Deteriorates Because Indexes Do Not Work

SELECT * FROM user_tags
WHERE FIND_IN_SET('python', tags);

• Consider normalization using an intermediate table as needed
• If you must use FIND_IN_SET, narrow down the target records in advance (use in combination with LIMIT or other WHERE conditions)

Becoming Dependent on a Structure That Contradicts Normalization

• Aggregation and statistical processing per value is difficult
• Updating or deleting only some values is challenging
• Duplicates or spelling mistakes in values are prone to occur (e.g., “Python” and “python”)

Search Fails Due to Inclusion of Characters Other Than Commas or Spaces

• Spaces (spaces, tabs, line breaks) before and after values
• Full-width commas (，) are mixed in
• Unintentionally surrounded by double quotes or single quotes

FIND_IN_SET('python', 'php, python ,sql')
-- ⇒ Does not match (it becomes " python " with spaces)

• At the data registration stage, include TRIM() processing to remove spaces
• Preprocess input values with REPLACE(tags, ' ', '')
• Input restrictions on the frontend (exclude unnecessary spaces and symbols)

Effective as a Temporary Measure, But Not Suitable for Permanent Operation

7. Common Misconceptions and Failure Examples [Differences from LIKE / Handling Numbers]

Misconception ①: Not Understanding the Difference Between LIKE and FIND_IN_SET

-- Common Misuse
SELECT * FROM user_tags WHERE tags LIKE '%python%';

-- Correct Way to Write
SELECT * FROM user_tags WHERE FIND_IN_SET('python', tags);

Misconception ②: FIND_IN_SET Doesn’t Work as Intended with “Numeric” Values

-- tags column contains: 1,2,10,20
SELECT * FROM user_tags WHERE FIND_IN_SET(1, tags);

Misconception ③: Doesn’t Match Correctly Due to Spaces, Full-width Commas, Line Breaks, etc.

• Spaces (space, tab, newline) before or after values
• Full-width commas (、) are mixed in
• Surrounded by unintended double quotes or single quotes

FIND_IN_SET('python', 'php, python ,sql')
-- ⇒ Doesn't match (becomes " python " with spaces)

• At the data registration stage, include TRIM() processing to remove spaces
• Preprocess input values with REPLACE(tags, ' ', '')
• Input restrictions on the frontend (exclude unnecessary spaces and symbols)

Summary: Points for Using FIND_IN_SET Safely

8. Alternatives to FIND_IN_SET [Best Practices]

Switch to Normalized Table Design

SELECT users.*
FROM users
JOIN user_tag_relation ON users.id = user_tag_relation.user_id
JOIN tags ON user_tag_relation.tag_id = tags.id
WHERE tags.name = 'python';

Utilizing JSON Type (MySQL 5.7 and Later)

["php", "python", "sql"]

SELECT * FROM user_tags
WHERE JSON_CONTAINS(tags_json, '"python"');

Decompose and Reconstruct on the Application Side

$tags = explode(',', $record['tags']);
if (in_array('python', $tags)) {
    // Execute processing
}

FIND_IN_SET Should Be Used as an “Exceptional Handling”

9. [FAQ] Frequently Asked Questions and Their Answers

Q1. When is it correct to use the FIND_IN_SET function?

• Design requires storing multiple values in one column (e.g., tags, permissions, flags, etc.)
• You want to search without modifying an existing denormalized database
• For small to medium data volumes, limited use cases (admin panels, tools, etc.)

Q2. What is the difference between FIND_IN_SET and LIKE?

-- Example with LIKE (matches everything containing "python")
 tags LIKE '%python%'

-- Example with FIND_IN_SET (matches only the independent element "python")
 FIND_IN_SET('python', tags)

Q3. Why does using the FIND_IN_SET function make SQL slow?

Q4. When searching for numbers, is there no misrecognition between “1” and “10”?

-- Correct example
 FIND_IN_SET('1', '1,2,10') -- ⇒ 1 (first position)

-- Example prone to misunderstanding
 FIND_IN_SET(1, '1,2,10') -- ⇒ Similarly 1 (OK but ambiguous)

Q5. Can I use FIND_IN_SET in WordPress?

global $wpdb;
$sql = $wpdb->prepare("
  SELECT * FROM {$wpdb->prefix}postmeta
  WHERE meta_key = %s AND FIND_IN_SET(%s, meta_value)
", 'your_meta_key', 'search_value');

$results = $wpdb->get_results($sql);

Q6. What is the difference with JSON type? Is it more convenient than FIND_IN_SET?

-- Search with JSON
 SELECT * FROM users WHERE JSON_CONTAINS(tags_json, '"python"');

10. Summary: FIND_IN_SET is a “Convenient Exception” | An Opportunity to Review Structural Design

Reviewing the Features of FIND_IN_SET

Cases Where It Should Be Used and Cases to Avoid

• The search target is small-scale and the use is limited
• Modifying the existing system is difficult, and immediate response is needed
• Want to temporarily handle it in admin screens or batch processing, etc.

• Situations where search speed is required for large-scale data
• Businesses that require frequent updates, aggregation, and condition changes
• Designs that assume future expansion and maintenance

FIND_IN_SET is a “Convenient Exception”. The Essence Lies in Reviewing the Design

• Normalize the database and manage many-to-many relationships with intermediate tables
• If flexibility is needed, introduce JSON type to handle structured data