公用表达式和生成列

公用表达式

简称CTE，通过with语句实现，分为递归和非递归

非递归

select * from (select year(now())) as year;

使用CTE，sql语句更加容易理解，多个CTE语句中间有逗号隔开即可

with year as (select year(now())) select * from year;

递归

输出1到10

with recursive cte_num(num) as
(
select 1 union  all
select num + 1 from cte_num where num < 10
)
select * from cte_num;

层级查询

with recursive area_depth(id,name,path) as
(
select id,name,CAST(id as char(300))
from t_area where pid is null
union all
select a.id,a,name, CONCAT(ad.path, ',', a.id)
from area_depth as ad
join t_area as a
on ad.id = a.pid
)
select * from area_depth order by path;

生成列

生成列是根据数据表中定义列时指定的表达式计算得到的，分为virtual和stored。virtual是数据查询得到的，stored是写入数据生成的。

create table t_genearted_column(
a double,
b double,
c double as (a*a + b * b)
);
insert into t_genearted_column (a,b) values (1,1);

如下图所示，没有插入c列的数据，其中也会有数据，不能像c列插入值，要插入只能插入default

创建表明确指明virsual生成列。

create table t_genearted_column1(
a double,
b double,
c double generated always as (a + b) virtual
);

添加生成列

alter table table_name add column c double generated always as (a + b) stored

修改生成列

alter table table_name modify column c double generated always as (a + b) stored

删除

alter table table_name drop column c