MYSQL在插入时获得自动增量/mysql_insert_id() [英] MYSQL get auto increment/mysql_insert_id() at INSERT

问题描述

我想在执行INSERT时获取新条目的auto_incr值，并动态创建表字段'url'.

示例:

数据库表字段:
id，类别，性别，年龄，网址(我想在网址末尾插入auto_incr)

变量:
$ category =员工；
$ sex =男性；
$ age = 30;

插入表VALUES(NULL，$ category，$ sex，$ age，'mywebsite.com/employee-male-30-00000001')

注意:假设新插入的ID为00000001

我当前正在使用空白网址插入新条目，然后获取mysql_insert_id()，然后使用该网址更新新条目.

是否有更好的方法可以仅通过一次数据库交互来完成此任务?

解决方案

您可以使用一个事务完成此操作，但是您不能使用一个 SQL语句完成此操作./p>

原因是您无法获得生成的ID，直到为时已晚，无法修改其他列中的值.例如，如果要尝试使用触发器执行此操作，则无法在BEFORE触发器中获取自动增量值，因为尚未生成该值.但是您无法在AFTER触发器中修改url之类的列的值.

唯一的解决方案是执行INSERT，然后立即使用UPDATE修改您的url.在事务内执行此操作，以确保没有其他线程可以看到部分完成的行.

mysql_query("START TRANSACTION");
mysql_query("INSERT INTO `table` 
             VALUES(NULL,$category,$sex,$age,'mywebsite.com/employee-male-30-')");
mysql_query("UPDATE `table` SET url = CONCAT(url, LPAD(id, 8, '0')) 
             WHERE id = LAST_INSERT_ID()");
mysql_query("COMMIT");

如果使用旧的ext/mysql接口，则必须执行START TRANSACTION和COMMIT作为查询字符串，因为该接口没有任何直接函数.

但是最好切换到PDO，因为 ext/mysql已弃用，并将在以后的PHP版本中将其删除.这样一来，您就可以停止将PHP变量放入SQL字符串中.

$pdo = new PDO(...);

$pdo->beginTransaction();

$stmt = $pdo->prepare("
    INSERT INTO `table` 
    SET category = ?, sex = ?, age = ?, url = ?");
$stmt->execute([$category, $sex, $age, "mywebsite.com/employee-male-30-"]);

$pdo->exec("
    UPDATE `table` 
    SET url = CONCAT(url, LPAD(id, 8, '0')) 
    WHERE id = LAST_INSERT_ID()");

$pdo->commit();

I would like to get auto_incr value of new entry while performing INSERT, and create the table field 'url' on the fly.

Example:

database table fields:
id,category,sex,age,url (i want to insert a url with the auto_incr at the end)

variables:
$category = employee;
$sex = male;
$age = 30;

INSERT INTO table VALUES(NULL,$category,$sex,$age,'mywebsite.com/employee-male-30-00000001')

note: assuming the newly inserted id is 00000001

I am currently inserting the new entry with a blank url, and then getting the mysql_insert_id(), and then updating the new entry with the url.

Is there a better way to accomplish this with just one database interaction?

解决方案

You can do it with one transaction, but you cannot do this with one SQL statement.

The reason is that you can't get the id generated until it is too late to modify the values in other columns. For example, if you were to try to do this with triggers, you can't get the auto-increment value in a BEFORE trigger because it hasn't been generated yet. But you can't modify the value of columns like url in an AFTER trigger.

The only solution is to perform the INSERT and then immediately use an UPDATE to modify your url. Do this within a transaction to ensure that no other thread can see the partially-complete row.

mysql_query("START TRANSACTION");
mysql_query("INSERT INTO `table` 
             VALUES(NULL,$category,$sex,$age,'mywebsite.com/employee-male-30-')");
mysql_query("UPDATE `table` SET url = CONCAT(url, LPAD(id, 8, '0')) 
             WHERE id = LAST_INSERT_ID()");
mysql_query("COMMIT");

If you're using the old ext/mysql interface, you have to execute the START TRANSACTION and COMMIT as query strings, because that interface doesn't have any direct functions for them.

But it would be better for you to switch to PDO, because ext/mysql is deprecated and will be removed in a future version of PHP. That would allow you to stop putting PHP variables into SQL strings.

$pdo = new PDO(...);

$pdo->beginTransaction();

$stmt = $pdo->prepare("
    INSERT INTO `table` 
    SET category = ?, sex = ?, age = ?, url = ?");
$stmt->execute([$category, $sex, $age, "mywebsite.com/employee-male-30-"]);

$pdo->exec("
    UPDATE `table` 
    SET url = CONCAT(url, LPAD(id, 8, '0')) 
    WHERE id = LAST_INSERT_ID()");

$pdo->commit();

这篇关于MYSQL在插入时获得自动增量/mysql_insert_id()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！