Search code examples
mysqlutf8mb4

Stored Procedure collation_connection is utf8mb4_general_ci instead of utf8mb4_unicode_ci using Hiedisql mysql

I am using Hiedisql V9.2.

I set default server character-set is utfmb4 and collation is utf8mb4_unicode_ci but after create stored procedure still show collation_connection =utf8mb4_general_ci.

Below is server my.ini settings.

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
init_connect                   = 'SET NAMES utf8mb4'
collation-server = utf8mb4_unicode_ci
character-set-server=utf8mb4

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

After create stored procedure still show collation_connection=utf8mb4_general_ci.

SHOW PROCEDURE STATUS where name LIKE 'ProcName'

+----------+----------------------+----------------------+--------------------+
| Name     | character_set_client | collation_connection | Database Collation |
+----------+----------------------+----------------------+--------------------+
| ProcName | utf8mb4              | utf8mb4_general_ci   | utf8mb4_unicode_ci |
+----------+----------------------+----------------------+--------------------+

So, how to fix collation_connection=utf8mb4_general_ci issue.

I want collation_connection=utf8mb4_unicode_ci.

Solution

  • Finally I got issue. It is issue of Hiedisql tool which I am using. Hiedisql tool set collation_connection=utf8mb4_general_ci before run script.

    How to fix:

    Just set below variable value before run any script.

    SET collation_connection = @@collation_database;

    OR

    SET collation_connection = 'utf8mb4_unicode_ci';

    Note: If you want to know what is actual collation, use mysql command prompt it will return actual result.