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

Question

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='２' 

Nanaam

+1 What is that second ２ character, anyway? :) – Dhiren

@Jørn U+FF12 FULLWIDTH DIGIT TWO. – Eldin

Answer 1

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)

Handball

Answer 2

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

Muggy

Yup, mysql did return the same record, as the answer described, this is related with collation. – Nanaam

Answer 3

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.

Paestum