[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
• Connection Character Sets and Collations