ALTER AUTO_INCREMENT value by result

Question

Basically what I want is a working version of the following code:

ALTER TABLE table_name
AUTO_INCREMENT =
(
    SELECT
        `AUTO_INCREMENT`
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_SCHEMA = 'database_name'
    AND TABLE_NAME = 'another_table_name'
);

Error:

1064 - 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 'AUTO_INCREMENT =

Cause:

According to MySQL Doc :

InnoDB uses an auto-increment counter in memory provided that the server runs. When the server stops and restarts, InnoDB reinitializes the counter for each table for the first INSERT for the table, as described previously.

This means that whenever I restart the server, my auto_increment values ​​are set to the lowest possible value.

ticket, - ticket_backup. id, . ticket . , ticket ticket_backup, ticket. 56 thousand ( ticket_backup) 0 . ALTER TABLE, , , id 1, , ticket_backup, , . , , - .

Answer 1

:

SELECT `AUTO_INCREMENT` INTO @AutoInc
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'another_table_name';

SET @s:=CONCAT('ALTER TABLE `database_name`.`table_name` AUTO_INCREMENT=', @AutoInc);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Answer 2

, .

:

ALTER TABLE table_name AUTO_INCREMENT=<INTEGER_VALUE>;

, , "SET"

ALTER TABLE table_name AUTO_INCREMENT =
(
    SELECT
        `AUTO_INCREMENT`
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_SCHEMA = 'database_name'
    AND TABLE_NAME = 'another_table_name'
);

Answer 3

What about

ALTER TABLE table_name 
SET AUTO_INCREMENT = (SELECT MAX(a.AUTO_INC_VAL) FROM database_name.table_name a) )

if current increment value is required or

ALTER TABLE table_name 
SET AUTO_INCREMENT = (SELECT MIN(a.AUTO_INC_VAL) FROM database_name.table_name a) )

if you want to receive the same initial amount INCREMENT VALUE