unity2021-unity2023连接SQLite数据库，并对数据库进行增删改查操作

步骤1，导入SQLite4Unity3d 插件包

插件包下载地址https://pan.baidu.com/s/1LSy2rMFX4D_z6ByZ_nLPjg 提取码: 4xs6

步骤2，在Assets文件夹下建立StreamingAssets文件夹存放SQLite数据库文件

步骤3，数据库连接验证及常用操作
创建SQLiteHelper.cs和User.cs，把SQLiteHelper.cs绑定到空对象执行操作

User.cs代码 （数据模型类）

using SQLite4Unity3d;

public class User
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    
    public string Name { get; set; }
    public int Age { get; set; }
}

SQLiteHelper.cs代码

using SQLite4Unity3d;
using System;
using System.Collections.Generic;
using UnityEngine;

public class SQLiteHelper : MonoBehaviour
{
    private SQLiteConnection _connection;
    private string _databasePath;

    // 数据库文件路径（放在 StreamingAssets 文件夹中）
    void Start()
    {
        // 获取数据库路径
        _databasePath = System.IO.Path.Combine(Application.streamingAssetsPath, "mydatabase.db");

        // 创建或打开数据库连接
        _connection = new SQLiteConnection(_databasePath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);

        // 创建表格（如果不存在）
        CreateTable();

        // 测试增删改查操作
        InsertData();
        ReadData();
        UpdateData();
        DeleteData();
    }

    // 创建数据表
    void CreateTable()
    {
        _connection.CreateTable<User>();
        Debug.Log("Table created or already exists.");
    }

    // 插入数据
    void InsertData()
    {
        // 插入新用户
        var user = new User
        {
            Name = "John Doe",
            Age = 30
        };

        _connection.Insert(user);
        Debug.Log("User inserted.");
    }

    // 查询数据
    void ReadData()
    {
        // 查询所有用户
        var users = _connection.Table<User>().ToList();
        foreach (var user in users)
        {
            Debug.Log($"User ID: {user.Id}, Name: {user.Name}, Age: {user.Age}");
        }
    }

    // 更新数据
    void UpdateData()
    {
        // 查找第一个用户并更新
        var user = _connection.Table<User>().FirstOrDefault();
        if (user != null)
        {
            user.Name = "Jane Doe";
            user.Age = 25;
            _connection.Update(user);
            Debug.Log("User updated.");
        }
    }

    // 删除数据
    void DeleteData()
    {
        // 查找并删除第一个用户
        var user = _connection.Table<User>().FirstOrDefault();
        if (user != null)
        {
            _connection.Delete(user);
            Debug.Log("User deleted.");
        }
    }

    // 当脚本销毁时，关闭数据库连接
    void OnDestroy()
    {
        if (_connection != null)
        {
            _connection.Close();
        }
    }
}

补充：原生sql语句的增删改查操作

using SQLite4Unity3d;
using UnityEngine;
using System;
using System.Collections.Generic;

public class DatabaseManager : MonoBehaviour
{
    private SQLiteConnection dbConnection;

    void Start()
    {
        string dbPath = $"{Application.persistentDataPath}/userDatabase.db";
        dbConnection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
        dbConnection.CreateTable<User>();
    }

    // 查询所有用户
    public void GetAllUsersSQL()
    {
        string sqlQuery = "SELECT * FROM User";
        var users = dbConnection.Query<User>(sqlQuery);

        foreach (var user in users)
        {
            Debug.Log($"ID: {user.id}, Name: {user.name}, Age: {user.age}");
        }
    }

    // 根据条件查询用户
    public void GetUsersByAgeAndNameSQL(int minAge, string nameSubstring)
    {
        string sqlQuery = "SELECT * FROM User WHERE age > ? AND name LIKE ?";
        var users = dbConnection.Query<User>(sqlQuery, minAge, "%" + nameSubstring + "%");

        foreach (var user in users)
        {
            Debug.Log($"ID: {user.id}, Name: {user.name}, Age: {user.age}");
        }
    }

    // 根据 ID 更新用户的姓名和年龄
    public void UpdateUserByIdSQL(int userId, string newName, int newAge)
    {
        string sqlQuery = "UPDATE User SET name = ?, age = ? WHERE id = ?";
        dbConnection.Execute(sqlQuery, newName, newAge, userId);
        Debug.Log("User Updated!");
    }

    // 根据 ID 删除用户
    public void DeleteUserByIdSQL(int userId)
    {
        string sqlQuery = "DELETE FROM User WHERE id = ?";
        dbConnection.Execute(sqlQuery, userId);
        Debug.Log("User Deleted!");
    }

    // 根据年龄删除用户
    public void DeleteUsersByAgeSQL(int ageLimit)
    {
        string sqlQuery = "DELETE FROM User WHERE age < ?";
        dbConnection.Execute(sqlQuery, ageLimit);
        Debug.Log("Users Deleted by Age!");
    }

    // 使用事务批量更新用户的年龄
    public void BatchUpdateUsersAgeSQL(int ageLimit, int newAge)
    {
        dbConnection.BeginTransaction();
        try
        {
            string sqlQuery = "UPDATE User SET age = ? WHERE age < ?";
            dbConnection.Execute(sqlQuery, newAge, ageLimit);
            dbConnection.Commit();
            Debug.Log("Batch Update Successful!");
        }
        catch (Exception ex)
        {
            dbConnection.Rollback();
            Debug.LogError($"Batch Update Failed: {ex.Message}");
        }
    }

    // 关闭数据库连接
    public void CloseDatabase()
    {
        dbConnection.Close();
    }
}

public class User
{
    [PrimaryKey, AutoIncrement]
    public int id { get; set; }
    public string name { get; set; }
    public int age { get; set; }
}