avatar

ShīnChvën ✨

Effective Accelerationism

Powered by Druid

MySQL Character Set And Collation Fast Note

Thu Jul 23 2020

MySQL Instance Character Set and Collation

Config via docker-compose File

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

vim /etc/mysql/my.cnf
# 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

systemctl restart mysql
# or
service mysql restart

Database Character Set and Collation

[reference]

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]

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]

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`;