[mysql] Perchè Select 'accetto'='accettò' mi da 1 ?

[peterx]

Come da titolo, xchè mysql non fa differezza sui caratteri accentati e la query riconosce uguali le due parole?

[Gimli[2BV!2B]]

Collation della connessione, che influenza ciò che ci viene trasmesso:

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)
Set di caratteri della connessione:

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
Gli accenti esistono usando latin1+latin1_swedish_ci:

mysql> Select 'accetto'='accettò';
+----------------------+
| 'accetto'='accettò' |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)
Vediamo cosa ne pensa utf8+utf8_general_ci:

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

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

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> Select 'accetto'='accettò';
+----------------------+
| 'accetto'='accettò' |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)

mysql> Select 'ÀÇÇÉTTŌ'='accettò';
+---------------------------+
| 'ÀÇÇÉTTŌ'='accettò' |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
utf8+utf8_general_ci ignora diacritici e maiuscole!

Rewind:

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

mysql> Select 'accetto'='accettò';
+----------------------+
| 'accetto'='accettò' |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.01 sec)


Collation Implementation Types (http://dev.mysql.com/doc/refman/5.5/en/charset-collation-implementations.html)
Connection Character Sets and Collations (http://dev.mysql.com/doc/refman/5.5/en/charset-connection.html)