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. Use BINARY 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 the WHERE clause.
• Performance analysis. Use EXPLAIN to analyze how LIKE queries impact performance and adjust your queries accordingly.