mysql通配符％vs %% [英] mysql wildcards % vs %%

问题描述

在mysql中带有'LIKE'的where子句时，'％'和'%%'有什么区别?

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%%"

推荐答案

在mysql中的模式匹配方面，%%和%之间没有区别.

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 5.0参考手册:: 11.5. 1个字符串比较函数:: LIKE

如果字符串被传递给诸如 sprintf 出现了我前面提到的格式字符串规则，尽管在这种情况下没有混淆.

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.

开发人员希望它成为传递给mysql的字符串中的单个%，因此编写了%%.

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%'";

---

使用LIKE运算符的一些示例SELECT

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)

这篇关于mysql通配符％vs %%的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！