SELECT 
    _rowid AS internal_row_id,  -- 顯示內(nèi)部行ID
    t.*                         -- 查詢所有列
FROM your_table_name t
LIMIT 10;

-- 檢查表是否有主鍵或唯一索引
SHOW INDEX FROM your_table_name;
-- 如果表沒有主鍵，且至少有一個非空的唯一整數(shù)列，`_rowid` 會指向該列
-- 否則，MySQL 會使用內(nèi)部隱藏的 rowid

SELECT 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num,
    other_columns
FROM your_table_name;

SET @row_number = 0;
SELECT 
    (@row_number:=@row_number + 1) AS row_num,
    other_columns
FROM your_table_name;

-- 1. 首先創(chuàng)建一個按時間分區(qū)的表（如果尚未存在）
CREATE TABLE IF NOT EXISTS time_partitioned_data (
    id INT AUTO_INCREMENT,
    data_value VARCHAR(255),
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p_min VALUES LESS THAN (TO_DAYS('2023-01-01'))
);
-- 2. 創(chuàng)建存儲過程來自動管理分區(qū)
DELIMITER //
CREATE PROCEDURE auto_manage_partitions(IN table_name VARCHAR(64), IN days_ahead INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE partition_name VARCHAR(64);
    DECLARE partition_value VARCHAR(64);
    DECLARE max_value DATE;
    DECLARE new_partition_date DATE;
    DECLARE new_partition_name VARCHAR(64);
    DECLARE new_partition_value INT;
    DECLARE alter_sql TEXT;
    -- 獲取當前最大分區(qū)值
    SELECT MAX(TO_DAYS(created_at)) INTO @max_day 
    FROM time_partitioned_data;
    SET max_value = IFNULL(FROM_DAYS(@max_day), CURDATE());
    -- 創(chuàng)建未來分區(qū)
    SET new_partition_date = max_value;
    WHILE DATEDIFF(DATE_ADD(new_partition_date, INTERVAL 1 MONTH), max_value) <= days_ahead DO
        SET new_partition_date = DATE_ADD(new_partition_date, INTERVAL 1 MONTH);
        SET new_partition_name = CONCAT('p_', DATE_FORMAT(new_partition_date, '%Y%m'));
        SET new_partition_value = TO_DAYS(new_partition_date);
        -- 檢查分區(qū)是否已存在
        SELECT COUNT(*) INTO @partition_exists 
        FROM information_schema.PARTITIONS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'time_partitioned_data' 
        AND PARTITION_NAME = new_partition_name;
        IF @partition_exists = 0 THEN
            SET alter_sql = CONCAT('ALTER TABLE ', table_name, 
                                 ' ADD PARTITION (PARTITION ', new_partition_name, 
                                 ' VALUES LESS THAN (', new_partition_value, '))');
            PREPARE stmt FROM alter_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            SELECT CONCAT('Created partition: ', new_partition_name, ' for date: ', new_partition_date) AS message;
        END IF;
    END WHILE;
    -- 可選：刪除舊分區(qū)（例如保留最近12個月的數(shù)據(jù)）
    /*
    SELECT PARTITION_NAME, PARTITION_DESCRIPTION INTO @old_partition, @old_value
    FROM information_schema.PARTITIONS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'time_partitioned_data'
    AND PARTITION_NAME != 'p_min'
    ORDER BY PARTITION_DESCRIPTION ASC
    LIMIT 1;
    IF TO_DAYS(CURDATE()) - @old_value > 365 THEN
        SET @drop_sql = CONCAT('ALTER TABLE ', table_name, ' DROP PARTITION ', @old_partition);
        PREPARE stmt FROM @drop_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT CONCAT('Dropped old partition: ', @old_partition) AS message;
    END IF;
    */
END //
DELIMITER ;
-- 3. 創(chuàng)建事件定期執(zhí)行分區(qū)管理
CREATE EVENT IF NOT EXISTS manage_partitions_event
ON SCHEDULE EVERY 1 MONTH
STARTS CURRENT_TIMESTAMP
DO
CALL auto_manage_partitions('time_partitioned_data', 90); -- 提前創(chuàng)建未來90天的分區(qū)
-- 啟用事件調(diào)度器
SET GLOBAL event_scheduler = ON;