mysql 8.0 CTE 介绍

2019-01-29 12:08  413人阅读  评论 (0)
Tags: mysqlcte

简介

MySQL8.0添加了CTE功能,以前很多需要多次查询的需求,现在可以一次查询搞定了.

非递归

建表语句

-- 分类表
CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 博客表
CREATE TABLE `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `content` text NOT NULL,
  `created_time` timestamp NOT NULL,
  `updated_time` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查询

WITH b AS (SELECT category_id, count(*) AS count FROM blog GROUP BY category_id)
SELECT c.id, c.name, b.count FROM category AS c, b WHERE c.id = b.category_id;

递归

建表语句

CREATE TABLE `category_tree` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `name` varchar(45) NOT NULL,
  `id_path` varchar(200) NOT NULL,
  `name_path` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

表数据

INSERT INTO category_tree (id, pid, name) VALUES
(1,0,'家用电器'),
(2,0,'电脑'),
(3,0,'手机'),
(4,1,'冰箱'),
(5,1,'电视'),
(6,1,'空调'),
(7,2,'整机'),
(8,2,'配件'),
(9,2,'外设'),
(10,3,'手机通讯'),
(11,3,'运营商'),
(12,3,'手机配件'),
(13,4,'双门'),
(14,4,'三门'),
(15,4,'酒柜'),
(16,5,'曲面电视'),
(17,5,'超薄电视'),
(18,5,'4K电视'),
(19,6,'壁挂空调'),
(20,6,'柜式空调'),
(21,6,'中央空调'),
(22,7,'笔记本'),
(23,7,'台式机'),
(24,7,'平板电脑'),
(25,8,'CPU'),
(26,8,'内存'),
(27,8,'硬盘'),
(28,9,'键盘'),
(29,9,'鼠标'),
(30,9,'U盘'),
(31,10,'手机'),
(32,10,'老人机'),
(33,10,'对讲机'),
(34,11,'移动'),
(35,11,'联通'),
(36,11,'电信'),
(37,12,'CD卡'),
(38,12,'耳机'),
(39,12,'手机壳');

递归查询

WITH RECURSIVE category_paths (id, pid, name, id_path, name_path) AS (
    SELECT id, pid, name, cast(id AS char(200)), cast(name AS char(200)) FROM category_tree WHERE pid = 0
    UNION ALL
    SELECT c.id, c.pid, c.name, concat(cp.id_path, ',', c.id), concat(cp.name_path, ',', c.name) FROM category_paths AS cp
    JOIN category_tree AS c ON cp.id = c.pid
)
SELECT * FROM category_paths ORDER BY id_path;

递归更新

WITH RECURSIVE category_paths (id, pid, name, id_path, name_path) AS (
    SELECT id, pid, name, cast(id AS char(200)), cast(name AS char(200)) FROM category_tree WHERE pid = 0
    UNION ALL
    SELECT c.id, c.pid, c.name, concat(cp.id_path, ',', c.id), concat(cp.name_path, ',', c.name) FROM category_paths AS cp
    JOIN category_tree AS c ON cp.id = c.pid
)
UPDATE category_tree AS c JOIN category_paths AS cp ON c.id = cp.id SET c.id_path = cp.id_path, c.name_path = cp.name_path;