MySQL “列数与行的值数不匹配”但确实如此
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13044901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"Column count doesn't match value count at row" but it does
提问by Pask
I've got a mysql file with over than 14000 statements. All of them are inserts into a table, and when I import the file using console, it throws the following error:
我有一个包含超过 14000 条语句的 mysql 文件。所有这些都插入到表中，当我使用控制台导入文件时，它会引发以下错误：
ERROR 1136 (21S01) at line 1548: Column count doesn't match value count at row 45
I know that error appears when the rows specified in column names are different from the rows specified in VALUES list, but I've checked it out many times and the number of columns is exactly the same in both lists.
我知道当列名中指定的行与 VALUES 列表中指定的行不同时会出现错误，但我已经检查过很多次并且两个列表中的列数完全相同。
The row in position 45 is:
位置 45 的行是：
('00553', 'AAA', 'BBB', 'CCC', 'XXXXXXXXR', 'user address', 'spain', 'spain', '39212', '1900-01-21', '123456789', 'M', 'No disponible', 0, 'AAA', 'BBB ', 'CCC', 'XXXXXXXXR', NULL, '888993344', '', '', '', '', 'no', 'no') --> 26 columns
and the insert line associated with it, many lines above is:
以及与之关联的插入行，上面的许多行是：
INSERT INTO `users` (`id_patient`, `name`, `surname1`, `surname2`, `dni`, `address`, `city`, `state`, `postal_code`, `birthday`, `telephone`, `sex`, `email`, `lopd_status`, `lopd_name`, `lopd_surname1`, `lopd_surname2`, `lopd_dni`, `lopd_as`, `mobile_phone`, `notes`, `job`, `company`, `place`, `active_citation`, `signature`) --> 26 columns
I've looked for this error but it seems it only appears when count is different, but in this case, it is the same.
我一直在寻找这个错误，但它似乎只在计数不同时出现，但在这种情况下，它是相同的。
Any idea?
任何的想法？
EDIT: forgot to mention: if I throw the sql statement directly on phpmyadmin, it adds the line correctly without any errors. It only crashes when run from command line, although the statements above it are correctly inserted into database and they are almost the same than this one.
编辑：忘了提及：如果我直接在 phpmyadmin 上抛出 sql 语句，它会正确添加该行而没有任何错误。它只在从命令行运行时崩溃，尽管它上面的语句正确插入到数据库中并且它们几乎与此相同。
EDIT2: I've removed all lines until the 46 and when I launch the file the erros is the following:
EDIT2：我已经删除了 46 之前的所有行，当我启动文件时，错误如下：
ERROR 1136 (21S01) at line 1503: Column count doesn't match value count at row 45
It only changes the line, but that line is the INSERT statement, which is exactly the same as the other INSERT statements in the rest of the file. The previos line is also correct.
它只更改行，但该行是 INSERT 语句，它与文件其余部分中的其他 INSERT 语句完全相同。上一行也是正确的。
采纳答案by valplo
I faced a similar problem because of inserting parenthesis around data :-(:
由于在数据周围插入括号，我遇到了类似的问题:-(：
INSERT INTO Customers (Name,LastCredit,CreditDate) VALUES ( <-- Here.
("Nuclear Millitary Systems",500.0,CURRENT_DATE),
("Evil Corporation",67890.95,"2012-02-12"),
("Nuke Software Systems",5600.0,"2013-05-06"),
("RR Millitary",600.0,"2013-05-06"),
("Random Automation",560.0,"2012-05-01"),
("Evil Data Systems",600.0,"2013-03-01")
);
回答by Dolly
I was facing a similar error. It was just the matter of a missing comma. Check if all the items in the insert statement are separated by commas
我遇到了类似的错误。这只是缺少逗号的问题。检查插入语句中的所有项目是否用逗号分隔
回答by Neetigya
In my case, my database table has a trigger associated to another table, and there was one column missing in triggered table. Something like this:
就我而言，我的数据库表有一个与另一个表关联的触发器，并且触发表中缺少一列。像这样的东西：
Table1 columns:UserAccountID, Document type, permissions, Status
表1列：UserAccountID、文档类型、权限、状态
Triggered Table 2 columns:Revision number, Revision Status, UserAccountID, Permissions, Status
触发表 2 列：修订号、修订状态、UserAccountID、权限、状态
So in the table 2, "document type" column was missing. Check the triggers in the DB by
因此，在表 2 中，缺少“文档类型”列。检查数据库中的触发器
"show triggers from {Database Name}"
“显示来自{数据库名称}的触发器”
回答by Anand Rockzz
In my case I had a triggeron INSERT with column mismatch count :(&
在我的情况下，我在 INSERT 上有一个触发器，列不匹配计数:(&
回答by davidethell
Check for special hidden characters in the file. You might think that all whitespace in the file are spaces, but sometimes other characters can kill the insert in some way you can't understand by looking at the data.
检查文件中的特殊隐藏字符。您可能认为文件中的所有空格都是空格，但有时其他字符会以某种您无法通过查看数据来理解的方式杀死插入。
If all else fails, retype the INSERT manually and run it in a file by itself to see if it still fails.
如果所有其他方法都失败，请手动重新键入 INSERT 并在文件中单独运行它以查看它是否仍然失败。
回答by Yong Yang
I met the same issue too. @valplo's answer is right. Since I don't have enough reputation to comment, I write it here.
我也遇到了同样的问题。@valplo 的答案是正确的。由于我没有足够的声誉来发表评论，所以我写在这里。
The correct way to insert multiple values is to list the values after the key word VALUES, not parenthesis around multiple value entries, but parenthesis around single value entries, and separate each single value entry with comma.
插入多个值的正确方法是在关键字 之后列出值VALUES，而不是在多个值条目周围使用括号，而是在单个值条目周围使用括号，并用逗号分隔每个单个值条目。
The wrong way is:
错误的方法是：
INSERT INTO my_table(column_name_0, column_name_1) VALUES(
(val_00, val_01),
(val_10, val_11)
);
The correct way is:
正确的方法是：
INSERT INTO my_table(column_name_0, column_name_1) VALUES
(val_00, val_01),
(val_10, val_11);
回答by Accountant ?
In my case it was the number of "?" in the prepared statement
就我而言，它是“？”的数量。在准备好的声明中
prepare stmt1 from "insert into company (name, address, phone) values(?,?);";
/*note only 2 question marks which should be 3.*/
This also will trigger the same error
这也会触发同样的错误
insert into company (name, address, phone) values('foo','bar');
