执行为所有表添加列的存储过程时出现SQL语法错误求助

创建了一个为数据库所有表添加timestamp列的存储过程，数据库无表时执行正常，但创建测试表后执行存储过程时出现以下错误：

"The following query failed: "CALL `add_timestamp_column_to_tables`(); "

MySQL said: #1064 - There is an error in your SQL syntax; check the manual that corresponds to your version of MariaDB server to find out the correct syntax to use aro"

存储过程代码如下：

-- Stored procedure to add timestamp column to all tables
DELIMITER //

CREATE PROCEDURE add_timestamp_column_to_tables()
BEGIN
    DECLARE table_name VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;

-- Get the name of all tables
    DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Open cursor
    OPEN cur;

-- Loop to traverse all tables
    read_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- ALTER TABLE statement to add the timestamp column
        SET @sql = CONCAT('ALTER TABLE `', table_name, '` ADD COLUMN IF NOT EXISTS `timestamp_column` TIMESTAMP DEFAULT CURRENT_TIMESTAMP');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

  -- close cursor
    CLOSE cur;
END //

DELIMITER ;

你使用的MariaDB版本不支持ADD COLUMN IF NOT EXISTS语法。该语法是MySQL 8.0及以上版本才引入的特性，而MariaDB直到10.6.0版本才开始支持。当数据库无表时，循环内的ALTER TABLE语句不会执行，因此不会触发语法错误；一旦有表存在，执行包含IF NOT EXISTS的ALTER TABLE语句时，就会触发1064语法错误。

修改存储过程，先检查表中是否已存在目标列，仅当列不存在时才执行添加操作。修改后的代码如下：

-- Stored procedure to add timestamp column to all tables
DELIMITER //

CREATE PROCEDURE add_timestamp_column_to_tables()
BEGIN
    DECLARE table_name VARCHAR(255);
    DECLARE column_exists INT DEFAULT 0;
    DECLARE done INT DEFAULT FALSE;

    -- Get the name of all tables
    DECLARE cur CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = DATABASE();
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open cursor
    OPEN cur;

    -- Loop to traverse all tables
    read_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Check if the column already exists
        SELECT COUNT(*) INTO column_exists
        FROM information_schema.columns
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = table_name
          AND COLUMN_NAME = 'timestamp_column';

        -- Only add column if it doesn't exist
        IF column_exists = 0 THEN
            SET @sql = CONCAT('ALTER TABLE `', table_name, '` ADD COLUMN `timestamp_column` TIMESTAMP DEFAULT CURRENT_TIMESTAMP');
            PREPARE stmt FROM @sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END LOOP;

    -- close cursor
    CLOSE cur;
END //

DELIMITER ;

内容的提问来源于stack exchange，提问作者Iván Martínez