MySQL：VARCHAR类型


#MySQL 笔记

官方文档：https://dev.mysql.com/doc/refman/5.6/en/char.html

varchar和char是类似的，但又不同：

  • 存取方式不同。
  • 最大长度不同。
  • 是否去掉尾部的空格。

varchar特点如下：

  • 长度可选范围是[0, 65535]。这个长度是指字符数量。注意，InnoDB中，去掉TEXT、BLOB列后，行的长度不能超过65535字节。
  • 因为varchar是变长的，所以当存储的数据不超过255字节时，会额外的用1字节记录长度。当存储的数据超过255字节时，会额外的用2字节记录长度。
  • 默认情况下，严格SQL模式 是未开启的。对于char和varchar，当插入的字符数超过列设定的数量时，会将其截断后插入，并返回warning。若开启了严格SQL模式，这种情况是直接报错。
  • 无论处于哪种SQL模式，对于varchar，超出列长度的空格会在插入前被截断，并返回warning。对于char，会「偷偷的」截去超出长度的空格，不会返回warning。
  • varchar在存储时不会像char那样填充空格。在存储和读取时，会保留尾部的空格。

下表对比了char和varchar在存储上的不同：

要存储的值 CHAR(4)下如何存储 CHAR(4)下的存储大小 VARCHAR(4)下如何存储 VARCHAR(4)下的存储大小
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

为什么上表中存储大小比数据的大小要多1 byte ？ 因为 varchar 要额外用1字节记录数据长度。

所有的MySQL排序规则（collation，不包括like操作）都会忽略尾部的空格，我们看下官网的示例：

mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO names VALUES ('Monty');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Monty', myname = 'Monty  ' FROM names;
+------------------+--------------------+
| myname = 'Monty' | myname = 'Monty  ' |
+------------------+--------------------+
|                1 |                  1 |
+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT myname LIKE 'Monty', myname LIKE 'Monty  ' FROM names;
+---------------------+-----------------------+
| myname LIKE 'Monty' | myname LIKE 'Monty  ' |
+---------------------+-----------------------+
|                   1 |                     0 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

另外，如果某一列加了唯一索引，那么要小心尾部的空格，因为'a' 、'a '会被看成相同的值，所以存了'a' 后，就不能存'a '了，否则报错。

上面是对官方文档的简单翻译，下面给一些示例。

varchar 的可索引长度

创建表：

CREATE TABLE test_table (
    c1 varchar(500),
    KEY idx_c1(c1)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

查询创建语句：

mysql> show create table test_table
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `c1` varchar(500) DEFAULT NULL,       |
|            |   KEY `idx_c1` (`c1`(191))              |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

和 char 一样，使用 utf8mb4 字符集时，最长可索引一列的191个字符。

not null

若未指定not null，默认值是null；若声明了 not null，默认值是空字符串。

create table test_table (
    c1 varchar(2) not null
) engine = InnoDB character set = utf8mb4;

insert into test_table(c1) values('你好'); -- 成功
insert into test_table(c1) values('');  -- 成功
insert into test_table(c1) values(NULL);  -- 报错

create table test_table (
    c1 varchar(2) not null,
    c2 varchar(2) not null
) engine = InnoDB character set = utf8mb4;

insert into test_table(c1) values('你好'); -- 成功
insert into test_table(c1) values(null);  -- 报错 (1048, u"Column 'c1' cannot be null")
insert into test_table(c1, c2) values(null, null);  -- 报错 (1048, u"Column 'c1' cannot be null")
insert into test_table(c1, c2) values('你好', null); -- 报错 (1048, u"Column 'c2' cannot be null")
insert into test_table(c1, c2) values('你好', '世界'); -- 成功

CREATE TABLE test_table (
    c1 varchar(2) not null,
    c2 varchar(2)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

insert into test_table(c1) values('你好'); -- 成功
insert into test_table(c1) values(null);  -- 报错 (1048, u"Column 'c1' cannot be null")
insert into test_table(c1, c2) values(null, null);  -- 报错 (1048, u"Column 'c1' cannot be null")
insert into test_table(c1, c2) values('你好', null);  -- 成功
insert into test_table(c1, c2) values('你好', '世界'); -- 成功


( 本文完 )