# [FAQ] 不同数据类型字段关联，可能会返回 ERROR 1292 (22007)  Truncated incorrect value

## Key Words

ERROR 1292 (22007), Truncated incorrect value

## Description

```sql
mysql> create table tmp1(id int,cno varchar(255)); 
Query OK, 0 rows affected (0.19 sec)

mysql> create table table1(id int,cno varchar(255),cnum varchar(50)); 
Query OK, 0 rows affected (0.19 sec)

mysql> create table table2(id int,cnum decimal(26,4)); 
Query OK, 0 rows affected (0.20 sec)

mysql> insert into table1 values(1,'abc1','2123'); 
Query OK, 1 row affected (0.12 sec)

mysql> insert into table1 values(2,'abc2','2223'); 
Query OK, 1 row affected (0.11 sec)

mysql> insert into table1 values(3,'abc3','2323'); 
Query OK, 1 row affected (0.12 sec)

mysql> insert into table1 values(4,'abc4','23J23'); 
Query OK, 1 row affected (0.11 sec)

mysql> insert into table2 values(1,2123); 
Query OK, 1 row affected (0.13 sec)

mysql> insert into table2 values(2,2223); 
Query OK, 1 row affected (0.45 sec)

mysql> insert into table2 values(3,2323); 
Query OK, 1 row affected (1.13 sec)

mysql> desc tmp1;
+-------+--------------+------+------+---------+-------+
| Field | Type         | Null | Key  | Default | Extra |
+-------+--------------+------+------+---------+-------+
| id    | int(11)      | YES  |      | NULL    |       |
| cno   | varchar(255) | YES  |      | NULL    |       |
+-------+--------------+------+------+---------+-------+
2 rows in set (0.12 sec)
-----------------------------------------------------------------

mysql> desc table1;
+-------+--------------+------+------+---------+-------+
| Field | Type         | Null | Key  | Default | Extra |
+-------+--------------+------+------+---------+-------+
| id    | int(11)      | YES  |      | NULL    |       |
| cno   | varchar(255) | YES  |      | NULL    |       |
| cnum  | varchar(50)  | YES  |      | NULL    |       |
+-------+--------------+------+------+---------+-------+
3 rows in set (0.12 sec)

mysql> desc table2;
+-------+---------------+------+------+---------+-------+
| Field | Type          | Null | Key  | Default | Extra |
+-------+---------------+------+------+---------+-------+
| id    | int(11)       | YES  |      | NULL    |       |
| cnum  | decimal(26,4) | YES  |      | NULL    |       |
+-------+---------------+------+------+---------+-------+
2 rows in set (0.45 sec)

mysql> select * from table1;
+------+------+-------+
| id   | cno  | cnum  |
+------+------+-------+
|    1 | abc1 | 2123  |
|    2 | abc2 | 2223  |
|    3 | abc3 | 2323  |
|    4 | abc4 | 23J23 |
+------+------+-------+
4 rows in set (0.11 sec)

mysql> select * from table2;
+------+-----------+
| id   | cnum      |
+------+-----------+
|    1 | 2123.0000 |
|    2 | 2223.0000 |
|    3 | 2323.0000 |
+------+-----------+
3 rows in set (0.11 sec)

mysql> select table1.cno from table1 join table2 on table1.cnum=table2.cnum;
+------+
| cno  |
+------+
| abc1 |
| abc2 |
| abc3 |
+------+
3 rows in set, 1 warning (0.12 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '?' |
+---------+------+---------------------------------------+
1 row in set (0.12 sec)

mysql> insert into tmp1 (cno) select table1.cno from table1 join table2 on table1.cnum=table2.cnum; 
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '?'
```

## Content

不同数据类型的字段关联时，在进行转换比对的时候可能会出现数值不匹配的情况，如果是 select，会报 warning，如果是 insert select 会直接报错。在 MySQL 8.0 上行为一致，如果遇到类似的错误，需要业务侧调整改写 SQL 和 Schema 设计。