Why are MySQL LIKE Queries Case-Sensitive with utf8_general_ci Encoding?

Case Sensitivity in MySQL LIKE Queries

Problem:

Consider the following MySQL query:

SELECT concat_ws(title, description) as concatenated
HAVING concatenated LIKE '%SearchTerm%';

Despite using the LIKE operator, searches appear to be case sensitive when the table encoding is utf8_general_ci with a MyISAM storage engine.

Explanation:

The MyISAM storage engine uses case-insensitive comparisons for strings stored in utf8_general_ci encoding. However, the LIKE operator performs case-sensitive comparisons by default.

Optimal Solution:

To perform case-insensitive comparisons, use the BINARY keyword as follows:

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

String comparisons become case-sensitive when one of the operands is a binary string.

Alternative Solution:

Alternatively, use the COLLATE statement to specify a locale-specific collation:

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

This approach allows for case-insensitive comparisons while maintaining the original character encoding.

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