通过SQL语句手动插入表中，但键是自动增量的

Let's say I have a table of users and the id column is the primary key and auto incremented.

假设我有一个用户表，id列是主键并自动递增。

I want to just try and add user manually by this statement:

我想尝试通过以下语句手动添加用户：

INSERT INTO table_name (id, username, password)  
VALUES (?, Mike, Mike);

but I don't want to specify the ? value, just want to add to the next row.

但我不想指定？值，只想添加到下一行。

Thanks everyone, I forgot to set the id column to auto increment. it works now.

谢谢大家，我忘了将id列设置为自动增量。它现在有效。

4 个解决方案

#1

So just don't use it...do it like this..

所以只是不要使用它...这样做..

And be sure you use single quotes for inserting strings

并确保使用单引号插入字符串

INSERT INTO table_name (username, password)
VALUES ('Mike', 'Mike');

As you said your field is auto incremented, SQL will automatically increment the value of the id field by 1

如您所说，您的字段是自动递增的，SQL将自动将id字段的值递增1

#2

If you want to manually insert a value to an auto incremented column you can use the IDENTITY_INSERT of sql. for example

如果要手动将值插入自动递增列，可以使用sql的IDENTITY_INSERT。例如

SET IDENTITY_INSERT MyTable ON
insert into MyTable(id, name) values (1,'asdf');
insert into MyTable(id, name) values (3,'htgfds');
insert into MyTable(id, name) values (123,'dsfg');
SET IDENTITY_INSERT MyTable OFF

you can read more here IDENTITY_INSERT

你可以在这里阅读更多IDENTITY_INSERT

#3

If id is a auto incremented value just leave that column out of the insert and it will fill in with the auto incremented value. For example from BOL http://msdn.microsoft.com/en-us/library/aa933196(v=SQL.80).aspx

如果id是一个自动递增的值，只需将该列从插入中删除，它将使用自动递增的值填充。例如来自BOL http://msdn.microsoft.com/en-us/library/aa933196(v=SQL.80).aspx

CREATE TABLE new_employees
(
 id_num int IDENTITY(1,1),
 fname varchar (20),
 minit char(1),
 lname varchar(30)
)

INSERT new_employees
   (fname, minit, lname)
VALUES
   ('Karin', 'F', 'Josephs')

INSERT new_employees
   (fname, minit, lname)
VALUES
   ('Pirkko', 'O', 'Koskitalo')

#4

This should do the job for you:

这应该为你做的工作：

INSERT INTO table_name (username, password)
VALUES('username_u','password_p');

The autoincrement value will be automatically added by SQL.

自动增量值将由SQL自动添加。

#1