我有一个名为 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(,但没有成功。
我真的看不出发生了什么。
此处的示例测试
谢谢
NUMERIC(10,2( 列(如price
列(上的AVG()
函数生成类型为 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 t2G
*************************** 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
你的选择是错误的。 为:
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