困难插入

发布于 2024-11-06 19:42:39 字数 416 浏览 0 评论 0原文

我正在使用 mysql/php。

我的表看起来像

id (int autoincrement)  
voucher(varchar 25)

我正在生成优惠券代码，其中代码为：

1 个随机数（例如 64）
1 个固定数（例如 352）
id 字段的值（1,2,3,4,5...）
1 个校验位（luhn ）算法）。

使用 php 执行此操作的明显问题是我必须执行选择，获取下一个自动增量值，计算代码，插入，此时可能已插入另一行。

相反，我想做的是，

插入凭证（凭证）值（'64352'）并让它生成我的其余部分。

我该怎么做？函数/触发器？

原文

I am using mysql/php.

my table looks like

id (int autoincrement)  
voucher(varchar 25)

I am generating voucher codes where the code is:

1 random number (eg 64)
1 fixed number (eg 352)
value of id field (1,2,3,4,5...)
1 checkdigit (luhn algorithm).

The obvious issue in doing this with php is that I have to do a select, get the next autoincrement value, calculate the code, insert, by which time another row could have been inserted.

What I want to do is instead,

do insert into vouchers (voucher) value('64352') and have it generate the rest of me.

How can I do this? function/trigger?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谢绝鈎搭 2024-11-13 19:42:39

如果您使用 InnoDB，一个简单的解决方法就是使用现有代码并将其包装在 MySQL 事务中。即伪代码：

mysql_query("START TRANSACTION");

# get next autoincrement value into: $next

# do your INSERT query

# get the actual last inserted ID into: $actual

if ($next === $actual) {
  mysql_query("COMMIT");
} else {
  mysql_query("ROLLBACK");
  # and raise an Exception or return an error
}

编辑/添加：

既然您提到了触发器，我就对此进行了研究并相信它是可行的。有几个问题。

从触发器访问“下一个自动增量ID”...我不知道执行此操作的“好”方法。在 INSERT 查询中，您只能访问 NEW 而不能访问 OLD（现有行）值（请参阅触发语法）。我在示例中要做的只是在服务器上维护一个单独的计数器@vcount。要将此值初始化为当前的 ID，您只需执行“SET @vcount = 42;”
卢恩算法。您必须将其实现为 SQL 函数。这里是一个 SQL 中的 Luhn 验证器，您可以从中复制。或者，您可以使用 MD5 等本机 MySQL 函数进行哈希/校验和（如果您需要短凭证代码，则仅使用前 X 个字符）。无论如何，您都需要创建一个哈希函数...我将在下面使用“Luhn”。

无论如何，触发器如下所示：

delimiter //
CREATE TRIGGER make_voucher_code BEFORE INSERT ON vouchers
FOR EACH ROW
BEGIN
    SET @vcount = @vcount + 1;
    SET NEW.voucher = CONCAT(
        NEW.voucher, 
        CAST(@vcount AS CHAR),
        Luhn(CONCAT(NEW.voucher, CAST(@vcount AS CHAR))));
END;
//
delimeter ;

然后，在您的 INSERT 中，您将按照您的建议在查询中放入“64352”。触发器将附加其余部分。

就我个人而言，除非您或其他人可以更好地解决自动增量/@vcount 问题，否则我仍然更喜欢使用 MySQL 事务，这比保持所有内容原子性并将所有应用程序代码保留在 PHP 中更好。

If you are using InnoDB, an easy fix would just be to use your existing code and wrap it in a MySQL transaction. i.e. in pseudocode:

mysql_query("START TRANSACTION");

# get next autoincrement value into: $next

# do your INSERT query

# get the actual last inserted ID into: $actual

if ($next === $actual) {
  mysql_query("COMMIT");
} else {
  mysql_query("ROLLBACK");
  # and raise an Exception or return an error
}

EDIT/ADD:

Since you mentioned triggers, I looked into that and believe it's doable. A couple of issues.

Accessing the "next autoincrement ID" from the trigger ... I don't know a "good" way to do this. In INSERT queries you have access only to NEW and not OLD (existing rows) values (see Trigger syntax). What I'm doing to do in the example is just maintain a separate counter @vcount on the server. To initialize this value to whatever the ID currently is, you would just do "SET @vcount = 42;"
Luhn algorithm. You will have to implement this as a SQL function. Here's a Luhn validator in SQL you could crib from. Alternately you could hash/checksum with a native MySQL function like MD5 (and use only the first X chars if you need a short voucher code). In any case you need to make a hash function ... I'll just use "Luhn" below.

Anyway here's what the trigger would look like:

delimiter //
CREATE TRIGGER make_voucher_code BEFORE INSERT ON vouchers
FOR EACH ROW
BEGIN
    SET @vcount = @vcount + 1;
    SET NEW.voucher = CONCAT(
        NEW.voucher, 
        CAST(@vcount AS CHAR),
        Luhn(CONCAT(NEW.voucher, CAST(@vcount AS CHAR))));
END;
//
delimeter ;

Then, in your INSERTs, you would put just '64352' in the query, as you've suggested. The trigger would append the rest.

Personally, unless you or someone else can solve the autoincrement/@vcount problem better, I would still prefer doing the MySQL transaction, which will do a better job than this of keeping everything atomic and keeping all your app code in PHP.

回复收藏 0 原文