MySQL set AUTO_INCREMENT value to MAX(id) + 1 shortcut?

Question

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 :

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.

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;
}

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.

QUESTION

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

Answer 1

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