当达到行版本64次限制时，操作将被拒绝，

并给出错误信息，建议使用 COPY 或 INPLACE 算法重建表。

4092 (HY000): Maximum row versions reached for table test/t1.

No more columns can be added or dropped instantly. Please use COPY/INPLACE.

自 MySQL 9.1.0 起，次数上升为 255。


复现

-- 创建测试表

CREATE TABLE test_instant_limit (
    id INT PRIMARY KEY
);
  • 1.
  • 2.
  • 3.


-- 使用存储过程来执行65次ALTER TABLE操作

DELIMITER //
CREATE PROCEDURE test_instant_limit_procedure()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE alter_result VARCHAR(255);
    
    -- 循环64次，使用INSTANT算法添加列
    WHILE i <= 64 DO
        SET @sql = CONCAT('ALTER TABLE test_instant_limit ADD COLUMN col_', i, ' INT, ALGORITHM=INSTANT');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('成功添加列 col_', i, ' 使用INSTANT算法') INTO alter_result;
        SELECT alter_result;
        
        SET i = i + 1;
    END WHILE;
    
    -- 尝试第65次INSTANT更改
    BEGIN
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
            SET alter_result = CONCAT('错误：', @errno, ' (', @sqlstate, '): ', @text);
        END;
        
        SET @sql = 'ALTER TABLE test_instant_limit ADD COLUMN col_65 INT, ALGORITHM=INSTANT';
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        IF alter_result IS NULL THEN
            SET alter_result = '成功添加列 col_65 使用INSTANT算法（超出预期）';
        END IF;
        
        SELECT alter_result;
    END;
END //
DELIMITER ;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.


-- 执行存储过程

CALL test_instant_limit_procedure();
  • 1.

MySQL 8.0 使用 ALGORITHM=INSTANT算法，即时添加或删除列，有64次数限制_存储过程


-- 查看次数

SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES 
        WHERE NAME LIKE 'test/test_instant_limit';
+-------------------------+--------------------+
| NAME                    | TOTAL_ROW_VERSIONS |
+-------------------------+--------------------+
| test/test_instant_limit |                 64 |
+-------------------------+--------------------+
1 row in set (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.


参考手册：

When a table with instantly added or dropped columns is rebuilt by table-rebuilding  ALTER TABLE or  OPTIMIZE TABLE operation, the TOTAL_ROW_VERSIONS value is reset to 0. The maximum number of row versions permitted is 64 (255 as of MySQL 9.1.0), as each row version requires additional space for table metadata. When the row version limit is reached, ADD COLUMN and DROP COLUMN operations using ALGORITHM=INSTANT are rejected with an error message that recommends rebuilding the table using the COPY or INPLACE algorithm.


ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.

 https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html