“列计数与行处的值计数不匹配”但它是 [英] "Column count doesn't match value count at row" but it does

问题描述

我有一个超过14000个语句的mysql文件。
所有这些都是插入到表中，当我使用控制台导入文件时，它会引发以下错误：

  ERROR 1136（21S01）在第1548行：列计数与第45行的值计数不匹配
  

我知道当列名称中指定的行不同于VALUES列表中指定的行，但是我已经检查了多次，列数在两个列表中是完全相同的。

位置45的行是：

 （'00553'，'AAA' 'BBB'，'CCC'，'XXXXXXXXR'，'用户地址'，'西班牙'，'西班牙'，'39212'，'1900-01-21'，'123456789'，'M' 0，'AAA'，'BBB'，'CCC'，'XXXXXXXXR'，NULL，'888993344'，''，''，''，'' 26列
  

和与其关联的插入行，上面的许多行是：

  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 
  

我找到了这个错误，但似乎只有当count不同时才出现，

编辑：忘记提及：if I直接在phpmyadmin上抛出sql语句，它正确地添加行，没有任何错误。它只在从命令行运行时崩溃，尽管它上面的语句正确插入到数据库中，它们几乎与此相同。

EDIT2：我已删除所有行直到46，当我启动文件erros是以下：

 错误1136（21S01）在行1503：列计数与第45行的值计数不匹配
  

它只更改行， INSERT语句，它与文件其余部分中的其他INSERT语句完全相同。

解决方案

我遇到了类似的问题，因为在数据中插入圆括号： - （：

  INSERT INTO客户（姓名，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），
 ，600.0，2013-03-01）
）; 
  

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:

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.

The row in position 45 is:

('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.

EDIT2: I've removed all lines until the 46 and when I launch the file the erros is the following:

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.

解决方案

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")
  ); 

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