MySQL“列数与值数不匹配"但计数确实匹配 [英] MySQL "Column count doesn't match value count" but the count DOES match

问题描述

当我尝试执行列数匹配的查询时，MySQL发出此错误.这是表格的结构:

MySQL is issuing this error when I try to execute a query where the column count does match. Here is the structure of the table:

mysql> desc S_3068; 
+-------------------+----------------------+------+-----+---------+-------+ 
| Field             | Type                 | Null | Key | Default | Extra | 
+-------------------+----------------------+------+-----+---------+-------+ 
| SfmID             | smallint(5) unsigned | NO   | PRI | 1       |       | 
| DatValue          | float                | NO   |     | 0       |       | 
| DatRawValue       | int(10) unsigned     | NO   |     | 0       |       | 
| DatTime           | int(10) unsigned     | NO   | PRI | 0       |       | 
| DatBusOrder       | tinyint(3) unsigned  | NO   | PRI | 1       |       | 
| DatFormulaVersion | tinyint(3) unsigned  | NO   |     | 0       |       | 
+-------------------+----------------------+------+-----+---------+-------+ 
6 rows in set (0.00 sec) 

执行此查询时出现上述错误:

I get the aforementioned error when I execute this query:

mysql> insert ignore into S_3068 values (133, 15.82, 5542, 1339309260, 0, 1); 
ERROR 1136 (21S01): Column count doesn't match value count at row 1 

如您所见，列计数与值计数匹配.现在更令人困惑的是，该查询在SfmID = 132时可以正常工作:

As you can see, the column count does match the value count. Now what's even more puzzling is that the query works perfectly fine with SfmID = 132:

mysql> insert ignore into S_3068 values (132, 15.82, 5542, 1339309260, 0, 1); 
Query OK, 1 row affected (0.00 sec) 

SfmID是一个无符号的smallint，对我来说没有任何意义.

SfmID being a unsigned smallint, that doesn't make any sense to me.

在此问题上的任何帮助将不胜感激.

Any help on this matter would be greatly appreciated.

该错误是由与表相关联的触发器引起的.请查看评论以获取更多信息.

The error was caused by a trigger associated to the table. Please see comments for more information.

推荐答案

该错误是由与该表关联的触发器引起的，该触发器在另一个表上对值133进行了侧插入，但对值132却没有.是关于另一个表(列数的确是错误的)，而不是关于我要在其中插入数据的主表.

The error was caused by a trigger associated to the table, doing a side insert on another table for value 133 but not for value 132. The error issued by MySQL was about the other table (which column count was indeed wrong) and not about the main table in which I was inserting data.

这篇关于MySQL“列数与值数不匹配"但计数确实匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！