SELECT auto_increment при создании копии структуры таблицы?

Question

[AlikDex]

Суть в краце вот в чем: нужно создать таблицу и перенести в нее данные из старой таблицы, при этом сохранить счетчик инкремента. Таблиц много однотипных, поэтому надо это дело как-то автоматизировать. На данный момент последовательность запросов такая:

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,AUTO_INCREMENT=@ai;

Проблема в том, что такой подход не работает. Выдает ошибку:
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

Как решить только средствами мускула, без прокладки?

Answer 1

[Алексей Сумин]

Создайте хранимую процедурку типа такой:

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 ;

Затем в нужном месте вызываете:
call update_autoincrement( 'test', 70 );

приведенный код является примером, он рабочий, но не хватает проверки входных значений на безопасность.

Comments

[AlikDex]

спасибо. Пришлось помучаться с настройкой прав для процедур, но этот вариант рабочий.
Странно, почему без процедуры не работает? не подскажете?

[Алексей Сумин]

вот так у меня сработало и без процедуры, в обычном sql редакторе:
set @ai = 500;
set @sql = concat( 'ALTER TABLE `test` AUTO_INCREMENT = ', @ai );
PREPARE stmt FROM @sql;
EXECUTE stmt ;

Answer 2

[Марат]

Под рукой нет SQL сервера (возможны ошибки синтаксиса). Но следующий вопрос не давал бы мне спокойствия:
А если в таблице источнике (old) было удаление строки (то есть id записи идут не подряд для autoincrement поля) ? Корректно ли оно отработает (и нет ли подводных камней при переносе при вашей схеме) ?

Я бы делал так:
1) Создал целевую (new) таблицу без auto_increment поля
2) Скопировал в нее все данные INSERT `new_admin_logs` (SELECT * FROM `old_admin_logs`);
3) Повесил триггер на INSERT (для функционала auto_increment)

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$$

P.S. Если таблиц много, можно отдельно завести таблицу для хранения последних значений автоинкремент полей
P.S.S. Я так переносил в Oracle старый (у которого нет Autoincrement полей) из MSSQL

Comments

[AlikDex]

немного не то. не всегда автоинкремент текущий равен ид+1 Да и триггер на каждую таблицу не очень вариант.