mysql - 错误代码：1265。列的数据被截断

Question

我有一个名为 pt_products 的表，其中包含以下字段

id  int(11) NO  PRI     auto_increment
merchant    varchar(64) NO  MUL     
filename    varchar(255)    NO  MUL     
name    varchar(255)    NO  MUL     
description text    NO          
image_url   varchar(255)    NO          
buy_url text    NO          
price   decimal(10,2)   NO      0.00    
category    varchar(64) NO  MUL     
brand   varchar(64) NO  MUL     
rating  int(11) NO      0   
reviews int(11) NO      0   
search_name varchar(255)    NO  MUL     
normalised_name varchar(255)    NO  MUL     
original_name   varchar(255)    NO          
voucher_code    varchar(255)    NO          
categoryid  int(11) NO      0   
dupe_hash   varchar(32) NO  UNI     
ean varchar(255)    NO  MUL 0   
asin    char(10)    NO          
Amazon_Top_Sales    bigint(7)   NO      0   
availability    varchar(255)    NO          
mpn varchar(255)    NO  MUL     
delivery_cost   decimal(10,2)   NO      0.00    

我创建了一个新表

create table graph (
    id int primary key auto_increment,
    ean varchar(255) unsigned not null,
    avg_price decimal(10,2) not null,
   created_at timestamp default current_timestamp
);

然后，我想将表 pt_products 中的字段插入到上面创建的图表中

insert into graph(ean, avg_price)
select ean, avg(price) avg_price
from pt_products
group by ean;

那就是我收到错误的时候

4566 row(s) affected, 64 warning(s): 1265 Data truncated for column 'avg_price' at row 8438 1265 Data truncated for column 'avg_price' at row 8439 1265 Data truncated for column 'avg_price' at row 8440 1265 Data truncated for column 'avg_price' at row 9235 1265 Data truncated for column 'avg_price' at row 9300 1265 Data truncated for column 'avg_price' at row 9333 1265 Data truncated for column 'avg_price' at row 9337 1265 Data truncated for column 'avg_price' at row 9338 1265 Data truncated for column 'avg_price' at row 9339 1265 Data truncated for column 'avg_price' at row 9341 1265 Data truncated for column 'avg_price' at row 9369 1265 Data truncated for column 'avg_price' at row 9370 1265 Data truncated for column 'avg_price' at row 9371 1265 Data truncated for column 'avg_price' at row 9375 1265 Data truncated for column 'avg_price' at row 9376 1265 Data truncated for column 'avg_price' at row 9382 1265 Data truncated for column 'avg_price' at row 9385 1265 Data truncated for column 'avg_price' at row 9386 1265 Data truncated for column 'avg_price' at row 9389 1265 Data truncated for column 'avg_price' at row 9393 1265 Data truncated for column 'avg_price' at row 9401 1265 Data truncated for column 'avg_price' at row 9459 1265 Data truncated for column 'avg_price' at row 9460 1265 Data truncated for column 'avg_price' at row 9532 1265 Data truncated for column 'avg_price' at row 9596 1265 Data truncated for column 'avg_price' at row 9608 1265 Data truncated for column 'avg_price' at row 9617 1265 Data truncated for column 'avg_price' at row 9618 1265 Data truncated for column 'avg_price' at row 9626 1265 Data truncated for column 'avg_price' at row 9669 1265 Data truncated for column 'avg_price' at row 9675 1265 Data truncated for column 'avg_price' at row 9677 1265 Data truncated for column 'avg_price' at row 9678 1265 Data truncated for column 'avg_price' at row 9680 1265 Data truncated for column 'avg_price' at row 9683 1265 Data truncated for column 'avg_price' at row 9695 1265 Data truncated for column 'avg_price' at row 9710 1265 Data truncated for column 'avg_price' at row 9711 1265 Data truncated for column 'avg_price' at row 9739 1265 Data truncated for column 'avg_price' at row 9759 1265 Data truncated for column 'avg_price' at row 9760 1265 Data truncated for column 'avg_price' at row 9768 1265 Data truncated for column 'avg_price' at row 9775 1265 Data truncated for column 'avg_price' at row 9777 1265 Data truncated for column 'avg_price' at row 9780 1265 Data truncated for column 'avg_price' at row 9789 1265 Data truncated for column 'avg_price' at row 9797 1265 Data truncated for column 'avg_price' at row 9829 1265 Data truncated for column 'avg_price' at row 9856 1265 Data truncated for column 'avg_price' at row 9874 1265 Data truncated for column 'avg_price' at row 9897 1265 Data truncated for column 'avg_price' at row 9902 1265 Data truncated for column 'avg_price' at row 9917 1265 Data truncated for column 'avg_price' at row 9936 1265 Data truncated for column 'avg_price' at row 9949 1265 Data truncated for column 'avg_price' at row 9961 1265 Data truncated for column 'avg_price' at row 9976 1265 Data truncated for column 'avg_price' at row 9985 1265 Data truncated for column 'avg_price' at row 9991 1265 Data truncated for column 'avg_price' at row 9995 1265 Data truncated for column 'avg_price' at row 10003 1265 Data truncated for column 'avg_price' at row 10005 1265 Data truncated for column 'avg_price' at row 10007 1265 Data truncated for column 'avg_price' at row 10010 Records: 4566  Duplicates: 0  Warnings: 215

价格字段是

price   decimal(10,2)

和目标表图中的 avg_price 相同。

avg_price decimal(10,2) not null,

数据不应该在那里吗？

我试图将 avg_price 从图表更改为小数（20,4），但没有成功。

我真的看不到发生了什么。

示例测试在这里

谢谢

标签： mysqlsql

Answer 1

The AVG() function on a NUMERIC(10,2) column like your price column produces a result with the type NUMERIC(14,6).

Demo:

mysql> create table t ( price numeric(10,2));

mysql> create table t2 as select avg(price) avg_price from t;

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `avg_price` decimal(14,6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

(NUMERIC and DECIMAL are synonyms.)

When you try to stuff values with six digits of scale into a column with two digits of scale, MySQL must assume that it's possible that it will lose some information along the way.

Demo: Storing a number with two digits of scale is okay, and causes no warnings:

mysql> insert into t select 3.14;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

But a number with greater scale results in a warning:

mysql> insert into t select 3.1415927;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1265 | Data truncated for column 'price' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

You can explicitly cast the numeric expression to the same data type, to avoid the warning:

mysql> insert into t select cast(3.1415927 as decimal(10,2));
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0