SELECT auto_increment when creating copy of table structure?

Question

MySQL

The bottom line is this: you need to create a table and transfer data from the old table into it, while maintaining the increment counter. There are many tables of the same type, so this business needs to be automated somehow. At the moment, the sequence of requests is as follows:

CREATE TABLE IF NOT EXISTS `new_admin_logs` (
  `id` int(11) NOT NULL,
  `name` varchar(40) NOT NULL DEFAULT '',
  `date` int(11) unsigned NOT NULL DEFAULT '0',
  `ip` varchar(16) NOT NULL DEFAULT '',
  `action` int(11) NOT NULL DEFAULT '0',
  `extras` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `new_admin_logs`
  ADD PRIMARY KEY (`id`), ADD KEY `date` (`date`);
  
INSERT `new_admin_logs` SELECT * FROM `old_admin_logs`;

SET @ai=(SELECT AUTO_INCREMENT FROM `information_schema`.`tables` WHERE `table_name`='old_admin_logs' AND `table_schema`=DATABASE());

ALTER TABLE `new_admin_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,[email protected];

The problem is that this approach doesn't work. Gives an error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@ai' at line 2
How to solve with muscle only, no padding?

Answer 1

Create a stored procedure like this:

DELIMITER $$

CREATE  PROCEDURE `update_autoincrement`( IN tablename VARCHAR(55),  IN ai_value INT )
LANGUAGE SQL 
NOT DETERMINISTIC 
SQL SECURITY INVOKER  

BEGIN
   
   set @sql = concat( 'ALTER TABLE `' , tablename , '` AUTO_INCREMENT = ', ai_value );
   PREPARE stmt FROM @sql;
   EXECUTE stmt ;

END;
$$
delimiter ;

Then you call it in the right place:
the given code is an example, it works, but there is not enough check of input values ​​for safety.

Answer 2

There is no SQL server at hand (syntax errors are possible). But the following question would not give me peace of mind:
What if there was a row deletion in the source table (old) (that is, record id's are not in a row for the autoincrement field) ? Will it work correctly (and are there any pitfalls when transferring with your scheme)?
I would do this:
1) Create a target (new) table without an auto_increment field
2) Copy all data into it INSERT `new_admin_logs` (SELECT * FROM `old_admin_logs`);
3) Hung a trigger on INSERT (for auto_increment functionality)

DELIMITER $$
CREATE TRIGGER auto_inc_new_admin_logs
BEFORE INSERT ON new_admin_logs FOR EACH ROW
BEGIN
  DECLARE new1 INT; 
  SELECT  max(id) into new1 FROM new_admin_logs;
  SET NEW.id = new1 + 1;   
END$$

PS If there are a lot of tables, you can separately create a table to store the last values ​​of autoincrement fields
PSS I transferred the old one (which does not have Autoincrement fields) from MSSQL to Oracle