0

I've configured my server (5.5.41) with:

character-set-server   = utf8                                                                                                   
collation-server       = utf8_unicode_ci                                                                                        
skip-character-set-client-handshake

However

mysql> SELECT CHARSET(USER()), COLLATION(USER());
+-----------------+-------------------+
| CHARSET(USER()) | COLLATION(USER()) |
+-----------------+-------------------+
| utf8            | utf8_general_ci   |
+-----------------+-------------------+

Why is the client collation utf8_general_ci when it should be utf8_unicode_ci?

mysql> SHOW VARIABLES LIKE '%char%'; and SHOW VARIABLES LIKE '%collat%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+


mysql> SHOW VARIABLES LIKE '%collat%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
  • What is the output of SHOW VARIABLES LIKE '%char%'; and SHOW VARIABLES LIKE '%collat%';? Please edit your question to show these. – Vérace Mar 11 '15 at 14:41
  • I've added the output in the question itself. – flm Mar 11 '15 at 14:46
  • In what situation do you depend on collation-server instead of the collation of the column(s) involved? – Rick James Mar 11 '15 at 17:19
  • When using literals for instance. SELECT collation('bla') returns utf8_general_ci. So, my_field REGEXP 'some_expression' generates an error. my_field COLLATE utf8_unicode_ci REGEXP 'some_expression' works. – flm Mar 12 '15 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.