How to make sure that there are no gaps in the generated values ​​of an auto-incrementing primary key?

Question

MySQL

MySQL 5.7 has an InnoDB table with an auto-increment surrogate primary key: id INT UNSIGNED NOT NULL AUTO_INCREMENT. An application writes data to this table through a stored function in single-threaded mode, making records to the table one by one. How can I make sure that there are no gaps in the generated id values ​​(I am not responsible for the operation of the external application, but it may try to write data that does not pass validation to the table)?
The stored function through which the application writes to the database table looks like this:

CREATE FUNCTION insert_material (n VARCHAR(255), d DATE) RETURNS INT UNSIGNED
BEGIN
INSERT INTO Material(name, date) VALUE(n, d);
RETURN LAST_INSERT_ID();
END

What is the easiest way to modify this function to ensure that there are no gaps in the generated id values?

Answer 1

Stop doing nonsense ;)
Stop paying attention to the holes in the auto-increment and focus on the application logic.

Answer 2

autoincrement can have increments greater than 1. eg master-to-master replication. Insert it duplicate update also increases it even in the case of an update. fighting with holes is pointless