Adjust MySQL data table character set

The structure of the Data table T1 table.

Mysql> Show CREATE TABLE T1\g

1. Row ***************************

Table:t1

Create table:create Table ' T1 ' (

' id ' int (one) not NULL auto_increment,

' Account ' varchar (+) not NULL DEFAULT ' ',

PRIMARY KEY (' id '),

UNIQUE KEY ' uniq_account ' (' account ')

) Engine=innodb auto_increment=4 DEFAULT Charset=utf8

1 row in Set (0.00 sec)

1. Adjust the default charset to UTF8MB4.

mysql> ALTER TABLE T1 default CharSet utf8mb4;

Query OK, 0 rows affected (0.07 sec)

records:0 duplicates:0 warnings:0

Mysql> Show CREATE TABLE T1\g

1. Row ***************************

Table:t1

Create table:create Table ' T1 ' (

' id ' int (one) not NULL auto_increment,

' Account ' varchar (+) CHARACTER SET UTF8 not NULL DEFAULT ' ',

PRIMARY KEY (' id '),

UNIQUE KEY ' uniq_account ' (' account ')

) Engine=innodb auto_increment=4 DEFAULT charset=utf8mb4

1 row in Set (0.00 sec)

2. Adjust the Account field collate to Utf8mb4_bin.

mysql> ALTER TABLE T1 modify column account varchar (+) collate utf8mb4_bin NOT null default ';

Query OK, 0 rows affected (0.71 sec)

records:0 duplicates:0 warnings:0

Mysql> Show CREATE TABLE T1\g

1. Row ***************************

Table:t1

Create table:create Table ' T1 ' (

' id ' int (one) not NULL auto_increment,

' Account ' varchar (+) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not NULL DEFAULT ' ',

PRIMARY KEY (' id '),

UNIQUE KEY ' uniq_account ' (' account ')

) Engine=innodb auto_increment=4 DEFAULT charset=utf8mb4

1 row in Set (0.00 sec)

3. Adjust the default charset, and the fields charset to UTF8.

mysql> ALTER TABLE T1 convert to CharSet UTF8;

Query OK, 0 rows affected (1.65 sec)

records:0 duplicates:0 warnings:0

Mysql> Show CREATE TABLE T1\g

1. Row ***************************

Table:t1

Create table:create Table ' T1 ' (

' id ' int (one) not NULL auto_increment,

' Account ' varchar (+) not NULL DEFAULT ' ',

PRIMARY KEY (' id '),

UNIQUE KEY ' uniq_account ' (' account ')

) Engine=innodb auto_increment=4 DEFAULT Charset=utf8

1 row in Set (0.00 sec)

Adjust MySQL data table character set