SQLite的力排序上选择

Question

我有一个很奇怪的问题与sqlite3的 - 运行sqlite3的3.20.1版本的Windows。

在两个不同的表同样的SELECT查询产生不同的结果。但是列数据类型和数据在两者中都是相同的。这里是CREATE和INSERT语句。

CREATE TABLE key(key INTEGER UNIQUE NOT NULL); 
CREATE TABLE map(key INTEGER UNIQUE NOT NULL, char TEXT); 

WITH RECURSIVE cnt(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 9999) INSERT INTO key(key) SELECT x FROM cnt WHERE x >=1000 ORDER BY random(); 
WITH RECURSIVE cnt(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM cnt LIMIT 9999) INSERT INTO map(key) SELECT x FROM cnt WHERE x >=1000 ORDER BY random(); 

这应该插入随机排序成两列，从1000到9999的数字。但是，第二个表上的SELECT语句总是排序的。这是结果

sqlite> SELECT key FROM map LIMIT 10; 
1000 
1001 
1002 
1003 
1004 
1005 
1006 
1007 
1008 
1009 
sqlite> SELECT key FROM key LIMIT 10; 
6165 
1856 
2457 
2343 
9095 
8005 
4958 
4781 
8334 
1863 

我甚至尝试了以下;

sqlite> DELETE FROM map; 
sqlite> INSERT INTO map(key) SELECT key FROM key; 
sqlite> 
sqlite> SELECT key FROM map LIMIT 10; 
1000 
1001 
1002 
1003 
1004 
1005 
1006 
1007 
1008 
1009 

它仍然排序！使用REPLACE也没有帮助。 我试过甚至通过先添加id列来改变表格模式，但也没有成功。

经过一番实验后，我发现SELECT *显示它在正确的顺序，但SELECT key给出了有序的结果。

sqlite> SELECT * FROM map LIMIT 10; 
8418| 
5869| 
9753| 
2886| 
8354| 
8244| 
4063| 
6692| 
5440| 
3508| 

你能解释一下吗？它是一个错误还是一个功能？我怎样才能得到预期的输出？

Answer 1

的documentation说：

如果返回多行SELECT语句不具有ORDER BY子句，行返回的顺序是未定义的。

如果您想查询以随机顺序返回行，你必须在查询本身使用ORDER BY random()。

（要在内部顺序返回行，可使用ORDER BY rowid，但这并不对WITHOUT ROWID tables工作。）

---

对于唯一约束的索引使用更少的存储空间，则表，因为它只包含一个列。 所以只要你想只读此列，查询优化器更倾向于直接从指数（作为covering index）读取的值，因为从磁盘读取数据较少，使查询速度更快：

sqlite> EXPLAIN QUERY PLAN SELECT key FROM map; 
0|0|0|SCAN TABLE map USING COVERING INDEX sqlite_autoindex_map_1 

那返回的行然后碰巧被排序只是一个副作用。

Answer 2

这应该插入随机排序成两列，从1000到9999的数字。但是，第二个表上的SELECT语句总是排序的。

你不懂SQL。 SQL表格代表无序集合。这意味着SQL引擎可以决定如何存储它想要的数据。

SQL查询返回无序套。这意味着SQL引擎可以决定结果集的排序 - 除非您指定了排序。

在这种情况下，SQLite是基于表的唯一键，返回结果集 - 它使用的查询索引。 （SQLite的可能实际上是决定了唯一键是表的主键）