Mysql 字符串类型｜小册免费学

dHy
345 阅读3分钟

本篇文章主要是总结一下日常碰到的一些有关字符串有意思的地方。

字符串类型

类型最大长度存储空间要求含义
CHAR(M)M个字符M×W个字节固定长度的字符串
VARCHAR(M)M个字符L+1 或 L+2 个字节可变长度的字符串
TEXT2¹⁶-1 个字节L+2 个字节小型的字符串

一般使用最多的就是 char, varchar，先说说他们的代表类型：

  • char(M)

CHAR(M)中的 M 代表该类型最多可以存储的字符数量，注意，是字符数量，不是字节数量。其中M的取值范围是0~255。

但是在不同的字符集下需要的存储空间不一致，所以在多字节字符集下，char 和 varchar 的实现完全相同，都是变长存储！

在不同的字符集下，char 可以存储1个字符字节，也可以存一个😊

  • VARCHAR(M)

VARCHAR(M)中的M也是代表该类型最多可以存储的字符数量，理论上的取值范围是1~65535。

因为是变长的，所以这个和其他数据库存储也是一样的想法：

  • 占用字节数
  • 真实存储

这个和 redis 中的 SDS 这种动态字符串也是一致的。它也会按照真实存储占用来动态标注前面的占用字节数

  1. 编码一个字符最多需要W个字节
  2. M代表该类型最多可以存储的字符数量

则前面的占用字节数：

  • M×W < 256时，只需要一个字节来表示占用的字节数
  • 当M×W >= 256且M×W < 65536时，需要两个字节来表示占用的字节数

所以综上：

  1. 默认字符集推荐设置为 UTF8MB4
  2. 可以把 CHAR 全部用 VARCHAR 替换，底层存储的本质实现一模一样

排序

这个算是一个坑吧，自己之前因为大小写，比对字符串大小，爬过这个默认不区分大小写的坑。。。

mysql> SHOW CHARSET LIKE 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.01 sec)
  1. _ci 结尾，表示不区分大小写（Case Insentive）
  2. _cs 表示大小写敏感
  3. _bin 表示通过存储字符的二进制进行比较
  4. 比较 MySQL 字符串，默认采用不区分大小

也就是说：

mysql> SELECT 'a' = 'A';
+-----------+
| 'a' = 'A' |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

我们当时的比较是 Asian 和 asian 比较，业务中规定是不一样的，所以这个时候就需要切换比较规则。当然如果你的业务不需要很强的大小写规范，切记不要改。

索引

我们如何为字符串类型的字段简历索引呢？

上面已经说过了，其实都是变长的，既然变长，将一个字符串字段设置为索引是没有必要的，加了查询性能也不会提高，还可能走全表扫描，但是我们是可以通过一些字符串尽可能公有的部分做 前缀索引：

alter table SUser add index index2(email(6));

上面就是对 email 字段，创建的index2索引里面，对于每个记录都是只取前6个字节。这个和全字段做索引的区别在哪呢？

在构建索引的时候，会把该字段的内容排序+主键。但是 email(6) 只取了前6个，这样在走索引匹配的时候，会优先匹配前面部分的，二分扫描的范围可以快速扩散。

但是这个扫描很取决于前缀索引长度。定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。这个时候就有个疑问，那我定义多少呢？

select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

在这些区分度中，选取你自己觉得可以接受的损失比例，然后再选择前缀长度最小即可【越小占用空间越小】

avatar
文章
阅读
粉丝