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:
The following 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);
The 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! 👍
