博客

简介

sqlite 无法直接修改列类型，所以需要建立临时表，先把数据拷贝到临时表中，然后再创建新表，然后再将临时表的数据拷贝回来。

示例

-- 创建临时表 sqlitestudio_temp_table，并与表 learn_info 的数据和结构一致
CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM learn_info;
-- 删除原来的表
DROP TABLE learn_info;
-- 新建表，该新建指令已经修改了列的数据类型
CREATE TABLE learn_info (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    type          INT,
    thought       TEXT,
    book          INT,
    chapter       INT,
    section       INT,
    number        TEXT,
    repeat        INT     DEFAULT 1,
    failed_repeat INT     DEFAULT 0,
    star          INT,
    comment       TEXT,
    span          INT,
    update_time   DATE    DEFAULT (datetime('now', 'localtime') ),
    version       INT,
    deleted       INT
);
-- 将临时表的数据插入到新表里面，如果修改后的字段无法插入，可以把那一列给去掉
INSERT INTO learn_info (
                           id,
                           type,
                           thought,
                           book,
                           chapter,
                           section,
                           number,
                           repeat,
                           failed_repeat,
                           star,
                           comment,
                           span,
                           update_time,
                           version,
                           deleted
                       )
                       SELECT id,
                              type,
                              thought,
                              book,
                              chapter,
                              section,
                              number,
                              repeat,
                              failed_repeat,
                              star,
                              comment,
                              span,
                              update_time,
                              version,
                              deleted
                         FROM sqlitestudio_temp_table;
-- 删除临时表
DROP TABLE sqlitestudio_temp_table;