Table of Contents

• 一.问题描述
• 二.解决方案
• 2.1 字符集问题
• 2.2 sql_mode的问题
• 参考:

一.问题描述

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)
2.2 sql_mode的问题

在my.ini里找到

sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION”

STRICT_TRANS_TABLES,

去掉,然后重启mysql就ok了