MySQL LIKE Keyword
The LIKE
keyword in MySQL is used for pattern matching in SQL queries. It allows you to search for a specified pattern in a column, commonly used with SELECT
statements to filter results.
Usage
The LIKE
keyword is utilized in WHERE
clauses to find records that match a specific pattern. It supports two wildcard characters: %
matches any sequence of characters, and _
matches a single character.
sql
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;
In this syntax, LIKE pattern
is used to filter rows where column_name
matches the specified pattern.
Examples
1. Basic Pattern Matching
sql
SELECT *
FROM products
WHERE product_name LIKE 'a%';
This query retrieves all rows from the products
table where the product_name
starts with the letter 'a'.
2. Single Character Wildcard
sql
SELECT *
FROM employees
WHERE last_name LIKE 'Sm_th';
This example fetches rows from the employees
table where the last_name
is five characters long, starting with "Sm" and ending with "th", with any single character in between.
3. Combining Wildcards
sql
SELECT *
FROM customers
WHERE address LIKE '%Street%';
Here, the query selects records from the customers
table where the address
contains the word "Street" anywhere within the string.
4. Enforcing Case Sensitivity
sql
SELECT *
FROM users
WHERE BINARY username LIKE 'Admin%';
This example enforces case sensitivity and retrieves rows where the username
starts with "Admin", considering the exact case.
Tips and Best Practices
- Use wildcards sparingly. Overuse of
%
can lead to full table scans, which may affect performance. Indexing is often ineffective when%
is at the start of a pattern. - Case sensitivity and collations.
LIKE
is case-insensitive for most collations, but some are case-sensitive. UseBINARY
to enforce case sensitivity if needed. It can also be used with Unicode character sets, affecting special characters. - Optimize with indexes. Consider creating indexes on columns frequently used with
LIKE
for improved query performance, though note that wildcards at the start of the pattern can negate these benefits. - Combine with other conditions. Enhance query efficiency by combining
LIKE
with other conditions in theWHERE
clause. - Performance analysis. Use
EXPLAIN
to analyze howLIKE
queries impact performance and adjust your queries accordingly.