Column count doesn't match value count at row

问题:

So I read the other posts but this question is unique. So this SQL dump file has this as the last entry.

INSERT INTO `wp_posts` VALUES(2781, 3, '2013-01-04 17:24:19', '2013-01-05 00:24:19'.

I'm trying to insert this value to the table...

INSERT INTO `wp_posts` VALUES(5, 5, '2005-04-11 09:54:35', '2005-04-11 17:54:35'

it gives me the error, "Column count doesn't match value count at row 1." So I'm lost on the concept of how the column and row apply here.

Doesn't 2781,3 mean row 2781 and column 3? And doesn't 5,5 mean row 5 and column 5?

回答1:

The error means that you are providing not as much data as the table wp_posts does contain columns. And now the DB engine does not know in which columns to put your data.

To overcome this you must provide the names of the columns you want to fill. Example:

insert into wp_posts (column_name1, column_name2)
values (1, 3)

Look up the table definition and see which columns you want to fill.

And insert means you are inserting a new record. You are not modifying an existing one. Use update for that.

回答2:

1. you missed the comma between two values or column name
2. you put extra values or an extra column name

回答3:

You should also look at new triggers.

MySQL doesn't show the table name in the error, so you're really left in a lurch. Here's a working example:

use test;
create table blah (id int primary key AUTO_INCREMENT, data varchar(100));
create table audit_blah (audit_id int primary key AUTO_INCREMENT, action enum('INSERT','UPDATE','DELETE'), id int, data varchar(100) null);
insert into audit_blah(action, id, data) values ('INSERT', 1, 'a');
select * from blah;
select * from audit_blah;
truncate table audit_blah;

delimiter //
/* I've commented out "id" below, so the insert fails with an ambiguous error: */
create trigger ai_blah after insert on blah for each row 
begin 
  insert into audit_blah (action, /*id,*/ data) values ('INSERT', /*NEW.id,*/ NEW.data);
end;//

/* This insert is valid, but you'll get an exception from the trigger: */
insert into blah (data) values ('data1');

回答4:

MySQL will also report "Column count doesn't match value count at row 1" if you try to insert multiple rows without delimiting the row sets in the VALUES section with parentheses, like so:

INSERT INTO `receiving_table`
  (id,
  first_name,
  last_name)
VALUES 
  (1002,'Charles','Babbage'),
  (1003,'George', 'Boole'),
  (1001,'Donald','Chamberlin'),
  (1004,'Alan','Turing'),
  (1005,'My','Widenius');