Мой коллега спросил меня, как рекурсивно запрашивать MySQL, и я был ошеломлен.

MySQL

предисловие

Бизнес-сценарий, над которым я недавно работаю, включает рекурсивные запросы к базе данных. Oracle используется нашей компанией, как мы все знаем, Oracle имеет свою собственную функцию рекурсивных запросов, поэтому ее очень просто реализовать.

Однако я помню, что в MySQL нет функции рекурсивных запросов, так как же ее реализовать в MySQL?

Отсюда и эта статья.

Основные познавательные пункты статьи:

  • Рекурсивный запрос Oracle, начните с подключения по предыдущему использованию
  • функция find_in_set
  • функции concat, concat_ws, group_concat
  • Пользовательская функция MySQL
  • Вручную реализовать рекурсивный запрос MySQL

Рекурсивный запрос Oracle

В Oracle рекурсивные запросы реализуются через start с соединением по предыдущему синтаксису.

В зависимости от того, находится ли предшествующее ключевое слово в дочернем узле или в родительском узле, и содержит ли оно запрошенный в данный момент узел, существует четыре случая.

ранее на стороне дочернего узла (рекурсивно вниз)

Первый случай: начните с идентификатора дочернего узла = ' узел запроса ', соединитесь по предыдущему идентификатору дочернего узла = идентификатору родительского узла.

select * from dept start with id='1001' connet by prior id=pid;

Здесь текущий узел и его дочерние узлы опрашиваются рекурсивно в соответствии с условием id='1001'. результат поискасдерживать себяи все дочерние узлы.

Второй случай: начните с идентификатора родительского узла = 'узел запроса', соединитесь по предыдущему идентификатору дочернего узла = идентификатору родительского узла.

select * from dept start with pid='1001' connect by prior id=pid;

Здесь рекурсивно запрашиваем все дочерние узлы текущего узла в соответствии с условием pid='1001'. Результат запроса содержит только все его дочерние узлы,не содержит себя.

На самом деле, подумайте об этом, потому что начальное условие основано на родительском узле как корневом узле и повторяется вниз, естественно, не включая текущий узел.

до на стороне родительского узла (восходящая рекурсия)

Третий случай: начните с идентификатора дочернего узла = ' узел запроса ', соединитесь по предыдущему идентификатору родительского узла = идентификатор дочернего узла

select * from dept start with id='1001' connect by prior pid=id;

Здесь, согласно условию id='1001', рекурсивно запрашиваем текущий узел и его родительские узлы. результат поискавключая меняи все его родительские узлы.

Четвертый случай: начните с идентификатора родительского узла = 'узел запроса', соединитесь по предыдущему идентификатору родительского узла = идентификатору дочернего узла.

select * from dept start with pid='1001' connect by prior pid=id;

Здесь, согласно условию pid='1001', дочерний узел первого поколения текущего узла и его родительский узел опрашиваются рекурсивно. Результат запроса включает собственные дочерние узлы первого поколения и все родительские узлы. (включая меня)

На самом деле эту ситуацию легко понять, потому что условие начала запроса父节点Это корневой узел и рекурсивно вверх, естественно необходимо включать дочерние узлы первого уровня текущего родительского узла.

Вышеуказанные четыре ситуации могут показаться запутанными и легко запоминающимися на первый взгляд, но это не так.

мы просто должны помнитьПозиция Prior находится на стороне дочернего узла, поэтому он рекурсирует вниз, а на стороне родительского узла — вверх.

  • Если начальное условие является дочерним узлом, оно, естественно, включает свой собственный узел.
  • Если начальным условием является родительский узел, текущий узел, естественно, не включается при рекурсии вниз. Восходящая рекурсия должна включать текущий узел и его дочерние узлы первого поколения.

Рекурсивный запрос MySQL

Как видите, для Oracle очень удобно реализовывать рекурсивные запросы. Однако в MySQL это не обрабатывается за нас, поэтому нам нужно вручную реализовать рекурсивный запрос самостоятельно.

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

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept`  (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pid` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1000', '总公司', NULL);
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1001', '北京分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1002', '上海分公司', '1000');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1003', '北京研发部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1004', '北京财务部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1005', '北京市场部', '1001');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1006', '北京研发一部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1007', '北京研发二部', '1003');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1008', '北京研发一部一小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1009', '北京研发一部二小组', '1006');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1010', '北京研发二部一小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1011', '北京研发二部二小组', '1007');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1012', '北京市场一部', '1005');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1013', '上海研发部', '1002');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1014', '上海研发一部', '1013');
INSERT INTO `dept`(`id`, `name`, `pid`) VALUES ('1015', '上海研发二部', '1013');

Правильно, рекурсия Oracle только что использует эту таблицу.

图1

Кроме того, перед этим нам нужно просмотреть несколько функций в MYSQL, которые будут использоваться позже.

функция find_in_set

Синтаксис функции: find_in_set(str,strlist)

str представляет запрашиваемую строку, а strlist — это строка, разделенная запятыми, например ('a,b,c').

Эта функция используется для поиска позиции строки str в списке строк strlist и возвращает результат в виде 1 ~ n. Возвращает 0, если не найдено.

Возьмите каштан:

select FIND_IN_SET('b','a,b,c,d');

Результат возвращает 2 . Потому что позиция b является второй позицией подстроки.

Кроме того, при запросе данных таблицы он также используется следующим образом:

select * from dept where FIND_IN_SET(id,'1000,1001,1002');

Результат возвращает все записи с id в списке strlist, а именно id = '1000', id = '1001', id = '1002' три записи.

Увидев это, я не знаю, есть ли у вас вдохновение для рекурсивного запроса, который мы хотим решить.

В качестве примера возьмем рекурсивный нисходящий запрос для всех дочерних узлов. Я подумал, можно ли найти строку strlist, содержащую текущий узел и все дочерние узлы, объединенные запятыми, и передать его в функцию find_in_set. Вы можете запросить все необходимые вам рекурсивные данные.

Итак, теперь возникает вопрос, как создать такую ​​строку strlist.

Это требует использования следующей функции конкатенации строк.

функции concat, concat_ws, group_concat

1. Среди функций конкатенации строк самой простой является concat. Он используется для объединения N строк, например,

select CONCAT('M','Y','S','Q','L') from dual;

Результатом является строка «MYSQL».

Во-вторых, concat использует запятую в качестве разделителя по умолчанию, в то время как concat_ws может указывать разделитель.Первый параметр передается в разделителе, таком как подчеркивание.

3. Функция group_concat более мощная, она может объединять поля в строки с определенными разделителями при группировке.

Использование: group_concat ([различное] поле для конкатенации [порядок по полю сортировки по возрастанию/убыванию] [разделитель 'разделитель'] )

Вы можете видеть, что есть необязательные параметры, вы можете дедуплицировать значения полей для объединения, вы также можете сортировать и указывать разделитель. Если не указано, значения по умолчанию разделяются запятыми.

Для таблицы отделов мы можем соединить все идентификаторы в таблице запятыми. (поле group by grouping здесь не используется, можно считать, что группа всего одна)

Пользовательская функция MySQL для реализации рекурсивного запроса

Можно обнаружить, что вышеупомянутая проблема сращивания струн также была решена. Затем возникает проблема, как построить строку с рекурсивным отношением.

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

В качестве примера возьмем нисходящую рекурсию.(Объясняя, как писать собственные функции, объясните рекурсивную логику)

delimiter $$ 
drop function if exists get_child_list$$ 
create function get_child_list(in_id varchar(10)) returns varchar(1000) 
begin 
	declare ids varchar(1000) default ''; 
	declare tempids varchar(1000); 
 
	set tempids = in_id; 
	while tempids is not null do 
		set ids = CONCAT_WS(',',ids,tempids); 
		select GROUP_CONCAT(id) into tempids from dept where FIND_IN_SET(pid,tempids)>0;  
	end while; 
	return ids; 
end  
$$ 
delimiter ;

(1) разделитель $$ , используемый для определения терминатора. Мы знаем, что терминатор MySQL по умолчанию — это точка с запятой, указывающая, что команда завершается и выполняется. Но в теле функции иногда мы хотим встретить точку с запятой без окончания, поэтому нам нужно временно изменить терминатор на произвольное другое значение. Я установил здесь значение $$, что означает, что он завершится, когда встретится $$, и выполнит текущий оператор.

(2) удалить функцию, если существует get_child_list$$. Если функция get_child_list уже существует, сначала удалите ее. Обратите внимание, что вам нужно использовать текущий пользовательский терминатор $$ для завершения и выполнения оператора. Потому что это нужно отличать от тела функции ниже.

(3) создать функцию get_child_list создает функцию. И параметр передается в идентификаторе дочернего узла корневого узла, вам нужно обратить внимание на то, чтобы обязательно указать тип и длину параметра, например, здесь varchar(10). возвращает varchar(1000) используется для определения типа параметра возвращаемого значения.

(4) Тело функции окружено элементами begin и end. Используется для написания конкретной логики.

(5) declare используется для объявления переменных, а default может использоваться для установки значений по умолчанию.

Определенные здесь идентификаторы используются в качестве возвращаемого значения всей функции, которая используется для вставки в рекурсивную строку, разделенную запятыми, которая нам нужна в конце.

А tempids — это разделенная запятыми строка всех дочерних узлов, временно сгенерированная в цикле while ниже.

(6) набор используется для присвоения значений переменным. Здесь назначьте входящий корневой узел tempids.

(7) while do ... end while, оператор цикла, логика цикла включена. Обратите внимание, что в конце end while требуется точка с запятой.

Внутри цикла используйте функцию CONCAT_WS, чтобы объединить окончательные идентификаторы результатов и временно сгенерированные временные идентификаторы с запятыми.

Затем возьмите FIND_IN_SET(pid,tempids)>0 в качестве условия, пройдите все pid в tempids, найдите все идентификаторы дочерних узлов с этим в качестве родительского узла и соедините эти идентификаторы дочерних узлов с запятыми через GROUP_CONCAT(id) в tempids вверх и обновите темпиды перезаписываются.

При следующем входе в цикл идентификаторы будут снова объединены, и все дочерние узлы всех дочерних узлов будут снова найдены. Цикл повторяется, рекурсивно обходит дочерние узлы слой за слоем. Пока не будет установлено, что tempids пуст, что указывает на то, что все дочерние узлы были пройдены, весь цикл завершается.

Здесь в качестве примера используйте «1000», то есть: (см. взаимосвязь данных таблицы на рисунке 1)

第一次循环：
  tempids=1000	ids=1000	tempids=1001,1002 (1000的所有子节点)
第二次循环：
  tempids=1001,1002	 ids=1000,1001,1002	 tempids=1003,1004,1005,1013 (1001和1002的所有子节点)
第三次循环：
  tempids=1003,1004,1005,1013 
  ids=1000,1001,1002,1003,1004,1005,1013 
  tempids=1003和1004和1005及1013的所有子节点
...
最后一次循环，因找不到子节点，tempids=null，就结束循环。

(8) возвращаемые идентификаторы; используется для возврата идентификаторов в качестве возвращаемого значения функции.

(9) После завершения тела функции не забудьте использовать терминатор $$, чтобы завершить всю логику и выполнить ее.

(10) Наконец, не забудьте сбросить терминатор на точку с запятой по умолчанию.

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

Выше приведен нисходящий рекурсивный запрос для всех дочерних узлов, включая текущий узел. Вы также можете изменить логику, чтобы не включать текущий узел. Я не буду это демонстрировать.

Реализовать рекурсивные запросы вручную (восходящая рекурсия)

Восходящая рекурсия проще, чем нисходящая.

Потому что при рекурсии вниз каждый уровень рекурсии соответствует родительскому узлу, соответствующему нескольким дочерним узлам.

При рекурсии вверх дочерний узел на каждом уровне рекурсии соответствует только одному родительскому узлу, и связь относительно проста.

Точно так же мы можем определить функцию get_parent_list для получения всех родительских узлов корневого узла.

delimiter $$ 
drop function if exists get_parent_list$$ 
create function get_parent_list(in_id varchar(10)) returns varchar(1000) 
begin 
	declare ids varchar(1000); 
	declare tempid varchar(10); 
	 
	set tempid = in_id; 
	while tempid is not null do 
		set ids = CONCAT_WS(',',ids,tempid); 
		select pid into tempid from dept where id=tempid; 
	end while; 
	return ids; 
end 
$$ 
delimiter ;

Найдите вторую и первую группу Пекинского отдела исследований и разработок и его рекурсивного родительского узла следующим образом:

Меры предосторожности

Мы использовали функцию group_concat для объединения строк. Однако следует отметить, что он имеет ограничение по длине, по умолчанию это 1024 байта. в состоянии пройтиshow variables like "group_concat_max_len"; Проверять.

Обратите внимание, что единицами измерения являются байты, а не символы. В MySQL одна буква занимает 1 байт, а в обычно используемой нами utf-8 китайский символ занимает 3 байта.

Это по-прежнему очень фатально для рекурсивных запросов. Из-за общей рекурсии уровень связи является относительно глубоким и, вероятно, превышает максимальную длину. (Хотя общая склейка это строка чисел, то есть один байт)

Итак, у нас есть два пути решения этой проблемы:

  1. Измените файл конфигурации MySQL my.cnf и добавьтеgroup_concat_max_len = 102400 #你要的最大长度.

  2. Выполните любое из следующих утверждений.SET GLOBAL group_concat_max_len=102400;или SET SESSION group_concat_max_len=102400;

    Разница между ними в том, что глобальный является глобальным, и любое открытие нового сеанса вступит в силу, но обратите внимание, что текущий открытый сеанс не вступит в силу. Сеанс вступит в силу только в текущем сеансе, другие сеансы не вступят в силу.

    Что у них общего, так это то, что все они перестанут работать после перезапуска MySQL, в зависимости от конфигурации в файле конфигурации. Поэтому рекомендуется изменить файл конфигурации напрямую. Обычно достаточно длины 102400. Предполагая, что длина идентификатора составляет 10 байт, он также может состоять из 10 000 идентификаторов.

Кроме того, есть ограничение на использование функции group_concat, то есть одновременно нельзя использовать limit. как,

Изначально я хотел проверить только 5 кусков данных на склейку, но сейчас это не действует.

Однако при желании этого можно добиться с помощью подзапроса,

Категории