mysql改变表table的字符集sql语句

修改mysql表的字符集我们直接使用alter即可完成修改了,不过修改时要注意编码之间包含关系了,最好对数据表进行备份,以免出现乱码问题.

正确的改变table字符集的语句是:alter table xxx convert to character set utf8;

而不是想当然的:alter table xxx default charset utf8;

字符集从GBK转成utf8,会增大字段所占用的空间,有可能会改变字段的类型,比如text有可能会自动变成medium text,但是varchar没有自动变成medium text.

下面看测试,建一张GBK的表,代码如下:

1. mysql>SHOWCREATETABLExxx;
2. CREATETABLE`xxx`(
3. `id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,
4. `title`VARCHAR(255)NOTNULLDEFAULT'',
5. `body`text,
6. PRIMARYKEY(`id`)
7. )ENGINE=InnoDBDEFAULTCHARSET=gbk

用想当然的办法把它转成utf8,代码如下:

1. mysql>ALTERTABLExxxDEFAULTcharset=utf8;
2. QueryOK,0ROWSaffected(0.13sec)
3. Records:0Duplicates:0Warnings:0
5. mysql>
6. mysql>SETnamesutf8;
7. QueryOK,0ROWSaffected(0.00sec)

插入测试数据:

mysql> INSERT INTO xxx SET title='我爱北京天安门';

Query OK, 1 ROW affected (0.06 sec)

成功了,然后您就认为万事OK了？代码如下:

1. mysql>INSERTINTOxxxSETtitle='?';
2. QueryOK,1ROWaffected,1warning(0.03sec)
3. Warning(Code1366):IncorrectstringVALUE:'xE3xA4x87'FORCOLUMN'title'atROW1

注意 title和body字段的CHARACTER SET 仍然为gbk,代码如下:

1. mysql>SHOWCREATETABLExxx;
2. |xxx|CREATETABLE`xxx`(
3. `id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,
4. `title`VARCHAR(255)CHARACTERSETgbkNOTNULLDEFAULT'',
5. `body`textCHARACTERSETgbk,
6. PRIMARYKEY(`id`)
7. )ENGINE=InnoDBAUTO_INCREMENT=3DEFAULTCHARSET=utf8|

正确的做法:

1. mysql>DROPTABLExxx;
2. QueryOK,0ROWSaffected(0.02sec)
4. mysql>CREATETABLE`xxx`(
5. ->`id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,
6. ->`title`VARCHAR(255)NOTNULLDEFAULT'',
7. ->`body`text,
8. ->PRIMARYKEY(`id`)
9. ->)ENGINE=InnoDBDEFAULTCHARSET=gbk;
10. QueryOK,0ROWSaffected(0.06sec)
12. mysql>ALTERTABLExxxCONVERTTOCHARACTERSETutf8;
13. QueryOK,0ROWSaffected(0.14sec)
14. Records:0Duplicates:0Warnings:0
16. mysql>INSERTINTOxxxSETtitle='?';
17. QueryOK,1ROWaffected(0.04sec)
19. mysql>SHOWCREATETABLExxx;
20. |xxx|CREATETABLE`xxx`(
21. `id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,
22. `title`VARCHAR(255)NOTNULLDEFAULT'',
23. `body`mediumtext,
24. PRIMARYKEY(`id`)
25. )ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=utf8

可是你注意到 body字段从text变成 mediumtext了吗？text字段,最多存储65535字节,换成GBK的字符就是32767个字符,这32767个gbk字符转成utf8却要占用98301字节,已经超过text的存储能力,所以被自动转成了mediumtext.

下面测试VARCHAR的情况:

VARCHAR除了数据部分,还有1-2个字节用来保存数据的长度,如果只使用一个字节,那么长度上限为255(2^8-1),如果使用二个字节,长度上限为65535(2^16-1).

所以VARCHAR最多存储65535字节,换成GBK字符为32767个,代码如下:

1. mysql>DROPTABLExxx;
2. QueryOK,0ROWSaffected(0.02sec)
4. mysql>
5. mysql>CREATETABLE`xxx`(
6. `id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,
7. `title`VARCHAR(32768)NOTNULLDEFAULT'',
8. PRIMARYKEY(`id`)
9. )ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=gbk;
10. ERROR1074(42000):COLUMNLENGTHtoobigFORCOLUMN'title'(MAX=32767);USEBLOBORTEXTinstead
11. mysql>
12. mysql>

可是 32767也是不成的,还有每行记录的总长度限制(不包括text和BLOB字段) 65535,代码如下:

1. mysql>CREATETABLE`xxx`(`id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,`title`VARCHAR(32767)NOTNULLDEFAULT'',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=gbk;
2. ERROR1118(42000):ROWSIZEtooLARGE.ThemaximumROWSIZEFORtheusedTABLETYPE,NOTcountingBLOBs,IS65535.YouhaveTOCHANGESOMECOLUMNSTOTEXTORBLOBs
3. mysql>CREATETABLE`xxx`(`id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,`title`VARCHAR(32766)NOTNULLDEFAULT'',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=gbk;
4. ERROR1118(42000):ROWSIZEtooLARGE.ThemaximumROWSIZEFORtheusedTABLETYPE,NOTcountingBLOBs,IS65535.YouhaveTOCHANGESOMECOLUMNSTOTEXTORBLOBs
5. mysql>CREATETABLE`xxx`(`id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,`title`VARCHAR(32765)NOTNULLDEFAULT'',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=gbk;
6. ERROR1118(42000):ROWSIZEtooLARGE.ThemaximumROWSIZEFORtheusedTABLETYPE,NOTcountingBLOBs,IS65535.YouhaveTOCHANGESOMECOLUMNSTOTEXTORBLOBs
7. mysql>CREATETABLE`xxx`(`id`INT(10)UNSIGNEDNOTNULLAUTO_INCREMENT,`title`VARCHAR(32764)NOTNULLDEFAULT'',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=2DEFAULTCHARSET=gbk;
8. QueryOK,0ROWSaffected(0.06sec)

32764 * 2 + 2 + 4 = 65534 最接近于65535了,32765 * 2 + 2 + 4 = 65536 超过65535.

*2是因为gbk字符占用2字节.

+2是VARCHAR还需要额外2字节保存数据的长度.

+4是id字段INT UNSIGNED占了4字节.

合理吗？转换字符集为utf8之后,VARCHAR(32764) 已经不足以保存 32764个utf8字符,代码如下:

1. mysql>ALTERTABLExxxCONVERTTOCHARACTERSETutf8;
2. ERROR1074(42000):COLUMNLENGTHtoobigFORCOLUMN'title'(MAX=21845);USEBLOBORTEXTinstead
3. //phpfensi.com
4. 21845*3=65535.

后面附一些关于mysql数据库字符集修改方法.

MySQL:修改默认字符集,转换字符集(MySQL 5.X).

1.修改 MySQL 数据库默认字符集(mysql database default character set).

alter database testdb default character set = gb2312; -www.phpfensi.com-

2. 修改 MySQL 数据表默认字符集(mysql table default character set)

alter table ip_to_country default character set = gb2312;

注意:修改 MySQL 的默认字符集,不管是在数据库级别,还是数据表级别,对已经存储的字符数据无任何改变,只是新增的表或列,开始使用新的字符集.