MySQL Функции: строковые, числовые, определяемые пользователем, сохраненные.

Каковы MySQL Функции?

MySQL может делать гораздо больше, чем просто хранить и извлекать данные, Он может также выполнять манипуляции с данными перед тем, как его получить или сохранить. Именно там. MySQL Здесь на помощь приходят функции. Функции — это просто фрагменты кода, которые выполняют операцию, а затем возвращают результат. Некоторые функции принимают параметры, а другие — нет.

Давайте кратко рассмотрим пример. По умолчанию, MySQL Сохраняет данные даты в формате «ГГГГ-ММ-ДД». Предположим, мы разработали приложение, и наши пользователи хотят получать дату в формате «ДД-ММ-ГГГГ». Мы можем использовать... MySQL Для этого используется встроенная функция DATE_FORMAT. DATE_FORMAT — одна из наиболее часто используемых функций в... MySQL, и мы подробно рассмотрим это позже на этом уроке.

Независимо от своего типа, функция всегда возвращает одно значение, может принимать ноль или более параметров внутри скобок, и может использоваться везде, где разрешено использование выражений. — в списке SELECT, предложении WHERE или предложении ORDER BY.

Зачем использовать MySQL Функции?

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

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

Программисты могут подумать: «Зачем вообще этим заниматься?» MySQL Функции? Того же эффекта можно добиться с помощью скриптового или программного языка. Действительно, этого можно достичь, написав процедуру в прикладной программе.

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

Это становится проблемой, когда приложению приходится интегрироваться с другими системами. Когда мы используем MySQL Функции, такие как DATE_FORMAT, встроены в базу данных, и любое приложение, которому нужны данные, получает их в требуемом формате. Сокращает объем доработок бизнес-логики и уменьшает несоответствия данных..

Еще одна причина задуматься MySQL Их функция заключается в том, что они могут помочь снизить сетевой трафик в клиент-серверных приложениях.Бизнес-уровню достаточно вызвать хранимую функцию, без необходимости извлекать необработанные строки из сети для их обработки. В среднем, использование функций может значительно повысить общую производительность системы.

Виды MySQL функции

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

Встроенные функции

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

• Строковые функции – работать со строковыми типами данных
• Числовые функции – работать с числовыми типами данных
• Функции даты – работать с типами данных даты
• Агрегатные функции – работать со всеми вышеперечисленными типами данных и создавать обобщенные наборы результатов.
• Другие функции – MySQL Также поддерживаются и другие типы встроенных функций, но в этом уроке мы ограничимся группами, перечисленными выше.

Теперь давайте подробно рассмотрим каждую из упомянутых выше групп. Мы объясним наиболее часто используемые функции, используя нашу тестовую базу данных «Myflixdb».

Строковые функции

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

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;

ВОТ

• UCASE(`title`) Это встроенная функция, которая принимает заголовок в качестве параметра и возвращает его в верхнем регистре.
• AS `upper_case_title` присваивает вычисляемому столбцу псевдоним, поэтому результирующий набор содержит читаемый заголовок вместо исходного выражения.

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

Наряду с UCASE, стоит помнить и о двух других организациях: LCASE преобразует строку в нижний регистр, и CONCAT Объединяет две или более строк в одну. Полный список см. в разделе... MySQL справочник строковых функций.

Числовые функции

Как уже упоминалось, числовые функции работают с числовыми типами данных. Мы также можем выполнять математические вычисления над числовыми данными непосредственно в наших SQL-запросах.

Арифметические операторы

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

Ниже приведены примеры каждого оператора.

Целочисленное деление (DIV) — Функция DIV отбрасывает дробную часть и возвращает только целое число.

SELECT 23 DIV 6;

Выполнение приведенного выше скрипта дает нам следующий результат: 3.

Оператор отдела (/) — В отличие от DIV, оператор деления сохраняет десятичную часть результата.

SELECT 23 / 6;

Выполнение приведенного выше скрипта дает нам следующий результат: 3.8333.

нижеtracоператор (-)

SELECT 23 - 6;

Выполнение приведенного выше скрипта дает нам следующий результат: 17.

Оператор сложения (+)

SELECT 23 + 6;

Выполнение приведенного выше скрипта дает нам следующий результат: 29.

Оператор умножения (*)

SELECT 23 * 6 AS `multiplication_result`;

Результат:

Оператор деления по модулю (% или MOD)

Оператор деления по модулю делит N на M и дает нам остаток. Рассмотрим пример с оператором деления по модулю, используя те же значения, что и в предыдущих примерах.

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

Выполнение любого из этих скриптов дает нам 5.

Давайте теперь посмотрим на некоторые распространенные числовые функции в MySQL.

ПОЛ – Эта функция удаляет десятичные знаки из числа и округляет его до ближайшего целого числа в меньшую сторону. Приведенный ниже скрипт демонстрирует ее использование.

SELECT FLOOR(23 / 6) AS `floor_result`;

Результат:

КРУГЛЫЙ – Эта функция округляет число до ближайшего целого числа. Поскольку 23 / 6 дает 3.8333, функция ROUND возвращает 4, а функция FLOOR возвращает 3 — эти две функции не взаимозаменяемы.

SELECT ROUND(23 / 6) AS `round_result`;

Результат:

RAND – Эта функция генерирует случайное число. Его значение меняется при каждом вызове функции. Приведенный ниже скрипт демонстрирует ее использование.

SELECT RAND() AS `random_result`;

Функции даты

Функции работы с датами работают с типами данных «дата» и «дата и время». Функция DATE_FORMAT решает проблему «YYYY-MM-DD против DD-MM-YYYY», описанную во введении.

ФОРМАТ ДАТЫ Принимает два параметра: значение даты для форматирования и строку формата, сформированную из заполнителей. Приведенный ниже скрипт возвращает каждую дату выпуска в формате день-месяц-год, запрошенном нашими пользователями.

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

Ниже перечислены наиболее часто используемые заполнители формата.

В повседневной работе постоянно встречаются еще три функции работы с датами:

• CURDATE () Возвращает текущую дату в формате ГГГГ-ММ-ДД.
• ТЕПЕРЬ() возвращает текущую дату и времени.
• DATEDIFF(d1, d2) Возвращает количество дней между двумя датами — это основа любого отчета о просроченной арендной плате.

Полный список смотрите в MySQL Справочник по функциям даты и времени.

Сохраненные функции

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

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

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

ВОТ

• “СОЗДАТЬ ФУНКЦИЮ sf_name ([параметр(ы)])” является обязательным и сообщает MySQL Сервер должен создать функцию с именем `sf_name`, у которой внутри скобок определены необязательные параметры.
• “ВОЗВРАЩАЕТ data_type” Этот параметр является обязательным и указывает тип данных, которые возвращает функция.
• «ДЕТЕРМИНИСТИЧЕСКИЙ» Объявляет, что функция возвращает одно и то же значение при передаче одних и тех же аргументов. «НЕ ДЕТЕРМИНИСТИЧЕСКИЙ» утверждает обратное.
• «НАЧАЛО… КОНЕЦ» Оборачивает процедурный код, который выполняет функция.

Предположим, мы хотим узнать, какие из арендованных фильмов просрочены. Мы можем создать хранимую функцию, которая принимает дату возврата в качестве параметра и сравнивает её с текущей датой на сервере. Если текущая дата больше даты возврата, фильм просрочен, и мы возвращаем «Да»; в противном случае мы возвращаем «Нет».

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT DETERMINISTIC
BEGIN
    DECLARE sf_value VARCHAR(3);
    IF CURDATE() > return_date THEN
        SET sf_value = 'Yes';
    ELSEIF CURDATE() <= return_date THEN
        SET sf_value = 'No';
    END IF;
    RETURN sf_value;
END|
DELIMITER ;

⚠️ Внимание — не присваивайте этой функции имя DETERMINISTIC. В теле функции вызывается CURDATE(), поэтому один и тот же аргумент может вернуть «Нет» сегодня и «Да» завтра. Объявление зависящей от времени функции DETERMINISTIC вводит в заблуждение оптимизатор и небезопасно для репликации на основе операторов. Используйте НЕ ДЕТЕРМИНИСТИЧЕСКИЙ всякий раз, когда в теле функции вызываются CURDATE(), NOW() или RAND().

Выполнение приведенного выше скрипта создает сохраненную функцию `sf_past_movie_return_date`. Давайте теперь протестируем ее.

SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(),
       sf_past_movie_return_date(`return_date`) AS `is_overdue`
FROM `movierentals`;

Выполнение приведенного выше сценария в MySQL При использовании Workbench для работы с базой данных myflixdb мы получаем следующие результаты.

Обратите внимание на две строки со значением NULL. Когда `return_date` равно NULL, оба сравнения дают NULL, а не TRUE или FALSE, поэтому ни одна из ветвей IF не выполняется, и функция возвращает NULL — ожидаемый результат, поскольку у невозвращенного фильма нет даты возврата для сравнения.

Пользовательские функции

Когда одного SQL недостаточно для обеспечения высокой скорости, MySQL Это позволяет использовать третий вариант. Пользовательские функции (UDF) пишутся на компилируемом языке, таком как... C or C++Они встраиваются в разделяемую библиотеку и регистрируются на сервере. После добавления они вызываются так же, как и любые другие функции. Поскольку пользовательская функция (UDF) выполняется как нативный код внутри процесса сервера, она подходит для ресурсоемких вычислений, но ошибка в ней может привести к сбою сервера, поэтому пользовательские функции используются гораздо реже, чем хранимые функции.

Встроенные, сохраненные или определяемые пользователем функции: что следует использовать?

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

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

Часто задаваемые вопросы (FAQ)