One of the more common error message in MySQL goes like this: “ERROR 1136 (21S01): Column count doesn’t match value count at row 1“.

This error typically occurs when you’re trying to insert data into a table, but the number of columns that you’re trying to insert don’t match the number of columns in the table.

In other words, you’re either trying to insert too many columns, or not enough columns.

To fix this issue, make sure you’re inserting the correct number of columns into the table.

Alternatively, you can name the columns in your INSERT statement so that MySQL knows which columns your data needs to be inserted into.

The error can also occur if you pass the wrong number of columns to a ROW() clause when using the VALUES statement.

Example of Error

Suppose we have the following table:

+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        2 |        3 |
|        4 |        5 |        6 |
+----------+----------+----------+

The following code will cause the error:

INSERT INTO t1 VALUES (7, 8, 9, 10);

Result:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

In this case, I tried to insert data for four columns into a table that only has three columns.

We’ll get the same error if we try to insert too few columns:

INSERT INTO t1 VALUES (7, 8);

Result:

ERROR 1136 (21S01): Column count doesn't match value count at row 1

Solution 1

The obvious solution is to insert the correct number of rows. Therefore, we could rewrite our code as follows:

INSERT INTO t1 VALUES (7, 8, 9);

Result:

Query OK, 1 row affected (0.00 sec)

Solution 2

Another way of doing it is to explicitly name the columns for which we want to insert data. This technique can be used to insert less columns than are in the table.

Example:

INSERT INTO t1 (column_0, column_1) VALUES (7, 8);

Result:

Query OK, 1 row affected (0.00 sec)

This method may result in a different error if there are any constraints that require a value to be passed for that column (for example, if the table has a NOT NULL constraint on that column). Therefore, you’ll need to ensure you’re complying with any constraints on the column when doing this.