MySQL 学习笔记

MySQL是一个开源的关系型数据库,非常流行。

如无特殊说明,本专题文章的 SQL 均在 5.6 版本中验证。

👉文章列表
MySQL 学习笔记 ⏬ 创建和删除数据库 切换和查看数据库 创建和删除表 在表中增加、删除、修改列 添加和删除索引 增删查改 创建和删除主键 大小写和反引号 字符串类型值的大小写 SQL注释 CHAR类型 VARCHAR类型 整型数字 时间戳 不要使用utf8 唯一索引与NULL 唯一索引的单列长度限制 NULL的判等 DDL、DML、DCL的含义 查看表的状态 show table status InnoDB存储引擎的限制 if和case的使用 自定义函数 InnoDB 索引 事务 事务隔离级别的概念、查看和修改 读已提交(read committed)隔离级别下的锁 可重复读(repeatable read)隔离级别下的锁 修改自增主键id的类型 使用 load data 快速导入数据 使用 select into outfile 导出数据

MySQL: VARCHAR类型


官方文档: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('你好', '世界'); -- 成功

( 本文完 )

文章目录