[mysql] innodb_autoinc_lock_mode = 0

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode'

SET GLOBAL innodb_autoinc_lock_mode=0;

DELIMITER //

DROP PROCEDURE IF EXISTS fix_id;
CREATE PROCEDURE fix_id()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = 'b';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tableName;
        SELECT tableName AS '** DEBUG:';

        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @COUNT = 0;
        SET @sql = CONCAT('UPDATE ', tableName, ' SET id = @COUNT:=@COUNT+1;');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET @T = NULL;
        SET @sql = CONCAT('SELECT MAX(id) INTO @T FROM ', tableName, ';');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET @sql = CONCAT('ALTER TABLE ', tableName, ' AUTO_INCREMENT = ', IFNULL(@T, 0) + 1);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP read_loop;

    CLOSE cur;
END;
//

DELIMITER ;

-- CALL fix_id()

vi /etc/mysql/mysql.conf.d/mysqld.cnf

# innodb_autoinc_lock_mode = 0

systemctl restart mysql

https://stackoverflow.com/questions/41922381/mysql-innodb-auto-increment-lock-workaround

MySql innoDB auto increment lock workaround

https://velog.io/@sjy5386/SQL-AUTOINCREMENT-%EA%B0%92-%EC%B4%88%EA%B8%B0%ED%99%94%EC%9E%AC%EC%A0%95%EB%A0%AC

[MySQL/MariaDB] AUTO_INCREMENT 값 초기화/재정렬

https://hoing.io/archives/1289

MySQL - auto_increment 와 innodb_autoinc_lock_mode

https://stackoverflow.com/questions/970597/change-auto-increment-starting-number/76938064#76938064

Change auto increment starting number?

https://stackoverflow.com/questions/38347110/prevent-innodb-auto-increment-on-duplicate-key/76938065#76938065

Prevent InnoDB auto increment ON DUPLICATE KEY