MySQL

MySQL:简介 Ubuntu 安装 MySQL MySQL:官方文档地址 MySQL:DDL、DQL、DML、DCL的含义 MySQL:Mac 中启动 关闭 MySQL 服务 MySQL:有哪些好用的管理工具? MySQL:命令行工具 mycli MySQL:CHAR类型 MySQL:VARCHAR类型 MySQL:整型数字 MySQL:datetime 类型 MySQL:时间戳 MySQL:创建和删除数据库 MySQL:切换和查看数据库 MySQL:创建和删除表 MySQL:在表中增加、删除、修改列 MySQL:创建和删除主键 MySQL:使用 rename 修改表名 MySQL:修改自增主键id的类型 MySQL:如何创建一个相同的表 MySQL:修改表的字符编码 MySQL:增删查改 MySQL:插入数据 MySQL:插入多行数据 MySQL:使用 insert set 插入数据 MySQL:大小写和反引号 MySQL:字符串类型值的大小写 MySQL:SQL注释 MySQL:不要使用utf8 MySQL:NULL的判等 MySQL:InnoDB存储引擎的限制 MySQL:if和case的使用 MySQL:使用 load data 快速导入数据 MySQL:使用 select into outfile 导出数据 MySQL:查询和设置 sql_mode MySQL:严格模式 MySQL:NOT NULL 字段不插入数据,会发生什么? MySQL:无符号整数列插入负数会发生什么? MySQL:关于 null 的那些事 MySQL:大表行数查询 MySQL:自动生成创建时间、更新时间;自动更新更新时间 MySQL:insert ignore MySQL:字符集排序规则 MySQL:如果连续更新一个字段两次,结果是? MySQL:字符串转数字 MySQL:尾部空格 MySQL:添加和删除索引 MySQL:唯一索引与NULL MySQL:唯一索引的单列长度限制 MySQL:InnoDB 索引 MySQL:字符集排序规则对唯一索引的影响 MySQL:唯一索引冲突消耗主键 ID MySQL 使用 index hint 指定索引:ignore index、force index、use index MySQL:查看客户端连接信息 MySQL:查看表的状态 show table status MySQL:如何治理连接数 ? MySQL:如何监控和处理慢查询与长事务 ? MySQL:自定义函数 MySQL:now() 函数 MySQL:unix_timestamp() 函数 MySQL:from_unixtime() 函数 MySQL:version() 函数 MySQL:current_timestamp() 函数 MySQL:cast 函数 MySQL:convert 函数 MySQL:使用 greatest、least 函数获取行最大值、最小值 MySQL:使用 group_concat 函数连接多行数据为一个字符串 MySQL:获取版本号 MySQL:Java 类型映射 MySQL下创建只能有一行记录的table 关于MySQL的字符集 理解数据库中的undo日志、redo日志、检查点 ubuntu下源码安装MySQL MySQL:JOIN解惑 如何快速更新数据库中的百万条数据

MySQL:CHAR类型


#MySQL


官方文档:https://dev.mysql.com/doc/refman/5.6/en/char.html 。 MySQL: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个字符。

在严格模式下,不会截断,会直接报错。具体见 MySQL:严格模式

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字节。具体可参考MySQL: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 。


( 本文完 )