在我看来，你想：

• 查询表名并将其存储在变量/游标中。
• 循环通过光标
• 生成和执行动态SQL语句

理想的语言是PL/SQL，但MySQL不支持它。但是，类似这样的东西可以工作：

DELIMITER //

CREATE PROCEDURE add_new_column()
BEGIN
  DECLARE table_name VARCHAR(100);
  DECLARE done INT DEFAULT FALSE;
  DECLARE cur CURSOR FOR
    SELECT TABLE_NAME
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = 'your_schema_name'
      AND TABLE_NAME LIKE 'xyz111%';

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO table_name;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @alter_query := CONCAT('ALTER TABLE ', table_name, ' ADD COLUMN date_column2 DATE');
    PREPARE stmt FROM @alter_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SET @update_query := CONCAT('UPDATE ', table_name, ' SET date_column2 = date_column');
    PREPARE stmt FROM @update_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END //

DELIMITER ;