How to insert data to MySQL having auto incremented primary key

Question

I ve created a table with a primary key and enabled AUTO INCREMENT  how do I get MYSQL use 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       Here is the query I used  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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

The default keyword works for me   mysql gt  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

[mysql] How to insert data to MySQL having auto incremented primary key?

Examples related to mysql

Examples related to insert