80 lines
1.9 KiB
Markdown
80 lines
1.9 KiB
Markdown
|
#sql
|
|||
|
|
#unity/日常积累
|
|||
|
|
|
|||
|
|
![[Pasted image 20250510173018.png]]
|
|||
|
|
|
|||
|
|
#### 场景 1：获取表记录数（正确用法）
|
|||
|
|
|
|||
|
|
``` cs
|
|||
|
|
// ✅ 正确使用 ExecuteScalar
|
|||
|
|
public int GetUserCount()
|
|||
|
|
{
|
|||
|
|
string sql = "SELECT COUNT(*) FROM Users";
|
|||
|
|
return _db.ExecuteScalar<int>(sql); // 直接返回整数
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// ❌ 错误用法（用 Execute 会返回受影响行数，此处永远返回 0）
|
|||
|
|
public int GetUserCount_Wrong()
|
|||
|
|
{
|
|||
|
|
string sql = "SELECT COUNT(*) FROM Users";
|
|||
|
|
return _db.Execute(sql); // 错误！返回的是 0
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
#### 场景 2：删除过期数据
|
|||
|
|
|
|||
|
|
``` cs
|
|||
|
|
// ✅ 正确使用 Execute
|
|||
|
|
public int DeleteExpiredUsers()
|
|||
|
|
{
|
|||
|
|
string sql = "DELETE FROM Users WHERE LastLogin < datetime('now','-1 year')";
|
|||
|
|
return _db.Execute(sql); // 返回被删除的行数
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// ❌ 错误用法（用 ExecuteScalar 会返回 null）
|
|||
|
|
public void DeleteExpiredUsers_Wrong()
|
|||
|
|
{
|
|||
|
|
string sql = "DELETE FROM Users WHERE ...";
|
|||
|
|
var result = _db.ExecuteScalar<object>(sql); // 无意义操作
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### 4. 特殊场景注意事项
|
|||
|
|
|
|||
|
|
#### 参数化查询（防 SQL 注入）
|
|||
|
|
|
|||
|
|
``` cs
|
|||
|
|
// ✅ 安全方式
|
|||
|
|
public int GetAdultsCount(int minAge)
|
|||
|
|
{
|
|||
|
|
string sql = "SELECT COUNT(*) FROM Users WHERE Age > ?";
|
|||
|
|
return _db.ExecuteScalar<int>(sql, minAge);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// ❌ 危险方式（直接拼接字符串）
|
|||
|
|
public int GetAdultsCount_Unsafe(int minAge)
|
|||
|
|
{
|
|||
|
|
string sql = $"SELECT COUNT(*) FROM Users WHERE Age > {minAge}"; // SQL 注入风险
|
|||
|
|
return _db.ExecuteScalar<int>(sql);
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
### 5.事务中的使用
|
|||
|
|
|
|||
|
|
``` cs
|
|||
|
|
public void BatchUpdate()
|
|||
|
|
{
|
|||
|
|
_db.RunInTransaction(() =>
|
|||
|
|
{
|
|||
|
|
// 混合使用两种方法
|
|||
|
|
int count = _db.ExecuteScalar<int>("SELECT COUNT(*) FROM Logs");
|
|||
|
|
if (count > 1000)
|
|||
|
|
{
|
|||
|
|
int deleted = _db.Execute("DELETE FROM Logs WHERE Id IN (SELECT Id FROM Logs LIMIT 100)");
|
|||
|
|
Debug.Log($"已清理 {deleted} 条日志");
|
|||
|
|
}
|
|||
|
|
});
|
|||
|
|
}
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
![[Pasted image 20250510173323.png]]
|