sqlite3中一个表怎么可能有单个主键的多个条目?
sqlite3 中的表如何可能具有单个主键的多个条目?这就是我识别问题的方法:
$ sqlite3 dbName.db
sqlite> .s
CREATE TABLE 'tableName' (
columnOne INTEGER NOT NULL,
columnTwo INTEGER NOT NULL,
columnThree INTEGER NOT NULL,
columnFour INTEGER NOT NULL,
columnFive REAL NOT NULL,
PRIMARY KEY ( columnOne, columnTwo, columnThree, columnFour )
);
sqlite> SELECT count(1) AS nb FROM tableName GROUP BY columnOne, columnTwo, columnThree, columnFour HAVING nb > 1;
[A whole bunch of results, some with nb up to 34!]
更新 请求重复条目的示例:
$ sqlite3 observation.db
sqlite> .mode column
sqlite> .s
CREATE TABLE 'observation' (
station INTEGER NOT NULL,
specie INTEGER NOT NULL,
isAvg INTEGER NOT NULL,
date INTEGER NOT NULL,
value REAL NOT NULL,
PRIMARY KEY ( station, specie, isAvg, date )
);
sqlite> SELECT * FROM observation WHERE station = 105001 AND specie = 3 AND isAvg = 1 AND date = 1308650400;
station specie isAvg date value
---------- ---------- ---------- ---------- ----------
105001 3 1 1308650400 31.0
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
@mu 太短:数据库由每小时运行一次的 tcl 脚本填充,并使用以下查询之一来插入数据:
INSERT OR REPLACE INTO observation (station, specie, isAvg, date, value) VALUES ($stationId, $speciesId, 0, $date, $value);
INSERT OR REPLACE INTO observation (station, specie, isAvg, date, value) VALUES (${stationId}, ${speciesId}, 1, ${date}, ${speciesAvg});
我只是想到了其他东西,我不知道是否这可以帮助...:
sqlite3 observation.db
sqlite> pragma integrity_check;
integrity_check
rowid 53202997 missing from index sqlite_autoindex_observation_1
rowid 53202998 missing from index sqlite_autoindex_observation_1
rowid 53202999 missing from index sqlite_autoindex_observation_1
rowid 53203000 missing from index sqlite_autoindex_observation_1
rowid 53203006 missing from index sqlite_autoindex_observation_1
rowid 53584951 missing from index sqlite_autoindex_observation_1
[...]
and more of the same (100 such lines since integrity_check stops at 100 by default..)
[...]
How is it possible for a table to have multiple entries for a single primary key in sqlite3 ? This is how I identified the problem:
$ sqlite3 dbName.db
sqlite> .s
CREATE TABLE 'tableName' (
columnOne INTEGER NOT NULL,
columnTwo INTEGER NOT NULL,
columnThree INTEGER NOT NULL,
columnFour INTEGER NOT NULL,
columnFive REAL NOT NULL,
PRIMARY KEY ( columnOne, columnTwo, columnThree, columnFour )
);
sqlite> SELECT count(1) AS nb FROM tableName GROUP BY columnOne, columnTwo, columnThree, columnFour HAVING nb > 1;
[A whole bunch of results, some with nb up to 34!]
UPDATES
A sample of duplicate entries was requested:
$ sqlite3 observation.db
sqlite> .mode column
sqlite> .s
CREATE TABLE 'observation' (
station INTEGER NOT NULL,
specie INTEGER NOT NULL,
isAvg INTEGER NOT NULL,
date INTEGER NOT NULL,
value REAL NOT NULL,
PRIMARY KEY ( station, specie, isAvg, date )
);
sqlite> SELECT * FROM observation WHERE station = 105001 AND specie = 3 AND isAvg = 1 AND date = 1308650400;
station specie isAvg date value
---------- ---------- ---------- ---------- ----------
105001 3 1 1308650400 31.0
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
105001 3 1 1308650400 2.42523266
@mu is too short : The database is filled by a tcl script which runs every hour and uses one of the following queries to insert data :
INSERT OR REPLACE INTO observation (station, specie, isAvg, date, value) VALUES ($stationId, $speciesId, 0, $date, $value);
INSERT OR REPLACE INTO observation (station, specie, isAvg, date, value) VALUES (${stationId}, ${speciesId}, 1, ${date}, ${speciesAvg});
I just thought of something else, I don't know if this can help... :
sqlite3 observation.db
sqlite> pragma integrity_check;
integrity_check
rowid 53202997 missing from index sqlite_autoindex_observation_1
rowid 53202998 missing from index sqlite_autoindex_observation_1
rowid 53202999 missing from index sqlite_autoindex_observation_1
rowid 53203000 missing from index sqlite_autoindex_observation_1
rowid 53203006 missing from index sqlite_autoindex_observation_1
rowid 53584951 missing from index sqlite_autoindex_observation_1
[...]
and more of the same (100 such lines since integrity_check stops at 100 by default..)
[...]
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
直接回答您的问题:
仅当您的主键索引有错误时才有可能。
该问题由您的integrity_check错误显示,因为sqlite3使用此索引来确定主键唯一性。当索引不好时,您将能够插入重复记录
要解决的问题:我将构建并重新填充一个新表。请记住,当您复制记录时,您将需要处理这些重复的记录。
另外,您没有显示 sqlite3 版本号,这将有助于解决问题。
To answer your question directly:
It is only possible when there is an error with your primary key index.
The problem is shown by your integrity_check error, since sqlite3 uses this index to determine primary key uniqueness. When the index is bad you will be able to insert duplicate records
To solve: I would build and repopulate a new table. Remember when you copy the records you will need to deal with these duplicate records.
Also, you don't show your sqlite3 version number, that would help in solving the problem.