Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.0k views
in Technique[技术] by (71.8m points)

mysql wildcards % vs %%

What is the difference in '%' and '%%', when used in mysql where clause with 'LIKE' ?

select * from `wp_users` u where u.user_nicename like "%lastuser%"

VS

select * from `wp_users` u where u.user_nicename like "%%lastuser%%"
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

There is no difference between %% and % when it comes to pattern matching in mysql.

I've seen developers get confused over this when they try to match a literal % and therefor write %%. This is most often because of the fact that format-strings often use a double % to indicate that you'd like it to be treated as an exact literal.


MySQL documentation of LIKE


What's the origin of the string, and where is it going?

If the string is passed to a function such as sprintf the format-string rule I mentioned earlier is present, though there is no confusion in that case.

The developer want it to be a single % in the string passed to mysql, and therefor wrote %%.

$query = sprintf (
  "SELECT ... FROM ... WHERE id <> %d AND data LIKE '%%hello world%%'",
  50
);

// $query => "SELECT ... FROM ... WHERE id <> 50 AND data LIKE '%hello world%'";

A few sample SELECTs using the LIKE operator

mysql> SELECT 'abc' LIKE 'ab%';
+------------------+
| 'abc' LIKE 'ab%' |
+------------------+
|                1 |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT 'abc' LIKE 'ab%%';
+-------------------+
| 'abc' LIKE 'ab%%' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 'abc' LIKE 'ab\%';
+-------------------+
| 'abc' LIKE 'ab\%' |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT 'ab%' LIKE 'ab\%';
+-------------------+
| 'ab%' LIKE 'ab\%' |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...