如何将数据插入具有自动递增主键的 MySQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8753371/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert data to MySQL having auto incremented primary key?
提问by Salman Raza
I've created a table with a primary key and enabled AUTO_INCREMENT, how do I get MYSQL use AUTO_INCREMENT?
我已经创建了一个带有主键的表并启用了AUTO_INCREMENT，如何使用 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 I used, I've tried "" and "1" for the first value but it doesn't work.
这是我使用的查询，我尝试了 "" 和 "1" 作为第一个值，但它不起作用。
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');
采纳答案by Celada
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'
);
回答by Adrian Cornish
Set the auto increment field to NULL or 0 if you want it to be auto magically assigned...
如果您希望它自动神奇地分配，请将自动增量字段设置为 NULL 或 0...
回答by Kzqai
The defaultkeyword works for me:
该default关键字的工作对我来说：
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 --version5.1.66:
我正在运行mysql --version5.1.66：
mysql Ver 14.14 Distrib **5.1.66**, for debian-linux-gnu (x86_64) using readline 6.1
回答by Amar Palsapure
回答by Xedret
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_INCREMENTcolumn, since you are supplying all the values you can do either one of the following options.
我在这里看到了三种可能性，它们将帮助您插入到您的表中，而不会弄得一团糟，但“指定”了AUTO_INCREMENT列的值，因为您提供了所有值，您可以执行以下任一选项。
First approach (Supplying NULL):
第一种方法（提供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):
第二种方法（提供''{Simple quote / apostrophes} 虽然它会给你一个警告）：
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.
只要包含所有值的顺序与创建表时定义的顺序相同，这些示例中的任何一个都应该足以插入到该表中。
