MySQL 将 AUTO_INCREMENT 值设置为 MAX(id) + 1 个快捷方式? [英] MySQL set AUTO_INCREMENT value to MAX(id) + 1 shortcut?

问题描述

在删除部分行后，我经常需要重置"表的 AUTO_INCREMENT 值.让我用一个实际例子来解释:

I constantly need to "reset" the AUTO_INCREMENT value of my tables, after I delete a part of my rows. Let me explain with an actual example :

我有一个名为 CLIENT 的表.假设在删除一些行之前，auto_increment 被设置为 11.然后我删除最后的 4 行.auto_increment 仍然设置为 11.所以当我再次插入一些客户端时，它会造成一个 id 孔.

I have a table called CLIENT. Let us say before removing some rows, the auto_increment was set to 11. Then I delete the 4 lasts rows. The auto_increment is still set to 11. So when I will insert some clients again, it will make a hole of id.

我总是需要清理"auto_increment，例如在下面使用此功能:

I always need to "clean" the auto_increment, e.g. using this function below :

function cleanAutoIncrement($tableName, $columnAutoIncrement, $pdo)
{
    $r = false;

    try {
        $p = $pdo->prepare("SELECT IFNULL(MAX($columnAutoIncrement) + 1, 1) AS 'max' FROM $tableName LIMIT 1;");
        $p->execute();

        $max = $p->fetch(PDO::FETCH_ASSOC)['max'];

        $p = $pdo->prepare("ALTER TABLE $tableName AUTO_INCREMENT = $max;"):

        $p->execute();

        $r = true;
    }
    catch(Exception $e) {
        $r = false;
    }

    return $r;
}

该函数所做的是获取表中的最大id，然后将其增加1，并返回其值(如果表中没有行，则返回1).然后我更改表以重置干净"的 id，以免留下任何 id 漏洞.

What the function do is to get the maximum id in the table, then increments it of 1, and return its value (if there was no rows in table, it return 1). Then I alter the table to reset a "clean" id in order not to let any hole of id.

问题

是否有任何 MySQL 命令可以执行此任务而无需手动执行此操作?

Is there any MySQL command to perform this task without having to do this manually ?

推荐答案

关闭此问题，MySQL 中不存在快捷方式，不建议执行此任务.

To close this question, no shortcut exists in MySQL and it is not recommended to perform this task.

这篇关于MySQL 将 AUTO_INCREMENT 值设置为 MAX(id) + 1 个快捷方式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！