MySQL: '%' does not match with a text starting with a TAB character for a TEXT type column with the utf8mb4_bin collation

user_1130106 0
Viewed 55

'%' does not match with a part starting with a TAB character when the column is a TEXT, its collation is utf8mb4_bin, it is indexed, and the index is used in a query.

This is an example to reproduce the behavior.

CREATE TABLE table1(
  id INT PRIMARY KEY AUTO_INCREMENT,
  text1 TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);

CREATE INDEX idx_text1 ON table1 (text1(100));

INSERT INTO table1 (text1) VALUES ('a\tb');
INSERT INTO table1 (text1) VALUES ('ab');

SELECT * FROM table1 WHERE text1 LIKE 'a%';

I expected this shows the both records, but it only showed the second record.

+----+-------+
| id | text1 |
+----+-------+
|  2 | ab    |
+----+-------+
1 row in set (0.00 sec)

By making any changes below, SELECT * FROM table1 WHERE text1 LIKE 'a%' returned the both records.

  • Change the collation to utf8mb4_general_ci or utf8mb4_unicode_ci
  • Change not to create the index on the text1 column
  • Change the text1's type to VARCHAR

This happened on MySQL 5.6.

Is this an expected behavior?

mysql
0 Answers
Related