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: CHAR类型


官方文档:https://dev.mysql.com/doc/refman/5.6/en/char.html 。VARCHAR类型 前面部分对该文档做了简单的意译。

本文中每个示例中都会创建一个test_table表,在示例结束后会将该表drop掉。

本文讨论的是 InnoDB 存储引擎下的 char。

char的长度是字符数,而非字节数

char 类型必须声明长度,这个长度是指字符数。

创建表:

create table test_table (
    c1 CHAR(2)
) engine = InnoDB character set = utf8mb4;

插入数据:

insert into test_table(c1) values('你好');

查询数据:

mysql> select * from test_table;
+------+
| c1   |
+------+
| 你好 |
+------+

你好在 utf8 中是几个字节? 我们进入 Python3 交互模式看一下:

>>> s = '你好'
>>> s.encode('utf8')
b'\xe4\xbd\xa0\xe5\xa5\xbd'

分别占3个字节,共6个字节。

char 的默认值

若为通过 default 显式指定默认值,MySQL会将 null 作为默认值。 创建表:

create table test_table (
    c1 CHAR(2)
) engine = InnoDB character set = utf8mb4;

查看表创建语句,会发现 DEFAULT NULL :

mysql> show create table test_table;
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `c1` char(2) DEFAULT NULL             |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

但是若使用了 not null,MySQL 不会自动设置默认值,那么默认值是什么?我们看下面的示例:

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

mysql> show create table test_table;
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `c1` char(2) NOT NULL                 |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

mysql> insert into test_table values();
mysql> insert into test_table(c1) values(default);
mysql> select * from test_table;
+----+
| c1 |
+----+
|    |
|    |
+----+
mysql> select concat('\'', c1, '\'') from test_table;
+------------------------+
| concat('\'', c1, '\'') |
+------------------------+
| ''                     |
| ''                     |
+------------------------+

这种情况下,是空字符串。

最好还是显式的指定默认值。如何指定?例如:

create table test_table (
    c1 CHAR(2) not null default '你好'
) engine = InnoDB character set = utf8mb4;

看下效果:

mysql> insert into test_table values();
mysql> insert into test_table(c1) values(default);
mysql> select * from test_table;
+------+
| c1   |
+------+
| 你好 |
| 你好 |
+------+

char 会截断超出长度范围的字符

创建表:

create table test_table (
    c1 CHAR(2)
) engine = InnoDB character set = utf8mb4;

插入数据:

insert into test_table(c1) values('你好世界');
insert into test_table(c1) values('abcd');

查询数据:

mysql> select * from test_table;
+------+
| c1   |
+------+
| 你好 |
| ab   |
+------+

可以看到,只保留了2个字符。

char 会去掉后部的空格

为什么?

https://dev.mysql.com/doc/refman/8.0/en/char.html :

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

创建表:

create table test_table (
    c1 CHAR(100)
) engine = InnoDB character set = utf8mb4;

插入数据:

insert into test_table(c1) values('你好世界    ');
insert into test_table(c1) values('abcd\t');

查询:

mysql> select concat('\'', c1 ,'\'') from test_table;
+------------------------+
| concat('\'', c1 ,'\'') |
+------------------------+
| '你好世界'             |
| 'abcd '                |
+------------------------+

char 长度最大值是 255

create table test_table (
    c1 CHAR(256)
) engine = InnoDB character set = utf8mb4;

执行上面的SQL会报错,并建议换成 blob、text类型:

(1074, u"Column length too big for column 'c1' (max = 255); use BLOB or TEXT instead")

下面的便不会报错:

create table test_table (
    c1 CHAR(255)
) engine = InnoDB character set = utf8mb4;

char 长度最小值是 0

char的长度不能是负数:

create table test_table (
    c1 CHAR(-1)
) engine = InnoDB character set = utf8mb4;

执行失败信息如下:

(1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1)\n) engine = InnoDB character set = utf8mb4' at line 2")

char 的长度可以是 0:

create table test_table (
    c1 CHAR(0)
) engine = InnoDB character set = utf8mb4;

既然长度为0,那么支持的值只有两个,一个是 NULL,一个是空字符串。

插入数据:

insert into test_table(c1) values('你好');
insert into test_table(c1) values('');
insert into test_table(c1) values(NULL);

查看数据:

mysql> select * from test_table;
+--------+
| c1     |
+--------+
|        |
|        |
| <null> |
+--------+

mysql> select concat('\'', c1 ,'\'') from test_table;
+------------------------+
| concat('\'', c1 ,'\'') |
+------------------------+
| ''                     |
| ''                     |
| <null>                 |
+------------------------+

null 与 空字符串是不同的

create table test_table (
    c1 CHAR(10)
) engine = InnoDB character set = utf8mb4;

insert into test_table(c1) values('你好');
insert into test_table(c1) values('');
insert into test_table(c1) values(NULL);

看下查询效果:

mysql> select * from test_table;
+--------+
| c1     |
+--------+
| 你好   |
|        |
| <null> |
+--------+
3 rows in set

mysql> select * from test_table where c1 is null;
+--------+
| c1     |
+--------+
| <null> |
+--------+
1 row in set

mysql> select * from test_table where c1 = '';
+----+
| c1 |
+----+
|    |
+----+
1 row in set

not null

create table test_table (
    c1 CHAR(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);  -- 报错

当插入 null 时,报错如下:

(1048, u"Column 'c1' cannot be null")

指定索引长度

CREATE TABLE test_table (
    c1 CHAR(255),
    KEY idx_c1(c1(10))
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

查看下创建语句:

mysql letian@localhost:test> show create table test_table;
+------------+-----------------------------------------+
| Table      | Create Table                            |
+------------+-----------------------------------------+
| test_table | CREATE TABLE `test_table` (             |
|            |   `c1` char(255) DEFAULT NULL,          |
|            |   KEY `idx_c1` (`c1`(10))               |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+------------+-----------------------------------------+

这种情况下,c1是能插入255个字符的,但是只会索引前10个字符。这是一种「前缀索引」。在查询数据时,查询条件的中的c1也会取前10个字符,查到数据后,再去看是否与c1的完整值匹配。

char 的可索引长度

每个列的长度不能大于767字节;所有组成索引列的长度和不能大于3072字节。具体可参考InnoDB存储引擎的限制

验证1:

CREATE TABLE test_table (
    c1 CHAR(255),
    c2 CHAR(255),
    c3 CHAR(255),
    c4 CHAR(255),
    c5 CHAR(255),
    KEY idx_c1(c1),
    KEY idx_c12(c1, c2),
    KEY idx_c123(c1, c2, c3),
    KEY idx_c1234(c1, c2, c3, c4)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
mysql> show create table test_table;
+------------+-------------------------------------------------------------+
| Table      | Create Table                                                |
+------------+-------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (                                 |
|            |   `c1` char(255) DEFAULT NULL,                              |
|            |   `c2` char(255) DEFAULT NULL,                              |
|            |   `c3` char(255) DEFAULT NULL,                              |
|            |   `c4` char(255) DEFAULT NULL,                              |
|            |   KEY `idx_c1` (`c1`(191)),                                 |
|            |   KEY `idx_c12` (`c1`(191),`c2`(191)),                      |
|            |   KEY `idx_c123` (`c1`(191),`c2`(191),`c3`(191)),           |
|            |   KEY `idx_c1234` (`c1`(191),`c2`(191),`c3`(191),`c4`(191)) |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                     |
+------------+-------------------------------------------------------------+

为什么是 191 ? 因为 utf8mb4 支持4字节字符,191×4 < 767 < 192×4。

验证2:

CREATE TABLE test_table (
    c1 CHAR(255),
    c2 CHAR(255),
    c3 CHAR(255),
    c4 CHAR(255),
    c5 CHAR(255),
    KEY idx_c12345(c1, c2, c3, c4, c5)
) ENGINE = InnoDB CHARACTER SET = utf8mb4;

idx_c12345 含有5个字段,执行会报错:

(1071, u'Specified key was too long; max key length is 3072 bytes')

因为 191 × 5 × 4 > 3072 。


( 本文完 )

文章目录