MySQL 字元串指南

了解 MySQL 如何存儲和顯示你的字元串變數，以便你能更好地控制你的數據。

字元串是你在 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        |
+---------+---------+-----------------------------------+

總結

字元串是資料庫中最常用的數據類型之一，而 MySQL 仍然是當今最流行的資料庫系統之一。我希望你能從這篇文章中學到一些新的東西，並能用你的新知識來提高你的資料庫技能。

via: https://opensource.com/article/23/1/strings-mysql

作者：Hunter Coleman 選題：lkxed 譯者：wxy 校對：wxy

本文由 LCTT 原創編譯，Linux中國 榮譽推出

本文轉載來自 Linux 中國: https://github.com/Linux-CN/archive