MySQL INSERT INTO 语句生成“错误代码:1136.列计数与行的值计数不匹配"； [英] MySQL INSERT INTO statement generating "Error Code: 1136. Column count doesn't match value count at row"

问题描述

我正在尝试从旧表创建一个新表，以便删除一些重复项并实现复合主键.我已经使用 LIKE 从旧表创建了新表，因此列应该是相同的.

I am trying to create a new table from an old table so I can remove some duplicates and implement a composite primary key. I have created the new table from the old one using LIKE, so the columns should be identical.

为什么这不起作用?

INSERT INTO PricesNEWtest (Global_MarketCap,pkey)
   VALUES ((SELECT max(Global_MarketCap), pkey
   FROM Prices GROUP BY pkey));

错误产生:

错误代码:1136.列数与第 1 行的值数不匹配

Error Code: 1136. Column count doesn't match value count at row 1

上面的例子只有两行所以更清晰，但实际上表包含 15 列，这是完整的 INSERT INTO 语句，它生成相同的错误:

The example above only has two rows so it's more legible, but in reality the tables contain 15 columns, this is the full INSERT INTO statement, which generates the same errror:

INSERT INTO PricesNEWtest (Global_MarketCap,Global_Volume24h,BTC_Dominance,Rank,Name,
Symbol,ChangePerc24h,Price,BTC_Price,MarketCap,Volume24h,DateTime,Date,pkDummy,pkey)
VALUES ((SELECT max(Global_MarketCap), max(Global_Volume24h), max(BTC_Dominance), max(Rank), max(Name),
    max(Symbol), max(ChangePerc24h), max(Price), max(BTC_Price), max(MarketCap), max(Volume24h),
    max(DateTime), max(Date), max(pkDummy), pkey
    FROM Prices GROUP BY pkey));

我为 VALUES 添加了双括号，因为没有它我会得到错误代码 1064，但我不完全理解为什么需要双括号.我按 pkey 字段分组(目前有一些我想删除的重复项)，这意味着我需要总结其余的字段.正如您从屏幕截图中看到的那样，SELECT 语句本身运行良好.

有没有另一种方法可以做到这一点，我可以尝试?或者有没有更简单的方法可以直接从原始表中删除重复项?
我使用的是 MySQL 5.7.14

I added the double brackets for VALUES because without it I get error code 1064, but I don't fully understand why the double brackets are necessary. I am grouping by the pkey field (which currently has some duplicates I want to delete), which means I need to summarize the rest of the fields. The SELECT statement works fine on its own as you can see from the screenshot.

Is there another way to do this that I could try? Or is there an easier way to remove the duplicates directly from the original table?
I am using MySQL 5.7.14

任何帮助将不胜感激！

推荐答案

您只是使用 SELECT 语句对 INSERT 使用了错误的语法:

You just have the wrong syntax to INSERT with a SELECT statement:

INSERT INTO PricesNEWtest (Global_MarketCap, pkey)
SELECT max(Global_MarketCap), pkey
FROM Prices 
GROUP BY pkey

这篇关于MySQL INSERT INTO 语句生成“错误代码:1136.列计数与行的值计数不匹配"；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！