Mysql SQL statement for changing the character set of a table

We can directly use alter to modify the character set of the mysql table. However, we should pay attention to the inclusion relationship between codes when modifying the data set. It is best to back up the data table to avoid garbled characters.

The correct statement for changing the table character set is:
Alter table xxx convert to character set utf8;
Rather than taking it for granted:
Alter table xxx default charset utf8;

Convert the character set from GBK to utf8,
It will increase the space occupied by the field, and may change the field type:
For example, text may automatically become medium text.
However, varchar is not automatically converted to medium text.

See the test below:

Create a GBK table:
Mysql> show create table xxx;
Create table 'xxx '(
'Id' INT (10) unsigned not null AUTO_INCREMENT,
'Title' VARCHAR (255) not null default '',
'Body' text,
Primary key ('id ')
) ENGINE = InnoDB default charset = gbk
 
Convert it to utf8 by taking it for granted:
Mysql> alter table xxx DEFAULT charset = utf8;
Query OK, 0 ROWS affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
Mysql>
Mysql> SET names utf8;
Query OK, 0 ROWS affected (0.00 sec)
 
Insert test data:
Mysql> insert into xxx SET title = 'I Love Tiananmen Square in Beijing ';
Query OK, 1 ROW affected (0.06 sec)
 
Success... Then you think everything is OK?
 
Mysql> insert into xxx SET title = 'ignore ';
Query OK, 1 ROW affected, 1 warning (0.03 sec)
Warning (Code 1366): Incorrect string VALUE: 'xe3xa4x87 'for column 'title' at ROW 1
 
Note that the character set of the title and body fields is still gbk
Mysql> show create table xxx;
| Xxx | create table 'xxx '(
'Id' INT (10) unsigned not null AUTO_INCREMENT,
'Title 'varchar (255) character set gbk not null default '',
'Body' text character set gbk,
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 3 default charset = utf8 |
 
Correct practice:
 
Mysql> drop table xxx;
Query OK, 0 ROWS affected (0.02 sec)
 
Mysql> create table 'xxx '(
-> 'Id' INT (10) unsigned not null AUTO_INCREMENT,
-> 'Title' VARCHAR (255) not null default '',
-> 'Body' text,
-> Primary key ('id ')
->) ENGINE = InnoDB default charset = gbk;
Query OK, 0 ROWS affected (0.06 sec)
 
Mysql> alter table xxx convert to character set utf8;
Query OK, 0 ROWS affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
 
Mysql> insert into xxx SET title = 'ignore ';
Query OK, 1 ROW affected (0.04 sec)
 
Mysql> show create table xxx;
| Xxx | create table 'xxx '(
'Id' INT (10) unsigned not null AUTO_INCREMENT,
'Title' VARCHAR (255) not null default '',
'Body' mediumtext,
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = utf8
 
But have you noticed that the body field has changed from text to mediumtext?
 
The text field, which can store up to 65535 bytes. If you replace it with a GBK character, it will contain 32767 characters. The conversion of these 32767 gbk characters to utf8 takes up 98301 bytes and exceeds the storage capacity of text, therefore, it is automatically converted to mediumtext.
 
The following describes how to test VARCHAR:
 
In addition to data, VARCHAR also has 1-2 bytes to save the Data Length. If only one byte is used, the maximum length is 255 (2 ^ 8-1). If two bytes are used, the maximum length is 65535 (2 ^ 16-1 ).
Therefore, VARCHAR can store a maximum of 65535 bytes and replace it with 32767 GBK characters:
Mysql> drop table xxx;
Query OK, 0 ROWS affected (0.02 sec)
 
Mysql>
Mysql> create table 'xxx '(
'Id' INT (10) unsigned not null AUTO_INCREMENT,
'Title' VARCHAR (32768) not null default '',
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = gbk;
ERROR 1074 (42000): column length too big for column 'title' (MAX = 32767); use blob or text instead
Mysql>
Mysql>

However, 32767 is not acceptable .. There is also a limit on the total length of each record line (excluding text and BLOB fields) 65535:

Mysql> create table 'xxx' ('id' INT (10) unsigned not null AUTO_INCREMENT, 'title' VARCHAR (32767) not null default '', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = gbk;
ERROR 1118 (42000): row size too LARGE. The maximum row size for the used table type, NOT counting BLOBs, IS 65535. You have to change some columns to text or BLOBs
Mysql> create table 'xxx' ('id' INT (10) unsigned not null AUTO_INCREMENT, 'title' VARCHAR (32766) not null default '', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = gbk;
ERROR 1118 (42000): row size too LARGE. The maximum row size for the used table type, NOT counting BLOBs, IS 65535. You have to change some columns to text or BLOBs
Mysql> create table 'xxx' ('id' INT (10) unsigned not null AUTO_INCREMENT, 'title' VARCHAR (32765) not null default '', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = gbk;
ERROR 1118 (42000): row size too LARGE. The maximum row size for the used table type, NOT counting BLOBs, IS 65535. You have to change some columns to text or BLOBs
Mysql> create table 'xxx' ('id' INT (10) unsigned not null AUTO_INCREMENT, 'title' VARCHAR (32764) not null default '', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 2 default charset = gbk;
Query OK, 0 ROWS affected (0.06 sec)
 
32764*2 + 2 + 4 = 65534 is closest to 65535,
32765*2 + 2 + 4 = 65536 + 65535.
* 2 because the gbk character occupies 2 bytes.
+ 2 is the length of data stored in VARCHAR in two additional bytes.
+ 4 indicates that the id field int unsigned occupies 4 bytes.
Is it reasonable?
 
After the conversion character set is utf8, VARCHAR (32764) is insufficient to save 32764 utf8 characters:
 
Mysql> alter table xxx convert to character set utf8;
ERROR 1074 (42000): column length too big for column 'title' (MAX = 21845); use blob or text instead
 
21845*3 = 65535.

The following appendix describes how to modify the character set of the mysql database.

MySQL: Modify the default Character Set and convert the character set (MySQL 5.X)
 
1. Modify the default character set of the MySQL database (mysql database default character set)
 
Alter database testdb default character set = gb2312; -www.2cto.com-
 
2. Modify the default character set of a MySQL DATA table (mysql table default character set)
 
Alter table ip_to_country default character set = gb2312;
 
Note: modifying the default Character Set of MySQL does not change the stored character data at the database or data table level. Only new tables or columns are added to start using the new character set.