mysql年份分表后查询方法

SELECT * FROM data_2023 WHERE condition;

SELECT * FROM data_2022 WHERE condition
UNION ALL
SELECT * FROM data_2023 WHERE condition;

<?php
$years = [2022, 2023, 2024];
$results = [];

foreach ($years as $year) {
    $table = "data_" . $year;
    $sql = "SELECT * FROM $table WHERE condition";
    $result = mysqli_query($conn, $sql);
    while ($row = mysqli_fetch_assoc($result)) {
        $results[] = $row;
    }
}
?>

DELIMITER //

CREATE PROCEDURE GetDataByYears(IN start_year INT, IN end_year INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_year INT;
    DECLARE cur_table VARCHAR(255);
    DECLARE cur_result CURSOR FOR SELECT CONCAT('data_', year) FROM (SELECT @rownum:=@rownum+1 AS rownum, year FROM (SELECT DISTINCT YEAR(date_column) AS year FROM data_2022 UNION ALL SELECT DISTINCT YEAR(date_column) AS year FROM data_2023 UNION ALL SELECT DISTINCT YEAR(date_column) AS year FROM data_2024) AS years ORDER BY year) AS sorted_years WHERE rownum BETWEEN start_year AND end_year;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_result;

    read_loop: LOOP
        FETCH cur_result INTO cur_year, cur_table;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql = CONCAT('SELECT * FROM ', cur_table, ' WHERE condition');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur_result;
END //

DELIMITER ;

CALL GetDataByYears(2022, 2023);

<?php
$table = "data_" . $year;
$sql = "SHOW TABLES LIKE '$table'";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 表存在，执行查询
} else {
    // 表不存在，处理错误
}
?>

START TRANSACTION;

-- 执行多个表的插入或更新操作

COMMIT;