0

After reading the discussion here (How to reset AUTO_INCREMENT in MySQL?) and ViralPatel's post (https://www.viralpatel.net/reseting-mysql-autoincrement-column/) and a few more posts, I found out that the problem with ALTER TABLE tablename AUTO_INCREMENT = 1 is that it still won't "restart" an id (which is an INT primary key). I also tried it myself but the id just kept incrementing from the last existing id in the table. I want to avoid having to explicitly declare an id for my database. Particularly, this quote:

Note that you cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.

I came up with this function but I am worried that it will be a bad thing because it HAS to retrieve the whole column of ids before returning a new id.

function id_from_one($table, $id_column_name) {
    include 'includes/dbh.inc.php'; // This just creates the $conn variable and connects to the database.
    $counter = 1;
    $sql     = "SELECT $id_column FROM $table;";
    $result  = $conn->query($sql);
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            if ($counter == $row[$id_column_name]) { $counter++; }
            else { break; }
        }
    }
    $conn->close();
    return $counter;
}

Am I understanding this correctly? Or is there actually a way to do this? Why does MySQL do that anyway?

astigmatik
  • 134
  • 1
  • 8
  • 1
    If the value of this column matters to you than either it shouldn't be an id, or it shouldn't be an AI. – Strawberry Nov 04 '20 at 07:21
  • 1
    The whole idea behind the auto_inc is that it's unique to each record, and the reason why mysql will 'adjust' it so as not to overlap with existing values. If you dont want this feature, then an auto_inc is probably a bad choice. – FreudianSlip Nov 04 '20 at 07:26

0 Answers0