mysql查询所有父级名称拼接起来（指定搜索名称）

结构

CREATE TABLE `sys_region` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `pid` bigint unsigned NOT NULL DEFAULT '0' COMMENT '父级id',
  `region_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '名称',
  `region_type` tinyint unsigned NOT NULL DEFAULT '2' COMMENT '级别',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `parent_id` (`pid`) USING BTREE,
  KEY `region_type` (`region_type`) USING BTREE,
  KEY `region_name` (`region_name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='省市区';

数据

INSERT INTO `sys_region` (`id`, `pid`, `region_name`, `region_type`) VALUES ('2', '1', '北京', '1');
INSERT INTO `sys_region` (`id`, `pid`, `region_name`, `region_type`) VALUES ('16', '1', '山东省', '1');
INSERT INTO `sys_region` (`id`, `pid`, `region_name`, `region_type`) VALUES ('37', '2', '北京市', '2');
INSERT INTO `sys_region` (`id`, `pid`, `region_name`, `region_type`) VALUES ('174', '16', '东营市', '2');
INSERT INTO `sys_region` (`id`, `pid`, `region_name`, `region_type`) VALUES ('403', '37', '东城区', '3');
INSERT INTO `sys_region` (`id`, `pid`, `region_name`, `region_type`) VALUES ('1844', '174', '东城区', '3');

查询

WITH RECURSIVE region_path AS (
    SELECT id, region_name, pid,region_type,
        CAST(id AS CHAR(255)) AS region_ids,
        CAST(region_name AS CHAR(255)) AS region_names
    FROM sys_region
    WHERE pid =1
    UNION ALL
    SELECT d.id, d.region_name, d.pid, d.region_type,
        CONCAT(p.region_ids, '/', d.id),
        CONCAT(p.region_names, '/', d.region_name)
    FROM sys_region d
    JOIN region_path p ON d.pid = p.id
)
SELECT * FROM region_path
WHERE region_name LIKE '%东城区%';