SQLite 数据库升级——新增字段处理总结

5,340 阅读4分钟

一、场景

在工作中,由于新需求总是不断,我们经常会遇到项目中的一些表的结构要改变,比如最常见的就是 新增字段 了。这里我总结一下我遇到这种情况时的处理方法,SQLite 也有一些坑,希望能帮到有同样需求的朋友们。下面我是用的 fmdb 进行的数据库操作,用原生或者其他工具的升级思想也是一样的。

二、升级操作

用户升级 app 时,当我们判断到旧项目的版本号(或者数据库的单独版本号)小于某个版本号时,就进行我们的升级操作。

1、新增普通字段

如果你要新增的是个普通字段,并不是个 主键(后面会介绍什么情况下会新增主键),那就好办了,操作很简单,直接执行新增字段语句即可。

先复习一下 SQL新增字段 语句: alter table mydownload add column 'IsFree' varchar(100) default '1' 其中的 column 可以省略,同时也可以不给 default 默认值。

下面是判断升级并进行新增操作的具体代码:

if (![dbPointer columnExists:@"LoginUserId" inTableWithName:@"mydownload"]) {
    // 如果不存在 LoginUserId 字段则执行添加 LoginUserId 语句,默认值是当前登录的用户id
    NSString *addStr = [NSString stringWithFormat:@"alter table mydownload add column 'LoginUserId' varchar(100) default '%@'", [Global sharedGlobal].loginInfo.userId];
    if ([dbPointer executeUpdate:addStr]){
        DebugLog(@"添加 LoginUserId 字段成功!");
    } else {
        DebugLog(@"添加 LoginUserId 字段失败!");
    }
}

⚠️ 注意: 这里要说一下,我们的 SQLite 是个阉割的数据库,有很多数据库操作语句都是不支持的,比如不支持 批量增加字段,所以如果你要新增多个字段,那也只能一个一个的加了。

2、新增、修改主键或删除某字段

如果你新增的是个 主键。比如你想在以前的文章 id 为主键的的基础上,再新增一个用户 id,把文章 id 和用户 id 作为 联合主键,那此时就不能执行新增字段方法了,也是因为 SQLite 的限制,

⚠️ 注意: SQLite 限制了 ALTER TABLE 的部分功能,只能将列添加到表的末尾或更改表的名称。 如果要在表的结构中进行更复杂的更改,则必须 重新创建表。 您可以将现有数据保存到临时表,删除旧表,创建新表,然后从临时表中复制数据。

例如,假设您有一个名为“person”的表,其列名为“id”,“name”和“age”,并且您要从此表中删除列“age”。 以下 SQL 语句步骤说明了如何完成此操作:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE person_backup(id,name);
INSERT INTO person_backup SELECT id,name FROM person;
DROP TABLE person;
CREATE TABLE person(id,name);
INSERT INTO person SELECT id,name FROM person_backup;
DROP TABLE person_backup;
COMMIT;

具体例子

下面再把我项目中具体的一个简略例子展示出来供大家参考: 原表状态: 原下载表 mydownload 只有两个字段 EpisodeIdDownloaderFilePath,主键是 EpisodeId 。下载的东西不跟随用户。 新需求: 现在要求下载的东西跟随用户走,即下载列表只展示当前用户下载的东西。这就需要新增一个 LoginUserId 用户 id 字段,把它和 EpisodeId 作为一个联合主键,这样下载的东西就可以和用户 id 绑定了。 具体代码操作:

 if (![dbPointer columnExists:@"LoginUserId" inTableWithName:@"mydownload"]) {
        // 如果不存在 LoginUserId 字段则将原来的表改名
        [dbPointer beginTransaction];
        BOOL isRollBack = NO;
        @try {
            if ([dbPointer executeUpdate:@"ALTER TABLE mydownload RENAME TO temp_mydownload"]) {
                
                NSString *executeStr = @"CREATE TABLE mydownload (EpisodeId varchar(100), LoginUserId varchar(100),  DownloaderFilePath varchar(100),CONSTRAINT PK_mydownload PRIMARY KEY(EpisodeId,LoginUserId) )";
                
                if ([dbPointer executeUpdate:executeStr]) {
                    // 从旧数据表把旧数据插入新的数据表中
                    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO mydownload (EpisodeId,LoginUserId,DownloaderFilePath) select EpisodeId,'%@','1','',DownloaderFilePath from temp_mydownload", [Global sharedGlobal].loginInfo.userId];// 复制改名后的表到新建的表(注意:一列对应一列的进行复制,新增的字段可以用''来补)
                    if ([dbPointer executeUpdate:insertSql]) {
                        [dbPointer executeUpdate:@"drop table temp_mydownload"];// 删除旧表
                        
                        [[NSUserDefaults standardUserDefaults] setObject:@"YES" forKey:@"hasModifyDownloadDatabase"];// 标记已经升级过下载表
                    };
                    
                }
            }
        } @catch (NSException *exception) {
            isRollBack = YES;
            // 事务回退
            [dbPointer rollback];
        } @finally {
            if (!isRollBack) {
                // 事务提交
                [dbPointer commit];
            }
        }
        
    }

以上的总结参考了并部分摘抄了以下文章,非常感谢以下作者的分享!:

1、《sqlite alter table添加多列》

2、《sqlite并不支持建表后修改主键,或删除列,如果要修改,请参考如下做法》

3、《FMDB数据库升级增加表字段》

转载请备注原文出处,不得用于商业传播——凡几多