Deep Analysis of MySQL NOT LIKE Operator: From Pattern Matching to Precise Exclusion

Problem Background and Scenario Analysis

In practical database application development, there is often a need to filter or exclude data records based on specific patterns. MySQL provides powerful pattern matching capabilities, with LIKE and NOT LIKE operators being core tools for implementing these requirements. This article will explore the correct usage of the NOT LIKE operator through a specific case study.

Case Problem Description

Assume we have a developer configuration table developer_configurations_cms containing a cfg_name_unique field that stores various configuration names. The database contains the following sample data:

categories_posts
categories_news
posts_add
news_add

The developer's requirement is to query all records that do not contain the "categories" string. The initial query statement is as follows:

SELECT *
FROM developer_configurations_cms
WHERE developer_configurations_cms.cat_id = '1'
AND developer_configurations_cms.cfg_variables LIKE '%parent_id=2%'
AND developer_configurations_cms.cfg_name_unique NOT LIKE '%categories%'

However, this query still returns the categories_posts and categories_news records, which clearly does not meet expectations.

NOT LIKE Operator Working Principle

MySQL's NOT LIKE operator is used to exclude records that match a specified pattern. Its basic syntax is:

expr NOT LIKE pat [ESCAPE 'escape_char']

This operator performs simple regular expression comparison, returning 1 (TRUE) or 0 (FALSE). If either the expression or pattern is NULL, the result is also NULL.

Wildcard Detailed Explanation

The NOT LIKE operator supports two main wildcards:

• Percent (%): Matches any number of characters (including zero characters)
• Underscore (_): Matches exactly one character

To match the wildcard characters themselves, escape characters must be used. By default, the backslash (\) is used as the escape character.

Problem Analysis and Solution

The issue with the original query lies in using NOT LIKE '%categories%', which means any field value containing the "categories" substring would be excluded. However, observing the data reveals that categories_posts and categories_news both start with "categories_".

The correct solution is to modify the pattern matching condition to only exclude records starting with "categories":

SELECT *
FROM developer_configurations_cms
WHERE developer_configurations_cms.cat_id = '1'
AND developer_configurations_cms.cfg_variables LIKE '%parent_id=2%'
AND developer_configurations_cms.cfg_name_unique NOT LIKE 'categories%'

This modified query uses NOT LIKE 'categories%', which only excludes records starting with "categories", without excluding other records containing the "categories" substring.

Performance Considerations and Best Practices

When using the NOT LIKE operator, the following points should be noted:

1. Pattern Position Affects Performance: Patterns starting with wildcards (like %categories) typically cannot effectively utilize indexes and may cause full table scans
2. Prefer Exact Matching: Whenever possible, use exact matching or prefix matching to better utilize indexes
3. Escape Character Usage: Remember to use escape characters when matching text containing wildcard characters

Extended Application Scenarios

The NOT LIKE operator can also be combined with other conditions to implement more complex filtering logic. For example, multiple patterns can be excluded simultaneously:

SELECT *
FROM developer_configurations_cms
WHERE cfg_name_unique NOT LIKE 'categories%'
AND cfg_name_unique NOT LIKE 'temp%'
AND cfg_name_unique NOT LIKE 'test%'

Conclusion

MySQL's NOT LIKE operator is a powerful tool for handling pattern exclusion requirements. By correctly understanding wildcard behavior and pattern matching mechanisms, unwanted records can be effectively filtered out. In practical applications, appropriate matching patterns should be selected based on specific data characteristics and query requirements, while paying attention to performance optimization and code maintainability.