SQLite4Unity3d 查询、添加、修改、删除数据

一、数据模型定义

[Table("Player")]
public class Player {
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    
    [Indexed]  // 为高频查询字段添加索引
    public string Name { get; set; }
    
    public int Score { get; set; }
}

[Table]特性映射数据库表，[PrimaryKey]标记主键，[Indexed]提升查询性能‌

二、数据库连接

string dbPath = Path.Combine(Application.streamingAssetsPath, "game.db");
var connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);

 使用Application.streamingAssetsPath适配多平台路径，ReadWrite和Create标志确保可读写‌

三、核心操作实现‌

‌1. 数据查询‌

• ‌条件查询

  var players = connection.Table<Player>()
      .Where(p => p.Score > 80)
      .OrderByDescending(p => p.Score)
      .ToList();  // 获取分数>80并按分数降序排列的玩家‌:ml-citation{ref="1,7" data="citationList"}

• ‌原生SQL查询‌

  var results = connection.Query<Player>(
      "SELECT * FROM Player WHERE Name LIKE ?", "%John%");  // 模糊查询‌:ml-citation{ref="7" data="citationList"}

‌2. 数据添加

var newPlayer = new Player { Name = "Alice", Score = 95 };
connection.Insert(newPlayer);  // 插入单条数据‌:ml-citation{ref="7" data="citationList"}

‌3. 数据修改

var target = connection.Table<Player>().First(p => p.Id == 1);
target.Score = 100;
connection.Update(target);  // 更新指定ID的玩家分数‌:ml-citation{ref="7" data="citationList"}

‌‌4. 数据删除‌

var deleteTarget = connection.Table<Player>().First(p => p.Id == 2);
connection.Delete(deleteTarget);  // 删除指定ID的记录‌:ml-citation{ref="7" data="citationList"}

‌四、高级技巧

1. 批量事务处理‌

connection.BeginTransaction();
try {
    for(int i=0; i<1000; i++) {
        connection.Insert(new Player{ Name = $"Bot_{i}", Score = i });
    }
    connection.Commit();  // 批量插入时事务提升10倍性能‌:ml-citation{ref="4,7" data="citationList"}
} catch {
    connection.Rollback();
}

‌2. 动态条件构建

var query = connection.Table<Player>();
if(needFilter) query = query.Where(p => p.Score < 60);
var results = query.ToList();  // 动态组合查询条件‌:ml-citation{ref="7" data="citationList"}

五、调试与优化‌

1. ‌实时查看数据库‌
   • 使用DB Browser for SQLite打开game.db文件，路径：Application.streamingAssetsPath/game.db‌
2. ‌性能监控‌

   var sw = System.Diagnostics.Stopwatch.StartNew();
   // 执行数据库操作
   Debug.Log($"操作耗时：{sw.ElapsedMilliseconds}ms");‌:ml-citation{ref="7" data="citationList"}

‌六、跨平台适配

该方案覆盖了SQLite4Unity3d的核心功能，通过ORM简化开发流程，同时保留原生SQL的灵活性‌。建议优先使用Linq语法保证代码可维护性，复杂场景切换原生SQL优化性能‌