SHINCHVEN'S HEXO

MySQL Character Set And Collation Fast Note

Count: 172Reading time: 1 min
2020/07/23 Share

MySQL Instance Character Set and Collation

Config via docker-compose File

docker-compose.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
version: '3.5'

services:

mysql:
image: mysql
container_name: mysql
restart: always
command: [
'mysqld',
'--character-set-server=utf8mb4', # character set server
'--collation-server=utf8mb4_unicode_ci', # collation-server
... # your other configs
]

Config in my.cnf

edit my.cnf file

/etc/mysql/my.cnf
1
2
3
4
5
6
# append character configs to mysqld section of my.cnf file
[mysqld]
... # your other configs
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4
init-connect = 'SET NAMES utf8mb4'

restart your mysql service

bash command
1
2
3
systemctl restart mysql
# or
service mysql restart

Database Character Set and Collation

[reference]

1
2
3
4
5
6
7
CREATE DATABASE db_name
CHARACTER SET `utf8mb4`
COLLATE `utf8mb4_unicode_ci`;

ALTER DATABASE db_name
CHARACTER SET `utf8mb4`
COLLATE `utf8mb4_unicode_ci`;

Table Character Set and Collation

[reference]

1
2
3
4
5
6
7
CREATE TABLE tbl_name (column_list)
CHARACTER SET `utf8mb4`
COLLATE `utf8mb4_unicode_ci`;

ALTER TABLE tbl_name
CHARACTER SET `utf8mb4`
COLLATE `utf8mb4_unicode_ci`;

Column Character Set and Collation

[reference]

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE t1
(
col1 VARCHAR(5)
CHARACTER SET `utf8mb4`
COLLATE `utf8mb4_unicode_ci`
);

ALTER TABLE t1 MODIFY
col1 VARCHAR(5)
CHARACTER SET `utf8mb4`
COLLATE `utf8mb4_unicode_ci`;
CATALOG
  1. 1. MySQL Instance Character Set and Collation
    1. 1.1. Config via docker-compose File
    2. 1.2. Config in my.cnf
      1. 1.2.1. edit my.cnf file
      2. 1.2.2. restart your mysql service
  2. 2. Database Character Set and Collation
  3. 3. Table Character Set and Collation
  4. 4. Column Character Set and Collation