Data Truncated 问题?

[itdevelopcn]

错误SQL表现

insert into test1(key,value1) select key,case WHEN days >=0 and days<30 THEN ‘1’ ELSE ‘2’ eND from test_data where nature = ‘123456’ ;

1265 - Data Truncated

1.单独这个,正确

select key,case WHEN days >=0 and days<30 THEN ‘1’ ELSE ‘2’ eND from test_data where nature = ‘123456’ ;

2.单独去掉case when,正确

insert into test1(key,value1) select key,days from test_data where nature = ‘123456’ ;

[小王同学]

辛苦按照模版提供下 tidb 版本信息。我们这边复现试下

[itdevelopcn]

tidb_version()
| Release Version: v3.0.3 Git Commit Hash: 836982c617fbaa42d74616eb9ba9ed4cae46c46b Git Branch: HEAD UTC Build Time: 2019-08-30 02:42:42 GoVersion: go version go1.12 linux/amd64 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false

[Lucien-卢西恩 (路西恩)]

@itdevelopcn

select ke

-- table a schema
mysql> show create table aG;
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

ERROR:
No query specified
-- table b schema
mysql> show create table bG;
*************************** 1. row ***************************
       Table: b
Create Table: CREATE TABLE `b` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

ERROR:
No query specified
-- TiDB cluster version
mysql> select tidb_version()G
*************************** 1. row ***************************
tidb_version(): Release Version: v3.0.3
Git Commit Hash: 836982c617fbaa42d74616eb9ba9ed4cae46c46b
Git Branch: HEAD
UTC Build Time: 2019-08-30 02:42:42
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)
-- table a data
mysql> select * from a;
+------+-------+
| col1 | col2  |
+------+-------+
|    1 | 23456 |
|    2 |  2456 |
+------+-------+
2 rows in set (0.00 sec)
-- table b data
mysql> select * from b;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)
-- query context
mysql> select col1,case when col1>=0 and col1<=0 then 1 else 2 end from a where col2=23456;
+------+-------------------------------------------------+
| col1 | case when col1>=0 and col1<=0 then 1 else 2 end |
+------+-------------------------------------------------+
|    1 |                                               2 |
+------+-------------------------------------------------+
1 row in set (0.00 sec)

-- insert context 
mysql> insert into b(col1,col2) select col1,col2 from a where col2=23456;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from b;
+------+-------+
| col1 | col2  |
+------+-------+
|    1 |     2 |
|    1 | 23456 |
|    1 |     2 |
+------+-------+
2 rows in set (0.00 sec)

mysql> select col1,case when col1>=0 and col1<=0 then 1 else 2 end from a where col2=23456;
+------+-------------------------------------------------+
| col1 | case when col1>=0 and col1<=0 then 1 else 2 end |
+------+-------------------------------------------------+
|    1 |                                               2 |
+------+-------------------------------------------------+
1 row in set (0.00 sec)
-- insert case when
mysql> insert into b(col1,col2) select col1,case when col1>=0 and col1<=0 then 1 else 2 end from a where col2=23456;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from b;
+------+-------+
| col1 | col2  |
+------+-------+
|    1 |     2 |
|    1 | 23456 |
|    1 |     2 |
|    1 |     2 |
+------+-------+
4 rows in set (0.00 sec)

我测试了一下 3.0.3 没有问题，建议 review 操作步骤，如果还是有问题，可以按照我的测试方法，提供准确的测试步骤和 schema 信息。

[itdevelopcn]

多谢,反馈.