SQLite起始行ID为0 [英] SQLite starting rowid of 0
问题描述
我正在尝试建立一个SQLite表，其中rowid从0开始而不是默认值1.最终目标是能够运行第一个INSERT语句并将其插入到rowid 0.对于第一个INSERT，该选项为0.
I'm trying to set up a SQLite table where the rowid starts from 0 instead of the default 1. The end goal is to be able to run the first INSERT statement and have it insert to rowid 0. Explicitly setting rowid to 0 for that first INSERT is not an option.
我尝试了一些与AUTOINCREMENT相关的事情，但没有运气能使它正常工作.我发现的唯一成功方法是插入rowid为-1的行，然后稍后将其删除.这行得通，但是很乱，我想找到一种更干净的方法.我正在使用内置的sqlite3库在Python 2.7中工作.
I've tried a few things related to AUTOINCREMENT but am not having any luck getting this to work cleanly. The only successful way I've found is to insert a row with rowid of -1 and then delete it later. This works but it's messy and I'd like to find a cleaner way of doing it. I am working in Python 2.7 with the built-in sqlite3 library.
最重要的问题:
除了手动插入-1值然后在以后将其删除之外，是否有一种更干净的方法从0开始启动rowid?
Is there a cleaner way to start rowid from 0 other than manually inserting a -1 value and then removing it later?
一些附带信息:
我在这里找到了类似的问题，并使用了一些AUTOINCREMENT设置:设置开始SQLite中AUTOINCREMENT的值
I found a similar question here and played with some AUTOINCREMENT settings: Set start value for AUTOINCREMENT in SQLite
sqlite_sequence表似乎不适用于负数.我用以下方法对其进行了测试:
The sqlite_sequence table doesn't seem to work with negative numbers. I used the following to test it:
import sqlite3
con = sqlite3.Connection('db.db')
cur = con.cursor()
cur.execute("CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)")
cur.execute("INSERT INTO sqlite_sequence (name,seq) VALUES (?,?)", ('test',-1))
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 1
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 2
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 3
cur.execute("SELECT rowid, id, val FROM test")
print cur.fetchall()
在sqlite_sequence中插入-1时，应将下一个rowid设置为0，但使用的是1.如果将sqlite_sequence初始化为正数，则行号将与预期的一样.
With the -1 inserted into sqlite_sequence it should set the next rowid to 0, but it's using 1 instead. If sqlite_sequence is initialized to a positive number the rowids are as expected.
import sqlite3
con = sqlite3.Connection('db.db')
cur = con.cursor()
cur.execute("CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, val TEXT)")
cur.execute("INSERT INTO sqlite_sequence (name,seq) VALUES (?,?)", ('test',10))
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 11
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 12
cur.execute("INSERT INTO test (val) VALUES (?)", ('testval',)) #becomes rowid 13
cur.execute("SELECT rowid, id, val FROM test")
print cur.fetchall()
自动递增不支持这样的负数吗?我在SQLite文档中找不到任何提及.
Does auto-increment not support negative numbers like this? I couldn't find any mention of it in the SQLite documentation.
推荐答案
文档表示，使用AUTOINCREMENT，
The documentation says that, with AUTOINCREMENT,
为新行选择的ROWID比该表中以前存在的最大ROWID至少大一个.
the ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.
因此，该算法不仅查看 sqlite_sequence 表中的值，而且还查看表中的最后一行，并使用这两个值中的较大者.
So the algorithm looks not only at the value in the sqlite_sequence table, but also at the last row in the table, and uses the larger of these two values.
当表为空时，将最大的实际rowid假定为零.这样做是为了使第一个插入的rowid变为 1 .
When the table is empty, the largest actual rowid is instead assumed to be zero. This is done so that the first inserted rowid becomes 1.
因此，生成小于一个rowid的唯一方法是在表中已有另一行.
Therefore, the only way to generate a rowid less than one is to have another row already in the table.
这篇关于SQLite起始行ID为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
