Error 1136 - Column count doesn’t match value count at row 1

When trying to insert a new data row into a table, you might run into this error:

Column count doesn't match value count at row 1.

That error message typically means the number of values provided in the INSERT statement is bigger or smaller than the number of columns the table has, while at the same time, you did not specify the columns to be inserted. So MySQL doesn’t know which data to insert in which column and it throws back the error.

For example, you have this table employees:

CREATE TABLE employees (
  emp_no int(11) NOT NULL,
  birth_date date NOT NULL,
  first_name varchar(14) NOT NULL,
  last_name varchar(16) NOT NULL,
  gender enum('M','F') NOT NULL,
  hire_date date NOT NULL,
  email text,
  PRIMARY KEY (emp_no);

And you try to insert a new data rows into that table with this INSERT statement:

INSERT INTO employees
  VALUES('400000', '1990-09-09', 'Joe', 'Smith', 'M', '2009-09-11');

As you can see, there are 7 columns in the table employees but you are providing only 6 values in the INSERT statement. MySQL returns the error:

Column count doesn't match value count at row 1

To fix this

1. Provided the full required data

If you omit the column names when inserting data, make sure to provide a full row of data that matches the number of columns

INSERT INTO employees
  VALUES('400000', '1990-09-09', 'Joe', 'Smith', 'M', '2009-09-11', '[email protected]');

Or if the email field is empty:

INSERT INTO employees
  VALUES('800000', '1990-09-09', 'Joe', 'Smith', 'M', '2009-09-11', '');

2. Specify the columns to be inserted in case not all columns are going to have value.

INSERT INTO employees.employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
  VALUES('400000', '1990-09-09', 'Joe', 'Smith', 'M', '2009-09-11');

Sometimes, all the values are provided but you still see this error, you likely forgot to use the delimiter between two particular values and make it appear as one value. So double-check the delimiter and make sure you did not miss any semicolon.

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.