探究MySQL中varchar的定義長度

更新時間：2015年05月30日 10:07:53 投稿：goldensun

這篇文章主要介紹了探究MySQL中varchar的定義長度,文中主要對其長度究竟是字節(jié)還是字符做出了相關(guān)實驗,需要的朋友可以參考下

UTF8字符集下：

SQL>create table test(id int auto_increment,name varchar(10),primary key(id));
SQL>insert into test values(null,'1234567890');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'一二三四五六七八九十');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'abcdefghig');
Query OK, 1 row affected (0.01 sec)
SQL>insert into test values(null,12345678901);
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>insert into test values(null,'一二三四五六七八九十1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>insert into test values(null,'一二三四五六七八九十一');
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>select id,name,length(name),char_length(name) from test;

+----+--------------------------------+--------------+-------------------+
 | id | name | length(name) | char_length(name) |
 +----+--------------------------------+--------------+-------------------+
 | 1 | 1234567890 | 10 | 10 |
 | 2 | 一二三四五六七八九十 | 30 | 10 |
 | 3 | abcdefghig | 10 | 10 |
 +----+--------------------------------+--------------+-------------------+
 3 rows in set (0.00 sec)

GBK字符集下：

SQL>create table test(id int auto_increment,name varchar(10),primary key(id));
SQL>insert into test values(null,'1234567890');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'一二三四五六七八九十');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'abcdefghig');
Query OK, 1 row affected (0.01 sec)
SQL>insert into test values(null,12345678901);
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>insert into test values(null,'一二三四五六七八九十1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>insert into test values(null,'一二三四五六七八九十一');
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>select id,name,length(name),char_length(name) from test;

+----+----------------------+--------------+-------------------+
 | id | name | length(name) | char_length(name) |
 +----+----------------------+--------------+-------------------+
 | 1 | 1234567890 | 10 | 10 |
 | 2 | 一二三四五六七八九十 | 20 | 10 |
 | 3 | abcdefghig | 10 | 10 |
 +----+----------------------+--------------+-------------------+
 3 rows in set (0.00 sec)

由此可見，varchar定義的長度的單位是字符，哪怕是1個多字節(jié)字符也是1個字符，如中文和英文字母都被當(dāng)作1個字符來對待。

那么varchar能夠定義的最大長度是多少呢？這個和你當(dāng)前所使用的字符集有關(guān)。拋開字符，其最大長度為65535字節(jié)(這是最大行大小，由所有列共享)，而放在不同的字符集下，能夠定義的最大長度就會有所不同，如UTF8下是21845。據(jù)說MySQL5中varchar的長度也為字符，而MySQL4中的則為字節(jié)，未經(jīng)證實，感興趣的有環(huán)境可以自己測下。

順便補(bǔ)充一下，char數(shù)據(jù)類型定義的長度也為字符，其最大長度為255。

SQL>create table test(id int auto_increment,name char(5),primary key(id));
Query OK, 0 rows affected (0.09 sec)
SQL>insert into test values(null,'123');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'12345');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'一二三');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,'一二三四五');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,123456);
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>insert into test values(null,'一二三四五1');
ERROR 1406 (22001): Data too long for column 'name' at row 1
SQL>select id,name,length(name),char_length(name) from test;

+----+-----------------+--------------+-------------------+
 | id | name | length(name) | char_length(name) |
 +----+-----------------+--------------+-------------------+
 | 1 | 123 | 3 | 3 |
 | 2 | 12345 | 5 | 5 |
 | 3 | 一二三 | 9 | 3 |
 | 4 | 一二三四五 | 15 | 5 |
 +----+-----------------+--------------+-------------------+
 4 rows in set (0.00 sec)

您可能感興趣的文章: