mysql in 当行查询,当在字符串中搜索带点（。）的值时，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

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.

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, 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)

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

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

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