Sqlite3:如何对表中的列重新排序? [英] Sqlite3: how to reorder columns in a table?

问题描述

sqlite3 表中的重新排序列似乎并不简单.至少firefox 中的sqlite manager 不支持这个功能.例如，将 column2 移动到 column3，将 column5 移动到 column2.有没有办法使用 sqlite 管理软件或脚本重新排序 sqlite 表中的列?谢谢.

It seems that it is not straightforward for reordering columns in a sqlite3 table. At least the sqlite manager in firefox does not support this feature. For example, move the column2 to column3 and move column5 to column2. Is there a way to reorder columns in sqlite table, either with a sqlite management software or a script? Thanks.

推荐答案

这在任何 DBMS 中都不是微不足道的任务.您几乎肯定必须按照您想要的顺序创建一个新表，并将数据从一个表移动到该顺序.没有对列重新排序的alter table 语句，因此无论是在sqlite 管理器中还是在任何其他地方，您都找不到在同一个表中执行此操作的方法.

This isn't a trivial task in any DBMS. You would almost certainly have to create a new table with the order that you want, and move your data from one table to the order. There is no alter table statement to reorder the columns, so either in sqlite manager or any other place, you will not find a way of doing this in the same table.

如果你真的想改变顺序，你可以这样做:

If you really want to change the order, you could do:

假设你有 tableA:

Assuming you have tableA:

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

您可以创建一个 tableB，其中的列按您想要的方式排序:

You could create a tableB with the columns sorted the way you want:

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

然后将数据从 tableA 移动到 tableB:

Then move the data to tableB from tableA:

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

然后移除原来的tableA，将tableB重命名为TableA:

Then remove the original tableA and rename tableB to TableA:

DROP table tableA;
ALTER TABLE tableB RENAME TO tableA;

sqlfiddle 演示

这篇关于Sqlite3:如何对表中的列重新排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！