Вопросы по теме «Функции SQL в MySQL: строковые, числовые и функции даты»

Всего: 7 вопросов

1. 

Что такое функции SQL и чем однострочные функции отличаются от агрегатных?

Функции SQL — встроенные операции СУБД, которые принимают аргументы (столбцы, константы, выражения) и возвращают значение. Однострочные (скалярные) функции вычисляются для каждой строки отдельно и возвращают по значению на строку; их пишут в SELECT, WHERE, ORDER BY, а также в INSERT и UPDATE. Агрегатные (COUNT, SUM) сворачивают много строк в одно значение и работают вместе с GROUP BY.

Основные группы однострочных функций MySQL:

  • строковые — CONCAT, SUBSTRING, CHAR_LENGTH, REPLACE, TRIM;
  • числовые — ROUND, TRUNCATE, CEIL, FLOOR, MOD;
  • даты и времени — NOW, DATE_FORMAT, DATE_ADD, DATEDIFF, TIMESTAMPDIFF.

Однострочными являются и условные выражения CASE/IF, и функции для NULL (COALESCE, IFNULL, NULLIF). Почти для всех функций действует правило: если аргумент NULL, результат тоже NULL — ROUND(price * 0.93, 1) для винограда без цены вернёт NULL, а CONCAT с любым аргументом NULL потеряет всю строку. Функции в SELECT меняют только результат запроса, а не данные в таблице; чтобы сохранить преобразованное значение, функцию ставят в UPDATE ... SET product = TRIM(product). Проверить функцию можно и без таблицы: SELECT UPPER('Java');.

2. 

Какие строковые функции есть в MySQL и в чём разница между CHAR_LENGTH и LENGTH?

Строковые функции MySQL меняют, режут, склеивают и ищут текст: CONCAT соединяет строки, SUBSTRING, LEFT и RIGHT вырезают часть, UPPER и LOWER меняют регистр, TRIM убирает пробелы, REPLACE заменяет подстроку, LPAD/RPAD дополняют до нужной длины, INSTR и LOCATE ищут позицию, STRCMP сравнивает строки.

CHAR_LENGTH считает символы, LENGTH — байты. Для латиницы числа совпадают, а в utf8mb4 кириллическая буква занимает 2 байта: CHAR_LENGTH('виноград') = 8, LENGTH('виноград') = 16. Для проверки длины ввода нужен CHAR_LENGTH: VARCHAR(50) ограничивает символы.

Что важно помнить:

  • позиции считаются с 1: INSTR('MySQL', 'SQL') = 3 (в Java indexOf вернёт 2), «не найдено» — 0, а не -1; SUBSTRING('MySQL', 0, 2) молча возвращает пустую строку;
  • у INSTR(str, substr) и LOCATE(substr, str) аргументы в обратном порядке, перепутанный порядок даёт 0 без ошибки;
  • отрицательная позиция в SUBSTRING отсчитывается с конца: SUBSTRING('MySQL', -3) = SQL;
  • LPAD не только дополняет, но и обрезает: LPAD('JavaScript', 4, '.') = Java;
  • STRCMP сравнивает по правилам сопоставления (collation), поэтому в MySQL 8 STRCMP('java', 'JAVA') = 0;
  • CONCAT с любым аргументом NULL возвращает NULL.
3. 

Чем ROUND отличается от TRUNCATE, FLOOR и CEIL в MySQL и как работают DIV и MOD?

  • ROUND(x, d) округляет к ближайшему: ROUND(92.55665, 2) = 92.56; отрицательное d округляет до десятков и сотен: ROUND(1234.5, -2) = 1200.
  • TRUNCATE(x, d) отбрасывает лишние знаки без округления: TRUNCATE(92.55665, 2) = 92.55. На цене со скидкой разница видна сразу: 45.00 * 0.93 = 41.8500, ROUND(..., 1) = 41.9, TRUNCATE(..., 1) = 41.8.
  • FLOOR и CEIL (CEILING) округляют до целого вниз и вверх: 92 и 93 для 92.55665.
  • x DIV y — целочисленное деление, MOD(x, y) или x % y — остаток со знаком делимого: MOD(-10, 3) = -1. Вместе с CEIL они решают задачу упаковки: для 34 кг и ящиков по 15 кг CEIL(34 / 15) = 3 ящика, 34 DIV 15 = 2 полных, MOD(34, 15) = 4 кг в остатке.

Отличия от Java: оператор / в MySQL всегда дробный (7 / 2 = 3.5000, а в Java между двумя int — 3). Для точных чисел (DECIMAL, литерал 2.5) MySQL округляет половину от нуля: ROUND(-2.5) = -3, тогда как Math.round(-2.5) вернёт -2. Для приближённых значений (DOUBLE, 2.5e0) результат зависит от библиотеки C и обычно округляется к чётному: ROUND(2.5e0) = 2. Если аргумент NULL, любая из этих функций вернёт NULL.

4. 

Как отформатировать дату в MySQL через DATE_FORMAT и разобрать строку в дату через STR_TO_DATE?

DATE_FORMAT(дата, шаблон) превращает дату в строку, STR_TO_DATE(строка, шаблон) — строку в дату. Шаблон состоит из спецификаторов со знаком %, остальные символы выводятся как есть: DATE_FORMAT(sold_at, '%d.%m.%Y %H:%i') → 31.12.2025 23:59.

  • Основные спецификаторы: %Y — год из 4 цифр, %m — месяц 01–12, %d — день, %H — часы 00–23, %i — минуты, %s — секунды, %M и %W — названия месяца и дня недели.
  • Минуты — %i, а не %M. Ошибки нет: '%Y-%m-%d %H:%M' вернёт 2025-12-31 23:December. Названия выводятся на языке из lc_time_names (по умолчанию en_US), после SET lc_time_names = 'ru_RU'; они станут русскими.
  • Шаблоны Java другие: '%d.%m.%Y %H:%i' соответствует DateTimeFormatter.ofPattern("dd.MM.yyyy HH:mm"); в Java MM — месяц, mm — минуты.
  • Формат '%Y-%m' удобен как ключ месяца для группировки и сортируется правильно даже как строка; '%m.%Y' поставил бы 01.2025 раньше 12.2024. Сортировать по отформатированной дате нельзя — ORDER BY sold_at, а формат только в SELECT.

Если строка не совпадает с шаблоном, STR_TO_DATE в SELECT возвращает NULL с предупреждением Incorrect datetime value: STR_TO_DATE('31/12/2025', '%d.%m.%Y') = NULL. В INSERT со строгим режимом та же строка вызовет ошибку, поэтому при импорте плохие даты отлавливают заранее запросом WHERE STR_TO_DATE(...) IS NULL.

5. 

Как прибавить к дате интервал и посчитать разницу между датами в MySQL: DATE_ADD, DATEDIFF и TIMESTAMPDIFF?

  • Текущий момент. NOW() возвращает DATETIME, CURDATE() — DATE, CURTIME() — TIME, все в часовом поясе сессии. NOW() фиксирует время начала команды, SYSDATE() — время фактического вызова.
  • Прибавить срок. DATE_ADD(дата, INTERVAL 7 DAY), DATE_SUB или короткая запись дата + INTERVAL 1 MONTH. Несуществующий день прижимается к концу месяца: 31 января + 1 месяц = 28 февраля (в високосном 2024 году — 29-е), как и LocalDate.plusMonths(1) в Java. Номер дня при этом сохраняется: 28 февраля + 1 месяц = 28 марта; для «конца следующего месяца» пишут LAST_DAY(дата + INTERVAL 1 MONTH).
  • Разница дат. DATEDIFF(конец, начало) считает календарные дни и игнорирует время. TIMESTAMPDIFF(единица, начало, конец) учитывает время и отбрасывает неполные единицы: от 23:59 31 декабря до 10 января DATEDIFF даст 10, TIMESTAMPDIFF(DAY) — 9. Порядок аргументов у них противоположный, перепутанный DATEDIFF молча возвращает отрицательное число.
  • Возраст и полные периоды. TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) считает полные годы; вариант DATEDIFF(...) / 365 ошибается около дня рождения из-за високосных лет. По той же причине TIMESTAMPDIFF(MONTH, '2025-01-31', '2025-02-28') = 0.

Для «сколько дней просрочки» обычно нужен DATEDIFF, для «прошло ли 24 часа» — TIMESTAMPDIFF(HOUR).

6. 

Почему функция над столбцом в WHERE мешает использовать индекс и как переписать условие WHERE YEAR(sold_at) = 2025?

Индекс хранит значения sold_at в отсортированном виде, а значений YEAR(sold_at) в нём нет. Поэтому для WHERE YEAR(sold_at) = 2025 MySQL не может использовать обычный индекс по sold_at и вычисляет функцию для каждой строки: в EXPLAIN possible_keys = NULL, type = ALL. Результат при этом правильный — страдает только скорость.

Условие переписывают так, чтобы столбец остался «голым», а вычисления ушли на сторону константы:

  • YEAR(sold_at) = 2025 → sold_at >= '2025-01-01' AND sold_at < '2026-01-01' (верхняя граница не включается, поэтому продажа 31 декабря в 23:59 не теряется);
  • DATE(sold_at) = '2025-01-31' → sold_at >= '2025-01-31' AND sold_at < '2025-02-01';
  • DATEDIFF(NOW(), sold_at) <= 30 → sold_at >= CURDATE() - INTERVAL 30 DAY;
  • LEFT(product, 3) = 'ябл' → product LIKE 'ябл%';
  • kg * 2 > 40 → kg > 20.

Если фильтр по выражению нужен часто, с MySQL 8.0.13 можно создать функциональный индекс: CREATE INDEX idx_sales_year ON sales ((YEAR(sold_at))); (двойные скобки обязательны). Он работает только для запросов с тем же выражением. Функции в списке SELECT на использование индексов не влияют.

7. 

Как передать период дат в SQL-запрос из Java через JDBC и прочитать дату как LocalDate?

Границы периода вычисляют в Java и передают параметрами, а в SQL оставляют условие-диапазон по голому столбцу — так работает индекс и не нужно склеивать дату в строку запроса:

String sql = "SELECT id, DATE(sold_at) AS sold_day FROM sales WHERE sold_at >= ? AND sold_at < ?";
LocalDate from = LocalDate.of(2025, 1, 1);
LocalDate to = from.plusMonths(1);              // 2025-02-01, граница не включается
ps.setObject(1, from.atStartOfDay());           // LocalDateTime для DATETIME
ps.setObject(2, to.atStartOfDay());
LocalDate day = rs.getObject("sold_day", LocalDate.class);
String text = day.format(DateTimeFormatter.ofPattern("dd.MM.yyyy"));   // 15.01.2025, 31.01.2025

С JDBC 4.2 (Connector/J 8) setObject и getObject(..., LocalDate.class) работают с типами java.time напрямую, без java.sql.Date. Форматировать дату для пользователя лучше в Java через DateTimeFormatter, а не через DATE_FORMAT: так учитывается нужная локаль, а в ResultSet приходят даты, а не строки.

Если время берётся из NOW(), помните, что MySQL возвращает его в часовом поясе сессии (time_zone), а LocalDateTime.now() — в поясе JVM: при сервере в UTC и приложении в Europe/Kyiv разница составит 2–3 часа. Решения — UTC и на сервере, и в JVM, свойство Connector/J connectionTimeZone или передача времени из Java параметром.

Страница 1 из 1