MySQL由 4.0.20升能到 4.1.22，varchar字段长度改变，这是为什么？

wudicc

前几天把MySQL由 4.0.20升能到 4.1.22

最近发现写入的数据都少了一些字节。

最后查看表结构，发现表结构变了，定义的字段长度就小了。

+--------------+------------+------+-----+----------------+-------+
| Field           | Type       | Null  | Key | Default        | Extra |
+--------------+------------+------+-----+----------------+-------+
| StartNumber  | varchar(7) |      | PRI | 00000000000000 |       |

原来是：
+--------------+------------+------+-----+----------------+-------+
| Field        | Type       | Null | Key | Default        | Extra |
+--------------+------------+------+-----+----------------+-------+
| StartNumber  | varchar(14) |      | PRI | 00000000000000 |       |

这导致只能写进7个字符了。
为什么会这样呢？
我是直接复制数据库文件的。

phphp

总长度没有变短的, 拷贝到4.1.22上有字符集的概念, 你这个长度是原来的长度的1/2, 应该默认字符集是gbk
新环境只能插入7个字, 因此英文为7个字节, 中文最多7个汉字, 14个字节.

可以在4.1.22下创建一个正确的表,使用charset latin1,  用frm文件覆盖4.0.16拷过来的那个.
或4.1.22在 default-character-set=latin1下拷过去

这两个版本间myisam数据版本有升级, 最好还是把数据dump出来再导进去

cenalulu

总长度没有变短的, 拷贝到4.1.22上有字符集的概念, 你这个长度是原来的长度的1/2, 应该默认字符集是gbk
新 ...
phphp 发表于 2010-02-07 13:40

想问下，change log没看到有说字符集概念的新增
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-0.html

ls是凭经验？

phphp

想问下，change log没看到有说字符集概念的新增

ls是凭经验？
cenalulu 发表于 2010-02-07 19:31

根据他的表现猜的, create table ...default charset latin1好像是4.1才有的.
http://dev.mysql.com/doc/refman/4.1/en/news-4-1-1.html

ruochen

根据他的表现猜的, create table ...default charset latin1好像是4.1才有的.
phphp 发表于 2010-02-07 23:43

     LZ出来验证一下了

canjie

Incompatible change: MySQL interprets length specifications in character column definitions in characters. (Earlier
versions interpret them in bytes.) For example, CHAR(N) means N characters, not N bytes.
For single-byte character sets, this change makes no difference. However, if you upgrade to MySQL 4.1 and configure
the server to use a multi-byte character set, the apparent length of character columns changes. Suppose that a 4.0
table contains a CHAR(8) column used to store ujis characters. Eight bytes can store from two to four ujis characters.
If you upgrade to 4.1 and configure the server to use ujis as its default character set, the server interprets
character column lengths based on the maximum size of a ujis character, which is three bytes. The number of three-byte
characters that fit in eight bytes is two. Consequently, if you use SHOW CREATE TABLE to view the table definition,
MySQL displays CHAR(2). You can retrieve existing data from the table, but you can only store new values containing up
to two characters. To correct this issue, use ALTER TABLE to change the column definition. For example:
ALTER TABLE tbl_name MODIFY col_name CHAR(8);