Разделение и перестановка частей строки в MySQL, как explode()

Быстрый ответ

Если вам нужно в MySQL воспроизвести функциональность PHP-метода explode(), используйте функцию SUBSTRING_INDEX(). Она пригодится, когда вам требуется преобразовать строки вида 'a,b,c' в отдельные элементы:

# 'a, b, c' становятся отдельными строками 3...2...1...
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c', ',', numbers.n), ',', -1) as value
FROM (
  SELECT 1 n UNION SELECT 2 UNION SELECT 3
) numbers
WHERE numbers.n <= 1 + LENGTH('a,b,c') – LENGTH(REPLACE('a,b,c', ',', ''));

Таким образом мы получаем таблицу, включающую строки с значениями 'a', 'b' и 'c'. Здесь функция SUBSTRING_INDEX() применяется вместе с набором чисел, которые указывают позицию каждого элемента в списке.

Создание пользовательской функции SPLIT_STRING

Создание пользовательской функции в MySQL расширяет возможности обработки строк. Возьмем за основу функцию SPLIT_STRING, которая будет повторять функционал PHP-функции explode():

# Определение функции для разбиения строк
DELIMITER $$
CREATE FUNCTION SPLIT_STRING(str TEXT, delim VARCHAR(12), pos INT) RETURNS TEXT
BEGIN
  # Осуществляем разбиение строки с учетом поддержки UNICODE!
  RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(str, delim, pos), delim, -1);
END$$
DELIMITER ;

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

# Начинаем матч!
SELECT SPLIT_STRING(score, '-', 1) AS team1_score,
       SPLIT_STRING(score, '-', 2) AS team2_score
FROM game_results;

Создание упорядоченных разделенных строк с помощью хранимых процедур

Те, кто утверждает что AUTO_INCREMENT не работает во временных таблицах MySQL, скорее всего, недостаточно знакомы с хранимыми процедурами. С помощью методов REPLACE, CONCAT и временных таблиц возможно разделять строки и сохранять их порядок:

# Приступим к выполнению!
DELIMITER $$
CREATE PROCEDURE ExplodeString(source VARCHAR(255), delimiter VARCHAR(5))
BEGIN
  DECLARE id INT DEFAULT 0;
  DECLARE value VARCHAR(255);

  # Удаляем старые данные. Будьте внимательны, этот метод может быть ресурсоемким.
  DROP TABLE IF EXISTS temp_explode;
  CREATE TEMPORARY TABLE temp_explode (id INT AUTO_INCREMENT PRIMARY KEY, value VARCHAR(255));

  WHILE CHAR_LENGTH(source) > 0 DO
    SET value = SUBSTRING_INDEX(source, delimiter, 1);
    INSERT INTO temp_explode (value) VALUES (value);
    SET source = REPLACE(source, CONCAT(value, delimiter), '');
    SET id = id + 1;
  END WHILE;

  SELECT value FROM temp_explode ORDER BY id;
END$$
DELIMITER ;

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

# Запускаем процедуру!
CALL ExplodeString('score1-score2-score3', '-');

Перестановка элементов с помощью SUBSTRING_INDEX

При помощи функции SUBSTRING_INDEX вы сможете обращаться к элементам, даже если данные указаны в обратном порядке:

# Разделение... Теперь в обратном направлении...
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(field, delimiter, position), delimiter, -1) as part
FROM your_table;

Визуализация

Представим себе набор данных в виде цветовых строк, каждый из которых разделен запятыми:

"Красный,Зеленый,Синий,Желтый"

Допустим, нам требуется подсчитать каждый цвет отдельно с использованием SUBSTRING_INDEX():

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.n), ',', -1) as color
FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) as n
WHERE n.n <= 1 + (LENGTH(colors) – LENGTH(REPLACE(colors, ',', '')))

Теперь каждый цвет красиво отсортирован по отдельности:

🔴 (Красный), 🟢 (Зелёный), 🔵 (Синий), 🟡 (Жёлтый)

Использование строковых функций для сравнения данных

Сравнение численных значений в строках

В ходе работы со строками порой требуется сравнивать числовые значения. В таких случаях отлично подойдут функции CAST() и CONVERT():

# Гол! Однако, офсайд...
SELECT CAST(SPLIT_STRING(score, '-', 1) AS UNSIGNED) AS team1_score,
       CAST(SPLIT_STRING(score, '-', 2) AS UNSIGNED) AS team2_score
FROM game_results;

Подробнее об этом расскажет наш спикер на видео

Поиск подстроки с использованием функций CHAR_LENGTH и LOCATE

Если задача стоит в определении положения конкретных подстрок, на помощь придут функции CHAR_LENGTH() и LOCATE():

# В поисках Вальдо (разделителя).
SELECT LOCATE('-', field) FROM your_table;

Полезные материалы

1. MySQL :: MySQL 8.0 Reference Manual :: 12.8 String Functions and Operators — Полное руководство по всем функциям работы со строками в MySQL.
2. MYSQL – Как разбить значения на несколько строк в SQL – Stack Overflow — Обмен опытом в сообществе по разбивке значений в SQL запросах.
3. Can MySQL split a column? – Stack Overflow — Разбор подходов для имитации действия функции explode() в SQL на примерах.
4. SQL Server – Превращение строки с разделителями в отдельные строки – Stack Overflow — Обзор способов преобразования строк с разделителями в отдельные записи.
5. Regular Expressions in MySQL with the REGEXP Operator — Инструкции по использованию регулярных выражений в MySQL для сложных задач обработки строк.