Mysql ERROR 1136 (21S01)：列计数与第 1 行的值计数不匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21785975/
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
Mysql ERROR 1136 (21S01): Column count doesn't match value count at row 1
提问by Brandon
I have the following existing table in a mysql database:
我在 mysql 数据库中有以下现有表：
+---------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+----------------+
| f1 | int(11) | NO | PRI | NULL | auto_increment |
| field2 | int(11) | NO | MUL | NULL | |
| field3 | int(11) | YES | | NULL | |
| field4 | int(11) | YES | | NULL | |
| field6 | varchar(64) | YES | | NULL | |
| field7 | varchar(16) | YES | | NULL | |
| field8 | varchar(32) | YES | | NULL | |
| field9 | varchar(128) | YES | | NULL | |
| field10 | varchar(128) | YES | | NULL | |
| field11 | varchar(128) | YES | | NULL | |
| field12 | varchar(64) | YES | | NULL | |
| field13 | varchar(32) | YES | | NULL | |
| field14 | varchar(32) | YES | | NULL | |
| field15 | int(11) | YES | MUL | NULL | |
| field16 | date | YES | | NULL | |
| field17 | date | YES | | NULL | |
| field18 | int(11) | YES | MUL | NULL | |
| field19 | varchar(64) | YES | | NULL | |
| field20 | varchar(64) | YES | | NULL | |
| field21 | varchar(16) | YES | | NULL | |
| field22 | varchar(20) | YES | | NULL | |
| field23 | varchar(1000) | YES | | NULL | |
| field24 | int(11) | NO | MUL | NULL | |
| field25 | int(11) | NO | | 0 | |
| field26 | decimal(19,2) | YES | | 0.00 | |
| field27 | decimal(19,2) | YES | | 0.00 | |
| field28 | int(11) | YES | MUL | NULL | |
| field29 | int(11) | YES | MUL | NULL | |
| field30 | varchar(128) | YES | | NULL | |
| field31 | varchar(128) | YES | | NULL | |
| field32 | varchar(16) | YES | | NULL | |
| field33 | int(11) | YES | | NULL | |
| field34 | int(11) | YES | | NULL | |
| field35 | varchar(128) | YES | | NULL | |
| field36 | int(11) | YES | MUL | NULL | |
| field37 | int(11) | YES | | NULL | |
+---------------------+---------------+------+-----+---------+----------------+
I try the following statement to add another row and I'm getting the following error:
我尝试使用以下语句添加另一行，但出现以下错误：
ERROR 1136 (21S01): Column count doesn't match value count at row 1
Here are the ways I've tried to insert the row into the table:
以下是我尝试将行插入表的方法：
insert into table (Field, Type, Null, Key, Default, Extra) VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
insert into table VALUES ('contract_expiration','date','YES','','NULL','');
Both return the same error. There are no triggers on the table, I'm not sure what's going on.
两者都返回相同的错误。桌子上没有触发器，我不确定发生了什么。
Any suggestions? I'm relatively new to mysql administration, I know a bit but this has me stumped and searches for solutions have turned up nothing.
有什么建议？我对 mysql 管理比较陌生，我知道一点，但这让我很难过，对解决方案的搜索一无所获。
Any help that could be provided would be MUCH appreciated!
任何可以提供的帮助将不胜感激！
回答by Gordon Linoff
NULLis not a valid field name in:
NULL不是有效的字段名称：
insert into `table`(Field, Type, Null, Key, Default, Extra)
VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
And Keyand defaultare reserved words. Try this:
并且Key和default是保留字。尝试这个：
insert into `table`(Field, `Type`, `Null`, `Key`, `Default`, Extra)
VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
回答by Sverre
I had a similar case, where I took the table definition from a dev server and the data from a live server, and it turned out that they were actually not quite the same.
我有一个类似的案例，我从开发服务器获取表定义，从实时服务器获取数据，结果发现它们实际上并不完全相同。
The way I found out the difference was (there are probably smarter ways to do it, but this is how I did it):
我发现差异的方式是（可能有更聪明的方法来做到这一点，但我就是这样做的）：
SHOW CREATE TABLE mytable;
I ran this on all 3 cases (live database, dev database, and new database I created using CREATE TABLE xxx like xxx).
我在所有 3 个案例（实时数据库、开发数据库和我使用创建的新数据库CREATE TABLE xxx like xxx）上运行了这个。
Then I simply compared the 3 and found that the live and dev had a different set of columns, so I simply ran
然后我简单对比了3，发现live和dev的列集不同，所以我干脆跑了
ALTER TABLE xxx DROP yyy;
until the new table was the same as the table the dump was from; then I could import data.
直到新表与转储的表相同；然后我可以导入数据。
回答by AKASH GUDADHE
mysql> desc classroom; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | clId | int(11) | NO | PRI | NULL | auto_increment | | clFName | varchar(30) | NO | | NULL | | | clSName | varchar(10) | NO | | NULL | | | clCapc | int(3) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> select * from classroom; +------+------------+---------+--------+ | clId | clFName | clSName | clCapc | +------+------------+---------+--------+ | 1 | Classroom1 | cl1 | 100 | | 2 | 2 | 2 | 2 | | 3 | 3f | 3s | 3 | | 4 | 3f | 3s | 3 | | 5 | class4 | class4 | 100 | +------+------------+---------+--------+ 5 rows in set (0.00 sec)
I also have same error
我也有同样的错误
mysql> insert into classroom values('Gudadhe', 'Akash', 20);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
This error can be solved by specifying column names before inserting directly as follows
这个错误可以通过在直接插入之前指定列名来解决，如下
By writing classroom(clFName, clSName, clCapc) we are specifying columns into which we want to insert values
通过编写classroom(clFName, clSName, clCapc)，我们指定要插入值的列
mysql> insert into classroom(clFName, clSName, clCapc) values('Gudadhe', 'Akash', 20);
Query OK, 1 row affected (0.06 sec)
