MySQL 8: How to perform case-insensitive string comparison

Introduction

In the world of database management, attention to detail is key, and sometimes those details come down to the very case of the characters within the data you’re comparing. MySQL is a robust database management system that often requires comparing string values. By default, string comparisons in MySQL are case-insensitive for CHAR, VARCHAR, and TEXT fields that are using a collation that ends with ‘_ci’ (case-insensitive).

However, when dealing with columns that have a binary collation or even performing case-sensitive operations on a case-insensitive column, managing case sensitivity becomes crucial. In this tutorial, we will explore various ways you can perform case-insensitive string comparisons in MySQL 8. We will cover basic comparisons and move toward more complex examples. Let’s dive in and understand what it takes to compare strings irrespective of their case.

Understanding Collation and Character Sets

Before comparing strings, it’s important to understand what collation and character sets are as they dictate how strings are compared in MySQL:

• Character Set: A collection of symbols and encodings. UTF-8 is a common character set that includes characters from most of the world’s writing systems.
• Collation: A set of rules determining how characters in a given character set should be compared and sorted. In MySQL, the collation name suffix ‘_ci’ indicates case-insensitive collation, while ‘_cs’ indicates case-sensitive, and ‘_bin’ implies binary comparison, which is case sensitive as well.

Setting the Default Collation of a Table

By setting the default collation of a table to case-insensitive, any string comparison made against column values in that table will be case-insensitive unless otherwise specified. Here’s how to define a table with a case-insensitive collation:

CREATE TABLE example (
    message VARCHAR(100) COLLATE utf8mb4_unicode_ci,
);

This table has a VARCHAR column with a case-insensitive collation, meaning comparisons against the ‘message’ column are inherently case-insensitive.

Case-Insensitive Comparisons Using COLLATE

If your column does not have a case-insensitive collation by default, you can still perform a case-insensitive comparison using the COLLATE keyword:

SELECT 'CaseInsensitive' COLLATE utf8mb4_unicode_ci = 'caseinsensitive';

This query will return 1 or TRUE, because the collation has been set to case-insensitive for the purpose of this comparison.

Using LOWER() or UPPER()

Another way to perform a case-insensitive comparison, regardless of the column’s collation, is by using the LOWER() or UPPER() functions. Here’s an example:

SELECT LOWER('TEXT') = LOWER('text');

The output will be 1, as both strings are being compared in their lowercase form.

Using the LIKE Operator

The LIKE operator in MySQL performs case-insensitive comparisons in a pattern match. It’s useful when you are doing comparisons with wildcards. Here’s an example:

SELECT 'mysql' LIKE 'MY%';

This returns 1, indicating a successful case-insensitive comparison.

Utilizing Case-Insensitive Indexes

If performance is a consideration and you frequently perform case-insensitive searches, it’s a good idea to have a case-insensitive index on the field. This index allows MySQL to use the index for case-insensitive comparisons efficiently:

ALTER TABLE example ADD INDEX idx_message (message(10));

The index ‘idx_message’ will speed up lookups that use the `message` column in a case-insensitive context.

Comparing Complete Strings with Equality Operator

When you want to compare complete strings for equality, you can simply use the equality operator =:

SELECT 'a' = 'A'; -- Returns 1 if the default collation is case-insensitive

This comparison will depend on the default collation of the connection. If it’s case-insensitive, it will result in a match.

Case-Insensitive Searching

Often, you’ll want to search for a string within another string; the function LOCATE() can help. When paired with LOWER(), you can search case-insensitively:

SELECT LOCATE(LOWER('My'), LOWER('How is my MySQL tutorial?'));

This command will return the index of the first occurrence of ‘my’, regardless of case, which in this case is 9.

Regular Expressions

Regular expressions support case-insensitive searches directly. Here’s an example using the REGEXP_LIKE function:

SELECT 'CaseInsensitive' REGEXP_LIKE 'caseinsensitive'; -- Returns 1

This will return true for a match using a case-insensitive regular expression.

Conclusion

Performing a case-insensitive string comparison in MySQL can be achieved through various methods, allowing for flexibility depending on the scenario. By understanding collations, using functions like UPPER() or LOWER(), leveraging the LIKE operator, or employing regular expressions, you can easily manage case-insensitive comparisons. Optimize these techniques with indexes to yield faster query performance.