在sqlite中有自动增量吗？ [英] Is there an auto increment in sqlite?

问题描述

我想在Sqlite3中创建一个自动递增主键的表。我不知道这是否真的可能，但我希望只需指定其他领域。例如：

I am trying to create a table with an auto-incrementing primary key in Sqlite3. I am not sure if this is really possible, but I am hoping to only have to designate the other fields. For example:

CREATE TABLE people (id integer primary key auto increment, first_name varchar(20), last_name varchar(20));

然后，当我添加一个值，我希望只需要做：

Then, when I add a value, I was hoping to only have to do:

INSERT INTO people
VALUES ("John", "Smith");

这是否可能？

在Windows 7中运行 sqlite3 在下的cygwin 。

I am running sqlite3 under cygwin in Windows 7.

推荐答案

你免费获得一个名为ROWID的。

You get one for free, called ROWID. This is in every SQLite table whether you ask for it or not.

如果你包括一个类型为INTEGER PRIMARY KEY的列，那么该列指向（是一个别名）自动ROWID列。

If you include a column of type INTEGER PRIMARY KEY, that column points at (is an alias for) the automatic ROWID column.

无论何时插入一行，ROWID（通过任何名称调用）都会被赋予一个值。如果在INSERT上显式指定非NULL值，它将获取指定的值，而不是自动递增。如果您在INSERT上显式指定NULL值，它将获得下一个自动递增值。

ROWID (by whatever name you call it) is assigned a value whenever you INSERT a row, as you would expect. If you explicitly assign a non-NULL value on INSERT, it will get that specified value instead of the auto-increment. If you explicitly assign a value of NULL on INSERT, it will get the next auto-increment value.

此外，您应该尽量避免：

Also, you should try to avoid:

 INSERT INTO people VALUES ("John", "Smith");

并使用

 INSERT INTO people (first_name, last_name) VALUES ("John", "Smith");

。第一个版本是非常脆弱的 - 如果你在表定义中添加，移动或删除列，INSERT将失败或产生不正确的数据（值在错误的列中）。

instead. The first version is very fragile — if you ever add, move, or delete columns in your table definition the INSERT will either fail or produce incorrect data (with the values in the wrong columns).

这篇关于在sqlite中有自动增量吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！