MySQL事务和触发器
嘿伙计们，这是我无法弄清楚的一个。我们在数据库中有一个表，PHP 在其中插入记录。我创建了一个触发器来计算要插入的值。计算值应该是唯一的。然而，有时我会发现表中的几行有完全相同的数字。该数字是年、月、日以及当天的订单编号的组合。我认为插入的单个操作是原子的，并且在事务进行时表被锁定。我需要计算值是唯一的...服务器版本为 5.0.88。服务器是Linux CentOS 5，具有双核处理器。
这是触发器：
CREATE TRIGGER bi_order_data BEFORE INSERT ON order_data
FOR EACH ROW BEGIN
SET NEW.auth_code = get_auth_code();
END;
相应的例程如下所示：
CREATE FUNCTION `get_auth_code`() RETURNS bigint(20)
BEGIN
DECLARE my_auth_code, acode BIGINT;
SELECT MAX(d.auth_code) INTO my_auth_code
FROM orders_data d
JOIN orders o ON (o.order_id = d.order_id)
WHERE DATE(NOW()) = DATE(o.date);
IF my_auth_code IS NULL THEN
SET acode = ((DATE_FORMAT(NOW(), "%y%m%d")) + 100000) * 10000 + 1;
ELSE
SET acode = my_auth_code + 1;
END IF;
RETURN acode;
END
Hey guys, here is one I am not able to figure out. We have a table in database, where PHP inserts records. I created a trigger to compute a value to be inserted as well. The computed value should be unique. However it happens from time to time that I have exact same number for few rows in the table. The number is combination of year, month and day and a number of the order for that day. I thought that single operation of insert is atomic and table is locked while transaction is in progress. I need the computed value to be unique...The server is version 5.0.88. Server is Linux CentOS 5 with dual core processor.
Here is the trigger:
CREATE TRIGGER bi_order_data BEFORE INSERT ON order_data
FOR EACH ROW BEGIN
SET NEW.auth_code = get_auth_code();
END;
Corresponding routine looks like this:
CREATE FUNCTION `get_auth_code`() RETURNS bigint(20)
BEGIN
DECLARE my_auth_code, acode BIGINT;
SELECT MAX(d.auth_code) INTO my_auth_code
FROM orders_data d
JOIN orders o ON (o.order_id = d.order_id)
WHERE DATE(NOW()) = DATE(o.date);
IF my_auth_code IS NULL THEN
SET acode = ((DATE_FORMAT(NOW(), "%y%m%d")) + 100000) * 10000 + 1;
ELSE
SET acode = my_auth_code + 1;
END IF;
RETURN acode;
END
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
要么表被锁定（使用 MyISAM），要么锁定记录（使用 InnoDB），但不能同时锁定两者。
既然你提到了“事务”，我假设正在使用InnoDB。
InnoDB 的优点之一是没有表锁，因此没有什么可以阻止许多触发器的主体同时执行并产生相同的结果。
Either table is locked (MyISAM is used) or records may be locked (InnoDB is used), not both.
Since you mentioned "transaction", I assume that InnoDB is in use.
One of InnoDB advantages is absence of table locks, so nothing will prevent many triggers' bodies to be executed simultaneously and produce the same result.