当在字符串中搜索带点(.)的值时，MySQL查询返回0行 [英] MySQL query returns 0 rows when searching for value with dot (.) in string

问题描述

如果我尝试在mysql数据库中搜索一个值，并且字符串值中包含点，则查询返回0行.示例:

If I try to search for a value in mysql database and the string value contains dot in it, query returns 0 rows. Example:

SELECT * FROM table WHERE `username`='marco.polo'  --> 0 rows
SELECT * FROM table WHERE `username` LIKE '%.polo%'  --> 0 rows
SELECT * FROM table WHERE `username` LIKE 'polo'  --> Success

将服务器和数据库移至另一个位置后出现.我知道点是一组扩展的正则表达式，但不应将其应用于equal或LIKE运算符，只是因为我不在查询中使用REGEXP.

This appeared after moving server and database to another place. I know that dot is a set of extended regular expressions, but it should not apply to equal nor LIKE operator, simply because I don't use REGEXP in query.

我已经在本地数据库上测试了相同的查询，并且工作正常. mysql中是否可以有一个特殊的设置来将点处理与通常情况不同?

I've tested the same query on my local database and it works fine. Could there be a special setting in mysql that treats dot differently than it usually does?

推荐答案

user1084605，我尝试复制该问题(使用MySQL版本5.1.37)，但结果与您完全相反.见下文:

user1084605, I tried to replicate the problem (using MySQL version 5.1.37), but got exactly the opposite results as you. See below:

mysql> create table test (username varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values ('marco.polo');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test WHERE `username`='marco.polo';
+------------+
| username   |
+------------+
| marco.polo | 
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE `username` LIKE '%.polo%';
+------------+
| username   |
+------------+
| marco.polo | 
+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test WHERE `username` LIKE 'polo';
Empty set (0.00 sec)

根据MySQL文档，使用LIKE运算符时，唯一的特殊字符是％"(百分比:匹配0、1或许多字符)和"_"(下划线:仅匹配一个字符). http://dev.mysql.com/doc/refman/5.0/zh-CN/string-comparison-functions.html

According to the MySQL docs, the only special characters when using the LIKE operator are "%" (percent: matches 0, 1, or many characters) and "_" (underscore: matches one and only one character). http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

一个." (句点)对于MySQL的REGEXP运算符确实具有特殊含义，但它仍应与您的列中的文字时间段匹配. http://dev.mysql.com/doc/refman/5.0/en/regexp.html

A "." (period) does have special meaning for MySQL's REGEXP operator, but it should still match a literal period in your column. http://dev.mysql.com/doc/refman/5.0/en/regexp.html

您可以复制我上面运行的SQL语句并粘贴结果以作答复吗?

Can you replicate the SQL statements I ran above and paste your results in reply?

这篇关于当在字符串中搜索带点(.)的值时，MySQL查询返回0行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！