I have the following existing table in a mysql database:
+---------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+----------------+
| f1 | int(11) | NO | PRI | NULL | auto_increment |
| field2 | int(11) | NO | MUL | NULL | |
| field3 | int(11) | YES | | NULL | |
| field4 | int(11) | YES | | NULL | |
| field6 | varchar(64) | YES | | NULL | |
| field7 | varchar(16) | YES | | NULL | |
| field8 | varchar(32) | YES | | NULL | |
| field9 | varchar(128) | YES | | NULL | |
| field10 | varchar(128) | YES | | NULL | |
| field11 | varchar(128) | YES | | NULL | |
| field12 | varchar(64) | YES | | NULL | |
| field13 | varchar(32) | YES | | NULL | |
| field14 | varchar(32) | YES | | NULL | |
| field15 | int(11) | YES | MUL | NULL | |
| field16 | date | YES | | NULL | |
| field17 | date | YES | | NULL | |
| field18 | int(11) | YES | MUL | NULL | |
| field19 | varchar(64) | YES | | NULL | |
| field20 | varchar(64) | YES | | NULL | |
| field21 | varchar(16) | YES | | NULL | |
| field22 | varchar(20) | YES | | NULL | |
| field23 | varchar(1000) | YES | | NULL | |
| field24 | int(11) | NO | MUL | NULL | |
| field25 | int(11) | NO | | 0 | |
| field26 | decimal(19,2) | YES | | 0.00 | |
| field27 | decimal(19,2) | YES | | 0.00 | |
| field28 | int(11) | YES | MUL | NULL | |
| field29 | int(11) | YES | MUL | NULL | |
| field30 | varchar(128) | YES | | NULL | |
| field31 | varchar(128) | YES | | NULL | |
| field32 | varchar(16) | YES | | NULL | |
| field33 | int(11) | YES | | NULL | |
| field34 | int(11) | YES | | NULL | |
| field35 | varchar(128) | YES | | NULL | |
| field36 | int(11) | YES | MUL | NULL | |
| field37 | int(11) | YES | | NULL | |
+---------------------+---------------+------+-----+---------+----------------+
I try the following statement to add another row and I’m getting the following error:
ERROR 1136 (21S01): Column count doesn't match value count at row 1
Here are the ways I’ve tried to insert the row into the table:
insert into table (Field, Type, Null, Key, Default, Extra) VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
insert into table VALUES ('contract_expiration','date','YES','','NULL','');
Both return the same error. There are no triggers on the table, I’m not sure what’s going on.
Any suggestions? I’m relatively new to mysql administration, I know a bit but this has me stumped and searches for solutions have turned up nothing.
Any help that could be provided would be MUCH appreciated!
BlitZ
12.1k3 gold badges49 silver badges68 bronze badges
asked Feb 14, 2014 at 17:39
1
NULL is not a valid field name in:
insert into `table`(Field, Type, Null, Key, Default, Extra)
VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
And Key and default are reserved words. Try this:
insert into `table`(Field, `Type`, `Null`, `Key`, `Default`, Extra)
VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
answered Feb 14, 2014 at 17:41
Gordon LinoffGordon Linoff
1.2m58 gold badges647 silver badges791 bronze badges
7
mysql> desc classroom; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | clId | int(11) | NO | PRI | NULL | auto_increment | | clFName | varchar(30) | NO | | NULL | | | clSName | varchar(10) | NO | | NULL | | | clCapc | int(3) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> select * from classroom; +------+------------+---------+--------+ | clId | clFName | clSName | clCapc | +------+------------+---------+--------+ | 1 | Classroom1 | cl1 | 100 | | 2 | 2 | 2 | 2 | | 3 | 3f | 3s | 3 | | 4 | 3f | 3s | 3 | | 5 | class4 | class4 | 100 | +------+------------+---------+--------+ 5 rows in set (0.00 sec)
I also have same error
mysql> insert into classroom values('Gudadhe', 'Akash', 20);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
This error can be solved by specifying column names before inserting directly as follows
By writing classroom(clFName, clSName, clCapc) we are specifying columns into which we want to insert values
mysql> insert into classroom(clFName, clSName, clCapc) values('Gudadhe', 'Akash', 20);
Query OK, 1 row affected (0.06 sec)
answered Oct 8, 2018 at 9:41
AKASH GUDADHEAKASH GUDADHE
3171 gold badge6 silver badges15 bronze badges
I had a similar case, where I took the table definition from a dev server and the data from a live server, and it turned out that they were actually not quite the same.
The way I found out the difference was (there are probably smarter ways to do it, but this is how I did it):
SHOW CREATE TABLE mytable;
I ran this on all 3 cases (live database, dev database, and new database I created using CREATE TABLE xxx like xxx).
Then I simply compared the 3 and found that the live and dev had a different set of columns, so I simply ran
ALTER TABLE xxx DROP yyy;
until the new table was the same as the table the dump was from; then I could import data.
answered Jul 3, 2014 at 10:20
SverreSverre
1,31813 silver badges21 bronze badges
1
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.
Remove the brackets for Values(..), i.e., it should be Values (..), (..) instead.
insert into user(username,password,email,created,last_updated)
values
('TEST USERNAME','TEST PASSWORD','Test@test.com',current_timestamp(),current_timestamp()),
('TEST USERNAME 2','TEST PASSWORD 2','Test2@test.com',current_timestamp(),current_timestamp());
From Docs, the syntax is:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] {VALUES | VALUE} (value_list) [, (value_list)] ... [ON DUPLICATE KEY UPDATE assignment_list]…
INSERT statements that use VALUES syntax can insert multiple rows. To
do this, include multiple lists of comma-separated column values, with
lists enclosed within parentheses and separated by commas. Example:INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
I have the following existing table in a mysql database:
+---------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+----------------+
| f1 | int(11) | NO | PRI | NULL | auto_increment |
| field2 | int(11) | NO | MUL | NULL | |
| field3 | int(11) | YES | | NULL | |
| field4 | int(11) | YES | | NULL | |
| field6 | varchar(64) | YES | | NULL | |
| field7 | varchar(16) | YES | | NULL | |
| field8 | varchar(32) | YES | | NULL | |
| field9 | varchar(128) | YES | | NULL | |
| field10 | varchar(128) | YES | | NULL | |
| field11 | varchar(128) | YES | | NULL | |
| field12 | varchar(64) | YES | | NULL | |
| field13 | varchar(32) | YES | | NULL | |
| field14 | varchar(32) | YES | | NULL | |
| field15 | int(11) | YES | MUL | NULL | |
| field16 | date | YES | | NULL | |
| field17 | date | YES | | NULL | |
| field18 | int(11) | YES | MUL | NULL | |
| field19 | varchar(64) | YES | | NULL | |
| field20 | varchar(64) | YES | | NULL | |
| field21 | varchar(16) | YES | | NULL | |
| field22 | varchar(20) | YES | | NULL | |
| field23 | varchar(1000) | YES | | NULL | |
| field24 | int(11) | NO | MUL | NULL | |
| field25 | int(11) | NO | | 0 | |
| field26 | decimal(19,2) | YES | | 0.00 | |
| field27 | decimal(19,2) | YES | | 0.00 | |
| field28 | int(11) | YES | MUL | NULL | |
| field29 | int(11) | YES | MUL | NULL | |
| field30 | varchar(128) | YES | | NULL | |
| field31 | varchar(128) | YES | | NULL | |
| field32 | varchar(16) | YES | | NULL | |
| field33 | int(11) | YES | | NULL | |
| field34 | int(11) | YES | | NULL | |
| field35 | varchar(128) | YES | | NULL | |
| field36 | int(11) | YES | MUL | NULL | |
| field37 | int(11) | YES | | NULL | |
+---------------------+---------------+------+-----+---------+----------------+
I try the following statement to add another row and I’m getting the following error:
ERROR 1136 (21S01): Column count doesn't match value count at row 1
Here are the ways I’ve tried to insert the row into the table:
insert into table (Field, Type, Null, Key, Default, Extra) VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
insert into table VALUES ('contract_expiration','date','YES','','NULL','');
Both return the same error. There are no triggers on the table, I’m not sure what’s going on.
Any suggestions? I’m relatively new to mysql administration, I know a bit but this has me stumped and searches for solutions have turned up nothing.
Any help that could be provided would be MUCH appreciated!
BlitZ
12.1k3 gold badges49 silver badges68 bronze badges
asked Feb 14, 2014 at 17:39
1
NULL is not a valid field name in:
insert into `table`(Field, Type, Null, Key, Default, Extra)
VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
And Key and default are reserved words. Try this:
insert into `table`(Field, `Type`, `Null`, `Key`, `Default`, Extra)
VALUES ("contract_expiration", "date", "YES", "", "NULL", "");
answered Feb 14, 2014 at 17:41
Gordon LinoffGordon Linoff
1.2m58 gold badges647 silver badges791 bronze badges
7
mysql> desc classroom; +---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | clId | int(11) | NO | PRI | NULL | auto_increment | | clFName | varchar(30) | NO | | NULL | | | clSName | varchar(10) | NO | | NULL | | | clCapc | int(3) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> select * from classroom; +------+------------+---------+--------+ | clId | clFName | clSName | clCapc | +------+------------+---------+--------+ | 1 | Classroom1 | cl1 | 100 | | 2 | 2 | 2 | 2 | | 3 | 3f | 3s | 3 | | 4 | 3f | 3s | 3 | | 5 | class4 | class4 | 100 | +------+------------+---------+--------+ 5 rows in set (0.00 sec)
I also have same error
mysql> insert into classroom values('Gudadhe', 'Akash', 20);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
This error can be solved by specifying column names before inserting directly as follows
By writing classroom(clFName, clSName, clCapc) we are specifying columns into which we want to insert values
mysql> insert into classroom(clFName, clSName, clCapc) values('Gudadhe', 'Akash', 20);
Query OK, 1 row affected (0.06 sec)
answered Oct 8, 2018 at 9:41
AKASH GUDADHEAKASH GUDADHE
3171 gold badge6 silver badges15 bronze badges
I had a similar case, where I took the table definition from a dev server and the data from a live server, and it turned out that they were actually not quite the same.
The way I found out the difference was (there are probably smarter ways to do it, but this is how I did it):
SHOW CREATE TABLE mytable;
I ran this on all 3 cases (live database, dev database, and new database I created using CREATE TABLE xxx like xxx).
Then I simply compared the 3 and found that the live and dev had a different set of columns, so I simply ran
ALTER TABLE xxx DROP yyy;
until the new table was the same as the table the dump was from; then I could import data.
answered Jul 3, 2014 at 10:20
SverreSverre
1,31813 silver badges21 bronze badges
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
