c# - 如何抑制 MySql.Data.MySqlClient.MySqlException 重复条目

Question

所以我有一个具有多列唯一约束的数据库。显然是为了防止重复。在调试我的应用程序时，我的控制台完全充满了 MySql.Data.MySqlClient.MySqlException

有没有办法抑制这些，因为这是数据库的预期功能？捕获异常似乎并没有做到。它将根据我的 catch(MySqlException e) 语句进行处理，但仍会在控制台中显示 MySql.Data.MySqlClient.MySqlException。

这是我的代码:

public void InsertNewTeamHistory(TeamHistoryRow teamHistoryRow)
    {
        //open connection
        if (this.OpenConnection)
        {
            try
            {
                var query =
                    "INSERT INTO teamhistory(MatchURL, Date, TeamOne, TeamOneScore, TeamTwo, TeamTwoScore, Map, Event, Outcome) " +
                    "VALUES(?MatchURL, ?Date, ?TeamOne, ?TeamOneScore, ?TeamTwo, ?TeamTwoScore, ?Map, ?Event, ?Outcome)";

                //create command and assign the query and connection from the constructor
                using (var cmd = new MySqlCommand(query, _connection))
                {
                    if (cmd.Connection.State == ConnectionState.Open)
                    {
                        cmd.Parameters.Add("?MatchURL", MySqlDbType.VarChar).Value = teamHistoryRow.MatchUrl;
                        cmd.Parameters.Add("?Date", MySqlDbType.Date).Value = teamHistoryRow.Date;
                        cmd.Parameters.Add("?TeamOne", MySqlDbType.VarChar).Value = teamHistoryRow.TeamOne;
                        cmd.Parameters.Add("?TeamOneScore", MySqlDbType.Int32).Value = teamHistoryRow.TeamOneScore;
                        cmd.Parameters.Add("?TeamTwo", MySqlDbType.VarChar).Value = teamHistoryRow.TeamTwo;
                        cmd.Parameters.Add("?TeamTwoScore", MySqlDbType.Int32).Value = teamHistoryRow.TeamTwoScore;
                        cmd.Parameters.Add("?Map", MySqlDbType.VarChar).Value = teamHistoryRow.Map;
                        cmd.Parameters.Add("?Event", MySqlDbType.VarChar).Value = teamHistoryRow.Event;
                        cmd.Parameters.Add("?Outcome", MySqlDbType.VarChar).Value = teamHistoryRow.Outcome;

                        //Execute command
                        cmd.ExecuteNonQuery();

                        //close connection
                        this.CloseConnection();
                    }
                    else
                    {
                        throw new Exception("Failed - Connection to the DB NOT open.");
                    }
                }
            }
            catch (MySqlException e)
            {
                switch (e.Number)
                {
                    case 1062:
                        Console.WriteLine("Found Duplicate TeamHistoryRow, Not Inserting.");
                        break;
                    case 0:
                        Console.WriteLine("Well, we fucked up.");
                        break;
                    default:
                        throw;
                }
            }
            finally
            {
                try
                {
                    this.CloseConnection();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
            }
        }
    }

Answer 1

MySql 有一个很好用且易于使用的功能，可以在您的语句中添加一条规则，告诉您在发现重复键时该怎么做。

INSERT INTO ..... ON DUPLICATE KEY UPDATE .....

所以你的查询语句可以重写为

INSERT INTO teamhistory(MatchURL, Date, TeamOne, TeamOneScore, TeamTwo, 
                       TeamTwoScore, Map, Event, Outcome) 
                       VALUES(?MatchURL, ?Date, ?TeamOne, 
                       ?TeamOneScore, ?TeamTwo, ?TeamTwoScore, 
                       ?Map, ?Event, ?Outcome) 
ON DUPLICATE KEY UPDATE MatchUrl = ?MatchUrl

这假定字段 MatchUrl 是触发重复异常的键。因此，当检测到重复键时，sql 会执行 UPDATE 更改表中的任何内容

通过标准化command MERGE，其他数据库系统也可以使用相同的逻辑。