使用具有WithoutRowID的主键 [英] Using a Primary Key with a WithoutRowID

问题描述

我试图从SQLite压缩每一点性能，但我遇到一个似乎很奇怪的问题，因为在SQLite中该功能似乎毫无意义.

I am trying to squeeze every little bit of performance out of SQLite and I have a problem which seems to be odd, in that the functionality seems pointless in SQLite.

例如:

CREATE TABLE "A_TEST" ( "ID" INTEGER PRIMARY KEY ,  "X" TEXT NULL) WITHOUT ROWID

然后尝试插入一条记录:

then try to insert a record:

Insert into A_TEST (X) VALUES('Test String')

您将收到"NOT NULL约束失败"的错误

You will get an error of "NOT NULL constraint failed"

这是否意味着在插入WithoutRowID时我必须指定自己的主键值?

Does this mean, with a WithoutRowID, I have to specify my own Primary Key Value when inserting?

我认为WithoutRowID没有意义的原因是:

The reason why I think the WithoutRowID is pointless is that:

1. 您必须指定自己的主键值，这会使任何大容量插入选择语句变得多余，因为在插入...时，我必须在主键中指定自己的值.

1. You have to specify your own Primary Key Value which makes any mass insert select statement redundant as I would have to specify my own value in the primary key when inserting....

实际上，如果我不使用WithoutRowID，我将拥有2个主键，因为SQLite会管理自己的RowID以及我的主键值.在1.7GB的数据库上，具有WithoutRowID可以将文件中的索引大小减小到1.3GB，因此400MB的差异可以节省很多.

I will in effect, have 2 primary keys if I don't use WithoutRowID, because SQLite manages its own RowID as well as my own Primary Key value. On the 1.7GB database, having WithoutRowID reduces the size of the indexes in the file to just 1.3GB so 400MB difference is pretty huge savings.

请告诉我，我不必提供自己的主键ID，而实际上，如果它是INTEGER，它将相对于主键提供唯一ID.

Please tell me that I don't have to provide my own Primary Key ID and that it will in fact provide a Unique ID against a Primary Key if it is an INTEGER.

推荐答案

这是否意味着，在没有WithoutRowID的情况下，我必须在插入时指定自己的主键值?

Does this mean, with a WithoutRowID, I have to specify my own Primary Key Value when inserting?

是的，但是您可以在插入时通过子选择自动生成它:

Yes, but you can generate it with a subselect automatically at the time of inserting:

CREATE TABLE "A_TEST" ( 
    "ID" INTEGER PRIMARY KEY,
    "X" TEXT NULL
) WITHOUT ROWID;

INSERT INTO A_TEST (ID,X) VALUES (
    (SELECT IFNULL(MAX(id),0)) + 1 FROM "A_TEST"),
    'Test String'
);

IFNULL(MAX(id),0)) + 1将为空表返回1，否则将比具有当前最大值的行多一个.

The IFNULL(MAX(id),0)) + 1 will return 1 for an empty table, and otherwise one more than the row with the current highest value.

这对于批量插入并不能保持不变，因为所有IFNULL(MAX(id),0)) + 1均被同时求值并且具有相同的值，从而导致唯一约束冲突.但是，您可以生成大量插入语句，以便第一个偏移量为+ 1，第二个偏移量为+ 2，依此类推:

This does not work unchanged for mass inserts, since all IFNULL(MAX(id),0)) + 1 are evaluated at the same time and would have the same value, leading to a unique constraint violation. However, you can generate the mass insert statements so that the offset is + 1 for the first one, + 2 for the second one and so on:

INSERT INTO A_TEST (ID,X) VALUES 
(
    (SELECT IFNULL(MAX(id),0)) + 1 FROM "A_TEST"),
    'Test String 1'
),
(
    (SELECT IFNULL(MAX(id),0)) + 2 FROM "A_TEST"),
    'Test String 2'
);

这篇关于使用具有WithoutRowID的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！