MySQL ERROR 1406 (22001): Data too long for column ‘c1‘ at row 1

文章目錄

• 一.問題描述
• 二.解決方案
• • 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
           

二.解決方案

網上找了一些解決方案，大緻可以分為三類

1. 字元集的問題
2. 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了

參考:

1. https://blog.csdn.net/zj15527620802/article/details/79770807