mysql insert语句限制插入两条一样的数据，可控制并发插入相同的数据

 

 

传入sql语句的变量{$_uid}, {$_type},{$_info}, {_extfield0}
INSERT INTO log(uid, `type`, info, extfield0) SELECT {$_uid}, {$_type},{$_info}, {_extfield0} FROM DUAL
WHERE NOT EXISTS(SELECT 1 FROM log WHERE uid={$_uid} AND type = {$_type} AND info={$_info} AND extfield0 = {$_extfield0});

 

原理：在insert的时候查询该数据表中是否存在含有满足条件的记录，如果存在则不执行，执行成功影响行数+1，否则影响行数为0

如果需要获取该语句影响的行数，可以使用ROW_COUNT()来判断是否有插入数据，下面给出一个存储过程的例子供参考

.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(IN $_uid INT,IN $_type INT,IN $_info VARCHAR(255),IN $_extfield0 VARCHAR(255))
BEGIN
    INSERT INTO log(uid, `type`, info, extfield0) SELECT $_uid, $_type,$_info, $_extfield0 FROM DUAL 
    WHERE NOT EXISTS(SELECT 1 FROM log WHERE uid=$_uid AND type = $_type AND info=$_info AND extfield0 = $_extfield0);
    IF ROW_COUNT() = 1 THEN 
        # 插入唯一记录成功 执行后续操作
        SELECT "插入唯一数据成功";
    ELSE
        SELECT "插入唯一数据失败";
    END IF;
END$$

DELIMITER ;

 

转载于:https://www.cnblogs.com/sky-yu/p/9837696.html