MySQL 错误代码：1136 列数与 sp 内第 1) 行的值数不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3185217/
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
Error Code: 1136 Column count doesn't match value count at row 1) inside sp
提问by Matt Bannert
i have some troubles filling a predefined table with a stored procedure. mytable has 6 fields: uid,testrun,exp1,exp2,exp3,weightedvalue where uid is an autoincrement PK. My sp contains an insert statement like:
我在用存储过程填充预定义表时遇到了一些麻烦。mytable 有 6 个字段：uid、testrun、exp1、exp2、exp3、weightedvalue，其中 uid 是自动增量 PK。我的 sp 包含一个插入语句，如：
CREATE PROCEDURE test (IN testrun INT)
BEGIN
.... some declare statements ...
INSERT INTO exp_factors(testrun,exp1,exp2,exp3,weightedvalue) VALUES
(testrun,
exp1,
exp2_1 + exp2_2,
exp3_1 + exp3_2,
exp1 * 0,2 + (exp2_1+exp2_2) * 0.5 + (exp3_1+exp3_2) * 0.3);
END
Unfortunately this results in the error stated in the title. I understand that I insert only 5 of 6 fields but obviously I do not want to enter the autoincrement PK uid manually. How can I enter my exp values to this table without passing on the autoincrement id. Of course I could just create a table without an extra PK, but that′s not what i want.
不幸的是，这导致了标题中所述的错误。我知道我只插入了 6 个字段中的 5 个，但显然我不想手动输入自动增量 PK uid。如何在不传递自动增量 ID 的情况下将我的 exp 值输入到该表中。当然，我可以只创建一个没有额外 PK 的表，但这不是我想要的。
Thanks for any suggestions in advance!
感谢您提前提供任何建议！
回答by Mike
You have a comma in the last line of your insert, making 6 columns instead of 5:
插入的最后一行有一个逗号，它是 6 列而不是 5 列：
exp1 * 0,2 + (exp2_1+exp2_2) * 0.5 + (exp3_1+exp3_2) * 0.3
^
I guess that this should be a decimal point, not a comma.
我想这应该是小数点，而不是逗号。
回答by Praveen Kumar
The above exception is thrown when the number of columns in the insert statement is lesser than the number of values in the query
当insert语句中的列数小于查询中的值数时抛出上述异常
for example
例如
INSERT INTO PERSON (PERSON_ID,FIRST_NAME,LAST_NAME,GENDER,BIRTH_DATE,CITY,STATE,COUNTRY,POSTAL_CODE) VALUES (NULL,'JHON','DOE','M','1988-06-17','7TH CROSS','HERE','THERE','WHERE','5600012');
Here the the number of values is 10 but the no of columns in the query is 9 thus the database throws the above exception.
这里的值数为 10，但查询中的列数为 9，因此数据库抛出上述异常。
to solve the problem we have to add STREETcolumn to the query
为了解决这个问题，我们必须在查询中添加STREET列
INSERT INTO PERSON (PERSON_ID,FIRST_NAME,LAST_NAME,GENDER,BIRTH_DATE,STREET,CITY,STATE,COUNTRY,POSTAL_CODE) VALUES (NULL,'PRAVEEN','KUMAR','M','1988-06-17','7TH CROSS','BANGALORE','KARNATAKA','INDIA','560078');
