varchar(20):20指的是表中的a字段能存储的最大字符个数
In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data.
The length prefix indicates the number of bytes in the value.
A column uses one length byte if values require no more than 255 bytes,
two length bytes if values may require more than 255 bytes.
mysql> create table t1( a varchar());
Query OK, rows affected (0.27 sec)
mysql> show create table t1;
+-------+---------------------------------------------------------------
| Table | Create Table
+-------+---------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
`a` varchar() DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------
row in set (0.00 sec)
mysql> insert into t1 select repeat("a",);
ERROR (): Data too long for column 'a' at row
mysql> insert into t1 select repeat("a",);
Query OK, row affected (0.09 sec)
Records: Duplicates: Warnings:
mysql> insert into t1 select repeat("我",);
ERROR (): Data too long for column 'a' at row
mysql> insert into t1 select repeat("我",);
Query OK, row affected (0.11 sec)
Records: Duplicates: Warnings:
mysql> insert into t1 select "我我我我我我我我我我我我我我我我我我我1";
Query OK, row affected (0.09 sec)
Records: Duplicates: Warnings:
mysql> insert into t1 select "我我我我我我我我我我我我我我我我我我我11";
ERROR (): Data too long for column 'a' at row
varchar 存储极限:
表为utf8字符集:
mysql> show create table a;
+-------+-------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------+
| a | CREATE TABLE `a` (
`a` varchar() DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------+
row in set (0.01 sec)
mysql> create table a( a varchar());
ERROR (): Column length too big for column 'a' (max = ); use BLOB or TEXT instead
mysql> create table a( a varchar());
ERROR (): Column length too big for column 'a' (max = ); use BLOB or TEXT instead
mysql> create table a( a varchar());
ERROR (): Row size too large. The maximum row size for the used table type, not counting BLOBs, is . This includes st
eck the manual. You have to change some columns to TEXT or BLOBs
mysql> create table a( a varchar());
Query OK, rows affected (0.31 sec)
表为latin1字符集:
mysql> create table a1 ( a varchar())charset=latin1;
ERROR (): Row size too large. The maximum row size for the used table type, not counting BLOBs,
eck the manual. You have to change some columns to TEXT or BLOBs
mysql> create table a1 ( a varchar())charset=latin1;
ERROR (): Row size too large. The maximum row size for the used table type, not counting BLOBs,
eck the manual. You have to change some columns to TEXT or BLOBs
mysql> create table a1 ( a varchar())charset=latin1;
ERROR (): Row size too large. The maximum row size for the used table type, not counting BLOBs,
eck the manual. You have to change some columns to TEXT or BLOBs
mysql> create table a1 ( a varchar())charset=latin1;
Query OK, rows affected (0.33 sec)
不能插入汉字：
mysql> insert into a1 select repeat("我",);
ERROR (HY000): Incorrect string value: '\xE6\x88\x91\xE6\x88\x91...' for column 'a' at row
mysql> insert into a1 select "我";
ERROR (HY000): Incorrect string value: '\xE6\x88\x91' for column 'a' at row 1
mysql> insert into a1 select repeat("a",);
Query OK, row affected (0.17 sec)
Records: Duplicates: Warnings: