一.问题描述

测试数据:

drop table test;

create table test (pid varchar(200),id varchar(200));

insert into test values (null,'中国');
insert into test values ('中国','广东');
insert into test values ('中国','广西');
insert into test values ('广东','深圳');
insert into test values ('深圳','福田');

with RECURSIVE t(pid,c1,c2,c3,lvl,id) as (
select id,null,null,null,1,id
  from test
where pid is null 
union all
select t.pid
      ,case when t.lvl=1 then test.id else t.c1 end as c1
      ,case when t.lvl=2 then test.id else t.c2 end as c2
      ,case when t.lvl=3 then test.id else t.c3 end as c3
      ,t.lvl+1   
      ,test.id
  from t
      ,test
where t.id=test.pid
)
select * from t

运行报错:

mysql> with RECURSIVE t(pid,c1,c2,c3,lvl,id) as (
    -> select id,null,null,null,1,id
    ->   from test
    -> where pid is null
    -> union all
    -> select t.pid
    ->       ,case when t.lvl=1 then test.id else t.c1 end as c1
    ->       ,case when t.lvl=2 then test.id else t.c2 end as c2
    ->       ,case when t.lvl=3 then test.id else t.c3 end as c3
    ->       ,t.lvl+1
    ->       ,test.id
    ->   from t
    ->       ,test
    -> where t.id=test.pid
    -> )
    -> select * from t;
ERROR 1406 (22001): Data too long for column 'c1' at row 1

二.解决方案

网上找了一些解决方案，大致可以分为三类

字符集的问题
sql_mode的问题

2.1 字符集问题

如下可以看到，我的字符集都是utf-8，所以这个可以排除

mysql> show variables like '%character%';
+--------------------------+----------------------------------------------+
| Variable_name            | Value                                        |
+--------------------------+----------------------------------------------+
| character_set_client     | utf8                                         |
| character_set_connection | utf8                                         |
| character_set_database   | utf8                                         |
| character_set_filesystem | binary                                       |
| character_set_results    | utf8                                         |
| character_set_server     | utf8                                         |
| character_set_system     | utf8                                         |
| character_sets_dir       | E:\mysql\mysql-8.0.19-winx64\share\charsets\ |
+--------------------------+----------------------------------------------+
8 rows in set, 1 warning (0.00 sec)