@ugin_root

Как получить значение по индексу из списка разделённого запятыми ("5,9,12")?

У меня есть такая таблица:

CREATE TABLE `category` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) UNSIGNED DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `parent` FOREIGN KEY (`parent_id`) REFERENCES `category` (`id`)
)


И есть рекурсивный запрос, для того что-бы получить древовидную структуру за один запрос:

WITH RECURSIVE category_path AS
(
  SELECT 
		c.parent_id, 
		c.id, 
		CONVERT(c.id, CHAR(1000)) AS ids
	FROM category AS c
	WHERE parent_id IS NULL
	
  UNION ALL
	
  SELECT 
		c.parent_id, 
		c.id, 
		CONCAT(p.ids, ',', c.id) AS ids
	FROM category AS c
		JOIN category_path AS p ON p.id = c.parent_id
)
SELECT 
	category_path.*
FROM category_path
ORDER BY category_path.ids


Результат такой (точками вырезанные строки):

parent	id	ids
null	1	1
1	190	1,190
....
1	2	1,2
2	103	1,2,103
....
2	3	1,2,3
...


Мне необходимо отсортировать этот результат по полю ids. В примере результата видно проблемы такой сортировки:
  • Она не сортирует каждый "узел" по порядку
  • Сортируются не числа а строки. Т.е. 190 будет выше чем 2


В MySQL есть функция FIND_IN_SET она ищет индекс указанного узла по его значению.
Меня интересует обратная функция. Т.е. извлечение значение по его индексу. Есть ли простой способ это сделать?
Пока что я нашёл 2 варианта это сделать.

Первый вариант такой:

SUBSTRING_INDEX(SUBSTRING_INDEX(ids, ',', 1), ',', -1) + 0 ASC,
SUBSTRING_INDEX(SUBSTRING_INDEX(ids, ',', 2), ',', -1) + 0 ASC,
SUBSTRING_INDEX(SUBSTRING_INDEX(ids, ',', 3), ',', -1) + 0 ASC,
#...


У этого варианта есть проблема, например если для строки "1,2,103" попытаться получить значение по индексу 5 то он вернёт 103 и для любого индекса больше 3 будет возвращено значение последней колонки.

Я немного подумал и сделал вариант у которого нет этой проблемы:

REGEXP_REPLACE(category_path.ids, '^((([0-9]+,){0})([0-9]+)((,[0-9]+)*)|.*)$','\\4') + 0,
REGEXP_REPLACE(category_path.ids, '^((([0-9]+,){1})([0-9]+)((,[0-9]+)*)|.*)$','\\4') + 0,
REGEXP_REPLACE(category_path.ids, '^((([0-9]+,){2})([0-9]+)((,[0-9]+)*)|.*)$','\\4') + 0,
#...


Но это всё равно слишком сложное и не очевидное решение т.к. использует регулярки.
И у него тоже есть недостаток. Чтобы отсортировать длинные "ветви" нужно написать десятки полей в ORDER BY

Пока писал вопрос у меня появилась идея которая решила мою проблему с сортировкой другим способом.
Для колонки ids я дополнил id каждого узла нулями до 10 символов (у вас может быть другое, всё зависит от типа, для INT(10) UNSIGNED это 10). И увеличил максимальную длину строки для этого поля (CHAR(11000)), так оно будет корректно работать для деревьев с максимальной вложенностью до 1000.

Вот такой запрос получился:

WITH RECURSIVE category_path AS
(
  SELECT 
		c.parent_id, 
		c.id,
		CONVERT(LPAD(c.id, 11, '0'), CHAR(11000)) AS ids
	FROM category AS c
	WHERE parent_id IS NULL
	
  UNION ALL
	
  SELECT 
		c.parent_id, 
		c.id, 
		CONCAT(p.ids, ',', LPAD(c.id, 11, '0')) AS ids
	FROM category AS c
		JOIN category_path AS p ON p.id = c.parent_id
)
SELECT category_path.*
FROM category_path
ORDER BY category_path.ids;


Теперь результат правильно сортируется, не нужно прописывать сортировки для каждого узла по порядку в колонке ids. Единственное я не уверен на счёт производительности для больших таблиц. Сейчас у меня всего 963 записи в таблице category и запрос выполняется 100 миллисекунд.

Результат получается такой:

parent	id	ids
null	1	0000000001
1	2	0000000001,0000000002
2	3	0000000001,0000000002,0000000003
3	4	0000000001,0000000002,0000000003,0000000004
4	5	0000000001,0000000002,0000000003,0000000004,0000000005
2	103	0000000001,0000000002,0000000103
103	104	0000000001,0000000002,0000000103,0000000104
104	105	0000000001,0000000002,0000000103,0000000104,0000000105
104	427	0000000001,0000000002,0000000103,0000000104,0000000427
103	353	0000000001,0000000002,0000000103,0000000353
353	354	0000000001,0000000002,0000000103,0000000353,0000000354
103	653	0000000001,0000000002,0000000103,0000000653
2	219	0000000001,0000000002,0000000219
....


Если у кого-то появятся идеи как сделать запрос проще или сделать его более быстрым или вдруг есть что-то чего я не учёл, то прошу написать об этом.

Свою задачу с сортировкой я в принципе решил, но вопрос о максимально простом извлечении значения по индексу из списка значений разделённых запятыми остаётся открытым.
  • Вопрос задан
  • 75 просмотров
Пригласить эксперта
Ответы на вопрос 1
BasiC2k
@BasiC2k
.NET developer (open to job offers)
Возможно Вам стОит попробовать использовать IN в запросе. Например:
SELECT * FROM Table WHERE Field IN (5,9,12)
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы