mysql插入时防止账号重复插入迸发

方案零 数据库添加唯一索引 

ALTER TABLE your_table ADD UNIQUE (name);　

 

 当数据库表有软删除  IsDeleted 字段的时候 就只能用以下方式了

注: 网友提了一种方案就是 IsDeleted  设置null  唯一索引不删除  为null的标识删除(此方案待验证)

方案一 INSERT INTO SELECT

CREATE TABLE Table_Test (
    Id INT ( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT,
     `Name` VARCHAR ( 10 ) DEFAULT NULL,
    Mobile VARCHAR ( 20 ) DEFAULT NULL,
    PRIMARY KEY ( Id ),
    UNIQUE INDEX Idx_Name ( `Name` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 0 DEFAULT CHARSET = UTF8;


INSERT INTO Table_Test (`Name`,Mobile) SELECT '王4','13578901236' FROM DUAL WHERE NOT EXISTS (SELECT NAME FROM Table_Test WHERE NAME = '王4') 

　　

方案二 触发器

 

CREATE TABLE `table_test`  (
  `Id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `Name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL,
  `Mobile` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NULL DEFAULT NULL,
  `IsDeleted` bit(1) NOT NULL,
  PRIMARY KEY (`Id`) USING BTREE
 
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_as_ci ROW_FORMAT = Dynamic;

-- 插入触发器
DELIMITER $$


CREATE TRIGGER trg_CheckUniqueNameOnInsert
BEFORE INSERT ON Table_Test
FOR EACH ROW
BEGIN
    IF NEW.IsDeleted = 0 THEN
        IF EXISTS (
            SELECT 1
            FROM Table_Test
            WHERE Name = NEW.Name AND IsDeleted = 0
        ) THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Duplicate entry for Name with IsDeleted=0';
        END IF;
    END IF;
END$$

 -- 更新触发器
DELIMITER $$

CREATE TRIGGER trg_CheckUniqueNameOnUpdate
BEFORE UPDATE ON Table_Test
FOR EACH ROW
BEGIN
    -- 检查更新后的记录是否满足唯一性约束
    IF NEW.IsDeleted = 0 THEN
        IF EXISTS (
            SELECT 1
            FROM Table_Test
            WHERE Name = NEW.Name AND IsDeleted = 0 AND Id <> NEW.Id
        ) THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Duplicate entry for Name with IsDeleted=0 on update';
        END IF;
    END IF;
END$$

DELIMITER ;

方案三 分布式事务锁

using StackExchange.Redis;
using System;

class Program
{
    static void Main(string[] args)
    {
        // 连接到 Redis
        ConnectionMultiplexer redis = ConnectionMultiplexer.Connect("localhost");
        IDatabase db = redis.GetDatabase();

        string key = "mykey";

        // 监控键
        var transaction = db.CreateTransaction();
        transaction.AddCondition(Condition.KeyEquals(key, "oldvalue"));

        // 设置新值
        transaction.StringSetAsync(key, "newvalue");

        // 提交事务
        bool committed = transaction.Execute();
        if (committed)
        {
            Console.WriteLine("事务成功执行！");
        }
        else
        {
            Console.WriteLine("事务失败：键已被其他客户端修改！");
        }

        redis.Close();
    }
}

方案四 悲观锁

SELECT * FROM Table_Test WHERE NAME='xxx' AND IsDeleted = 0 FOR UPDATE  

方案五 事务级别最高

 

using (var context = new YourDbContext())
{
    using (var transaction = context.Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
    {
        try
        {
            // 检查是否存在未删除的记录
            var exists = context.TableTest
                .Any(t => t.Name == "王4" && t.IsDeleted == 0);

            if (!exists)
            {
                // 插入新记录
                var newRecord = new TableTest
                {
                    Name = "王4",
                    Mobile = "13578901236",
                    IsDeleted = 0 // 标记为未删除
                };

                context.TableTest.Add(newRecord);
                context.SaveChanges();
            }

            transaction.Commit();
        }
        catch (Exception)
        {
            transaction.Rollback();
            throw;
        }
    }
}

　　

其他方案 队列(MQ)