Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
-
Windows 2012 R2 Server, Ubuntu LTS 16.04.2
Description
I found out there is different behavior in different collations for UTF8 escape wildcard LIKE matching.
mysql> SET collation_connection = utf8_unicode_ci;
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> SELECT '\%b' LIKE '%\%';
|
+------------------+
|
| '\%b' LIKE '%\%' |
|
+------------------+
|
| 1 |
|
+------------------+
|
1 row in set (0.00 sec)
|
|
mysql> SET collation_connection = utf8_general_ci;
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> SELECT '\%b' LIKE '%\%';
|
+------------------+
|
| '\%b' LIKE '%\%' |
|
+------------------+
|
| 0 |
|
+------------------+
|
1 row in set (0.00 sec)
|
|
mysql> SET collation_connection = utf8mb4_unicode_ci;
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> SELECT '\%b' LIKE '%\%';
|
+------------------+
|
| '\%b' LIKE '%\%' |
|
+------------------+
|
| 1 |
|
+------------------+
|
1 row in set (0.00 sec)
|
|
mysql> SET collation_connection = utf8mb4_general_ci;
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> SELECT '\%b' LIKE '%\%';
|
+------------------+
|
| '\%b' LIKE '%\%' |
|
+------------------+
|
| 0 |
|
+------------------+
|
1 row in set (0.00 sec)
|
|
mysql> SET collation_connection = utf8mb4_unicode_520_ci;
|
Query OK, 0 rows affected (0.00 sec)
|
|
mysql> SELECT '\%b' LIKE '%\%';
|
+------------------+
|
| '\%b' LIKE '%\%' |
|
+------------------+
|
| 1 |
|
+------------------+
|
1 row in set (0.00 sec)
|
I found out this problem on MySQL for a long time ago, but it says to use another collation instead, is there any ways to solve it?
Attachments
Issue Links
- duplicates
-
MDEV-17064 LIKE function has error behavior on the fields in which the collation is xxx_unicode_xx
- Closed
- links to