MySQL字符串指南

了解 MySQL 如何存储和显示您的字符串变量，以便更好地控制您的数据。

图片来源

Opensource.com

字符串是您在 MySQL 中最常用的数据类型之一。许多用户在他们的数据库中插入和读取字符串，而没有过多地考虑它们。本文旨在深入探讨 MySQL 如何存储和显示您的字符串变量，以便您可以更好地控制您的数据。

您可以将字符串分为两类：二进制和非二进制。您可能大部分时间都在考虑非二进制字符串。非二进制字符串具有字符集和排序规则。另一方面，二进制字符串存储诸如 MP3 文件或图像之类的内容。即使您在二进制字符串中存储一个词，例如 **song**，它的存储方式也与在非二进制字符串中不同。

我将重点关注非二进制字符串。 MySQL 中的所有非二进制字符串都与字符集和排序规则相关联。字符串的字符集控制可以在字符串中存储哪些字符，其排序规则控制您显示字符串时字符串的排序方式。

字符集

要查看系统上的字符集，请运行以下命令

SHOW CHARACTER SET;

此命令将输出四列数据，包括字符集

名称
简要描述
默认排序规则
字符集中每个字符的最大大小

MySQL 过去默认为 **latin1** 字符集，但自 8.0 版以来，默认值已为 **utf8mb4**。默认排序规则现在是 **utf8mb4_0900_ai_ci**。 **ai** 表示此排序规则不区分重音 ( **á = a** )， **ci** 指定它不区分大小写 ( **a = A** )。

不同的字符集以各种大小的内存块存储其字符。例如，如您从上面的命令中看到的，存储在 **utf8mb4** 中的字符以一到四个字节的大小存储在内存中。如果要查看字符串是否具有多字节字符，可以使用 **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     |
| BBBBB     |
| CCCCC     |
| aaaaa     |
| bbbbb     |
| 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**) 填充二进制类型的值，并使用空格填充 char 类型的值。

选择数据类型时要考虑的另一件事是您是否希望保留或剥离字符串后的空格。显示数据时，MySQL 会从使用 char 数据类型存储的数据中剥离空格，但不会从 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        |
+---------+---------+-----------------------------------+