Rubin's Blog

  • 首页
  • 关于作者
  • 隐私政策
享受恬静与美好~~~
分享生活的点点滴滴~~~
  1. 首页
  2. MySQL
  3. 正文

MySQL学习之字符串类型

2021年 4月 29日 960点热度 0人点赞 0条评论

前言

在设计数据库中,我们对字符串类型并不陌生。MySQl的字符串类型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET。不同的业务对应不同的类型,本博文主要是讲述了CHAR和VACHAR两种类型在使用过程中的一些注意事项。

定义

CHAR(N)表示保存定长字符,VARCHAR(N)表示保存变长字符。但是需要注意的是,无论是定长还是变长,N代表的是字符,而不是字节。CHAR的取值范围是0~255,VARCHAR为0~65536。在绝大部分场景下,VARCHAR完全满足我们的需求。但是如果超出了VARCHAR的范围,我们就需要考虑更大的字符类型 TEXT 或 BLOB。两者最大存储长度为 4G,其区别是 BLOB 没有字符集属性,纯属二进制存储。

字符集

我们对于字符集这个名词并不陌生。字符集就是我们对于字符的编码格式。我们在设计数据库的时候,推荐将MySQL数据库的字符集设置为UTF8MB4。这是因为UTF8MB4可以存储emoji表情,并且对于中文有更好的支持。原因就是UTF8MB4最大的字符编码长度为4字节,而UTF8是三个字节。也可以说UTF8MB4是UTF8的一个超集。因为不同版本默认字符集的不同,你要显式地在配置文件中进行相关参数的配置:character-set-server = utf8mb4。在UTF8MB4的编码格式下,CHAR可以存储一字节的字符,也可以存储4个字节的字符,所以原理上来讲,也是变长存储。所以在UTF8MB4的编码格式下,我们可以把CHAR全部用VARCHAR替换,是没有问题的。

排序规则

每一种字符集都有其默认的排序规则。查看默认排序规则的SQl为:SHOW CHARSET LIKE 'utf8%';

排序规则以 _ci 结尾,表示不区分大小写(Case Insentive),_cs 表示大小写敏感,_bin 表示通过存储字符的二进制进行比较。需要注意的是,比较 MySQL 字符串,默认采用不区分大小的排序规则。所以我们在存放像用户名等等需要区分大小写的字段的时候,需要单独设置。

修改字符集

修改表中已有列的字符集,使用命令 ALTER TABLE ... CONVERT TO ....;

不要使用 ALERT TABLE emoji_test CHARSET utf8mb4;

原因是这种写法只会影响修改之后的数据的字符集,不会修改老数据的字符集。

设计枚举字段

我们在设计枚举字段(比如用户性别、订单状态等等)的时候,习惯用TINYINT来表示。这种设计的不足表现在,不能很直观的看出每个数字的含义,而且可能出现预期之外的数据(比如性别0代表男,1代表女,2代表未知,有存入3等等非枚举项数据的风险)。

那么,对于枚举值我们应该怎么设计比较好呢?分为两种情况。

一是MySQL8.0.16之后,我们可以使用数据库原生提供 CHECK 约束功能来限制我们的枚举项。写法如下:

mysql> SHOW CREATE TABLE User\G

*************************** 1. row ***************************

       Table: User

Create Table: CREATE TABLE `User` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,

  PRIMARY KEY (`id`),

  CONSTRAINT `user_chk_1` CHECK (((`sex` = _utf8mb4'M') or (`sex` = _utf8mb4'F')))

) ENGINE=InnoDB

1 row in set (0.00 sec)


mysql> INSERT INTO User VALUES (NULL,'M');

Query OK, 1 row affected (0.07 sec)


mysql> INSERT INTO User VALUES (NULL,'Z');

ERROR 3819 (HY000): Check constraint 'user_chk_1' is violated.

从这段代码中看到,第 8 行的约束定义 user_chk_1 表示列 sex 的取值范围,只能是 M 或者 F。同时,当 15 行插入非法数据 Z 时,你可以看到 MySQL 显式地抛出了违法约束的提示。

二是在MySQL8.0.16之前,我们可以通过ENUM 字符串枚举类型并将参数 SQL_MODE 设置为严格模式来保证枚举值的正确定,代码如下:

mysql> SHOW CREATE TABLE User\G

*************************** 1. row ***************************

       Table: User

Create Table: CREATE TABLE `User` (

  `id` bigint NOT NULL AUTO_INCREMENT,

  `sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB

1 row in set (0.00 sec)


mysql> SET sql_mode = 'STRICT_TRANS_TABLES';

Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> INSERT INTO User VALUES (NULL,'F');

Query OK, 1 row affected (0.08 sec)


mysql> INSERT INTO User VALUES (NULL,'A');

ERROR 1265 (01000): Data truncated for column 'sex' at row 1

由于类型 ENUM 并非 SQL 标准的数据类型,而是 MySQL 所独有的一种字符串类型。抛出的错误提示也并不直观,这样的实现总有一些遗憾,主要是因为MySQL 8.0 之前的版本并没有提供约束功能。

本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: MySQL
最后更新:2022年 6月 9日

RubinChu

一个快乐的小逗比~~~

打赏 点赞
< 上一篇
下一篇 >

文章评论

razz evil exclaim smile redface biggrin eek confused idea lol mad twisted rolleyes wink cool arrow neutral cry mrgreen drooling persevering
取消回复
文章目录
  • 前言
  • 定义
  • 字符集
  • 排序规则
  • 修改字符集
  • 设计枚举字段
最新 热点 随机
最新 热点 随机
问题记录之Chrome设置屏蔽Https禁止调用Http行为 问题记录之Mac设置软链接 问题记录之JDK8连接MySQL数据库失败 面试系列之自我介绍 面试总结 算法思维
java面试系列之引用 问题记录之Chrome设置屏蔽Https禁止调用Http行为 JVM之GC日志分析 MHA高可用架构搭建 Redis之核心原理 MyBatis之缓存

COPYRIGHT © 2021 rubinchu.com. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

京ICP备19039146号-1