[转帖]MySql改变表table的字符集sql语句_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 805 | 回复: 0   主题: [转帖]MySql改变表table的字符集sql语句        下一篇 
youduoduo
注册用户
等级:新兵
经验:78
发帖:78
精华:0
注册:2011-11-26
状态:离线
发送短消息息给youduoduo 加好友    发送短消息息给youduoduo 发消息
发表于: IP:您无权察看 2014-11-11 15:40:33 | [全部帖] [楼主帖] 楼主

修改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的表:

 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


用想当然的办法把它转成utf8:

 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)


插入测试数据:
mysql> INSERT INTO xxx SET title='我爱北京天安门';

 Query OK, 1 ROW affected (0.06 sec)


成功了。。。然后您就认为万事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


注意 title和body字段的CHARACTER SET 仍然为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 |


正确的做法:

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='㤇';
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


可是你注意到 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个:

 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>


可是 32767也是不成的。。还有每行记录的总长度限制(不包括text和BLOB字段) 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 最接近于65535了,
32765 * 2 + 2 + 4 = 65536 超过65535。
*2是因为gbk字符占用2字节。
+2是VARCHAR还需要额外2字节保存数据的长度。
+4是id字段INT UNSIGNED占了4字节。
合理吗?

转换字符集为utf8之后,VARCHAR(32764) 已经不足以保存 32764个utf8字符:

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.


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

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

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

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


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

alter table ip_to_country default character set = gb2312;


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

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论