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> SHOW CREATE TABLE xxx; 
2. CREATE TABLE `xxx` ( 
3.   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
4.   `title` VARCHAR(255) NOT NULL DEFAULT '', 
5.   `body` text, 
6.   PRIMARY KEY (`id`) 
7. ) ENGINE=InnoDB DEFAULT CHARSET=gbk 

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

1. mysql> ALTER TABLE xxx DEFAULT charset=utf8; 
2. Query OK, 0 ROWS affected (0.13 sec) 
3. Records: 0  Duplicates: 0  Warnings: 0 
4.  
5. mysql>  
6. mysql> SET names utf8; 
7. Query OK, 0 ROWS affected (0.00 sec) 

插入测试数据:

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

Query OK, 1 ROW affected (0.06 sec)

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

1. mysql> INSERT INTO xxx SET title='㤇';      
2. Query OK, 1 ROW affected, 1 warning (0.03 sec) 
3. Warning (Code 1366): Incorrect string VALUE: 'xE3xA4x87' FOR COLUMN 'title' at ROW 1 

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

1. mysql> SHOW CREATE TABLE xxx; 
2. | xxx   | CREATE TABLE `xxx` ( 
3.   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
4.   `title` VARCHAR(255) CHARACTER SET gbk NOT NULL DEFAULT '', 
5.   `body` text CHARACTER SET gbk, 
6.   PRIMARY KEY (`id`) 
7. ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 | 

正确的做法:

1. mysql> DROP TABLE xxx; 
2. Query OK, 0 ROWS affected (0.02 sec) 
3.  
4. mysql> CREATE TABLE `xxx` ( 
5.     ->   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
6.     ->   `title` VARCHAR(255) NOT NULL DEFAULT '', 
7.     ->   `body` text, 
8.     ->   PRIMARY KEY (`id`) 
9.     -> ) ENGINE=InnoDB DEFAULT CHARSET=gbk; 
10. Query OK, 0 ROWS affected (0.06 sec) 
11.  
12. mysql> ALTER TABLE xxx CONVERT TO CHARACTER SET utf8; 
13. Query OK, 0 ROWS affected (0.14 sec) 
14. Records: 0  Duplicates: 0  Warnings: 0 
15.  
16. mysql> INSERT INTO xxx SET title='㤇';                         
17. Query OK, 1 ROW affected (0.04 sec) 
18.  
19. mysql> SHOW CREATE TABLE xxx; 
20. | xxx   | CREATE TABLE `xxx` ( 
21.   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
22.   `title` VARCHAR(255) NOT NULL DEFAULT '', 
23.   `body` mediumtext, 
24.   PRIMARY KEY (`id`) 
25. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=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> DROP TABLE xxx; 
2. Query OK, 0 ROWS affected (0.02 sec) 
3.  
4. mysql>  
5. mysql> CREATE TABLE `xxx` ( 
6.     `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
7.     `title` VARCHAR(32768) NOT NULL DEFAULT '', 
8.    PRIMARY KEY (`id`) 
9.     ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk; 
10. ERROR 1074 (42000): COLUMN LENGTH too big FOR COLUMN 'title' (MAX = 32767); USE BLOB OR TEXT instead 
11. mysql>  
12. mysql> 

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

1. 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;      
2. 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 
3. 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;  
4. 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 
5. 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;  
6. 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 
7. 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;  
8. Query OK, 0 ROWS affected (0.06 sec) 

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> ALTER TABLE xxx CONVERT TO CHARACTER SET utf8; 
2. ERROR 1074 (42000): COLUMN LENGTH too big FOR COLUMN 'title' (MAX = 21845); USE BLOB OR TEXT instead
3. //Vevb.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.Vevb.com-

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

alter table ip_to_country default character set = gb2312;

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