Error code 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.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS

Источник

Photo from Unsplash

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! 👍

Источник

Содержание

Database.Guide
Beginners
Categories
Fix “ERROR 1136 (21S01): Column count doesn’t match value count at row 1” when Inserting Data in MySQL
Example of Error
Solution 1
Solution 2
TablePlus
Error 1136 — Column count doesn’t match value count at row 1
To fix this
MySQL — How to fix the Column count doesn’t match value count at row 1 error
Level up your programming skills
About

Database.Guide

Beginners

Fix “ERROR 1136 (21S01): Column count doesn’t match value count at row 1” when Inserting Data in MySQL

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:

The following code will cause the error:

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:

Solution 1

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

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.

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.

Источник

TablePlus

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

September 13, 2019

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

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 :

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

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:

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

Or if the email field is empty:

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

Источник

MySQL — How to fix the Column count doesn’t match value count at row 1 error

Last Updated Jul 03, 2022

Learn how to fix the Column count doesn’t match value count at row 1 MySQL error Photo from Unsplash

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:

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:

But since MySQL requires 4 values for each column, the error gets triggered:

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:

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:

You can put as many column names as you need inside the parentheses.

Here’s another example of inserting data into three columns:

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:

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! 👍

Level up your programming skills

I’m sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I’ll send new stuff straight into your inbox!

About

Nathan Sebhastian is a software engineer with a passion for writing tech tutorials.
Learn JavaScript and other web development technology concepts through easy-to-understand explanations written in plain English.

Источник

You may get tis value, if you are missing the value for auto_increment column. The error is as follows −

mysql> insert into DemoTable1353 values('Chris',23);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

You need to provide the value for auto_increment or leave it to automatic generation.

Let us see an example and create a table −

mysql> create table DemoTable1353
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> Name varchar(20),
    -> Age int,
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.52 sec)

Insert some records in the table using insert command. We haven’t inserted auto increment value and it will generate on its own −

mysql> insert into DemoTable1353(Name,Age) values('Chris',23);
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable1353(Name,Age) values('David',21);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1353(Name,Age) values('Bob',24);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1353(Name,Age) values('John',47);
Query OK, 1 row affected (0.11 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1353;

This will produce the following output −

+----+-------+------+
| Id | Name  | Age  |
+----+-------+------+
|  1 | Chris |   23 |
|  2 | David |   21 |
|  3 | Bob   |   24 |
|  4 | John  |   47 |
+----+-------+------+
4 rows in set (0.00 sec)

Источник

I’m struggling here. This is my code

CREATE DEFINER=`****`@*****` PROCEDURE `setCountry`(
    IN `countryname` VARCHAR(100) CHARSET utf8mb4,
    IN `nationality` VARCHAR(100) CHARSET utf8mb4,
    IN `countryPrefix` INT(3)
)
    MODIFIES SQL DATA
INSERT INTO country(
    countryName,
    nationality,
    countryPrefix
)
SELECT
    @countryname
FROM
    (
    SELECT
        @countryname AS countryName,
        @nationality AS nationality,
        @countryprefix AS countryPrefix
) AS tmp
WHERE NOT EXISTS
    (
    SELECT
        countryName
    FROM
        country
    WHERE
        countryName = @countryname
)
LIMIT 1

And i’m trying to execute code with this sentence

call ccc.setCountry('Togo', 'Togolese', 228);

The ERROR it displays is this :

Error Code: 1136. Column count doesn’t match value count at row 1

asked Apr 29, 2020 at 14:15

You beed to select 3 columns for4 the insert.

and @countryname and countryName are different variables

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `setCountry`(
    IN `countryname` VARCHAR(100) CHARSET utf8mb4,
    IN `nationality` VARCHAR(100) CHARSET utf8mb4,
    IN `countryPrefix` INT(3)
)
    MODIFIES SQL DATA
BEGIN
      SET @countryname := countryName;
      SET  @nationality := nationality;
      SET @countryprefix := countryPrefix;

    INSERT INTO country(
        countryName,
        nationality,
        countryPrefix
    )
    SELECT    
        countryname,
        nationality,
        countryPrefix
    FROM
        (
        SELECT
            @countryname AS countryName,
            @nationality AS nationality,
            @countryprefix AS countryPrefix
    ) AS tmp
    WHERE NOT EXISTS
        (
        SELECT
            countryName
        FROM
            country
        WHERE
            countryName = @countryname
    )
    LIMIT 1;
END$$

DELIMITER ;

answered Apr 29, 2020 at 14:33

nbknbk

7,7395 gold badges12 silver badges27 bronze badges

Change from

SELECT
@countryname
FROM
(
SELECT
@countryname AS countryName,
@nationality AS nationality,
@countryprefix AS countryPrefix

SELECT
    @countryname AS countryName,
    @nationality AS nationality,
    @countryprefix AS countryPrefix

answered Apr 29, 2020 at 14:24

Источник

To fix this

Database.Guide

Beginners

Categories

Fix “ERROR 1136 (21S01): Column count doesn’t match value count at row 1” when Inserting Data in MySQL

Example of Error

Solution 1

Solution 2

TablePlus

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

To fix this

MySQL — How to fix the Column count doesn’t match value count at row 1 error

Level up your programming skills

About