MySQL changes table tables ' character Set SQL statement

The correct statement to change the table character set is:
ALTER TABLE XXX convert to character set UTF8;
Rather than take for granted:
ALTER TABLE XXX default CharSet UTF8;

The character set is converted from GBK to UTF8,
Increases the space occupied by the field and may change the type of the field:
For example, text is likely to automatically become medium text
But varchar did not automatically become medium text.

Look at the test below:

To build a GBK table:
Mysql> show CREATE TABLE xxx;
CREATE TABLE ' xxx ' (
' ID ' INT (a) UNSIGNED not NULL auto_increment,
' title ' VARCHAR (255) Not NULL DEFAULT ',
' Body ' text,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT CHARSET=GBK

Turn it into a 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)

To insert test data:
mysql> INSERT into xxx SET title= ' I love Beijing Tian ' an gate ';
Query OK, 1 ROW affected (0.06 sec)

It worked... And you think everything's OK?

mysql> INSERT into xxx SET title= ' 㤇 ';
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 (a) 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 |

The right approach:

mysql> DROP TABLE xxx;
Query OK, 0 ROWS affected (0.02 sec)

mysql> CREATE TABLE ' xxx ' (
-> ' id ' INT (a) 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= ' 㤇 ';
Query OK, 1 ROW affected (0.04 sec)

Mysql> show CREATE TABLE xxx;
| xxx | CREATE TABLE ' xxx ' (
' ID ' INT (a) 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 did you notice that the body field changed from text to Mediumtext?

Text field, storage up to 65535 bytes, replaced by GBK character is 32,767 characters, these 32,767 GBK characters into UTF8 but to occupy 98301 bytes, has exceeded the storage capacity of text, so automatically turned into a mediumtext.

The following test varchar:

varchar In addition to the data section, there are 1-2 bytes to hold the length of the data. If you use only one byte, the maximum length is 255 (2^8-1), and if you use two bytes, the maximum length is 65535 (2^16-1).
So varchar stores up to 65535 bytes and replaces GBK with 32,767 characters:
mysql> DROP TABLE xxx;
Query OK, 0 ROWS affected (0.02 sec)

Mysql>
mysql> CREATE TABLE ' xxx ' (
' ID ' INT (a) 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>

But 32767 is not the same. There are also total length limits for each line of records (excluding text and blob fields) 65535:

mysql> CREATE TABLE ' xxx ' (' id ' INT ' 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 are have to change SOME COLUMNS to TEXT OR BLOBs
mysql> CREATE TABLE ' xxx ' (' id ' INT ' 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 are have to change SOME COLUMNS to TEXT OR BLOBs
mysql> CREATE TABLE ' xxx ' (' id ' INT ' 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 are have to change SOME COLUMNS to TEXT OR BLOBs
mysql> CREATE TABLE ' xxx ' (' id ' INT ' 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 more than 65535.
*2 is because the gbk character occupies 2 bytes.
+2 is varchar also requires an additional 2 bytes to save the length of the data.
+4 is the ID field int unsigned takes up 4 bytes.
Is it reasonable?

After converting the character set to UTF8, VARCHAR (32764) is not enough to hold 32,764 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 is a little bit about MySQL database Character Set modification method

MySQL: Modifying the default character set, converting character set (MySQL 5.X)

1. Modify the MySQL database default character set (MySQL db default character set)

ALTER DATABASE TestDB default Character set = gb2312; -www.2cto.com-

2. Modify the MySQL data table default character set (MySQL table default character set)

ALTER TABLE ip_to_country default character set = gb2312;

Note: Modifying the default character set for MySQL, regardless of whether it is at the database level or at the level of the datasheet, has no change to the character data that has been stored. Just the new table or column, and start using the newly created character set.