Глава 7. Генерация, обработка и преобразование данных

Работа со строковыми данными¶

При работе со строковыми данными используется один из символьных типов данных: char, varchar, text (tinytext, text, mediumtext, longtext).

Генерация строк¶

Самый простой способ заполнить символьный столбец – заключить строку в кавычки.

Попытаемся заменить столбец vchar_fld строкой в 38 символов:

Получили исключение; данные остались не тронутыми:

Проверим в каком режиме работаем:

Если предпочтем механизм усечения строк вместо генерации исключений нужно выбрать режим ANSI:

Вызвав инструкцию UPDATE повторно, обнаружим что сервер выполнит ее без ошибок и обновит столбец:

Выполнив выборку столбца vchar_fld, увидим что строка действительно была усечена:

Чтобы вернуть настройки обратно в безопасное состояние с включенным строгим контролем данных, сбрасываем их в значение по умолчанию:

Включение одинарных кавычек¶

Поскольку строки указываются одинарными кавычками, следует обратить особое внимание на строки, содержащие внутри одинарные кавычки или апострофы.

Чтобы серевер игнорировал апостроф, необходимо добавить перед ним управляющий символ ' или символ обратной косой черты \

Однако, если извлекаете строку для добавления в файл (который будет читать другая программа), то может потребоваться включение управляющего символа как части извлеченной строки.

При получении данных для экспорта целесообразно использовать функцию quote() для всех символьных столбцов не генерируемых системой – которые пользователи заполняют вручную на обычном языке (комментарии, описания, адреса, отзывы). В них может встретиться любой неожиданный символ.

Включение специальных символов¶

Если приложение является многонациональным, в нем, вероятно, будут встречаться строки, содержащие символы, которых нет на клавиатуре и может потребоваться включить такие символы, например, с диакритическими знаками é или ö.

• CHAR(... USING binary) заставляет MySQL сгенерировать чистые, неиспорченные байты

• CONVERT(... USING cp850) принудительно расшифровывает их по старой западной DOS-таблице автора книги, превращая в правильные буквы Çüéâäàåçêë.

• Драйвер Python видит этот готовый текст и выводит его в Jupyter идеально чисто, без маркера b и без кракозябр.

Используем функцию concat() для соединения отдельных строк, одни из которых просто введем с клавиатуры, а другие сгенерируем с помощью char():

Используя функции char(), ascii() и concat() можно справиться с любой латиницей, даже если под рукой клавиатура без диакритических знаков и специальных символов.

Манипуляции строками¶

Каждый сервер данных включает множество встроенных функций для манипуляции строками.

Прежде чем продолжить, сбросим и обновим данные в string_tbl:

Строковые функции, возвращающие числовые значения¶

Увидим одни и те же результаты для всех строковых функций независимо от типа стобца, в котором хранятся строки.

Если подстрока не может быть найдена, функция position () возвращает значение 0.

Нестандартная функция locate() подобна функции position() но допускает необязательный третий параметр, используемый для указания начальной позиции поиска:

Сначала посмотрим порядок сортировки пяти строк используя запрос, а затем как строки сравниваются одна с другой, используя strcmp().

Наряду с функцией strcmp() MySQL позволят использовать для сравнения строк в предложении select операторы like и regexp.

Такие сравнения будут давать значения 1 (истина) или 0 (ложь).

Строковые функции, возвращающие строки¶

В некоторых случаях требуется изменять существующие строки, извлекая часть строки или добавляя к ней дополнительный текст.

В том числе когда нужно добавить к хранимой строке дополнительные символы:

Еще одно распространенное использование функции concat() – построение строки из отдельных фрагментов данных.

Функция concat() может обрабатывать любое выражение, возвращающее строку, и даже преобразовывать числа и даты в строковый формат, о чем свидетельствует столбец даты (create_date), использованный в качестве аргумента.

Хотя функция concat() полезна для добавления символов в начало или конец строки, может потребоваться добавить или заменить символы в середине строки.

Если третий аргумент больше нуля, то он указывает количество символов, которые заменяются вставляемой строкой:

Помимо вставки символов в строку, может потребоваться извлечение подстроки из строки.

Работа с числовыми данными¶

В отличие от строковых и временных данных, генерация числовых данных довольно проста. Можете ввести число, получить его из другого столбца или сгенерировать его путем вычислений. При выполнении вычислений доступны все обычные арифметические операторы, а для изменения приоритетов вычислений можно использовать скобки.

Основная проблема при хранении числовых данных заключается в том, что числа могут быть округлены, если они больше размера, указанного для числового столбца. Например, число 9,96 будет округлено до 10,0 если оно сохранено в столбце определенном как float(3,1).

Выполнение математических функций¶

Управление точностью чисел¶

При работе с числами с плавающей точкой не всегда требуется работа или отображение числа с полной точностью. Например, можно хранить данежные данные транзакции с точностью до шести знаков после запятой, но для их отображения понадобится округление до ближайшей сотой.

В большинстве случаев требуется сохранить хотя бы некоторую часть десятичных знаков после запятой, а не округлять значение до целого числа.

Для этого функция round() допускает необязательный второй аргумент, который указывает какое количество цифр справа от запятой следует оставить при округлении.

Работа со знаковыми данными¶

Если работаете с числовыми столбцами, в которых допускаются отрицательные значения, могут быть полезны некоторые специализированные числовые функции.

Допустим вас просят создать отчет, показывающий текущее состояние набора банковских счетов, используя данные из некой таблицы account:

| account_id | acct_type    | balance |
| ---------- | ------------ | ------- |
| 123        | MONEY MARKET | 785.22  |
| 456        | SAVINGS      | 0.00    |
| 789        | CHECKING     | -324.22 |

Представленный далее запрос возвращает три столбца, полезных при создании отчета:

SELECT account_id, SIGN(balance), ABS(balance)
FROM account;

| account_id | SIGN(balance) | ABS(balance) |
| ---------- | ------------- | ------------ |
| 123        | 1             | 785.22       |
| 456        | 0             | 0.00         |
| 789        | -1            | 324.22       |

Во втором столбце функция sign() возвращает значение -1, если баланс счета отрицательный, 0 если баланс нулевой и 1 если баланс положительный. Третий столбец получает абсолютное значение баланса счета с помощью функции abs().

Работа с временными данными¶

Из трех типов данных, обсуждаемых в этой главе (символьные, числовые и временные), когда дело доходит до создания и обработки данных, временные данные являются наиболее сложными.

Некоторая сложность вызвана множеством способов, которыми можно описать единственную дату и время. Большая часть сложности связана с системой отсчета.

Часовые пояса¶

MySQL хранит два разных часовых пояса: глобальный часовой пояс и часовой пояс сеанса, которые могут быть разными для каждого пользователя вошедшего в базу данных.

Значение system говорит о том, что сервер использует настройку часового пояса, в котором находится база данных.

Если вы сидите за компьютером в Санкт-Петербурге (Россия) и открываете сеанс по сети на сервере MySQL расположенном в Нью-Йорке, то можете изменить настройку часового пояса для своего сеанса с помощью команды:

Чтобы сеанс снова синхронизировался с системным временем самого сервера (а в качестве сервера мы используем наш собственный локальный компьютер):

Генерация временных данных¶

Генерировать временные данные можно любым из способов:

• копирование данных из существующего столбца date, datetime или time;

• выполнение встроенной функции, которая возвращает date, datetime или time;

• построение строкового представления временных данных для вычисления сервером.

Чтобы использовать последний метод, необходимо знать о различных компонентах, используемых при формировании дат.

Строковые представления временных данных¶

Пример инструкции, используемой для изменения даты возврата взятого напрокат фильма:

UPDATE rental
SET return_date = '2019-09-17 15:30:00'
WHERE rental_id = 99999;

Сервер определяет, что строка, указанная в предложении set должна быть значением datetime так как строка используется для заполнения столбца datetime. Поэтому сервер попытается преобразовать строку, разделяя ее на шесть компонентов (год, месяц, день, час, минута, секунда), включаемых в формат datetime по умолчанию.

Преобразование строки в дату¶

Если сервер не ожидает значения datetime или если вы хотите представить datetime в формате, отличном от формата по умолчанию, нужно указать серверу на необходимость преобразования строки в дату и время.

Пример простого запроса, который возвращает значение datetime c помощью функции cast():

Та же логика применяется и к типам date и time:

Когда строки преобразуются во временные значения (явно или неявно) вы должны предоставить все компоненты даты в необходимом порядке.

Функции генерации дат¶

Если нужно сгенерировать временные данные из строки, а строка находится в неверном виде для использования функции cast(), можно использовать встроенную функцию, которая принимает вместе со строкой даты строку формата.

Пусть, например, вы извлекаете из файла строку ‘September 17, 2019’ и вам нужно использовать ее для обновления столбца date. Поскольку строка не соответствует требуемому формату YYYY-MM-DD, можем использовать str_to_date() вместо того, чтобы переформатировать строку для использования функции cast():

UPDATE rental
SET return_date = STR_TO_DATE('September 17, 2019', '%M', '%d', '%Y')
WHERE rental_id = 99999;

Второй аргумент в вызове str_to_date() определяет формат строки даты. В данном случае строка содержит название месяца %M, числовое значение дня %d и четырехзначное числовое знаение года %Y.

Функция str_to_date() возвращает значение datetime, date или time в зависимости от содержимого строки формата. Например, если строка формата включает только %H, %i и %s, будет возвращено значение time.

Если пытаетесь сгенерировать текущую дату/время, то не нужно создавать строку, потому что имеются встроенные функции, которые обращаются к системным часам и возвращают текущую дату и/или время в виде строки:

Значения, возвращаемые этими функциями, имеют формат по умолчанию для возвращаемого временного типа.

Манипуляции временными данными¶

В этом разделе исследуются встроенные функции, которые принимают аргументы даты и возвращают даты, строки или числа.

Функции, возвращающие даты¶

Многие встроенные временные функции принимают в качестве аргумента одну дату и возвращают другую.

Пример, как добавить к текущей дате пять дней:

Второй аргумент состоит из трех элементов: ключевого слова interval, желаемого количества и типа интервала.

Например, вам сказали, что фильм вернули на 3 часа 27 минут 11 секунд позже, чем было указано изначально. Чтобы исправить:

UPDATE rental
SET return_date = DATE_ADD(return_date, INTERVAL '3:27:11' HOUR_SECOND)
WHERE rental_id = 99999;

Или, например, в отделе кадров выяснил, что сотрудник с id 4789 в базе данных старше на 9 лет и 11 месяцев, чем на самом деле:

UPDATE employee
SET birth_date = DATE_ADD(birth_date, INTERVAL '9-11' YEAR_MONTH)
WHERE emp_id = 4789;

Например, клиент банка входит 17 сентября 2019 года в систему онлайн-банкинга и планирует перевод на конец месяца:

Независимо от того, указываете ли вы значение date или datetime, функция last_day() всегда возвращает date.

Функции, возвращающие строки¶

Большинство временных функций, возвращающих строковые значения, используются для извлечения части даты или времени.

Например, чтобы извлечь из значения datetime только часть года:

Функции, возвращающие числовые значения¶

Еще одна распространенная задача при работе с датами – определение количества интервалов (дней, недель, лет) между двумя датами.

Функция datediff() игнорирует время дня в своих аргументах:

Если поменять аргументы местами и сначала указать более раннюю дату, datediff() вернет отрицательное значение:

Количество недель или лет можем высчитать простым делением на 7 дней в неделю или 365 дней в году:

Если нужна разница не в днях, а в других единицах, вместо datediff() используют функцию timestampdiff(). Она умеет считать и недели, и года, и месяцы.

Функции преобразования¶

Ранее было показано как использовать функцию cast() для преобразования строки в значение datetime.

Чтобы использовать cast(), вы предоставляете значение или выражение, ключевое слово as и тип, в который хотите преобразовать это значение.

Вот пример преобразования строки в целое число:

При преобразовании строки в число функция cast() пытается преобразовать всю строку слева направо. Если в строке обнаруживается знак, которого не может быть в числе, преобразование останавливается без сообщения об ошибке:

Преобразуются первые три цифры строки, а остальные отбрасываются. При этом сервер MySQL CLI выдает предупреждение, чтобы вы знали, что не вся строка была преобразована:

+---------------------------------------+
| CAST('999ABC111' AS UNSIGNED INTEGER) |
+---------------------------------------+
|                                   999 |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

Если вы конвертируете строку в значение date, datetime или time, следует придерживаться форматов по умолчанию для каждого типа, поскольку предоставить функции cast() строку формата нельзя. cast() умеет переводить строку в дату только в том случае, если строка уже записана в эталонном формате ISO.

Если ваша строка даты представлена не в формате по умолчанию (т.е. YYYY-MM-DD HH:MI:SS для типа datetime), то нужно использовать другую рассмотренную ранее функцию str_to_date()

Упражнения¶

Упражнение 7.1¶

Напишите запрос, который возвращает символы строки ‘Please find the substring in this string’ с 17-го по 25-й.

Упражнение 7.2¶

Напишите запрос, который возвращает абсолютное значение и знак (-1, 0 или 1) числа -25,76823. Верните также число, округленное до ближайших двух знаков после запятой.

Упражнение 7.3¶

Напишите запрос, возвращающий для текущей даты только часть, соответствующую месяцу.

Если в будущем потребуется вывести не просто имя месяца, а, например, сокращенное название (Aug) или совместить его с годом (August 2026), лучшим выбором станет date_format().