MySQL LIKE Operator

MySQL LIKE Operator: Pattern Matching in Strings

The MySQL LIKE operator searches string columns for partial text patterns inside WHERE clauses. Instead of requiring an exact match like =, LIKE lets you match prefixes, suffixes, or substrings using wildcards.

Basic Syntax and Wildcards

LIKE supports two pattern wildcards:

• % (Percent): Matches zero, one, or many characters of any kind.
• _ (Underscore): Matches exactly one single character.

SELECT column1, column2
FROM table_name
WHERE text_column LIKE 'pattern%';

Practical SQL Examples

The queries below search demo_customers by email domain and name prefix:

-- Prefix match (can use a B-tree index)
SELECT id, first_name, email
FROM demo_customers
WHERE first_name LIKE 'Al%';

-- Suffix / domain match
SELECT id, first_name, email
FROM demo_customers
WHERE email LIKE '%@example.com';

-- Exclude test accounts
SELECT id, first_name, email
FROM demo_customers
WHERE email NOT LIKE '%+test%';

Common Mistakes and Best Practices

• Leading Wildcards Disable Indexes: LIKE 'alex%' can use a B-tree index range scan, whereas LIKE '%alex%' forces a full table scan on every row. For fast substring search on large tables, use a MySQL FULLTEXT index (MATCH ... AGAINST).
• Mind Collation Case Sensitivity: Under default _ci collations (such as utf8mb4_0900_ai_ci), LIKE is case-insensitive. Use LIKE BINARY or a _cs/_bin collation when exact casing matters.
• Escape Literal % and _: To search for a literal 50% discount string, escape the percent sign: WHERE note LIKE '%50\%%'.

Frequently Asked Questions

Is LIKE case-sensitive in MySQL?

By default, no—most MySQL collations end in _ci (case-insensitive), so 'al%' matches both 'Alice' and 'alice'.

When should I use FULLTEXT instead of LIKE '%word%'?

Switch to FULLTEXT indexes whenever you search descriptions, articles, or logs with more than a few thousand rows.

Does LIKE work on numeric or date columns?

MySQL implicitly casts numbers or dates to strings for LIKE, but doing so disables indexes; use range comparisons like >= and < on dates instead.