MySQL Функції: рядкові, числові, визначені користувачем, збережені

Які MySQL Функції?

MySQL може робити набагато більше, ніж просто зберігати та отримувати дані. Це також може виконувати маніпуляції з даними перед тим, як отримати або зберегти його. Саме там MySQL І тут на допомогу приходять функції. Функції — це просто фрагменти коду, які виконують операцію, а потім повертають результат. Деякі функції приймають параметри, а інші — жодного.

Давайте коротко розглянемо приклад. За замовчуванням, MySQL зберігає типи даних дати у форматі «РРРР-ММ-ДД». Припустимо, що ми створили застосунок, і наші користувачі хочуть, щоб дата поверталася у форматі «ДД-ММ-РРРР». Ми можемо використовувати MySQL вбудована функція DATE_FORMAT для досягнення цього. DATE_FORMAT є однією з найчастіше використовуваних функцій у MySQL, і ми розглянемо це детальніше пізніше в цьому уроці.

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

Навіщо використовувати MySQL Функції?

Тепер, коли ми знаємо, що таке функція, наступне питання: чому ми взагалі повинні завантажувати цю роботу в базу даних.

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

Програмісти можуть думати: «Навіщо взагалі морочитися з MySQL Функції? Цього ж ефекту можна досягти за допомогою скриптової або програмної мови». Це правда, що ми можемо досягти цього, написавши процедуру в прикладній програмі.

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

Це стає проблемою, коли програму потрібно інтегрувати з іншими системами. Коли ми використовуємо MySQL такі функції, як DATE_FORMAT, ця функціональність вбудована в базу даних, і будь-яка програма, якій потрібні дані, отримує їх у потрібному форматі. Це зменшує переробку бізнес-логіки та зменшує невідповідності даних.

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

Види MySQL Функції

Визначившись з питаннями «що» та «чому», тепер ми можемо розглянути три сімейства функцій. MySQL пропонує: вбудовані функції, збережені функції та функції, визначені користувачем.

Вбудовані функції

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

• Рядкові функції – оперувати рядковими типами даних
• Числові функції – працювати з числовими типами даних
• Функції дати – оперувати типами даних дати
• Сукупні функції – працювати з усіма вищезазначеними типами даних і створювати підсумкові набори результатів.
• Інші функції - MySQL також підтримує інші типи вбудованих функцій, але ми обмежуємо цей урок групами, названими вище.

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

Рядкові функції

Рядкові функції працюють з текстовими значеннями. У нашій таблиці фільмів назви зберігаються з використанням комбінації малих та великих літер. Припустимо, нам потрібен запит, який повертає назви у верхньому регістрі. Функція «UCASE» приймає рядок як параметр і перетворює кожну літеру у верхню, як показано в сценарії нижче.

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

ТУТ

• UCASE(`назва`) — це вбудована функція, яка приймає заголовок як параметр і повертає його великими літерами.
• AS `заголовок_у_великому_регістрі` надає обчисленому стовпцю псевдонім, тому результуючий набір містить читабельний заголовок замість необробленого виразу.

Виконання наведеного вище сценарію в MySQL Порівняння Workbench з Myflixdb дає нам результати, показані нижче.

Варто пам'ятати двох супутників разом з UCASE: ЛКАСЕ перетворює рядок у нижній регістр та КОНКАТ об'єднує два або більше рядків в один. Повний список див. 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`;

Результат:

РАНД – ця функція генерує випадкове число. Його значення змінюється щоразу, коли викликається функція. Наведений нижче скрипт демонструє її використання.

SELECT RAND() AS `random_result`;

Функції дати

Функції дати працюють з типами даних дата та дата-час. DATE_FORMAT – це функція, яка вирішує проблему «РРРР-ММ-ДД проти ДД-ММ-РРРР», описану у вступі.

ФОРМАТ_ДАТИ приймає два параметри: значення дати для форматування та рядок форматування, побудований з заповнювачів. Наведений нижче скрипт повертає кожну дату випуску у форматі день-місяць-рік, який запитували наші користувачі.

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

Найчастіше використовувані заповнювачі формату наведено нижче.

Три інші функції дати постійно з'являються в повсякденній роботі:

• ЗВОРОЩЕННЯ() повертає поточну дату у форматі РРРР-ММ-ДД.
• NOW () повертає поточну дату та часу.
• ДАТА_ПОДІБНО(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` з необов'язковими параметрами, визначеними в дужках.
• «ПОВЕРТАЄ тип_даних» є обов'язковим і вказує тип даних, який повертає функція.
• «ДЕТЕРМІНІСТИЧНИЙ» оголошує, що функція повертає те саме значення щоразу, коли надаються ті самі аргументи. «НЕ ДЕТЕРМІНІСТИЧНИЙ» заявляє про протилежне.
• «ПОЧАТОК … КІНЕЦЬ» обгортає процедурний код, який виконує функція.

Припустимо, нам потрібно знати, які орендовані фільми вже повернуті. Ми можемо створити збережену функцію, яка приймає дату повернення як параметр і порівнює її з поточною датою на сервері. Якщо поточна дата більша за дату повернення, фільм вважається простроченим, і ми повертаємо «Так»; інакше ми повертаємо «Ні».

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 ;

⚠️ Увага — не позначайте цю функцію як ДЕТЕРМІНІСТИЧНУ. Тіло функції викликає 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 виконується як власний код всередині серверного процесу, вона підходить для важких обчислень, але помилка в одній з них може призвести до збою сервера, тому UDF використовуються набагато рідше, ніж збережені функції.

Вбудовані, збережені та користувацькі функції: які з них слід використовувати?

Усі три родини повертають одне значення та можуть бути викликані з будь-якого SQL-запиту, але вони відрізняються тим, хто їх пише, де вони виконуються та який рівень ризику вони несуть. У таблиці нижче підсумовано ці відмінності.

Як правило, починайте з вбудованої функції. Якщо жодна не підходить, напишіть збережену функцію, щоб правило зберігалося в одному місці. Звертайтеся до UDF лише тоді, коли збережена функція є помітно занадто повільною.

Поширені запитання