Why is MySQL's LIKE Operator Case Sensitive When Using a Binary String?

Case Sensitivity with LIKE in MySQL

While performing a LIKE search in MySQL using the query "SELECT concat_ws(title,description) as concatenated HAVING concatenated LIKE '%SearchTerm%';", it might be noticed that the searches are case sensitive, even though the table is encoded using the utf8_general_ci collation and MyISAM storage engine.

The reason for this behavior is that the LIKE operator performs case-sensitive comparisons when either of the operands is a binary string. In this case, the "concatenated" field is a binary string, causing the search to be case sensitive.

To resolve this issue and make the search case-insensitive, it's recommended to use the BINARY keyword with the LIKE operator:

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

This ensures that the string comparison is performed binary-wise, ignoring any case differences.

Alternatively, the COLLATE keyword can be used along with the utf8_bin collation to make the search case-insensitive:

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

Both of these methods effectively modify the search behavior, making it case-insensitive while preserving the performance benefits of the utf8_general_ci collation.

The above is the detailed content of Why is MySQL's LIKE Operator Case Sensitive When Using a Binary String?. For more information, please follow other related articles on the PHP Chinese website!