SQLite3：如何重新排序表中的列？

Question

看起来在SQLite3表中重新排序列并不是一件简单的事情。至少Firefox中的SQLite管理器不支持此功能。例如，将column2移动到column3，将column5移动到column2。是否有一种方法可以重新排序SQLite表中的列，无论是使用SQLite管理软件还是脚本？

- user938363

Answer 1

在任何数据库管理系统中，这都不是一个简单的任务。您几乎肯定需要创建一个新表并按照您想要的顺序移动数据。没有alter table语句来重新排序列，因此无论是在sqlite manager还是其他任何地方，您都无法在同一张表中完成此操作。

如果您真的想改变顺序，可以执行以下操作：

假设您有tableA：

create table tableA(
col1 int,
col3 int,
col2 int);

您可以创建一个表格B，将列按您想要的方式排序：

create table tableB(
col1 int,
col2 int,
col3 int);

将数据从tableA移动到tableB：

insert into tableB
SELECT col1,col2,col3 
FROM tableA;

然后删除原始的tableA并将tableB重命名为TableA：

DROP table tableA;
ALTER TABLE tableB RENAME TO tableA;

sqlfiddle演示

- Filipe Silva

---

对于上面的插入到表B的命令，它是否会保留id呢？我们需要在新表中拥有与表A中相同的id记录。 - user938363

当然。如果其中一个列是id，则它将被迁移到新表中。 - Filipe Silva

@FilipeSilva 如果ID列设置为AUTOINCREMENT会怎样？ - pavitran

如果其他表的外键引用了这个表，那么在删除该表之前必须先移除外键引用。请查看此处。 - M.C.

Answer 2

你可以始终按照自己的需求在SELECT语句中对列进行排序，就像这样：

SELECT column1,column5,column2,column3,column4
FROM mytable
WHERE ...

在表格本身中，您不需要“订购”它们。

- BWS

---

需要匹配两个表的模式，以便可以将数据从一个表复制到另一个表。Sqlite3导入在复制时无法匹配列。因此，我们最终会得到例如文本列下的整数列。 - user938363

在“INSERT”语句中，它允许按顺序放置VALUES，以明确匹配列。 - Kenneth

Answer 3

在sqlite3中，顺序是很重要的。从概念上讲，它不应该很重要，但试着做一个实验来证明它确实很重要：

CREATE TABLE SomeItems (
  identifier INTEGER PRIMARY KEY NOT NULL, 
  filename TEXT NOT NULL, path TEXT NOT NULL,  
  filesize INTEGER NOT NULL, thumbnail BLOB, 
  pickedStatus INTEGER NOT NULL, 
  deepScanStatus INTEGER NOT NULL, 
  basicScanStatus INTEGER NOT NULL, 
  frameQuanta INTEGER, 
  tcFlag INTEGER, 
  frameStart INTEGER, 
  creationTime INTEGER
);

将表格填充大约20,000个记录，其中缩略图是一个小的jpeg。然后执行几个类似于这样的查询：

time sqlite3 Catalog.db 'select count(*) from SomeItems where filesize = 2;'
time sqlite3 Catalog.db 'select count(*) from SomeItems where basicScanStatus = 2;'

无论返回多少记录，在我的机器上，第一个查询大约需要0m0.008秒，而第二个查询需要0m0.942秒。巨大的差异是由于Blob引起的；filesize在Blob之前，而basicScanStatus在Blob之后。

我们现在将Blob移动到了它自己的表中，我们的应用程序很满意。

- user8353681

---

虽然它完全没有回答问题，但这非常有趣。 - sapht

真的。Filipe Silva的回答确实回答了问题。我的回答更多是关于为什么需要这样做，即验证问题。我们不得不这样做，没有一种简单的方法来改变顺序，因此我们创建了第二个表并将慢列放入该表中。这对我们的性能有很大的提升。 - user8353681

确实，这个答案并没有（直接）回答问题。但它是非常有价值的输入。 - cezar

我想知道当将长文本列放置在记录末尾时，是否会出现类似的性能差异，以便较小的列不必“跨越”文本列。我只进行了一次测试，使用两个不同的表中相同的记录，但是在选择最右侧的整数列时没有任何区别。 - Gary Z

Answer 4

你可以使用Sqlite Browser重新排序它们。

- Shawn