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.

Could you please mention your version of MySQL? – Stewart Jan 30 '16 at 15:14 — Stewart, Jan 30 '16 at 15:14

score 54 · Answer 1 · answered May 22 '14 at 19:35

54

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.

answered May 22 '14 at 19:35

Chris J

1,527
14
19

I find `SHOW variables WHERE variable_name LIKE '%coll%' OR variable_name LIKE '%char%'` a nice summary – MSpreij Jan 10 '18 at 18:57
I have only these databases: bugs (for bugzilla), mysql, information_schema and performance_schema. I altered bugs and mysql databases as you suggested (No permissions to alter performance_schema or information_schema), but I still have utf8mb4 in character_set_database and character_set_server. I am trying to install OTRS helpdesk software and it strictly needs utf8. Also I cannot make suggested changes to my.cnf, because of new version of mysql. Can you pls help? – Martin Pohorský Mar 13 '18 at 16:17
Can one avoid the database restart? – mvw Aug 06 '19 at 12:13
1

A restart was not necessary on MySQL 5.7. – randy Nov 20 '20 at 15:57

tinyhare · Answer 2 · 2018-07-21T11:58:20.540

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

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

2 Answers2

Linked