背景
使用MySQL8.0 的CTE递归查询时,出现了这个报错
with recursive user_path(user_id, type, path) as (select user_id, type, user_id as path
from tbl_employee_superior
where superior_id = 'f587d912560844cca530478cd8dc853a'
union all
select tes.user_id, tes.type, concat(up.path, ' > ', tes.user_id)
from user_path up
join tbl_employee_superior tes on tes.superior_id = up.user_id)
select *
from user_path
order by path
具体报错:
[22001][1406] Data truncation: Data too long for column 'path' at row 1
原因
是因为参与递归concat的path字段超出了长度限制,也就是说在非递归语句中,一开始path字段的长度就确定了,而本例中,path长度来源于user_id字段长度,所以path一开始定义的长度就是user_id的长度。
解决方案
- 方案一:修改表字段user_id长度,使得递归时concat字段不会超出初始定义的长度。这个解决方案需要修改表字段的长度,但是随着业务数据增长(递归层数更深),难免会再出现超出初始长度的问题。
- 方案二:在cte表达式中非递归的查询语句中,将初始字段的长度重新转换一下,cast(xxx as CHAR(1024)) path。这种方式比较灵活一些,个人建议使用方案二。
with recursive user_path(user_id, type, path) as (select user_id, type, cast(user_id as CHAR(1024)) path
from tbl_employee_superior
where superior_id = 'f587d912560844cca530478cd8dc853a'
union all
select tes.user_id, tes.type, concat(up.path, ' > ', tes.user_id)
from user_path up
join tbl_employee_superior tes on tes.superior_id = up.user_id)
select *
from user_path
order by path
参考
https://dev.mysql.com/doc/refman/8.0/en/with.html