sqlite3中整数主键没有自动增量 [英] No autoincrement for Integer Primary key in sqlite3

问题描述

在 sqlite3 faq 中，提到一个整数主键被提供给一个空值会自动递增.但这不会发生在我身上.

In the sqlite3 faq, it is mentioned that an integer primary key being fed a null value would autoincrement. But this is not happening for me.

复制，sqlite3中的一个表，CREATE TABLE dummy(serial_num INTEGER PRIMARY KEY, name TEXT);并用python填充，

to replicate, a table in sqlite3, CREATE TABLE dummy( serial_num INTEGER PRIMARY KEY, name TEXT); and fill it using python,

import sqlite3 as lite
con = lite.connect('some.db')
cur=con.cursor()
data = "someone's name"
cur.execute("INSERT INTO dummy VALUES(NULL, ?)", data)
con.commit()

第一个属性 serial_num 显示为空白，而 name 属性很好.当我执行 SELECT serial_num FROM dummy 时，我只会得到一堆空格.我做错了什么?

The first attribute serial_num is being shown blank while the name attribute is fine. When I do SELECT serial_num FROM dummy I just get a bunch of blank spaces. What am I doing wrong?

推荐答案

这是 SQLite 的怪癖之一.来自精美手册:

This is one of SQLite's quirks. From the fine manual:

根据 SQL 标准，PRIMARY KEY 应始终表示 NOT NULL.不幸的是，由于长期的编码监督，SQLite 并非如此.除非该列是 INTEGER PRIMARY KEY SQLite 允许 PRIMARY KEY 列中的 NULL 值.我们可以更改 SQLite 以符合标准(我们将来可能会这样做)，但是当发现疏忽时，SQLite 已被广泛使用，以至于我们担心如果修复问题会破坏遗留代码.

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY SQLite allows NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem.

关于INTEGER PRIMARY KEY 的文档对于列成为这样的确切要求有一点不清楚特殊的 INTEGER PRIMARY KEY 自动递增，但实际情况是，如果您想在插入时使用 NULL 值来表示给我下一个自动递增值"，则该列需要为 NOT NULL:

The documentation on INTEGER PRIMARY KEY is a little unclear about what precisely is required for a column to be this special INTEGER PRIMARY KEY that auto-increments but the reality is that the column needs to be NOT NULL if you want to use the NULL value to mean "give me the next auto-incrementing value" when inserting:

create table dummy (
    serial_num integer primary key not null,
    name text
);

如果你省略了not null，你需要像这样插入:

If you leave out the not null, you need to do your inserts like this:

insert into dummy (name) values (?)

获取serial_num 的自动递增值.否则，SQLite 无法区分 NULL 表示给我下一个自动增量值"和 NULL 表示在 serial_num 中放置一个 NULL 值，因为该列允许 NULL".

to get the auto-increment value for serial_num. Otherwise, SQLite has no way of telling the difference between a NULL meaning "give me the next auto-increment value" and a NULL meaning "put a NULL value in serial_num because the column allows NULLs".

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