Hmm, why finding by '２' or '2' return the same record?

Question

Hi everyone, forgive my newbie question, but why finding by '２' or '2' in Mysql returns the same record?

For example:

Say I have a record with string field named 'slug', and the value is '２'. And the following SQLs returns same record.

SELECT * From articles WHERE slug='2'  
SELECT * From articles WHERE slug='２' 

Answer 1

they should not return the same row for equality, but if you use like you are probably getting the same row. using like mysql will use fuzzy matching, so 2 and ２ will be the same (afer all they are both a form of 2, aren't they?)

Answer 2

What is the datatype of slug? i think its numeric one. If so here mysql does cast it to int, and any ways '2' or ' 2 ' will become 2. This wont happen with string datatypes.

Answer 3

It has to do with the collation of your database:

mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> SELECT '２'='2';
+-----------+
| '２'='2' |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT '２'='2';
+-----------+
| '２'='2' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)