夏日博客

A-A+

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

2017年10月24日 PHP技术文章 暂无评论 阅读 0 views 次

修改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. //www.xiariboke.net  
  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 的默认字符集,不管是在数据库级别,还是数据表级别,对已经存储的字符数据无任何改变,只是新增的表或列,开始使用新的字符集.

标签：

给我留言