MySQL implode, explode

Опубликовано

Все мы знаем про замечательные функции PHP такие как implode ( объединяет элементы массива в строку заданным разделителем) и explode ( разбивает строку в массив по заданному разделителю).

Но иногда разработчикам приходится оперировать строками с разделителем (денормализованными данными) или же наоборот — получить какие-либо данные объединенные через разделитель в базе данных. Как оказалось сделать это в MySQL не так просто.После небольшого копания в документации обнаружилось, что для объединения записей в строку можно воспользоваться функций GROUP_CONCAT. По умолчанию разделителем является запятая (символ «,»). Но его можно сменить использую параметр SEPARATOR. Приведем небольшой пример.

Пусть у нас есть таблица со списком пользователей  — «users». Из неё необходимо выбрать все ID пользователей и собрать в строку с разделителем.

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user_name` VARCHAR(200) NULL DEFAULT NULL,
	PRIMARY KEY (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

И немного заполненных данных:

Теперь выполним следующий запрос для объединения данных в строку:

 SELECT GROUP_CONCAT(`id`) as string from `users`

В результате мы получим следующий набор данных:

Теперь объединим данные через символ «точка с запятой» («;») выполнив следующий запрос с использованием параметра SEPARATOR.

 SELECT GROUP_CONCAT(`id` SEPARATOR ';') as string from `users`

В результате выборка приняла вот такое значение:

Вот так с помощью функции GROUP_CONCAT можно объединять какие-либо данные из Базы Данных через разделитель. Кроме того, в функцию можно передавать несколько полей для их конкатенации (склеивания). Что бы избежать повторяющиеся данные, можно использовать параметр DISTINCT.

У этой функции есть ограничение на объем выводимых данных. По умолчанию 1024 символа для каждого объединения — для каждой выводимой строки. Если размер склееных данных больше, то он будет урезаться. Чтобы расширить размер нужно выполнить команду (только если у Вас есть необходимые привилегии на выполение)

SET group_concat_max_len = 4096; # Нужное значение

С объединением данных на этом все. А вот что бы разобрать строку через разделитель, с этим придется повозиться дольше. Нативной поддержки этого в MySQL я не нашёл. Единственное этому решение — написание хранимой процедуры для разбора строки.

Ниже представлен дамп хранимой процедуры.

DELIMITER //
CREATE PROCEDURE `explode`(IN `mylist` VARCHAR(255))
body:
BEGIN
  IF mylist = '' THEN LEAVE body; END IF;

  SET @saTail = mylist;

  WHILE @saTail != '' DO
    SET @sHead = SUBSTRING_INDEX(@saTail, ',', 1);
    SET @saTail = SUBSTRING( @saTail, LENGTH(@sHead) + 2 );
    ## Тут любой Ваш код
    ## Для примера добавление новых ID пользователей в таблицу users
    INSERT INTO users (id) VALUES (@sHead);
  END WHILE;

END//
DELIMITER ;

Процедура принимает всего 1 параметр ( для примера ). Это список значений через запятую. После этого проходит цикл по этому параметру и ищется разделитель (в нашем случае — это запятая). Каждое новое значение сохраняется в переменную @sHead и его уже можно использовать для SQL запросов.

Для вызова процедуры воспользуемся следующим кодом.

call explode('100000,1010000,1020000');

После чего новые ID пользователей появятся в таблице. Это достаточно простой пример. Чаще всего такие операции я использую для хранения денормализованных данных и последующей их нормализации (через тригеры).

Само собой поиск делается по нормализованным данным, а, если, необходимо просто вывести информацию — вывод денормализованных.

Данную процедуру Вы можете изменить и уже использовать для своих потребностей.

Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInShare on VK
  • Sergey Volvich

    отличный материал, спс! если нужен шаблон для веб-сайта http://www.jooom.ru

  • Здравствуйте.
    Спасибо.
    А у меня почему-то не работает ваша процедура из-за того, что mysql функция LENGTH() выдает длину вдвое большую, чем реальное число букв, которые в неё поступают как входной параметр. Не знаете почему так?

    • Вячеслав

      Используйте вместо CHAR_LENGTH вместо LENGTH. Первая считает длину строки в байтах, а вторая — в символах. У вас utf кодировка видимо, а там один символ — два байта. Отсюда и вдвое большая длина у LENGTH.

      • Вячеслав

        Т.е. наоборот
        CHAR_LENGTH — длина в символах (ее надо использовать)
        LENGTH — в байтах