ADSL拨号VPS包含了中国大陆（联通，移动，电信，）

中国香港，国外拨号VPS。

很久以前就遇到过数据库版本升级的引用场景，当时的做法是简单的删除旧的数据库文件，重建数据库和表结构，这种暴力升级的方式会导致旧的数据的丢失，现在看来这并不不是一个优雅的解决方案，现在一个新的项目中又使用到了数据库，我不得不重新考虑这个问题，我希望用一种比较优雅的方式去解决这个问题，以后我们还会遇到类似的场景，我们都想做的更好不是吗？

理想的情况是：数据库升级，表结构、主键和约束有变化，新的表结构建立之后会自动的从旧的表检索数据，相同的字段进行映射迁移数据，而绝大多数的业务场景下的数据库版本升级是只涉及到字段的增减、修改主键约束，所以下面要实现的方案也是从最基本的、最常用的业务场景去做一个实现，至于更加复杂的场景，可以在此基础上进行扩展，达到符合自己的预期的。

选型定型

网上搜索了下，并没有数据库升级数据迁移简单完整的解决方案，找到了一些思路

1.清除旧的数据，重建表

优点：简单
缺点：数据丢失

2.在已有表的基础上对表结构进行修改

优点：能够保留数据
缺点：规则比较繁琐，要建立一个数据库的字段配置文件，然后读取配置文件，执行SQL修改表结构、约束和主键等等，涉及到跨多个版本的数据库升级就变得繁琐并且麻烦了

3.创建临时表，把旧的数据拷贝到临时表，然后删除旧的数据表并且把临时表设置为数据表。

优点：能够保留数据，支持表结构的修改，约束、主键的变更，实现起来比较简单
缺点：实现的步骤比较多

综合考虑，第三种方法是一个比较靠谱的方案。

主要步骤

根据这个思路，分析了一下数据库升级了主要步骤大概如下：

获取数据库中旧的表修改表名,添加后缀“_bak”，把旧的表当做备份表创建新的表获取新创建的表遍历旧的表和新表，对比取出需要迁移的表的字段数据迁移处理删除备份表

使用到的SQL语句分析

这些操作都是和数据库操作有关系的，所以问题的关键是对应步骤的SQL语句了，下面分析下用到的主要的SQL语句：

获取数据库中旧的表

SELECT * from sqlite_master WHERE type='table'

结果如下，可以看到有type | name | tbl_name | rootpage | sql 这些数据库字段，我们只要用到name也就是数据库名称这个字段就行了

sqlite> SELECT * from sqlite_master WHERE type='table' ...> ;+-------+---------------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| type | name   | tbl_name  | rootpage | sql                                                     |+-------+---------------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| table | t_message_bak | t_message_bak | 2  | CREATE TABLE "t_message_bak" (messageID TEXT, messageType INTEGER, messageJsonContent TEXT, retriveTimeString INTEGER, postTimeString INTEGER, readState INTEGER, PRIMARY KEY(messageID))        || table | t_message  | t_message  | 4  | CREATE TABLE t_message ( messageID TEXT,  messageType INTEGER, messageJsonContent TEXT,  retriveTimeString INTEGER,  postTimeString INTEGER,  readState INTEGER,  addColumn INTEGER, PRIMARY KEY(messageID)) |+-------+---------------+---------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+2 行于数据集 (0.03 秒)

修改表名,添加后缀“_bak”，把旧的表当做备份表

-- 把t_message表修改为t_message_bak表 ALTER TABLE t_message RENAME TO t_message_bak

获取表字段信息

-- 获取t_message_bak表的字段信息PRAGMA table_info('t_message_bak')

sqlite> PRAGMA table_info('t_message_bak');+------+--------------------+---------+---------+------------+------+| cid | name    | type | notnull | dflt_value | pk |+------+--------------------+---------+---------+------------+------+| 0 | messageID   | TEXT | 0  | NULL  | 1 || 1 | messageType  | INTEGER | 0  | NULL  | 0 || 2 | messageJsonContent | TEXT | 0  | NULL  | 0 || 3 | retriveTimeString | INTEGER | 0  | NULL  | 0 || 4 | postTimeString  | INTEGER | 0  | NULL  | 0 || 5 | readState   | INTEGER | 0  | NULL  | 0 |+------+--------------------+---------+---------+------------+------+6 行于数据集 (0.01 秒)

使用子查询进行数据迁移处理

INSERT INTO t_message(messageID, messageType, messageJsonContent, retriveTimeString, postTimeString, readState) SELECT messageID, messageType, messageJsonContent, retriveTimeString, postTimeString, readState FROM t_message_bak

把t_message_bak表中的messageID, messageType, messageJsonContent, retriveTimeString, postTimeString, readState这些字段的值复制到t_message表中

代码实现

接下来就到了代码的实现步骤了

// 创建新的临时表，把数据导入临时表，然后用临时表替换原表- (void)baseDBVersionControl { NSString * version_old = ValueOrEmpty(MMUserDefault.dbVersion); NSString * version_new = [NSString stringWithFormat:@"%@", DB_Version]; NSLog(@"dbVersionControl before: %@ after: %@",version_old,version_new); // 数据库版本升级 if (version_old != nil && ![version_new isEqualToString:version_old]) {  // 获取数据库中旧的表  NSArray* existsTables = [self sqliteExistsTables];  NSMutableArray* tmpExistsTables = [NSMutableArray array];  // 修改表名,添加后缀“_bak”，把旧的表当做备份表  for (NSString* tablename in existsTables) {   [tmpExistsTables addObject:[NSString stringWithFormat:@"%@_bak", tablename]];   [self.databaseQueue inDatabase:^(FMDatabase *db) {    NSString* sql = [NSString stringWithFormat:@"ALTER TABLE %@ RENAME TO %@_bak", tablename, tablename];    [db executeUpdate:sql];   }];  }  existsTables = tmpExistsTables;  // 创建新的表  [self initTables];  // 获取新创建的表  NSArray* newAddedTables = [self sqliteNewAddedTables];  // 遍历旧的表和新表，对比取出需要迁移的表的字段  NSDictionary* migrationInfos = [self generateMigrationInfosWithOldTables:existsTables newTables:newAddedTables];  // 数据迁移处理  [migrationInfos enumerateKeysAndObjectsUsingBlock:^(NSString* newTableName, NSArray* publicColumns, BOOL * _Nonnull stop) {   NSMutableString* colunmsString = [NSMutableString new];   for (int i = 0; i<publicColumns.count; i++) {    [colunmsString appendString:publicColumns[i]];    if (i != publicColumns.count-1) {     [colunmsString appendString:@", "];    }   }   NSMutableString* sql = [NSMutableString new];   [sql appendString:@"INSERT INTO "];   [sql appendString:newTableName];   [sql appendString:@"("];   [sql appendString:colunmsString];   [sql appendString:@")"];   [sql appendString:@" SELECT "];   [sql appendString:colunmsString];   [sql appendString:@" FROM "];   [sql appendFormat:@"%@_bak", newTableName];   [self.databaseQueue inDatabase:^(FMDatabase *db) {    [db executeUpdate:sql];   }];  }];  // 删除备份表  [self.databaseQueue inDatabase:^(FMDatabase *db) {   [db beginTransaction];   for (NSString* oldTableName in existsTables) {    NSString* sql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS %@", oldTableName];    [db executeUpdate:sql];   }   [db commit];  }];  MMUserDefault.dbVersion = version_new; } else {  MMUserDefault.dbVersion = version_new; }}- (NSDictionary*)generateMigrationInfosWithOldTables:(NSArray*)oldTables newTables:(NSArray*)newTables { NSMutableDictionary<NSString*, NSArray* >* migrationInfos = [NSMutableDictionary dictionary]; for (NSString* newTableName in newTables) {  NSString* oldTableName = [NSString stringWithFormat:@"%@_bak", newTableName];  if ([oldTables containsObject:oldTableName]) {   // 获取表数据库字段信息   NSArray* oldTableColumns = [self sqliteTableColumnsWithTableName:oldTableName];   NSArray* newTableColumns = [self sqliteTableColumnsWithTableName:newTableName];   NSArray* publicColumns = [self publicColumnsWithOldTableColumns:oldTableColumns newTableColumns:newTableColumns];   if (publicColumns.count > 0) {    [migrationInfos setObject:publicColumns forKey:newTableName];   }  } } return migrationInfos;}- (NSArray*)publicColumnsWithOldTableColumns:(NSArray*)oldTableColumns newTableColumns:(NSArray*)newTableColumns { NSMutableArray* publicColumns = [NSMutableArray array]; for (NSString* oldTableColumn in oldTableColumns) {  if ([newTableColumns containsObject:oldTableColumn]) {   [publicColumns addObject:oldTableColumn];  } } return publicColumns;}- (NSArray*)sqliteTableColumnsWithTableName:(NSString*)tableName { __block NSMutableArray<NSString*>* tableColumes = [NSMutableArray array]; [self.databaseQueue inDatabase:^(FMDatabase *db) {  NSString* sql = [NSString stringWithFormat:@"PRAGMA table_info('%@')", tableName];  FMResultSet *rs = [db executeQuery:sql];  while ([rs next]) {   NSString* columnName = [rs stringForColumn:@"name"];   [tableColumes addObject:columnName];  } }]; return tableColumes;}- (NSArray*)sqliteExistsTables { __block NSMutableArray<NSString*>* existsTables = [NSMutableArray array]; [self.databaseQueue inDatabase:^(FMDatabase *db) {  NSString* sql = @"SELECT * from sqlite_master WHERE type='table'";  FMResultSet *rs = [db executeQuery:sql];  while ([rs next]) {   NSString* tablename = [rs stringForColumn:@"name"];   [existsTables addObject:tablename];  } }]; return existsTables;}- (NSArray*)sqliteNewAddedTables { __block NSMutableArray<NSString*>* newAddedTables = [NSMutableArray array]; [self.databaseQueue inDatabase:^(FMDatabase *db) {  NSString* sql = @"SELECT * from sqlite_master WHERE type='table' AND name NOT LIKE '%_bak'";  FMResultSet *rs = [db executeQuery:sql];  while ([rs next]) {   NSString* tablename = [rs stringForColumn:@"name"];   [newAddedTables addObject:tablename];  } }]; return newAddedTables;}

问题

sqlite 删除表文件的大小不变的问题

如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

(责任编辑：admin)