C#连接MySQL实战：从基础CRUD到性能优化

1. 项目概述C#与MySQL的桥梁搭建在工业控制、物联网和传统业务系统开发中C#与MySQL的组合堪称经典搭配。作为.NET生态的主力语言C#凭借其优雅的语法和强大的框架支持成为Windows平台开发的首选而MySQL作为最流行的开源关系型数据库以其稳定性和高性能在中小型项目中占据重要地位。两者结合既能满足业务系统对数据持久化的需求又能保持较低的技术成本。我曾在多个智能制造项目中采用这种技术组合比如某汽车零部件生产线的数据采集系统需要实时记录设备状态参数并生成报表。通过C#开发的上位机程序配合MySQL数据库不仅实现了每秒2000条数据的稳定写入还能支持20个并发客户端的实时查询。这种组合的实用性在实践中得到了充分验证。2. 环境准备与基础配置2.1 MySQL安装与初始化从MySQL官网下载最新社区版安装包时建议选择包含Workbench的完整套件。安装过程中有几个关键点需要注意身份验证方式选择Legacy Authentication旧式验证避免后续连接时出现加密方式不兼容的问题端口配置保持默认3306即可除非有特殊冲突字符集务必选择utf8mb4以支持完整的Unicode字符包括emoji设置root密码后建议立即创建专用应用账户CREATE USER appuser% IDENTIFIED BY SecurePass123!; GRANT ALL PRIVILEGES ON mydatabase.* TO appuser%; FLUSH PRIVILEGES;生产环境中务必限制IP访问范围将%替换为具体客户端IP2.2 Visual Studio环境配置在VS2022中新建C#项目后需要通过NuGet包管理器添加MySQL连接驱动右键项目 → 管理NuGet程序包搜索MySql.Data并安装最新稳定版当前为8.0.33或者通过包管理器控制台执行Install-Package MySql.Data -Version 8.0.33对于.NET Core/.NET 5项目建议使用MySqlConnector替代官方驱动它在异步操作和连接池管理方面表现更优Install-Package MySqlConnector3. 基础连接与CRUD操作3.1 建立数据库连接创建连接字符串时推荐将配置保存在appsettings.json中{ ConnectionStrings: { MySQL: serverlocalhost;databasemydb;userappuser;passwordSecurePass123!;CharSetutf8mb4; } }在代码中通过ConfigurationBuilder读取配置using MySql.Data.MySqlClient; using Microsoft.Extensions.Configuration; var builder new ConfigurationBuilder() .SetBasePath(Directory.GetCurrentDirectory()) .AddJsonFile(appsettings.json); var config builder.Build(); string connStr config.GetConnectionString(MySQL); using (var conn new MySqlConnection(connStr)) { try { await conn.OpenAsync(); Console.WriteLine(连接成功服务器版本 conn.ServerVersion); } catch (MySqlException ex) { Console.WriteLine($错误 {ex.Number}: {ex.Message}); } }3.2 参数化查询实践为防止SQL注入必须使用参数化查询。以下是完整的CRUD示例// 插入数据 var insertCmd new MySqlCommand( INSERT INTO products (name, price, stock) VALUES (name, price, stock), conn); insertCmd.Parameters.AddWithValue(name, 工业传感器); insertCmd.Parameters.AddWithValue(price, 299.99); insertCmd.Parameters.AddWithValue(stock, 100); await insertCmd.ExecuteNonQueryAsync(); Console.WriteLine(插入成功ID insertCmd.LastInsertedId); // 查询数据 var selectCmd new MySqlCommand( SELECT id, name, price FROM products WHERE stock minStock, conn); selectCmd.Parameters.AddWithValue(minStock, 50); using var reader await selectCmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { Console.WriteLine($产品{reader[name]}, 价格{reader.GetDecimal(2)}); } // 更新数据 var updateCmd new MySqlCommand( UPDATE products SET price price * factor WHERE stock 0, conn); updateCmd.Parameters.AddWithValue(factor, 1.1); int affectedRows await updateCmd.ExecuteNonQueryAsync(); Console.WriteLine($调价影响{affectedRows}条记录); // 事务处理示例 using var transaction await conn.BeginTransactionAsync(); try { var cmd1 new MySqlCommand(UPDATE account SET balance balance - 100 WHERE id 1, conn, transaction); var cmd2 new MySqlCommand(UPDATE account SET balance balance 100 WHERE id 2, conn, transaction); await cmd1.ExecuteNonQueryAsync(); await cmd2.ExecuteNonQueryAsync(); await transaction.CommitAsync(); Console.WriteLine(转账成功); } catch { await transaction.RollbackAsync(); Console.WriteLine(转账失败已回滚); }4. 高级应用与性能优化4.1 连接池管理MySQL .NET驱动默认启用连接池但需要合理配置// 优化后的连接字符串示例 serverlocalhost;databasemydb;userappuser;passwordSecurePass123!; Poolingtrue;MinimumPoolSize5;MaximumPoolSize100;ConnectionTimeout30; CharSetutf8mb4;DefaultCommandTimeout120关键参数说明Pooling启用连接池默认trueMinimumPoolSize初始连接数建议5-10MaximumPoolSize最大连接数根据服务器配置调整ConnectionTimeout获取连接超时秒DefaultCommandTimeout命令执行超时秒实际项目中我们通过压力测试发现当并发请求超过200时将MaximumPoolSize设置为CPU核心数×2 磁盘数×2能获得最佳性能4.2 批量操作优化对于物联网场景下的海量数据插入推荐使用LOAD DATA INFILE或批量插入// 批量插入示例每秒可处理1万条记录 var bulkCmd new MySqlCommand( INSERT INTO sensor_data (device_id, timestamp, value) VALUES (device_id, timestamp, value), conn); // 预先添加参数 bulkCmd.Parameters.Add(device_id, MySqlDbType.Int32); bulkCmd.Parameters.Add(timestamp, MySqlDbType.DateTime); bulkCmd.Parameters.Add(value, MySqlDbType.Float); // 批量准备数据 var data GenerateSensorData(); // 假设返回1000条记录 // 执行批量插入 foreach (var item in data) { bulkCmd.Parameters[device_id].Value item.DeviceId; bulkCmd.Parameters[timestamp].Value item.Timestamp; bulkCmd.Parameters[value].Value item.Value; await bulkCmd.ExecuteNonQueryAsync(); } // 更高效的MySqlBulkLoader方式 var loader new MySqlBulkLoader(conn) { TableName sensor_data, FieldTerminator ,, LineTerminator \n, FileName data.csv, // 预先准备好的CSV文件 NumberOfLinesToSkip 0, Local true }; int inserted await loader.LoadAsync(); Console.WriteLine($批量导入{inserted}条记录);4.3 存储过程调用对于复杂业务逻辑建议使用存储过程-- MySQL存储过程示例 DELIMITER // CREATE PROCEDURE sp_place_order( IN p_user_id INT, IN p_product_id INT, IN p_quantity INT, OUT p_order_id INT, OUT p_message VARCHAR(100) ) BEGIN DECLARE v_stock INT; DECLARE v_price DECIMAL(10,2); START TRANSACTION; SELECT stock, price INTO v_stock, v_price FROM products WHERE id p_product_id FOR UPDATE; IF v_stock p_quantity THEN INSERT INTO orders (user_id, product_id, quantity, total_price) VALUES (p_user_id, p_product_id, p_quantity, v_price * p_quantity); SET p_order_id LAST_INSERT_ID(); SET p_message 订单创建成功; UPDATE products SET stock stock - p_quantity WHERE id p_product_id; COMMIT; ELSE SET p_order_id -1; SET p_message CONCAT(库存不足剩余, v_stock); ROLLBACK; END IF; END // DELIMITER ;C#调用代码var cmd new MySqlCommand(sp_place_order, conn); cmd.CommandType CommandType.StoredProcedure; cmd.Parameters.AddWithValue(p_user_id, 123); cmd.Parameters.AddWithValue(p_product_id, 456); cmd.Parameters.AddWithValue(p_quantity, 2); cmd.Parameters.Add(p_order_id, MySqlDbType.Int32).Direction ParameterDirection.Output; cmd.Parameters.Add(p_message, MySqlDbType.VarChar, 100).Direction ParameterDirection.Output; await cmd.ExecuteNonQueryAsync(); Console.WriteLine($订单ID{cmd.Parameters[p_order_id].Value}); Console.WriteLine($结果{cmd.Parameters[p_message].Value});5. 常见问题排查与调试技巧5.1 连接问题诊断错误代码可能原因解决方案1045认证失败检查用户名/密码确认MySQL的auth_pluginmysql_native_password1130主机无访问权限在MySQL中执行GRANT授权或修改bind-address2003无法连接服务器检查MySQL服务状态、防火墙设置、网络连通性2013连接超时增大ConnectionTimeout值检查网络延迟5.2 性能问题优化查询缓慢使用EXPLAIN分析执行计划确保常用查询字段有索引避免SELECT *只查询必要字段连接泄漏确保所有连接都在using块中或手动Dispose监控SHOW STATUS LIKE Threads_connected死锁处理try { await command.ExecuteNonQueryAsync(); } catch (MySqlException ex) when (ex.Number 1213) // 死锁错误码 { await Task.Delay(new Random().Next(100, 500)); // 随机延迟 // 重试逻辑 }5.3 数据类型映射指南MySQL类型C#类型注意事项INTint注意NULL值处理使用int?DECIMALdecimal指定精度和范围DATETIMEDateTime时区问题需统一TEXTstring大文本考虑流式读取BLOBbyte[]大数据量需分块处理5.4 日志记录与监控建议在连接字符串中添加日志参数serverlocalhost;...;loggingtrue;然后在应用中配置日志记录// 使用Microsoft.Extensions.Logging var loggerFactory LoggerFactory.Create(builder { builder.AddConsole() .AddDebug() .SetMinimumLevel(LogLevel.Debug); }); MySqlConnectorLogManager.Provider new MicrosoftExtensionsLoggingLoggerProvider(loggerFactory);对于生产环境建议实现健康检查端点app.MapGet(/health, async () { try { using var conn new MySqlConnection(connStr); await conn.OpenAsync(); var cmd new MySqlCommand(SELECT 1, conn); await cmd.ExecuteScalarAsync(); return Results.Ok(Database healthy); } catch (Exception ex) { return Results.Problem(Database unavailable: ex.Message); } });6. 实际项目经验分享在工业物联网项目中我们遇到了传感器数据高频写入的挑战。通过以下优化实现了单机每秒2万的写入性能批量提交将数据缓存在内存中每500条或每1秒批量提交一次表分区按时间范围对数据表进行分区提高查询和维护效率连接复用在整个采集周期保持连接开放避免频繁重建连接异步写入使用async/await避免阻塞采集线程// 优化后的数据采集服务核心代码 public class DataCollector : BackgroundService { private readonly BatchQueueSensorData _queue new(500, TimeSpan.FromSeconds(1)); private readonly string _connStr; public DataCollector(IConfiguration config) { _connStr config.GetConnectionString(MySQL); _queue.OnBatchReady ProcessBatchAsync; } protected override async Task ExecuteAsync(CancellationToken stoppingToken) { using var conn new MySqlConnection(_connStr); await conn.OpenAsync(stoppingToken); while (!stoppingToken.IsCancellationRequested) { var data await ReadSensorDataAsync(); _queue.Enqueue(data); } } private async Task ProcessBatchAsync(IReadOnlyListSensorData batch) { using var conn new MySqlConnection(_connStr); await conn.OpenAsync(); using var transaction await conn.BeginTransactionAsync(); try { var cmd new MySqlCommand( INSERT INTO sensor_data (device_id, timestamp, value) VALUES (d, t, v), conn, transaction); cmd.Parameters.Add(d, MySqlDbType.Int32); cmd.Parameters.Add(t, MySqlDbType.DateTime); cmd.Parameters.Add(v, MySqlDbType.Float); foreach (var item in batch) { cmd.Parameters[d].Value item.DeviceId; cmd.Parameters[t].Value item.Timestamp; cmd.Parameters[v].Value item.Value; await cmd.ExecuteNonQueryAsync(); } await transaction.CommitAsync(); } catch { await transaction.RollbackAsync(); // 重试或记录错误 } } }另一个实用技巧是使用MySQL的JSON类型处理动态数据结构。在设备配置存储场景中特别有用// 动态配置存储示例 var config new { DeviceId SN-12345, Params new { SamplingRate 100, Thresholds new { Min 0.5, Max 4.5 } } }; var cmd new MySqlCommand( INSERT INTO device_config (device_id, config_json) VALUES (id, json), conn); cmd.Parameters.AddWithValue(id, config.DeviceId); cmd.Parameters.AddWithValue(json, JsonSerializer.Serialize(config.Params)); await cmd.ExecuteNonQueryAsync(); // 查询时提取JSON字段 var query new MySqlCommand( SELECT config_json-$.Thresholds.Min FROM device_config WHERE device_id id, conn); query.Parameters.AddWithValue(id, SN-12345); var minThreshold await query.ExecuteScalarAsync();