When you run an
INSERT statement, you might see MySQL responding with
Column count doesn't match value count at row 1 error.
This error occurs when the number of columns that your table has and the number of values you try to insert into the table is not equal.
For example, suppose you have a
pets table with 4 columns as follows:
+----+--------+---------+------+ | id | owner | species | age | +----+--------+---------+------+ | 1 | Jessie | bird | 2 | | 2 | Ann | duck | 3 | | 3 | Joe | horse | 4 | | 4 | Mark | dog | 4 | | 5 | Ronald | cat | 1 | +----+--------+---------+------+
When you want to insert data into the
pets table above, you need to provide values for all 4 columns, or you will trigger the
column count doesn't match value count error:
INSERT statement only provides one value for the table:
INSERT INTO pets VALUES("Jack");
But since MySQL requires 4 values for each column, the error gets triggered:
ERROR 1136 (21S01): Column count doesn't match value count at row 1
To resolve this error, you can either:
- Provide values equal to the number of columns you have in your table
- Or specify the column table you wish to insert values
Let’s learn how to do both.
First, you can specify the values for each column as in the following statement:
INSERT INTO pets VALUES(NULL, "Jack", "duck", 2);
The above statement will work because the
pets table has four columns and four values are provided in the statement.
But if you only have values for specific columns in your table, then you can specify the column name(s) that you want to insert into.
Here’s an example of inserting value only to the
species column. Note that the column name is added inside parentheses after the table name:
INSERT INTO pets(`species`) VALUES("panda");
You can put as many column names as you need inside the parentheses.
Here’s another example of inserting data into three columns:
INSERT INTO pets(`owner`, `species`, `age`) VALUES("Bruce", "tiger", 5);
id column is omitted in the example above.
Keep in mind that the same error will happen if you have more values to insert than the table columns.
Both statements below will return the error:
-- 👇 trying to insert five values INSERT INTO pets VALUES(NULL, "Jack", "duck", 2, TRUE); -- 👇 trying to insert two values, but specify three columns INSERT INTO pets(`owner`, `species`, `age`) VALUES("Jane", "cat");
By default, MySQL will perform an
INSERT statement expecting each column to have a new value.
When you specify the column names that you want to insert new values into, MySQL will follow your instructions and remove the default restrictions of inserting to all columns.
And those are the two ways you can fix the
Column count doesn't match value count at row 1 error in MySQL database server.
I’ve also written several articles on fixing other MySQL errors. I’m leaving the links here in case you need help in the future:
- Fix MySQL Failed to open file error 2
- Fix MySQL skip grant tables error
- Solve MySQL unknown column in field list error
And that’s it for column count doesn’t match value count error.
Thanks for reading! 👍