MySQL 憑藉開源免費、高效能、易部署,成為 .NET 應用 (特別是 .NET Core / 5+ 跨平台場景) 的熱門資料庫選擇。本文系統梳理:原生 ADO.NET (MySqlConnector)、ORM (EF Core/Dapper)、連線池、安全強化、效能調優、遷移策略,助你構建穩健的資料存取層。
| 維度 | MySqlConnector (社群推薦) | MySql.Data (Oracle 官方) |
|---|---|---|
| 效能 | ✅ 更快、記憶體佔用更低、完全異步實現 | 較舊、同步/異步混用 |
| 相容性 | .NET Standard 2.0+、.NET Core/5+/Framework 4.6.1+ | .NET Framework 4.5+、.NET Core 3.1+ |
| 功能完整度 | 支援所有主流協議、負載均衡、管線化 | 官方支援、某些企業功能優先 |
| 維護活躍度 | GitHub 星標 3k+、持續釋出 | 更新較慢 |
| NuGet 套件 | MySqlConnector | MySql.Data |
建議: 新專案**首選 MySqlConnector**,舊專案遷移成本極低 (命名空間相同 MySql.Data.MySqlClient),僅需替換 NuGet 套件。
// appsettings.json
"ConnectionStrings": {
"MySql": "Server=127.0.0.1;Port=3306;Database=mydb;Uid=app_user;Pwd=*****;Charset=utf8mb4;SslMode=Required;Connection Lifetime=300;Pooling=true;MinPoolSize=5;MaxPoolSize=100;"
}
關鍵參數: Charset=utf8mb4 (對應 character_set_client)、SslMode=Required (生產環境強制加密)、Pooling=true (啟用連線池)、Connection Lifetime (秒,避免長連線殭屍)。
public class UserRepository
{
private readonly string _connStr;
public UserRepository(IConfiguration cfg) => _connStr = cfg.GetConnectionString("MySql");
public async Task GetByIdAsync(int id)
{
const string sql = "SELECT id, username, email, created_at FROM users WHERE id = @id";
await using var conn = new MySqlConnection(_connStr);
await conn.OpenAsync();
await using var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@id", id);
await using var reader = await cmd.ExecuteReaderAsync();
if (await reader.ReadAsync())
return new User { Id = reader.GetInt32(0), Username = reader.GetString(1), Email = reader.GetString(2), CreatedAt = reader.GetDateTime(3) };
return null;
}
public async Task InsertAsync(User user)
{
const string sql = @"INSERT INTO users (username, email, created_at) VALUES (@u, @e, @c); SELECT LAST_INSERT_ID();";
await using var conn = new MySqlConnection(_connStr);
await conn.OpenAsync();
await using var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@u", user.Username);
cmd.Parameters.AddWithValue("@e", user.Email);
cmd.Parameters.AddWithValue("@c", user.CreatedAt);
return Convert.ToInt32(await cmd.ExecuteScalarAsync());
}
}
**永遠使用參數化**,絕不拼接字串。cmd.Parameters.AddWithValue 方便但隱含型別推斷風險,關鍵查詢建議顯式指定 MySqlDbType:
cmd.Parameters.Add(new MySqlParameter("@id", MySqlDbType.Int32) { Value = id });
dotnet add package MySql.EntityFrameworkCore // Oracle 官方 Provider
// 或
dotnet add package Pomelo.EntityFrameworkCore.MySql // 社群版,效能更強、功能更全
public class AppDbContext : DbContext
{
public DbSet Users => Set();
protected override void OnConfiguring(DbContextOptionsBuilder opt)
=> opt.UseMySql(Configuration.GetConnectionString("MySql"),
ServerVersion.AutoDetect(Configuration.GetConnectionString("MySql"))); // 自動偵測版本
}
dotnet ef migrations add InitialCreate
dotnet ef database update
// 種子資料
protected override void OnModelCreating(ModelBuilder b) {
b.Entity().HasData(new User { Id = 1, Username = "admin", Email = "admin@example.com", CreatedAt = DateTime.UtcNow });
}
追求極致效能、手寫 SQL 掌控力時,Dapper 是最佳拍檔:
public async Task> GetActiveUsersAsync()
{
const string sql = "SELECT id, username, email FROM users WHERE is_active = 1";
await using var conn = new MySqlConnection(_connStr);
return await conn.QueryAsync(sql); // 自動映射
}
// 多重映射 (One-to-Many)
var lookup = new Dictionary();
await conn.QueryAsync(
@"SELECT u.id, u.username, o.id, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id",
(u, o) => {
if (!lookup.TryGetValue(u.Id, out var user)) lookup[u.Id] = user = u;
if (o.Id > 0) user.Orders.Add(o);
return user;
},
splitOn: "id");
return lookup.Values;
SHOW STATUS LIKE 'Threads_connected';、PerformanceSchema、APM 工具 (Datadog, Elastic APM)。MySqlBulkLoader (LOAD DATA INFILE) 或 EF Core BulkInsert 擴充套件。cmd.CommandTimeout = 30 (秒),防止長查詢佔用連線。SELECT, INSERT, UPDATE, DELETE, EXECUTE,禁止 DROP, ALTER, GRANT。require_secure_transport=ON,客戶端 SslMode=VerifyFull (驗證 CA)。audit_log 插件,記錄所有 DDL/DML 操作。ByteFX.Data.MySqlClient (Mono 早期),命名空間不同,遷移時需批次替換 using 陳述式。MySqlConnector,無程式碼變更即可獲得效能提升。App.config 連線字串移至 appsettings.json,使用 IConfiguration 統一管理。.NET 連接 MySQL 的最佳路徑已清晰:**驅動用 MySqlConnector,ORM 首選 EF Core (Pomelo Provider) 或 Dapper,配置 utf8mb4 + SSL + 連線池,落實參數化與最小權限**。這套組合在高併發、跨平台、雲原生環境下均經過大規模生產驗證,能為業務系統提供穩如磐石的資料存取基石。