sqlite中是否有自动增量?

2021-12-08 00:00:00 cygwin sql sqlite

我正在尝试在 中创建一个带有自动递增 primary key 的表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.

例如:

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 的 cygwin 下运行 sqlite3.

I am running sqlite3 under cygwin in Windows 7.

推荐答案

您可以免费获得一个,称为 ROWID.无论您是否要求,这都在每个 SQLite 表中.

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).

相关文章