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

vmdwslir 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(352)

我有一个名为pt\u 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\u 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 decimal(10,2) not null,

数据不应该放在那里吗？
我试图改变平均价格从图表表十进制（20，4），没有运气。
我真的看不出发生了什么事。
此处为示例测试
谢谢

sql mysql

来源：https://stackoverflow.com/questions/62329272/error-code-1265-data-truncated-for-column

2条答案

按热度按时间

vnjpjtjt1#

这个 AVG() 在数字（10,2）列上的函数，如 price 列生成类型为numeric（14,6）的结果。
演示：

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

（数字和小数是同义词。）
当您尝试将具有六位数刻度的值填充到具有两位数刻度的列中时，mysql必须假设它可能会丢失一些信息。
演示：存储一个带有两位数刻度的数字是可以的，不会引起任何警告：

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

但规模更大的数字会产生警告：

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)

可以将数值表达式显式转换为相同的数据类型，以避免出现警告：

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

赞(0）回复(0）举报 2021-07-29

w8ntj3qf2#

你的选择错了。用于：

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

你会得到：

ean     avg_price
10086100372     929.500000
10343943704     3697.333333
10343943707     3834.000000
10343943708     9999.990000
884116311874    10499.500000
884116311875    10499.500000
884116321378    14789.000000

这就被打败了。
你需要四舍五入你的平均数为2位，因为你用2位小数。

select ean, round(avg(price),2) avg_price

这将为您提供：

10086100372     929.50
10343943704     3697.33
10343943707     3834.00
10343943708     9999.99
884116311874    10499.50
884116311875    10499.50
884116321378    14789.00

赞(0）回复(0）举报 2021-07-29

我来回答

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

2条答案

相关问题

热门标签

最新问答