"Выдернуть" параметры из строкового поля таблицы

155

23 апреля 2022, 00:10

Имеется таблица

CREATE TABLE `table1` (
    ...
    `prm` TEXT NULL DEFAULT NULL,
    `size` TEXT NULL DEFAULT NULL,
    `gender` TEXT NULL DEFAULT NULL,
    `color` TEXT NULL DEFAULT NULL,
    ...
) ENGINE=InnoDB;

Поле prm содержит следующие текстовые данные:

    Размер:XL|Цвет:Белый|Пол:female
    Размер:M|Цвет:Черный|Пол:male
    Размер:L|Пол:female
    ...

Необходимо в текстовые поля size,gender,color занести соответствущие значения,"выдернутые" из поля prm. В итоге должно получиться так:

size|gender|color
XL|Белый|female
M|Черный|male
L|NULL|female

Тут стоит уточнить, что поле param не обязательно содержим все данные (например как в последней записи).

Как можно изящнее решить данную задачу? Пока на ум приходит только громоздкая конструкция UPDATE c кучей вложенных SUBSTRUNG и LOCATE с переменными @.

Answer 1

Имя параметра (оно же - префикс его значения) выглядит как Параметр:. Значение параметра завершается символом | или окончанием строки. Так что извлечь это значение, зная ограничивающие его подстроки - несложно:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(prm, 'Параметр:', -1), '|', 1) AS `ЗначениеПараметра` ...

Конкретно для показанных исходных данных:

UPDATE table1
SET size   = CASE WHEN LOCATE('Размер:', prm)
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(prm, 'Размер:', -1), '|', 1)
                  ELSE NULL
                  END,
    gender = CASE WHEN LOCATE('Пол:', prm)
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(prm, 'Пол:', -1), '|', 1)
                  ELSE NULL
                  END,
    color  = CASE WHEN LOCATE('Цвет:', prm)
                  THEN SUBSTRING_INDEX(SUBSTRING_INDEX(prm, 'Цвет:', -1), '|', 1)
                  ELSE NULL
                  END;
SELECT * FROM table1;

fiddle

Answer 2

Короче, реализовал с помощью хранимых процедур/функций. Насколько это эффективно и грамотно - на ваш суд:

CREATE FUNCTION `getPrm`(`prm` TEXT,`str` TEXT) RETURNS text CHARSET utf8
BEGIN
IF ((@temp:=LOCATE(prm,str))=0) THEN RETURN NULL; END IF;
SET @temp:=@temp+CHAR_LENGTH(prm);
SET @temp2:=IF((@temp2:=LOCATE('|',str,@temp))=0,CHAR_LENGTH(str)+1,@temp2) - @temp;
RETURN SUBSTRING(str,@temp,@temp2);
END
UPDATE table1 SET 
color=`getPrm`('Цвет:',prm),
size=`getPrm`('Размер:',prm),
gender=`getPrm`('Пол:',prm);

ПОСЛЕДНИЕ ВОПРОСЫ

Все вопросы

ТОП-10

Все популярные вопросы

Что такое лимит 1000 строк в БД

Я не могу понять, что такое лимит 1000 строк в БД это у меня будет в HeidiSql видно 1000 строк или как, или если количество пользователей перевалит...

174

MySQL