Why is Case-Insensitive Searching in MySQL with LIKE Operator and utf8_general_ci Encoding Case-Sensitive?

Case-Insensitive Searching in MySQL

This post explores the issue where MySQL queries utilizing LIKE operator in tables with utf8_general_ci encoding and MyISAM storage engine result in case-sensitive searches.

To resolve this, there are two recommended solutions:

1. Using Binary Strings:

By appending BINARY keyword to the LIKE operator, case-insensitive searching can be achieved:

SELECT .... FROM .... WHERE `concatenated` LIKE BINARY '%SearchTerm%';

This approach performs better than others as it forces binary string comparison, making it more efficient.

2. Using COLLATE:

Alternatively, the COLLATE keyword can be used to specify a specific collation rule:

SELECT .... FROM .... WHERE `concatenated` like '%SearchTerm%' COLLATE utf8_bin;

By using utf8_bin collation, case-insensitive comparison is enforced. While this method provides more flexibility, it may have a slight performance impact compared to the binary string approach.

The above is the detailed content of Why is Case-Insensitive Searching in MySQL with LIKE Operator and utf8_general_ci Encoding Case-Sensitive?. For more information, please follow other related articles on the PHP Chinese website!