《MYSQL教程探究MySQL中varchar的定義長度》要點(diǎn)：
本文介紹了MYSQL教程探究MySQL中varchar的定義長度，希望對您有用。如果有疑問，可以聯(lián)系我們。
MYSQL學(xué)習(xí)UTF8字符集下：
MYSQL學(xué)習(xí)
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)
MYSQL學(xué)習(xí)?? GBK字符集下：
MYSQL學(xué)習(xí)
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)
MYSQL學(xué)習(xí)?? 由此可見,varchar定義的長度的單位是字符,哪怕是1個多字節(jié)字符也是1個字符,如中文和英文字母都被當(dāng)作1個字符來對待.
MYSQL學(xué)習(xí)?? 那么varchar能夠定義的最大長度是多少呢?這個和你當(dāng)前所使用的字符集有關(guān).拋開字符,其最大長度為65535字節(jié)(這是最大行大小,由所有列共享),而放在不同的字符集下,能夠定義的最大長度就會有所不同,如UTF8下是21845.據(jù)說MySQL5中varchar的長度也為字符,而MySQL4中的則為字節(jié),未經(jīng)證實(shí),感興趣的有環(huán)境可以自己測下.
MYSQL學(xué)習(xí)?? 順便補(bǔ)充一下,char數(shù)據(jù)類型定義的長度也為字符,其最大長度為255.
MYSQL學(xué)習(xí)
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)
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/5246.html
