MySQL - Character Set

About

character sets configuration in mysql

Articles Related

Management

Show Configuration

SHOW GLOBAL VARIABLES LIKE 'character_set%';

Variable_name           |Value                     |
------------------------|--------------------------|
character_set_client    |latin1                    |
character_set_connection|latin1                    |
character_set_database  |latin1                    |
character_set_filesystem|binary                    |
character_set_results   |latin1                    |
character_set_server    |latin1                    |
character_set_system    |utf8                      |
character_sets_dir      |/usr/share/mysql/charsets/|

List

• MySql - InnoDb

SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS cs;

Set

You can specify character sets at the server, database, table, and column level.

Server

MySql - Configuration

• conf file

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

• options of the daemon, use the:
  • --character-set-server
  • --collation-server

Database

MySQL - Database

• Set - Tables created in the database will use utf8 and utf8_general_ci by default for any character columns.

CREATE DATABASE mydb
  CHARACTER SET utf8
  COLLATE utf8_general_ci;

• See

USE db_name;
SELECT @@character_set_database, @@collation_database;
--or
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

@@character_set_database|@@collation_database|
------------------------|--------------------|
latin1                  |latin1_swedish_ci   |

mysql Client

If you want the mysql client program to communicate with the server using a character set different from the default, you can define it:

• at the connection level

mysql --default-character-set=utf8 ...

• or in the script

SET NAMES 'utf8';

Documentation / Reference

• https://dev.mysql.com/doc/refman/5.6/en/charset.html