mysqli_insert_id: ID generated by AUTO_INCREMENT column by the last query

$query = "INSERT INTO student (name, class, mark, gender) 
	VALUES ('John Doe', 'Three', 85, 'Male')";

if ($connection->query($query) === TRUE) {
    echo "Record inserted successfully!";
    echo "<br>Insert ID: " . $connection->insert_id;
} else {
    echo "Error: " . $connection->error;
}

$query = "INSERT INTO student (name, class, mark, gender) 
	VALUES ('John Doe', 'Three', 85, 'Male')";

if (mysqli_query($connection, $query)) {
    echo "Record inserted successfully!";
    echo "<br>Insert ID: " . mysqli_insert_id($connection);
} else {
    echo "Error: " . mysqli_error($connection);
}

Record inserted successfully!
Insert ID: 38

Understanding Auto-Increment IDs in MySQL

In MySQL, an auto-increment field is commonly used to generate unique, sequential numbers for a primary key. The value is automatically increased every time a new row is inserted, ensuring that each record has a unique identifier.

Key Scenarios with Auto-Increment IDs

• ID Auto-Incrementation: Each time a new row is inserted into a table with an auto-increment column, MySQL automatically assigns the next number in the sequence to the primary key, ensuring it is unique and incremented by 1 (by default).
  The starting value is typically 1, but it can be changed using the AUTO_INCREMENT property.

  CREATE TABLE IF NOT EXISTS `student` (
    `id` int(2) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `class` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `mark` int(3) NOT NULL DEFAULT '0',
    `gender` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT 'male',
    UNIQUE KEY `id` (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;

  Once a new record is inserted it will get the id = 50
• What Happens When a Row Is Deleted?: If a row is deleted, the associated ID is not reused by MySQL. For instance, if you have records with IDs 1, 2, 3, and 4, and you delete the record with ID 3, inserting a new row will assign it ID 5, not 3. MySQL doesn't fill the gap by reusing the deleted ID by default.
• Manual Insertion of IDs: You can explicitly specify the value of an auto-increment column when inserting a new row. However, if you insert a value manually, MySQL will still continue auto-incrementing from the highest existing ID. For example, if the highest ID is 10 and you manually insert ID 100, the next auto-incremented ID will be 101.
• Reusing Deleted IDs: Under certain conditions, it is possible to reuse deleted IDs:
  • If the table has been truncated (TRUNCATE TABLE), the auto-increment counter will reset, and MySQL will start assigning IDs from 1 again (or the defined starting value).
  • After deleting rows, you can manually reset the auto-increment counter using the following query:

    ALTER TABLE table_name AUTO_INCREMENT = next_value;

    For example, if you want to reset the counter to fill the gap after deleting a row, you can specify the next ID.
• Can You Manually Set the ID Value?: Yes, you can manually insert a value into an auto-increment column as long as it doesn't conflict with existing values. However, manually setting the ID may cause gaps in the sequence, which will not be reused unless you reset the auto-increment value using the `ALTER TABLE` statement.
• Skipping IDs: In cases where a transaction is started and rolled back, or an insert query fails, the auto-increment value will still be incremented. This can lead to gaps in the sequence, but these gaps are not automatically reused by MySQL.

Practical Examples

• Inserting Records and Auto-Increment:

  INSERT INTO student (name, class, mark, gender) 
  	VALUES ('John Doe', 'Three', 85, 'Male')

  Here, MySQL will automatically assign the next available ID to the `student` table's primary key.
• Manually Setting an ID:

  INSERT INTO student (id,name, class, mark, gender) 
  	VALUES (55,'John Doe', 'Three', 85, 'Male')

  Manually setting the ID to 55 will work, and future auto-incremented values will start from 56.
  If we try to add same id 55 again then we will get error.
  Error: Duplicate entry '55' for key 'student.id'
• Resetting the Auto-Increment Value:

  ALTER TABLE student AUTO_INCREMENT = 5;

  This command will reset the next auto-increment value to 5, reusing IDs after deletions or truncate or adjusting the sequence.

Conclusion

• By default, MySQL does not reuse deleted auto-increment IDs, and new rows will be assigned the next available ID in sequence.
• You can manually insert values into auto-increment columns, but this may create gaps in the ID sequence.
• In certain conditions, such as after truncating a table or using ALTER TABLE, you can reset or reuse auto-increment values.
• It is possible to encounter gaps in the sequence due to failed transactions or manual deletions, but these gaps are not automatically filled by MySQL.