1

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

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?

1

Stick these in the [mysqld] section of your MySQL config file:

skip-character-set-client-handshake
collation-server=utf8_unicode_ci
character-set-server=utf8

The documentation for this is here.

1
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci;

The defaults in CREATE DATABASE are useful only when doing CREATE TABLE, so I prefer to ignore the database and focus on being explicit on CREATE TABLE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.