Руководство по строкам в MySQL
Узнайте, как MySQL хранит и отображает строковые переменные, чтобы вы могли лучше контролировать свои данные.
Строки — один из наиболее распространенных типов данных, которые вы будете использовать в MySQL. Многие пользователи вставляют и читают строки в свои базы данных, не особо задумываясь о них. Цель этой статьи — дать вам более глубокое представление о том, как MySQL хранит и отображает строковые переменные, чтобы вы могли лучше контролировать свои данные.
Вы можете разбить строки на две категории: двоичные и недвоичные. Вероятно, большую часть времени вы думаете о недвоичных строках. Недвоичные строки имеют наборы символов и параметры сортировки. С другой стороны, двоичные строки хранят такие вещи, как файлы MP3 или изображения. Даже если вы сохраните слово в двоичной строке, например song, оно не будет храниться так же, как в недвоичной строке.
Я сосредоточусь на недвоичных строках. Все недвоичные строки в MySQL связаны с набором символов и параметрами сортировки. Набор символов строки определяет, какие символы могут храниться в строке, а параметры сортировки определяют порядок упорядочения строк при их отображении.
Наборы символов
Чтобы просмотреть наборы символов в вашей системе, выполните следующую команду:
SHOW CHARACTER SET;Эта команда выведет четыре столбца данных, включая набор символов:
- Имя
- Краткое описание
- Параметры сортировки по умолчанию
- Максимальный размер каждого символа в наборе символов
Раньше в MySQL по умолчанию использовался набор символов latin1, но начиная с версии 8.0 по умолчанию используется utf8mb4. Параметры сортировки по умолчанию теперь utf8mb4_0900_ai_ci. ai указывает, что это сопоставление нечувствительно к диакритическому знаку (á=a), а ci указывает, что оно нечувствительно к регистру (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 |
+------------+------------------+Сортировка
Параметры сортировки строки будут определять, как будут отображаться значения при выполнении инструкции SQL с предложением ORDER BY. Выбор параметров сортировки определяется выбранным вами набором символов. Когда вы выполнили команду 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';Если вы также хотите выбрать параметры сортировки, отличные от значений по умолчанию, вы можете добавить предложение COLLATE в команду SET NAMES.
Например, предположим, что в вашей базе данных хранятся слова на испанском языке. Параметры сортировки по умолчанию для 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, их недвоичные эквиваленты и их максимальная длина:
- двоичный: символ (255)
- varbinary: varchar (65 535)
- tinyblob: tinytext (255)
- BLOB: текст (65 535)
- mediumblob: средний текст (16 777 215)
- longblob: длинный текст (4 294 967 295)
Важно помнить, что в отличие от типов varbinary, varchar, text и blob, которые хранятся в полях переменной длины (то есть используют столько места, сколько необходимо), MySQL хранит типы двоичных и char в полях фиксированной длины. Таким образом, такое значение, как char(20) или binary(20), всегда будет занимать 20 байт, даже если вы храните в них менее 20 символов. MySQL дополняет значения значением ASCII NUL (0x00) для двоичных типов и пробелами для типов символов.
Еще одна вещь, которую следует учитывать при выборе типов данных, — хотите ли вы, чтобы пробелы после строки сохранялись или удалялись. При отображении данных 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 |
+---------+---------+-----------------------------------+Заворачивать
Строки — один из наиболее распространенных типов данных, используемых в базах данных, а MySQL остается одной из самых популярных систем баз данных, используемых сегодня. Я надеюсь, что вы узнали что-то новое из этой статьи и сможете использовать свои новые знания для улучшения своих навыков работы с базами данных.