SQLite数据库表字段修改与删除，sqlite数据库表字段

SQLite数据库表字段修改与删除，sqlite数据库表字段

我创建一张表，但是在使用过程中发现字段名称会发生改变，但是SQLite数据库只允许增加表字段(alter table my_table add field_name field_type;)，不允许修改和删除表字段。这时候只能使用复制表思想了。步骤如下:

1、将需要修改的表(my_table)重新命名(ALTER TABLE "my_table" RENAME TO sqlitemanager_temp_table_11970396993;)，

2、创建一个新的表命名为my_table,填入需要的字段名（CREATE TABLE "my_table" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" VARCHAR, "grender" VARCHAR DEFAULT "biao", "number" TEXT);）。

3、查询原来表(sqlitemanager_temp_table_11970396993)数据，向新创建的表(my_table)新增数据（INSERT INTO "my_table" ("id","name","grender","number") SELECT "id","name","grender","number" FROM sqlitemanager_temp_table_11970396993;）

4、销毁原来的表(sqlitemanager_temp_table_11970396993)

SQL语句实现如下:

PRAGMA foreign_keys = 0;

BEGIN TRANSACTION;

ALTER TABLE "my_table" RENAME TO sqlitemanager_temp_table_11970396993;

CREATE TABLE "my_table" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" VARCHAR, "grender" VARCHAR DEFAULT "biao", "number" TEXT DEFAULT "biao");

INSERT INTO "my_table" ("id","name","grender","number") SELECT "id","name","grender","number" FROM sqlitemanager_temp_table_11970396993;

DROP TABLE sqlitemanager_temp_table_11970396993;

COMMIT;

PRAGMA foreign_keys = 1;

修改字段名也是一样的思想.