How to set character_set_database and collation_database to utf8 in my.ini?

Question

I've googled a lot about this problem. To sum up, this is what my my.ini looks like:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[client]
database = abcdef
user = root
password = XXXXXX
default-character-set = utf8

[mysql]
default-character-set=utf8

[mysqld]
character_set_server=utf8
max_connections = 200
init_connect='SET collation_connection = utf8_general_ci' 
init_connect='SET NAMES utf8' 

When I get into mysql via cmd and issue: show variables like "%character%";show variables like "%collation%";, this is what I got:

+--------------------------+---------------------------------+
| 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     | utf8                            |
| character_set_system     | utf8                            |
| character_sets_dir       | D:\env\MySQL5.6\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set (0.00 sec)

+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | utf8_unicode_ci   |
+----------------------+-------------------+

I've already restart the MySQL service, so could anyone give me some idea on how to change character_set_database as well as collation_database to utf8? Thanks a lot.

Answer 1

This actually isn't a setting in the my.cnf (or my.ini in this case). mySQL gets this setting from the database's own collation (when it was created). Inorder to get this inline with the utf8 encoding you want, do this:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;

then do a restart on mysql (cant remember if its needed though), followed by a:

SHOW VARIABLES;

All should be well, Hope that helps!

---

side note: i think default-character-set is deprecated now-a-days (mySQL 5.5+) and seems to make the config file fidgety.

Answer 2

I do a summary:

determine which charset/collations are available

SHOW CHARSET;
SHOW COLLATION;

check charset

SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

set charset (in configure file -> my.cnf)

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

check database/table charset

SHOW CREATE DATABASE databasename;
SHOW CREATE TABLE tablename;

change the database/table charset：

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

set when create database/table:

CREATE DATABASE new_db CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
CREATE TABLE new_table (id INT) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

Note： I heard that in Mysql utf8 is not the true utf8，utf8mb4 is the real utf8. so, if you have special character that can't save to mysql, maybe you should use utf8mb4 and utf8mb4_general_ci