1

I just want everything default to utf8. I've checked this question but nothing help.

Currently, My /etc/my.cnf is 

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

But when I restart the server, create a new database, it is still latin1(character_set_database and character_set_server):

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| 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> 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)

When I create a database, It is latin1:

mysql> create database d1;
Query OK, 1 row affected (0.00 sec)

mysql> use d1;
Database changed
mysql> show variables like "character_set_database";
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)

When I create a table in this database, it can't recognize valid utf8 啊：

mysql> create table t1(name varchar(1) default '啊');
ERROR 1067 (42000): Invalid default value for 'name'

I know alter database d1 character set utf8; will fix this. But I just want everything default to utf8, is it possible?

Community
  • 1
  • 1
tcpiper
  • 2,456
  • 2
  • 28
  • 41

1 Answers1

0

This is tricky.

The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server.

So one would assume the default for the collation-database is the same as the default for the collation-server variable.

Please check the following:

  1. Is there any other config that would override your my.cnf, like /etc/mysql/mysql.cnf or ~/.my.cnf ?

  2. The client (not server!) is setting its own collation upon startup, so you could set a client collation/encoding through [mysql] (not mysqld) or look if this is already set somewhere.

  3. You do SHOW VARIABLES ... - this is querying SESSION based variables, try to query explicitly global settings through SHOW GLOBAL VARIABLES ...

Daniel W.
  • 31,164
  • 13
  • 93
  • 151
  • Is it like this:`[mysql] collation-server = utf8_unicode_ci character-set-server = utf8`? – tcpiper Dec 14 '16 at 14:51
  • I know this is old, but answer is only partial - show what variables? What values should be in which sections of my.cnf (this has changed so many times - it's sickening). – D Durham Apr 12 '19 at 12:51
  • @DDurham `SHOW VARIABLES` shows **all** variables, but they can be filtered using `LIKE '%character%'` for example. You can read about all this on the page in the comment before from 2016... did you even read it? Stuff hasn't changed in years. – Daniel W. Apr 12 '19 at 13:30