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?