如何从 SQLite 的现有表中添加或删除列

2017-11-17

SQLite 对 ALTER TABLE 的支持有限，你可以用它来将列添加到表的末尾或更改表的名称。如果要对表的结构进行更复杂的更改，则必须重新创建表。你可以将现有数据保存到临时表中，删除旧表，创建新表，然后从临时表中复制数据。

例如，假设你有一个名为 “t1” 的表，有 “a”、”b”、”c” 三列，并且你希望从该表中删除 “c” 列。以下步骤说明如何做到这一点：

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

案例一：添加列

现在有一个名为 ACCOUNT 的表，表中包含 ID、NAME、DATE 四列，建表语句如下：

CREATE TABLE ACCOUNT (ID BIGINT, NAME text, DATE datetime, PRIMARY KEY(ID, NAME));
INSERT INTO ACCOUNT VALUES (10,'Adin','2000-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (11,'Jada','2001-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (12,'Cain','2002-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (13,'Dora','2003-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (14,'Eden','2004-01-01 01:01:01');

此处输入图片的描述

现在希望在 ACCOUNT 表中的 NAME 列后增加一个 PASS 列，并为该列默认赋值 123456，需要依次执行：1、创建临时表；2、将旧表数据复制到临时表；3、删除旧表；4、创建新表；5、将临时表数据复制到新表；6、删除临时表。执行语句如下：

CREATE TEMPORARY TABLE TEMP (ID BIGINT, NAME text, DATE datetime, PRIMARY KEY(ID, NAME));
INSERT INTO TEMP SELECT ID, NAME, DATE FROM ACCOUNT;
DROP TABLE ACCOUNT;
CREATE TABLE ACCOUNT (ID BIGINT, NAME text, PASS text, DATE datetime, PRIMARY KEY(ID, NAME));
INSERT INTO ACCOUNT SELECT T.ID, T.NAME, '123456', T.DATE FROM TEMP T;
DROP TABLE TEMP;

此处输入图片的描述

语句执行完毕后，刷新界面，会发现 PASS 列已经成功的添加到了 ACCOUNT 表的 NAME 列后，并默认赋值 123456。

提示：使用 SELECT 形式的 INSERT 可以一次插入多行。只要字段匹配，INSERT 可以插入结果集的所有行。

上面的例子中，临时表 TEMP 包含 ID、NAME、DATE 字段，而新建的 ACCOUNT 表包含 ID、NAME、PASS、DATE 字段，使用 SELECT 形式将 TEMP 表数据 INSERT 到新表 ACCOUNT 过程中，为了保持字段匹配，同时进行赋值操作，在 PASS 所在列的索引处手动添加了默认值 123456。当然，如果想将 NAME 列的值赋到 PASS 列，可以参考以下语句：

1	INSERT INTO ACCOUNT SELECT T.ID, T.NAME, T.NAME, T.DATE FROM TEMP T;

此处输入图片的描述

语句执行完毕后，刷新界面，会发现 PASS 列已经成功的添加到了 ACCOUNT 表的 NAME 列后，并成功将 NAME 列的值赋到了 PASS 列。

案例二：删除列

现在有一个名为 ACCOUNT 的表，表中包含 ID、NAME、PASS、DATE 四列，建表语句如下：

CREATE TABLE ACCOUNT (ID BIGINT, NAME text, PASS text, DATE datetime, PRIMARY KEY(ID, NAME));
INSERT INTO ACCOUNT VALUES (10,'Adin','123456','2000-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (11,'Jada','123456','2001-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (12,'Cain','123456','2002-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (13,'Dora','123456','2003-01-01 01:01:01');
INSERT INTO ACCOUNT VALUES (14,'Eden','123456','2004-01-01 01:01:01');

此处输入图片的描述

现在希望将 ACCOUNT 表中的 DATE 列删除，需要依次执行：1、创建临时表；2、将旧表数据复制到临时表；3、删除旧表；4、创建新表；5、将临时表数据复制到新表；6、删除临时表。执行语句如下：

CREATE TEMPORARY TABLE TEMP(ID BIGINT, NAME text, PASS text, PRIMARY KEY(ID, NAME));
INSERT INTO TEMP SELECT ID, NAME, PASS FROM ACCOUNT;
DROP TABLE ACCOUNT;
CREATE TABLE ACCOUNT(ID BIGINT, NAME text, PASS text, PRIMARY KEY(ID, NAME));
INSERT INTO ACCOUNT SELECT ID, NAME, PASS FROM TEMP;
DROP TABLE TEMP;

此处输入图片的描述

语句执行完毕后，刷新界面，会发现 ACCOUNT 表中的 DATE 列已经成功删除。

案例一：添加列

案例二：删除列

参考资料