给MySQL正确设置表字符集_调整MySQL数据表字符集

数据表t1表的结构.

mysql> show create table t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`account` varchar(30) 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. 调整默认charset为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(11) NOT NULL AUTO_INCREMENT,

`account` varchar(30) 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. 调整account字段collate为utf8mb4_bin.

mysql> alter table t1 modify column account varchar(30) 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(11) NOT NULL AUTO_INCREMENT,

`account` varchar(30) 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. 调整默认charset, 和各字段charset为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(11) NOT NULL AUTO_INCREMENT,

`account` varchar(30) 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)