SQL sqlite中是否有自动增量？

Is there an auto increment in sqlite?

提问by ewok

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

我正在尝试primary key在Sqlite3 中创建一个自动递增的表。我不确定这是否真的可能，但我希望只需要指定其他字段。

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

Is this even possible?

这甚至可能吗？

I am running sqlite3under cygwinin Windows 7.

我在 Windows 7sqlite3下运行cygwin。

回答by Larry Lustig

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

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

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

如果包含类型为 INTEGER PRIMARY KEY 的列，则该列指向（是）自动 ROWID 列。

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.

正如您所期望的那样，每当您插入一行时，都会为 ROWID（无论您怎么称呼它）分配一个值。如果您在 INSERT 上显式分配一个非 NULL 值，它将获得该指定值而不是自动增量。如果在 INSERT 上显式分配 NULL 值，它将获得下一个自动递增值。

Also, you should try to avoid:

此外，您应该尽量避免：

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

and use

并使用

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

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

反而。第一个版本非常脆弱——如果您在表定义中添加、移动或删除列，则 INSERT 要么失败，要么产生不正确的数据（值在错误的列中）。

回答by Matt Hulse

Yes, this is possible. According to the SQLite FAQ:

是的，这是可能的。根据SQLite 常见问题解答：

A column declared INTEGER PRIMARY KEYwill autoincrement.

声明的列INTEGER PRIMARY KEY将自动递增。

回答by Matt Hulse

As of today — June 2018

截至今天 - 2018 年 6 月

---

---

Here is what official SQLite documentationhas to say on the subject (bold & italic are mine):

以下是官方 SQLite 文档对该主题的说明（粗体和斜体是我的）：

1. The AUTOINCREMENTkeyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

2. In SQLite, a column with type INTEGER PRIMARY KEY is an alias for theROWID(except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

3. On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is notexplicitly given a value, then it will be filled automatically with anunused integer, usuallyone more than the largest ROWID currently inuse.This is true regardless of whether or not the AUTOINCREMENT keyword is used.

4. If the AUTOINCREMENTkeyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuseof ROWIDs over the lifetime of the database.In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

1. 在AUTOINCREMENT关键字强加额外的CPU，内存，磁盘空间和磁盘I / O开销，如果不严格需要应该避免。通常不需要。

2. 在 SQLite 中，类型为INTEGER PRIMARY KEY的列是ROWID的别名（在 WITHOUT ROWID 表中除外），它始终是 64 位有符号整数。

3. 在 INSERT 中，如果 ROWID 或 INTEGER PRIMARY KEY 列没有明确指定一个值，那么它会自动填充一个未使用的整数，通常比当前使用的最大 ROWID 多一个。无论是否使用 AUTOINCREMENT 关键字，都是如此。

4. 如果AUTOINCREMENT关键字出现在 INTEGER PRIMARY KEY 之后，则会更改自动 ROWID 分配算法以防止在数据库的生命周期内重复使用ROWID。换句话说，AUTOINCREMENT 的目的是防止重复使用先前删除的行中的 ROWID。

回答by Uku Loskit

Have you read this? How do I create an AUTOINCREMENT field.

你读过这个吗？如何创建 AUTOINCREMENT 字段。

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

回答by sonvx

Beside rowid, you can define your own auto increment field but it is not recommended. It is always be better solution when we use rowid that is automatically increased.

除了 rowid，您还可以定义自己的自增字段，但不建议这样做。当我们使用自动增加的 rowid 时，它总是更好的解决方案。

The AUTOINCREMENTkeyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

该AUTOINCREMENT关键字会带来额外的 CPU、内存、磁盘空间和磁盘 I/O 开销，如果不是绝对需要，应避免使用。通常不需要。

Read herefor detailed information.

阅读此处了解详细信息。

回答by Denis Kutlubaev

One should not specify AUTOINCREMENTkeyword near PRIMARY KEY. Example of creating autoincrement primary key and inserting:

不应指定AUTOINCREMENT关键字 near PRIMARY KEY。创建自增主键并插入的示例：

$ sqlite3 ex1

CREATE TABLE IF NOT EXISTS room(room_id INTEGER PRIMARY KEY, name VARCHAR(25) NOT NULL, home_id VARCHAR(25) NOT NULL);

INSERT INTO room(name, home_id) VALUES ('test', 'home id test');

INSERT INTO room(name, home_id) VALUES ('test 2', 'home id test 2');

SELECT * FROM room;

will give:

会给：

1|test|home id test
2|test 2|home id test 2

回答by Kent Aguilar

I know this answer is a bit late.
My purpose for this answer is for everyone's reference should they encounter this type of challenge with SQLite now or in the future and they're having a hard time with it.

我知道这个答案有点晚了。
我的这个答案的目的是供每个人参考，如果他们现在或将来在 SQLite 中遇到这种类型的挑战，并且遇到困难时。

Now, looking back at your query, it should be something like this.

现在，回顾您的查询，它应该是这样的。

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

It works on my end. Like so,

它对我有用。像这样，

Just in case you are working with SQLite, I suggest for you to check out DB Browser for SQLite. Works on different platforms as well.

以防万一您正在使用 SQLite，我建议您查看DB Browser for SQLite。也适用于不同的平台。

回答by mkumar0304

SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. We can auto increment a field value by using AUTOINCREMENT keyword when creating a table with specific column name to auto incrementing it.

SQLite AUTOINCREMENT 是用于自动递增表中字段值的关键字。我们可以在创建具有特定列名的表时使用 AUTOINCREMENT 关键字自动增加字段值以自动增加它。

The keyword AUTOINCREMENT can be used with INTEGER field only. Syntax:

关键字 AUTOINCREMENT 只能与 INTEGER 字段一起使用。句法：

The basic usage of AUTOINCREMENT keyword is as follows:

AUTOINCREMENT 关键字的基本用法如下：

CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

For Example See Below: Consider COMPANY table to be created as follows:

例如，见下文： 考虑按如下方式创建 COMPANY 表：

sqlite> CREATE TABLE TB_COMPANY_INFO(
   ID INTEGER PRIMARY KEY   AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Now, insert following records into table TB_COMPANY_INFO:

现在，将以下记录插入表 TB_COMPANY_INFO：

INSERT INTO TB_COMPANY_INFO (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'MANOJ KUMAR', 40, 'Meerut,UP,INDIA', 200000.00 );

Now Select the record

现在选择记录

SELECT *FROM TB_COMPANY_INFO
ID      NAME            AGE     ADDRESS             SALARY
1       Manoj Kumar     40      Meerut,UP,INDIA     200000.00

回答by Luca Nanetti

What you do is correct, but the correct syntax for 'auto increment' should be without space:

您所做的是正确的，但“自动增量”的正确语法应该没有空格：

CREATE TABLE people (id integer primary key autoincrement, first_name string, last_name string);

(Please also note that I changed your varchars to strings. That's because SQLite internally transforms a varchar into a string, so why bother?)

（另请注意，我将您的 varchars 更改为字符串。那是因为 SQLite 在内部将 varchar 转换为字符串，所以何必呢？）

then your insert should be, in SQL language as standard as possible:

那么你的插入应该是尽可能标准的 SQL 语言：

INSERT INTO people(id, first_name, last_name) VALUES (null, 'john', 'doe');

while it is true that if you omit id it will automatically incremented and assigned, I personally prefer not to rely on automatic mechanisms which could change in the future.

虽然确实如果您省略 id 它会自动递增和分配，但我个人不喜欢依赖将来可能会改变的自动机制。

A note on autoincrement: although, as many pointed out, it is not recommended by SQLite people, I do not like the automatic reuse of ids of deleted records if autoincrement is not used. In other words, I likethat the id of a deleted record will never, ever appear again.

关于自动增量的说明：尽管正如许多人指出的那样，SQLite 人员不推荐这样做，但如果不使用自动增量，我不喜欢自动重用已删除记录的 id。换句话说，我喜欢被删除记录的 id 永远不会再出现。

HTH

HTH