1. Обзор содержания
В процессе использования MySQL очень важно понимать концепцию набора символов, порядок символов и влияние различных настроек на хранение и сравнение данных. Проблема «искаженных символов», с которой сталкиваются многие студенты в своей повседневной работе, вероятно, вызвана плохим пониманием набора символов и порядка символов, а также неправильными настройками.
В этой статье представлено следующее содержание от мелкого к более глубокому:
- Основные понятия и связи наборов символов и последовательностей символов
- Уровни настройки набора символов и порядка символов, поддерживаемые MySQL, и взаимосвязь между уровнями настройки
- Просмотр и настройка сервера, базы данных, таблицы, набора символов на уровне столбца и порядка символов
- Вы должны установить набор символов, последовательность слов?
Во-вторых, концепция и взаимосвязь набора символов и порядка символов.
При хранении данных MySQL обеспечивает поддержку различных наборов символов. В операции сравнения данных он обеспечивает поддержку разного порядка символов.
MySQL предоставляет различные уровни настроек, включая уровень сервера, уровень базы данных, уровень таблицы и уровень столбца, которые могут обеспечить очень точные настройки.
Какой набор символов, порядок символов? Проще говоря:
- Набор символов (набор символов): определяет символы и кодировку символов.
- Порядок символов (сопоставление): определяет правила сравнения символов.
Например:
Есть четыре символа: A, B, a, b, кодировка этих четырех символов: A = 0, B = 1, a = 2, b = 3 соответственно. Символ + кодировка здесь составляют набор символов.
Что, если мы хотим сравнить размер двух символов? Например, A, B или a, b, самый интуитивный способ сравнения — использовать их кодировку, например, потому что 0
Кроме того, для А и а, хотя они и кодируются по-разному, мы считаем, что символы верхнего и нижнего регистра должны быть равны, то есть А == а.
Два правила сравнения определены выше, и совокупность этих правил сравнения является сопоставлением.
- Одни и те же прописные и строчные буквы сравниваются с их размерами кодировки;
- Два символа равны, если они чувствительны к регистру.
3. Наборы символов и последовательности символов, поддерживаемые MySQL
MySQL поддерживает несколько наборов символов и последовательностей символов.
- Набор символов соответствует как минимум одному порядку символов (обычно 1-ко-многим).
- Два разных набора символов не могут иметь одинаковый порядок символов.
- Каждый набор символов имеет порядок символов по умолчанию.
Вышесказанное относительно абстрактно, мы узнаем, что происходит, прочитав следующие несколько разделов.
1. Проверьте поддерживаемые наборы символов
Наборы символов, поддерживаемые MYSQL, можно просмотреть следующими способами.
метод первый:
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
...省略Способ второй:
mysql> use information_schema;
mysql> select * from CHARACTER_SETS;
+--------------------+----------------------+-----------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+-----------------------------+--------+
| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |
| dec8 | dec8_swedish_ci | DEC West European | 1 |
...省略когда используешьSHOW CHARACTER SETПри просмотре вы также можете добавитьWHEREилиLIKEКвалификация.
Пример 1: ИспользованиеWHEREКвалификация.
mysql> SHOW CHARACTER SET WHERE Charset="utf8";
+---------+---------------+-------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)Пример 2: ИспользованиеLIKEКвалификация.
mysql> SHOW CHARACTER SET LIKE "utf8%";
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)2. Проверьте поддерживаемые последовательности символов
Точно так же вы можете просмотреть последовательность символов, поддерживаемую MYSQL, следующим образом.
Способ 1: черезSHOW COLLATIONСмотреть.
можно увидеть,utf8Набор символов содержит более 10 последовательностей символов. пройти черезDefaultЯвляется ли значениеYes, чтобы определить, является ли порядок символов по умолчанию.
mysql> SHOW COLLATION WHERE Charset = 'utf8';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
...略Способ 2: запросinformation_schema.COLLATIONS.
mysql> USE information_schema;
mysql> SELECT * FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8";
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |3. Конвенция о приземлении характера
Имя последовательности символов, соответствующий ему набор символов в качестве префикса, как показано ниже. Например, последовательность символовutf8_general_ci, указывая, что это набор символовutf8порядок символов.
Дополнительные правила могут относиться кофициальная документация.
MariaDB [information_schema]> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8" limit 2;
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME |
+--------------------+-----------------+
| utf8 | utf8_general_ci |
| utf8 | utf8_bin |
+--------------------+-----------------+
2 rows in set (0.00 sec)В-четвертых, набор символов и последовательность символов сервера
Цель: Когда вы создаете базу данных и не указываете набор символов и порядок символов, набор символов сервера и порядок символов сервера будут использоваться в качестве набора символов по умолчанию и сопоставления базы данных.
Как указать: Когда служба MySQL запущена, ее можно указать через параметры командной строки. Его также можно указать через переменные в файле конфигурации.
Набор символов по умолчанию для сервера и порядок символов: Когда MySQL компилируется, это определяется параметрами компиляции.
character_set_server,collation_serverВ соответствии с набором символов сервера и порядком символов сервера, соответственно.
1. Просмотрите набор символов сервера и порядок символов
соответствуют соответственноcharacter_set_server,collation_serverдве системные переменные.
mysql> SHOW VARIABLES LIKE "character_set_server";
mysql> SHOW VARIABLES LIKE "collation_server";2. Указать при запуске службы
Вы можете указать набор символов сервера и последовательность символов при запуске службы MySQL. Если не указано, порядок символов по умолчаниюlatin1,latin1_swedish_ci
mysqld --character-set-server=latin1 \
--collation-server=latin1_swedish_ciУкажите набор символов сервера отдельно.В настоящее время последовательность символов сервераlatin1порядок символов по умолчаниюlatin1_swedish_ci.
mysqld --character-set-server=latin13. Спецификация файла конфигурации
Помимо указания его в параметрах командной строки, его также можно указать в файле конфигурации, как показано ниже.
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf84. Модификация во время выполнения
Пример: модификация во время выполнения (она будет недействительна после перезапуска, если вы хотите сохранить то же самое после перезапуска, вам нужно записать это в файл конфигурации)
mysql> SET character_set_server = utf8 ;5. Укажите набор символов по умолчанию и порядок символов во время компиляции.
character_set_server,collation_serverЗначение по умолчанию может быть указано в параметрах компиляции при компиляции MySQL:
cmake . -DDEFAULT_CHARSET=latin1 \
-DDEFAULT_COLLATION=latin1_german1_ciПять, набор символов базы данных, последовательность символов
Цель: Задать набор символов и последовательность символов на уровне базы данных. Базы данных в одной и той же службе MySQL могут указывать разные наборы символов/последовательности символов соответственно.
1. Установите набор символов/порядок символов данных
При создании и изменении базы данных черезCHARACTER SET,COLLATEЗадает набор символов и параметры сортировки базы данных.
Создайте базу данных:
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]Измените базу данных:
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]Пример: создать базу данныхtest_schema, установлен набор символовutf8, правило сортировки по умолчаниюutf8_general_ci.
CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;2. Просмотрите набор символов/порядок символов в базе данных
Существует 3 способа просмотра набора символов/порядка символов в базе данных.
Пример 1: Просмотрtest_schemaнабор символов и сопоставление. (необходимо переключить базу данных по умолчанию)
mysql> use test_schema;
Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8 | utf8_general_ci |
+--------------------------+----------------------+
1 row in set (0.00 sec)Пример 2: Вы также можете просмотреть его с помощью следующей командыtest_schemaнабор символов, база данных (нет необходимости переключать базу данных по умолчанию)
mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE schema_name="test_schema";
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| test_schema | utf8 | utf8_general_ci |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)Пример 3: Вы также можете просмотреть набор символов, просмотрев оператор, создающий базу данных.
mysql> SHOW CREATE DATABASE test_schema;
+-------------+----------------------------------------------------------------------+
| Database | Create Database |
+-------------+----------------------------------------------------------------------+
| test_schema | CREATE DATABASE `test_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)3. Как определить набор символов базы данных и порядок символов
- При создании базы данных указать
CHARACTER SETилиCOLLATE, соответствующий набор символов и правило сопоставления имеют преимущественную силу. - При создании базы данных, если не указан набор символов или сопоставление,
character_set_server,collation_serverпреобладать.
Шесть, набор символов таблицы, порядок символов
Синтаксис для создания и изменения таблиц следующий, доступ к которому можно получить черезCHARACTER SET,COLLATEУстановите набор символов и порядок символов.
CREATE TABLE tbl_name (column_list)
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]]
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]1. Создайте таблицу и укажите набор символов/порядок символов
Пример выглядит следующим образом, указанный набор символовutf8, порядок символов является значением по умолчанию.
CREATE TABLE `test_schema`.`test_table` (
`id` INT NOT NULL COMMENT '',
PRIMARY KEY (`id`) COMMENT '')
DEFAULT CHARACTER SET = utf8;2. Просмотрите набор символов/порядок символов в таблице.
Опять же, есть 3 способа просмотра набора символов/порядка символов в таблице.
Способ 1: черезSHOW TABLE STATUSПроверьте статус стола, обратите вниманиеCollationдляutf8_general_ci, соответствующий набор символовutf8.
MariaDB [blog]> SHOW TABLE STATUS FROM test_schema \G;
*************************** 1. row ***************************
Name: test_table
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 11534336
Auto_increment: NULL
Create_time: 2018-01-09 16:10:42
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)Способ 2: просмотрinformation_schema.TABLESИнформация.
mysql> USE test_schema;
mysql> SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table";
+-----------------+
| TABLE_COLLATION |
+-----------------+
| utf8_general_ci |
+-----------------+Способ 3: пройтиSHOW CREATE TABLEподтверждать.
mysql> SHOW CREATE TABLE test_table;
+------------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+----------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)3. Как определить набор символов таблицы и порядок символов
ПредположениеCHARACTER SET,COLLATEЗначенияcharset_name,collation_name. Если вы создаете таблицу:
- Прозрачный
charset_name,collation_name, затем используйтеcharset_name,collation_name. - только ясно
charset_name,ноcollation_nameЕсли не указано, набор символов принимаетcharset_name, порядок символовcharset_nameСоответствующий порядок символов по умолчанию. - только ясно
collation_name,ноcharset_nameЕсли не указано, последовательность символовcollation_name, набор символов принимаетcollation_nameСвязанный набор символов. -
charset_name,collation_nameЕсли это неясно, используется набор символов и последовательность символов базы данных.
Семь, набор символов столбца, сортировка
Для столбцов типа CHAR, VARCHAR и TEXT можно указать набор символов/последовательность символов.Синтаксис следующий:
col_name {CHAR | VARCHAR | TEXT} (col_length)
[CHARACTER SET charset_name]
[COLLATE collation_name]1. Добавьте столбец и укажите набор символов/сопоставление
Пример выглядит следующим образом: (аналогично созданию таблицы)
mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;2. Просмотрите набор символов/порядок символов в столбце.
Примеры следующие:
mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column";
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME |
+--------------------+-----------------+
| utf8 | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)3. Определите набор символов столбца/правила сопоставления
ПредположениеCHARACTER SET,COLLATEЗначенияcharset_name,collation_name:
- если
charset_name,collation_nameпонятны, набор символов и последовательность символовcharset_name,collation_nameпреобладать. - только ясно
charset_name,collation_nameЕсли не указано, набор символовcharset_name, последовательность символовcharset_nameпорядок символов по умолчанию. - только ясно
collation_name,charset_nameЕсли не указано, последовательность символовcollation_name, набор символовcollation_nameСвязанный набор символов. -
charset_name,collation_nameЕсли это неясно, приоритет имеют набор символов и последовательность символов таблицы.
Восемь, выбор: когда устанавливать набор символов, порядок символов
Вообще говоря, настройку можно выполнить в трех местах:
- Настройте его при создании базы данных.
- Настройте при запуске сервера mysql.
- При компиляции mysql из исходного кода настройте его через параметры компиляции
1. Способ 1: Настройка при создании базы данных
Этот метод является более гибким и безопасным, и он не зависит от набора символов/порядка символов по умолчанию. Когда вы создаете базу данных, укажите набор символов/порядок символов.Если вы позже создадите таблицу и столбец, если вы не укажете его, набор символов/порядок символов соответствующей базы данных будет унаследован.
CREATE DATABASE mydb
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;2. Способ 2: настроить запуск сервера mysql
Вы можете добавить следующую конфигурацию, чтобы при запуске сервера mysql он настраивал сервер набора символов и сервер сопоставления.
Когда вы создаете базу данных/таблицу/столбец через клиент mysql, и набор символов/порядок символов не объявлен явно, тогда в качестве набора символов/порядка символов по умолчанию будет использоваться сервер набора символов/сервер сортировки.
Кроме того, набор символов/последовательность символов для подключения клиента и сервера по-прежнему необходимо устанавливать через SET NAMES.
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci3. Способ 3: При компиляции mysql из исходников задайте его через параметры компиляции
При компиляции, если указано-DDEFAULT_CHARSETа также-DDEFAULT_COLLATION,Так:
- При создании базы данных и таблицы он будет использоваться как набор символов/последовательность символов по умолчанию.
- Когда клиент подключается к серверу, он будет использовать его как набор символов/последовательность символов по умолчанию. (без отдельных НАЗВАНИЙ)
shell> cmake . -DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci9. Напишите это сзади
Эта статья подробно знакомит с содержанием, связанным с набором символов и порядком символов в MySQL.Эта часть содержания в основном направлена на хранение и сравнение данных. На самом деле остается еще очень важная часть контента, которая не была раскрыта: набор символов и настройки последовательности символов для соединения.
Есть также много искаженных проблем, вызванных неправильной настройкой подключенного набора символов и порядка символов.Эта часть контента также является большим количеством контента, который будет объяснен в следующей статье.
Из-за нехватки места некоторый контент не детализирован. Заинтересованные студенты могут общаться или просматривать официальные документы. Если есть какие-либо ошибки или неточности, просьба указать на них.
10. Ссылки по теме
10.1 Character Set Support
dev.mysql.com/doc/ref...