MySQL 字符串指南
  

暴躁的钢索 6872人觉得有帮助

{{ttag.title}}
字符串是你在 MySQL 中使用的最常见的数据类型之一。许多用户在他们的数据库中插入和读取字符串,而没有认真地了解过它们。本文旨在让你深入了解 MySQL 如何存储和显示你的字符串变量,以便你能更好地控制你的数据。
你可以把字符串分成两类:二进制和非二进制。你可能在大多数时候想到的是非二进制字符串。非二进制字符串有字符集和排序的不同。另一方面,二进制字符串存储诸如 MP3 文件或图像等东西。即使你在二进制字符串中存储了一个词,比如“歌曲”,它的存储方式也与非二进制字符串不同。
我将重点讨论非二进制字符串。MySQL 中的所有非二进制字符串都与字符集和排序相关。字符串的字符集控制哪些字符可以存储在字符串中,而它的排序方式控制当你显示字符串时如何排序。
字符集
要查看你系统中的字符集,请运行以下命令:
  • SHOW CHARACTER SET;

这个命令将输出四列数据,包括字符集:
  • 名称
  • 简要描述
  • 默认的排序方式
  • 字符集中每个字符的最大尺寸

MySQL 过去默认为 latin1 字符集,但自 8.0 版以来,默认为 utf8mb4。现在的默认排序方式是 utf8mb4_0900_ai_ci。ai 表示该排序对音调不敏感( á = a),而 ci 则指定它对大小写不敏感(a = A)。
不同的字符集将其字符存储在内存中不同大小的块中。例如,从上面的命令可以看出,存储在 utf8mb4 的字符被存储在 1 到 4 个字节大小的内存中。如果你想看看一个字符串是否包含多字节的字符,你可以使用 CHAR_LENGTH() 和 LENGTH() 函数。CHAR_LENGTH() 显示一个字符串包含多少个字符,而 LENGTH() 显示一个字符串有多少个字节,根据字符集的不同,它可能与一个字符串的字符长度相同,也可能不相同。下面是一个例子:
  • SET @a = CONVERT('data' USING latin1);
  • SELECT LENGTH(@a), CHAR_LENGTH(@a);
  • +------------+-----------------+
  • | LENGTH(@a) | CHAR_LENGTH(@a) |
  • +------------+-----------------+
  • |     4      |       4         |
  • +------------+-----------------+

这个例子表明,latin1 字符集以单字节为单位存储字符。其他字符集,如 utf16,允许多字节的字符:
  • SET @b = CONVERT('data' USING utf16);
  • SELECT LENGTH(@b), CHAR_LENGTH(@b);
  • +------------+------------------+
  • | LENGTH(@b) | CHAR_LENGTH(@b)  |
  • +------------+------------------+
  • |       8    |        4         |
  • +------------+------------------+

排序
当你运行带有 ORDER BY 子句的 SQL 语句时,字符串排序方式将决定值的显示方式。你对排序方式的选择是由你选择的字符集决定的。当你运行上面的 SHOW CHARACTER SET 命令时,你看到了每个字符集的默认排序方式。你可以很容易地看到某个特定字符集的所有排序方式。例如,如果你想查看 utf8mb4 字符集允许哪些排序,请运行:
  • SHOW COLLATION LIKE 'utf8mb4%';

排序方式可以是不区分大小写的,也可以是区分大小写的,或者是二进制的。让我们建立一个简单的表,向其中插入一些值,然后用不同的排序方式查看数据,看看输出结果有什么不同:
  • CREATE TABLE sample (s CHAR(5));
  • INSERT INTO sample (s) VALUES
  • ('AAAAA'), ('ccccc'),  ('bbbbb'), ('BBBBB'), ('aaaaa'), ('CCCCC');
  • SELECT * FROM sample;
  • +-----------+
  • | s         |
  • +-----------+
  • | AAAAA     |
  • | ccccc     |
  • | bbbbb     |
  • | BBBBB     |
  • | aaaaa     |
  • | CCCCC     |
  • +-----------+

在不区分大小写的情况下,你的数据会按字母顺序返回,但不能保证大写的单词会排在小写的单词之前,如下图所示:
  • SELECT * FROM sample ORDER BY s COLLATE utf8mb4_turkish_ci;
  • +-----------+
  • | s         |
  • +-----------+
  • | AAAAA     |
  • | aaaaa     |
  • | bbbbb     |
  • | BBBBB     |
  • | ccccc     |
  • | CCCCC     |
  • +-----------+

另一方面,当 MySQL 运行大小写敏感的搜索时,每个字母的小写将排在大写之前:
  • SELECT * FROM sample ORDER BY s COLLATE utf8mb4_0900_as_cs;
  • +-----------+
  • | s         |
  • +-----------+
  • | aaaaa     |
  • | AAAAA     |
  • | bbbbb     |
  • | BBBBB     |
  • | ccccc     |
  • | CCCCC     |
  • +-----------+

而按二进制排序方式将返回所有大写的值,然后再返回小写的值:
  • SELECT * FROM sample ORDER BY s COLLATE utf8mb4_0900_bin;
  • +-----------+
  • | s         |
  • +-----------+
  • | AAAAA     |
  • | ccccc     |
  • | bbbbb     |
  • | BBBBB     |
  • | aaaaa     |
  • | CCCCC     |
  • +-----------+

如果你想知道一个字符串使用哪种字符集和排序,你可以使用被恰当命名的 charset 和 collation 函数。运行 MySQL 8.0 或更高版本的服务器将默认使用 utf8mb4 字符集和 utf8mb4_0900_ai_ci 排序:
  • SELECT charset('data');
  • +-------------------+
  • | charset('data')   |
  • +-------------------+
  • | utf8mb4           |
  • +-------------------+
  • SELECT collation('data');
  • +--------------------+
  • | collation('data')  |
  • +--------------------+
  • | utf8mb4_0900_ai_ci |
  • +--------------------+

你可以使用 SET NAMES 命令来改变所使用的字符集或排序方式。
要从 utf8mb4 字符集改为 utf16,运行这个命令:
  • SET NAMES 'utf16';

如果你想选择默认以外的排序方式,你可以在 SET NAMES 命令中添加一个 COLLATE 子句。
例如,假设你的数据库存储西班牙语的单词。MySQL 的默认排序(utf8mb4_0900_ai_ci)将 ch 和 ll 视为两个不同的字符,并将它们排序。但在西班牙语中,ch 和 ll 是单独的字母,所以如果你想让它们按正确的顺序排序(分别排在 c 和 l 之后),你需要使用不同的排序。一个选择是使用 utf8mb4_spanish2_ci 排序方式:
  • SET NAMES 'utf8mb4' COLLATE 'utf8mb4_spanish2_ci';

储存字符串
MySQL 允许你为你的字符串值选择不同的数据类型。(甚至比其他流行的数据库,如 PostgreSQL 和 MongoDB 更多。)
下面是 MySQL 的二进制字符串数据类型的列表、它们的非二进制对应物,以及它们的最大长度:
  • binary:char(255)
  • varbinary:varchar(65,535)
  • tinyblob:tinytext(255)
  • blob:text(65,535)
  • mediumblob:mediumtext(16,777,215)
  • longblob:longtext(4,294,967,295)

要记住的一件重要事情是,与被存储在可变长度的字段中的 varbinary、varchar、text 和 blob 类型不同(也就是说,只使用需要的空间),MySQL 将二进制(binary)和字符(char)类型存储在固定长度的字段。因此,像 char(20) 或 binary(20) 这样的值将总是占用 20 个字节,即使你在其中存储了少于 20 个字符。对于二进制类型,MySQL用 ASCII NUL 值(0x00)填充这些值,对于 字符类型,用空格填充。
在选择数据类型时要考虑的另一件事是,你是否希望在字符串后面的空格被保留或剥离。在显示数据时,MySQL 会从以字符数据类型存储的数据中剥离空格,但不会剥离 varchar 的空格。
  • CREATE TABLE sample2 (s1 CHAR(10), s2 VARCHAR(10));
  • INSERT INTO sample2 (s1, s2) VALUES ('cat       ', 'cat       ');
  • SELECT s1, s2, CHAR_LENGTH(s1), CHAR_LENGTH(s2) FROM sample2;
  • +---------+---------+-----------------------------------+
  • | s1      | s2      | CHAR_LENGTH(s1) | CHAR_LENGTH(s2) |
  • +---------+---------+-----------------------------------+
  • | cat     | cat     |        3        |       10        |
  • +---------+---------+-----------------------------------+

打赏鼓励作者,期待更多好文!

打赏
3人已打赏

Mr程 发表于 2023-2-6 13:43
  
每天进步多一点,每天学习多一点
平凡的小网工 发表于 2023-2-12 19:01
  
楼主,是你让我深深地理解了‘人外有人,天外有天’这句话。谢谢你!在看完这帖子以后,我没有立即回复,因为我生怕我庸俗不堪的回复会玷污了这社区少有的帖子。但是我还是回复了,因为觉得如果不能在如此精彩的帖子后面留下自己的网名,那我会遗憾终生的!
发表新帖
热门标签
全部标签>
每日一问
技术盲盒
技术笔记
每周精选
干货满满
技术咨询
信服课堂视频
秒懂零信任
自助服务平台操作指引
新版本体验
标准化排查
产品连连看
安装部署配置
功能体验
GIF动图学习
玩转零信任
2023技术争霸赛专题
技术晨报
安全攻防
每日一记
深信服技术支持平台
天逸直播
华北区交付直播
社区帮助指南
畅聊IT
答题自测
专家问答
技术圆桌
在线直播
MVP
网络基础知识
升级
上网策略
测试报告
日志审计
问题分析处理
流量管理
运维工具
云计算知识
用户认证
原创分享
解决方案
sangfor周刊
VPN 对接
项目案例
SANGFOR资讯
专家分享
技术顾问
信服故事
SDP百科
功能咨询
终端接入
授权
设备维护
资源访问
地址转换
虚拟机
存储
迁移
加速技术
排障笔记本
产品预警公告
信服圈儿
S豆商城资讯
技术争霸赛
「智能机器人」
追光者计划
答题榜单公布
纪元平台
通用技术
卧龙计划
华北区拉练
以战代练
山东区技术晨报
文档捉虫活动
齐鲁TV

本版版主

217
272
151

发帖

粉丝

关注

本版达人

皮皮虾·真

本周建议达人

郑州网络

本周分享达人

二进制网络

本周提问达人