CODEWITHSUNDEEP
mysql
zhuguowei
zhuguowei

Reputation: 8477

confused of mysql like query with escape character?

I have a table there are below data

select * from t;
+----+----------------+
| id | a              |
+----+----------------+
|  1 | u5929u732b     |
|  2 | \u5929\u732b   |
|  3 | \\u5929\\u732b |
+----+----------------+

Query result use equal

select * from t where a = '\u5929\u732b';
+----+------------+
| id | a          |
+----+------------+
|  1 | u5929u732b |
+----+------------+

select * from t where a = '\\u5929\\u732b';
+----+--------------+
| id | a            |
+----+--------------+
|  2 | \u5929\u732b |
+----+--------------+

select * from t where a = '\\\\u5929\\\\u732b';
+----+----------------+
| id | a              |
+----+----------------+
|  3 | \\u5929\\u732b |
+----+----------------+

these result just as I expected so no problem, then I used like to query the result, and in this time I'm very confused

# as I expected
select * from t where a like '\u5929\u732b';
+----+------------+
| id | a          |
+----+------------+
|  1 | u5929u732b |
+----+------------+

# I do not understand I think it should return result of id = 2
select * from t where a like '\\u5929\\u732b';
+----+------------+
| id | a          |
+----+------------+
|  1 | u5929u732b |
+----+------------+

# I think it should return result of id = 3
select * from t where a like '\\\\u5929\\\\u732b';
+----+--------------+
| id | a            |
+----+--------------+
|  2 | \u5929\u732b |
+----+--------------+

So why like query is different with equal query?

Upvotes: 3

Views: 52

Answers (3)

Arion
Arion

Reputation: 31239

The LIKE is escaped by default with '\'. But you can change that by writing:

select * from t where a like '\\u5929\\u732b' ESCAPE '|'

Reference:

Upvotes: 2

Rahul
Rahul

Reputation: 77856

You need to use the wildcard characters with like operator 

select * from t where a like '%5929%';

Upvotes: 2

cosinepenguin
cosinepenguin

Reputation: 1575

The \ is an escape character in mysql. This means that the statement

select * from t where a like '\\u5929\\u732b';

is equivalent to

select * from t where a like 'u5929u732b;

so the returned answer is correct.

Your third query (select * from t where a like '\\\\u5929\\\\u732b';) is therefore a query asking for

select * from t where a like '\\u5929\\u732b;',

and therefore is also correct and the statement you could use to get the desired result!

Upvotes: 2

Related Questions