3

I have a MySql 5.6 database where the database is using a collation of utf8_unicode_ci. Every time I create a new table the table is created with utf8_unicode_ci, but the varchar columns are set with utf8_general_ci.

When I look at the variables I see the following 

'collation_connection', 'utf8_general_ci'
'collation_database', 'utf8_unicode_ci'

I can manually execute, SET collation_connection = utf8_unicode_ci; and then create my table and everything works as I'd like.

The question is, how can I set collation_connection by default? I've tried setting via my.cnf using the article below, but I can't seem to get my connection to change. Ideas?

Change MySQL default character set to UTF-8 in my.cnf?

Christopher Schultz
  • 20,221
  • 9
  • 60
  • 77
Blake
  • 31
  • 1
  • 3

1 Answers1

0

You could configure 'string to be executed by the server for each client that connects', by including this line in [mysqld] section of my.cnf configuration file:

[mysqld]
init_connect='SET collation_connection = utf8mb4_unicode_ci;'

Maybe better using SET NAMES (details here):

[mysqld]
init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;'

To set default charset and collation for new tables, you would specify:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Reference Manual explains:

The server character set and collation are used as default values if the database character set and collation are not specified in CREATE DATABASE statements. They have no other purpose.

Consider using utf8mb4 instead of utf8. Reference here.

Mauricio
  • 473
  • 5
  • 11