Вопросы по теме «Функции SQL в MySQL: строковые, числовые и функции даты»
Сначала изучите урок: Функции SQL в MySQL: строковые, числовые и функции даты
Всего: 7 вопросов
1. Что такое функции SQL и чем однострочные функции отличаются от агрегатных?
Что такое функции 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 и в чём разница между 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 (в JavaindexOfвернёт 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 8STRCMP('java', 'JAVA')= 0;CONCATс любым аргументомNULLвозвращаетNULL.
3. Чем ROUND отличается от TRUNCATE, FLOOR и CEIL в MySQL и как работают DIV и MOD?
Чем 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?
Как отформатировать дату в 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"); в JavaMM— месяц,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?
Как прибавить к дате интервал и посчитать разницу между датами в 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?
Почему функция над столбцом в 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?
Как передать период дат в 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 параметром.