How to insert data to MySQL with auto-incremented column(field)?

Question

I've created a table with a primary key and enabled AUTO_INCREMENT:

CREATE TABLE IF NOT EXISTS test.authors (
    hostcheck_id INT PRIMARY KEY AUTO_INCREMENT,
    instance_id INT,
    host_object_id INT,
    check_type INT,
    is_raw_check INT,
    current_check_attempt INT,
    max_check_attempts INT,
    state INT,
    state_type INT,
    start_time datetime,
    start_time_usec INT,
    end_time datetime,
    end_time_usec INT,
    command_object_id INT,
    command_args VARCHAR(25),
    command_line VARCHAR(100),
    timeout int,
    early_timeout INT,
    execution_time DEC(18,5),
    latency DEC(18,3),
    return_code INT,
    output VARCHAR(50),
    long_output VARCHAR(50),
    perfdata VARCHAR(50)
);

Then, with the query below, I've tried "" and "1" for the first value but it doesn't work:

INSERT INTO  test.authors VALUES ('1','1','67','0','0','1','10','0','1',
'2012-01-03 12:50:49','108929','2012-01-03 12:50:59','198963','21','',
'/usr/local/nagios/libexec/check_ping  5','30','0','4.04159','0.102','1',
'PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms','',
'rta=2.860000m=0%;80;100;0');

So, how to insert data to MySQL with auto-incremented column(field)?

score 185 · Answer 1 · edited Jan 07 '14 at 07:10

185

Set the auto increment field to NULL or 0 if you want it to be auto magically assigned...

edited Jan 07 '14 at 07:10

mac

627
1
9
21

answered Jan 06 '12 at 04:36

Adrian Cornish

23,227
13
61
77

1

This works for me unlike skipping autoincrement column value. – ASten Mar 03 '17 at 12:19
12

Prefer `NULL`, as `0`'s behaviour can be affected by [NO_AUTO_VALUE_ON_ZERO](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_auto_value_on_zero). – BenMorel Jun 18 '18 at 23:04
yup, this is how you can insert using a select instead of values – grantr Apr 26 '22 at 15:22

score 86 · Accepted Answer · answered Jan 06 '12 at 04:34

86

In order to take advantage of the auto-incrementing capability of the column, do not supply a value for that column when inserting rows. The database will supply a value for you.

INSERT INTO test.authors (
   instance_id,host_object_id,check_type,is_raw_check,
   current_check_attempt,max_check_attempts,state,state_type,
   start_time,start_time_usec,end_time,end_time_usec,command_object_id,
   command_args,command_line,timeout,early_timeout,execution_time,
   latency,return_code,output,long_output,perfdata
) VALUES (
   '1','67','0','0','1','10','0','1','2012-01-03 12:50:49','108929',
   '2012-01-03 12:50:59','198963','21','',
   '/usr/local/nagios/libexec/check_ping  5','30','0','4.04159',
   '0.102','1','PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms',
   '','rta=2.860000m=0%;80;100;0'
);

answered Jan 06 '12 at 04:34

Celada

21,627
4
64
78

but the query you pasted for me you put value for primery key?? – Salman Raza Jan 06 '12 at 04:45
3

@SalmanRaza: no, look: the column "hostcheck_id" is missing from the query I suggested. I gave values for only 23 columns but there are 24 columns in the table. I guess Adrian's suggestion would work too, but I didn't know you could do that! – Celada Jan 06 '12 at 04:51
4

@Celada great thing about this site - you learn something even when your own answer is correct :-) – Adrian Cornish Jan 06 '12 at 05:01
1

@All thanks for your reply ! it help me out i pass '0' value for primery key column – Salman Raza Jan 06 '12 at 06:05

Kzqai · Answer 3 · 2015-01-15T15:23:08.500

36

The default keyword works for me:

mysql> insert into user_table (user_id, ip, partial_ip, source, user_edit_date, username) values 
(default, '39.48.49.126', null, 'user signup page', now(), 'newUser');
---
Query OK, 1 row affected (0.00 sec)

I'm running mysql --version 5.1.66:

mysql  Ver 14.14 Distrib **5.1.66**, for debian-linux-gnu (x86_64) using readline 6.1

edited Jan 15 '15 at 15:23

answered Jun 28 '13 at 14:08

Kzqai

22,588
25
105
137

You can leave the column names out as long as the values match the order of the columns. It may be considered bad practice to do it, but for a one-time quick insert it's a good solution. – Shahar Oct 20 '15 at 14:24
@Shahar This is much better than leaving the column name out. By this way, you have an better idea of what is going on in your SQL – Ikhlak S. Apr 11 '16 at 12:51
@user3284463 I totally agree, I just think it's worth mentioning. – Shahar Apr 11 '16 at 13:20
1

Yeah, the problem is that the order may match NOW but won't necessarily match when the sql is run LATER. – Kzqai Oct 25 '16 at 17:02
Thanks a lot, for potgres 10.10 is not permitted to put 0 or null values, but with default it works; – tarmogoyf Feb 10 '20 at 15:48

score 13 · Answer 4 · answered Jan 06 '12 at 04:36

13

Check out this post

According to it

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

answered Jan 06 '12 at 04:36

Amar Palsapure

9,590
1
27
46

score 11 · Answer 5 · answered Jun 13 '19 at 00:06

I see three possibilities here that will help you insert into your table without making a complete mess but "specifying" a value for the AUTO_INCREMENT column, since you are supplying all the values you can do either one of the following options.

First approach (Supplying NULL):

INSERT INTO test.authors VALUES (
 NULL,'1','67','0','0','1','10','0','1','2012-01-03 12:50:49','108929',
 '2012-01-03 12:50:59','198963','21','',
 '/usr/local/nagios/libexec/check_ping  5','30','0','4.04159',
 '0.102','1','PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms',
 '','rta=2.860000m=0%;80;100;0'
);

Second approach (Supplying '' {Simple quotes / apostrophes} although it will give you a warning):

INSERT INTO test.authors VALUES (
 '','1','67','0','0','1','10','0','1','2012-01-03 12:50:49','108929',
 '2012-01-03 12:50:59','198963','21','',
 '/usr/local/nagios/libexec/check_ping  5','30','0','4.04159',
 '0.102','1','PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms',
 '','rta=2.860000m=0%;80;100;0'
);

Third approach (Supplying default):

INSERT INTO test.authors VALUES (
 default,'1','67','0','0','1','10','0','1','2012-01-03 12:50:49','108929',
 '2012-01-03 12:50:59','198963','21','',
 '/usr/local/nagios/libexec/check_ping  5','30','0','4.04159',
 '0.102','1','PING WARNING -DUPLICATES FOUND! Packet loss = 0%, RTA = 2.86 ms',
 '','rta=2.860000m=0%;80;100;0'
);

Either one of these examples should suffice when inserting into that table as long as you include all the values in the same order as you defined them when creating the table.

Super Kai - Kazuya Ito · Answer 6 · 2022-09-09T18:04:43.297

For auto-incremented column(field):

CREATE TABLE person (
    id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

You can use NULL or 0 to insert an auto-incremented value as shown below:

                        -- Here
INSERT INTO person VALUES (NULL, "John", "Smith")

                        -- Here
INSERT INTO person VALUES (0, "John", "Smith")

score -1 · Answer 7 · answered Feb 11 '21 at 15:02

-1

I used something like this to type only values in my SQL request. There are too much columns in my case, and im lazy.

insert into my_table select max(id)+1, valueA, valueB, valueC.... from my_table;

answered Feb 11 '21 at 15:02

Kilian

1,753
13
21

How to insert data to MySQL with auto-incremented column(field)?

7 Answers7

Linked

Related