How to insert data into MySQL with the addition of a primary key with automatic addition?

Question

I created a table with a primary key and enabled AUTO_INCREMENT , how can I use MYSQL 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 that I used, I tried "and" 1 "for the first value, but it does not 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'); 

Answer 1

To take advantage of the automatic increment of a column, do not add a value for this column when inserting rows. The database will provide you with value.

 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' ); 

Answer 2

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

Answer 3

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 run mysql --version 5.1.66:

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

Answer 4

Check out this post

According to this

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

Answer 5

I see three possibilities that will help you insert into your table without making a mess, but by “specifying” a value for the AUTO_INCREMENT column, since you provide all the values, you can do one of the following.

First approach (By providing 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' ); 

The second approach (Providing ' {Simple quotes / apostrophes}, although this 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 ( 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' ); 

When pasting into this table, one of these examples is sufficient if you include all the values ​​in the same order in which you defined them when creating the table.