Материал: Using_MySql,_MS_SQL_Server_and_Oracle(1)
Пример 50: определение и изменение кодировок
7.3.2. Пример 50: определение и изменение кодировок
Задача 7.3.2.a{525}: вывести всю информацию о текущих настройках СУБД относительно кодировок, используемых по умолчанию.
Задача 7.3.2.b{526}: изменить все настройки кодировок СУБД по умолчанию на UTF8.
Ожидаемый результат 7.3.2.a.
В результате выполнения запроса (запросов) отображаются текущие настройки СУБД относительно кодировок, используемых по умолчанию.
Ожидаемый результат 7.3.2.b.
В результате выполнения запроса (запросов) кодировки СУБД по умолчанию меняются на UTF8.
Решение 7.3.2.a{525}:
В MySQL информацию о кодировках можно получить следующим запросом (обратите внимание: символ _ экранирован символом \, т.к. _ обозначает «любой символ» — в данном случае это не критично, но всё равно следует писать правильно).
MySQL Решение 7.3.2.a
1SHOW GLOBAL VARIABLES
2WHERE `variable_name` LIKE 'character\_set%'
3OR `variable_name` LIKE 'collat%'
Результатом выполнения такого запроса будет таблица со следующими дан-
ными.
Variable_name |
Value |
character_set_client |
utf8 |
character_set_connection |
utf8 |
character_set_database |
utf8 |
character_set_filesystem |
Binary |
character_set_results |
utf8 |
character_set_server |
utf8 |
character_set_system |
utf8 |
character_sets_dir |
C:\Program Files\MySQL\MySQL Server 5.6\share\charsets\ |
collation_connection |
utf8_general_ci |
collation_database |
utf8_general_ci |
collation_server |
utf8_general_ci |
В MS SQL Server информацию о кодировках можно получить следующим запросом.
MS SQL Решение 7.3.2.a
1SELECT [collation_name]
2FROM [sys].[databases]
3WHERE [name] = 'master'
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 525/545
Пример 50: определение и изменение кодировок
Результатом выполнения такого запроса будет таблица со следующими дан-
ными.
collation_name
Cyrillic_General_CI_AS
MS SQL Server будет применять эту кодировку по умолчанию ко всем новым базам данных и их структурам, если в соответствующих запросах не буде указана иная кодировка.
В Oracle информацию о кодировках можно получить следующим запросом.
Oracle Решение 7.3.2.a
1-- Упрощённый вариант:
2SELECT value$
3 |
|
FROM |
sys.props$ |
4 |
|
WHERE |
name = 'NLS_CHARACTERSET'; |
5 |
|
|
|
|
|
|
|
6-- Расширенный вариант:
7SELECT parameter,
8value
9 FROM nls_database_parameters
10WHERE parameter = 'NLS_CHARACTERSET'
11OR parameter = 'NLS_NCHAR_CHARACTERSET';
Первый запрос вернёт следующую информацию.
VALUE$
AL32UTF8
Второй запрос вернёт следующую информацию.
PARAMETER |
|
VALUE |
|
|
|
|||
NLS_CHARACTERSET |
|
AL32UTF8 |
|
|
|
|||
NLS_NCHAR_CHARACTERSET |
|
AL16UTF16 |
|
|
|
|||
|
|
|
|
|
||||
Значение параметра |
NLS_CHARACTERSET |
отвечает за кодировку «обычных» |
||||||
данных (без приставки N, |
т.е. |
CHAR |
, |
VARCHAR2 |
и т.д.), а значение |
|||
|
|
|
|
|
|
|
|
|
NLS_NCHAR_CHARACTERSET — за кодировку «национальных данных» (т.е. NCHAR,
NVARCHAR2 и т.д.)
На этом решение данной задачи завершено.
Решение 7.3.2.b{525}.
Изменение кодировок на уровне всего сервера может привести к временному или постоянному искажению данных в имеющихся базах данных. Обязательно сделайте полную резервную копию перед проведением соответствующих экспериментов.
В MySQL изменить настройки кодировок СУБД по умолчанию можно или в конфигурационном файле my.ini (постоянные изменения), или запросами следующего вида (изменения действуют до перезапуска СУБД):
MySQL Решение 7.3.1.b
1-- Повторять для всех необходимых переменных:
2SET character_set_database = 'utf8';
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 526/545
Пример 50: определение и изменение кодировок
В MS SQL Server изменить настройки кодировок СУБД по умолчанию можно только достаточно сложным способом:
•остановить СУБД;
•выполнить из консоли следующую команду (её параметры могут отличаться в зависимости от версии СУБД):
MySQL Решение 7.3.1.b
1 sqlservr -m -T4022 -T3659 -s"СЕРВИС" -q"КОДИРОВКА"
•запустить СУБД.
Что касается самой кодировки, то «в чистом виде» UTF8 не поддерживается
вMS SQL Server, но хранения и обработки соответствующих данных можно добиться через использование других кодировок49.
И, наконец, в Oracle изменение кодировок по умолчанию возможно только с выполнением достаточно нетривиальной процедуры50 (описание которой явно выходит за рамки данной книги), а потому рекомендуется на стадии инсталляции СУБД указать все необходимые параметры по умолчанию, а также указывать кодировки при создании отдельных баз данных и таблиц.
Задание 7.3.2.TSK.A: провести во всех трёх СУБД эксперимент с изменением кодировки существующей базы данных, содержащей строки с кириллическими символами; проверить, как изменится отображение, поиск и упорядочение таких данных.
Задание 7.3.2.TSK.B: провести во всех трёх СУБД эксперимент с изменением кодировки соединения на отличную от кодировки базы данных, с которой предстоит работа; проверить, как изменится отображение, поиск и упорядочение строк, содержащих кириллические символы.
49https://msdn.microsoft.com/en-us/library/ms143726%28v=sql.110%29.aspx
50https://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#NLSPG011
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 527/545
Пример 51: вычисление дат
7.4. Полезные операции с данными
7.4.1. Пример 51: вычисление дат
Задача 7.4.1.a{528}: написать запрос, показывающий по каждой выдаче книг номер дня недели (1 — понедельник, 2 — вторник и т.д.) и порядковый номер соответствующего дня недели в месяце (например, первый четверг месяца, вторая среда месяца и т.д.)
Задача 7.4.1.b{532}: оформить решение задачи 7.4.1.а в виде функции, получающей на вход дату и возвращающей результат в виде одной строки (а не двух отдельных колонок).
Ожидаемый результат 7.4.1.a.
Для каждой даты выдачи книги требуемая по условию задачи информация представлена в столбцах «W» (порядковый номер соответствующего дня недели в месяце) и «D» (номер дня недели):
sb_id |
sb_start |
W |
D |
2 |
2011-01-12 |
2 |
3 |
3 |
2012-05-17 |
3 |
4 |
42 |
2012-06-11 |
2 |
1 |
57 |
2012-06-11 |
2 |
1 |
61 |
2014-08-03 |
1 |
7 |
62 |
2014-08-03 |
1 |
7 |
86 |
2014-08-03 |
1 |
7 |
91 |
2015-10-07 |
1 |
3 |
95 |
2015-10-07 |
1 |
3 |
99 |
2015-10-08 |
2 |
4 |
100 |
2011-01-12 |
2 |
3 |
Ожидаемый результат 7.4.1.b.
Поскольку само решение и является ожидаемым результатом, см. решение
7.4.1.b.
Решение 7.4.1.a{528}. (Также см. комментарии в решении 7.4.1.b{532}.)
Для начала отметим, что данная задача является расширенным вариантом классики вида «определить, является ли дата третьим вторником месяца» — здесь мы всего лишь определяем искомые параметры для каждой даты, а не проверяем их для некоей одной заданной даты.
Также следует пояснить, как производится расчёт. Рассмотрим календарь октября 2016-го года. Обратите внимание на следующее:
•вторая неделя месяца начинается с 3-го числа (понедельника) (к сожалению, интуитивно многие считают, что вторая неделя всегда начинается с 8-го числа любого месяца — это не так);
•несмотря на то, что понедельник 3-е октября относится ко второй неделе, он является первым понедельник месяца (в то время как суббота 8-е — уже второй субботой месяца, хоть и находится на той же второй неделе).
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 528/545
Пример 51: вычисление дат
1 |
2 |
3 |
4 |
5 |
6 |
7 |
Номер дня |
ПН |
ВТ |
СР |
ЧТ |
ПТ |
СБ |
ВС |
|
|
|
|
|
|
1 |
2 |
1-я неделя |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
2-я неделя |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
3-я неделя |
17 |
18 |
19 |
20 |
21 |
22 |
23 |
4-я неделя |
24 |
25 |
26 |
27 |
28 |
29 |
30 |
5-я неделя |
31 |
|
|
|
|
|
|
6-я неделя |
Учитывая только что рассмотренные примечания, напишем код. Для MySQL он выглядит следующим образом:
MySQL Решение 7.4.1.a
1SELECT `sb_id`,
2`sb_start`,
3CASE
4WHEN WEEKDAY(`sb_start` - INTERVAL DAY(`sb_start`)-1 DAY) <=
5 |
|
|
WEEKDAY(`sb_start`) |
||
6 |
|
THEN |
WEEK(`sb_start`, 5) - |
||
7 |
|
|
WEEK(`sb_start` |
- |
INTERVAL DAY(`sb_start`)-1 DAY, 5) + 1 |
8 |
|
ELSE |
WEEK(`sb_start`, 5) - |
||
9 |
|
|
WEEK(`sb_start` |
- |
INTERVAL DAY(`sb_start`)-1 DAY, 5) |
10END AS `W`,
11WEEKDAY(`sb_start`) + 1 AS `D`
12FROM `subscriptions`
Рассмотрение начнём со строк 8-9, в которых определяется номер недели месяца. Для получения этого результата мы выполняем следующие операции (рассмотрим на примере даты 2016.10.20).
|
|
|
Код |
Идея |
Результат |
||
|
WEEK(`sb_start`, 5) |
|
Получение для анализируемой |
42 |
|||
|
|
|
|
|
|
даты номера недели в году, на ко- |
|
|
|
|
|
|
|
торую она приходится. |
|
|
WEEK(`sb_start` - |
|
Получение номера недели в году |
39 |
|||
|
INTERVAL |
|
для первого числа месяца, на кото- |
|
|||
|
DAY(`sb_start`)-1 |
|
рый приходится анализируемая |
|
|||
|
DAY, 5) |
|
дата. Чтобы получить значение |
|
|||
|
|
|
|
|
|
первого числа месяца (2016.10.01) |
|
|
|
|
|
|
|
мы из анализируемой даты |
|
|
|
|
|
|
|
(2016.10.20) вычитаем значение, на |
|
|
|
|
|
|
|
1 меньшее, чем «число месяца» |
|
|
|
|
|
|
|
(20 - 19 = 1), анализируемой даты. |
|
|
WEEK(`sb_start`, 5) |
|
Итого вся операция. Да, значение 3 |
3 |
|||
|
- WEEK(`sb_start` - |
|
не является корректным с точки |
|
|||
|
INTERVAL |
|
зрения «номер недели месяца», но |
|
|||
|
DAY(`sb_start`)-1 |
|
оно корректно в контексте |
|
|||
|
DAY, 5) |
|
«2016.10.20 — это 3-й четверг ме- |
|
|||
|
|
|
|
|
|
сяца». |
|
Параметр «5» функции WEEK определяет форму возвращения результата (номер недели в году будет 0-53, началом недели считается понедельник, подробности см. в документации).
Остаётся рассмотреть строки 3-10, в которых применяется выражение CASE с двумя альтернативами. Условие в строках 4-5 сравнивает номера дней недели для анализируемой даты и первого числа месяца. Если номер дня первого числа
Работа с MySQL, MS SQL Server и Oracle в примерах © EPAM Systems, RD Dep, 2016–2018 Стр: 529/545