snippetsqlMinor
How do I set MySQL's "character_set_server" and "collation_server" variables when creating a database?
Viewed 0 times
character_set_servercollation_servercreatingdatabasemysqlvariableshowandwhenset
Problem
I'm using MySQL 5.5.37. How do I set the default "" and "" variables of my database when initially creating my db? I'm logged in as the root user but my command is not cutting it, as you can see below
Notice that "character_set_server" is set to "latin1" and "collation_server" is set to "latin1_swedish_ci" despite what I specified in my intiail command. How do I change my initial command so that these values are utf8 and utf8_bin respectively?
mysql> create database if not exists my_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;
Query OK, 1 row affected (0.00 sec)
mysql> use my_db
Database changed
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| collation_connection | utf8_general_ci |
| collation_database | utf8_bin |
| collation_server | latin1_swedish_ci |
+--------------------------+-------------------+Notice that "character_set_server" is set to "latin1" and "collation_server" is set to "latin1_swedish_ci" despite what I specified in my intiail command. How do I change my initial command so that these values are utf8 and utf8_bin respectively?
Solution
Stick these in the
The documentation for this is here.
[mysqld] section of your MySQL config file:skip-character-set-client-handshake
collation-server=utf8_unicode_ci
character-set-server=utf8The documentation for this is here.
Code Snippets
skip-character-set-client-handshake
collation-server=utf8_unicode_ci
character-set-server=utf8Context
StackExchange Database Administrators Q#166484, answer score: 4
Revisions (0)
No revisions yet.