MySQL CTE递归查询 [22001][1406] Data truncation: Data too long for column ‘xxx‘ at row 1

背景

使用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