-- 創(chuàng)建BLACKHOLE表示例
CREATE TABLE blackhole_demo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = BLACKHOLE;

-- 插入數(shù)據(jù)（數(shù)據(jù)將被"吞噬"）
INSERT INTO blackhole_demo (name) VALUES ('測試數(shù)據(jù)'), ('另一個測試');

-- 查詢總是返回空集
SELECT * FROM blackhole_demo;
-- 結(jié)果：Empty set (0.00 sec)

-- 在主從架構(gòu)中的典型應(yīng)用
-- 主服務(wù)器配置
CREATE TABLE critical_data (
    id INT PRIMARY KEY,
    business_data TEXT
) ENGINE = InnoDB;

CREATE TABLE audit_logs (
    id INT PRIMARY KEY,
    log_message TEXT,
    log_time DATETIME
) ENGINE = BLACKHOLE;  -- 不復(fù)制到從服務(wù)器

CREATE TABLE operational_metrics (
    id INT PRIMARY KEY,
    metric_name VARCHAR(100),
    metric_value DECIMAL(10,2)
) ENGINE = BLACKHOLE;  -- 僅在主服務(wù)器記錄，不復(fù)制

-- 性能對比測試腳本
DELIMITER $$

CREATE PROCEDURE performance_benchmark()
BEGIN
    DECLARE start_time BIGINT;
    DECLARE end_time BIGINT;
    DECLARE i INT DEFAULT 0;
    
    -- 測試BLACKHOLE引擎性能
    DROP TABLE IF EXISTS test_blackhole;
    CREATE TABLE test_blackhole (
        id INT,
        data VARCHAR(255)
    ) ENGINE = BLACKHOLE;
    
    SET start_time = UNIX_TIMESTAMP(NOW(6));
    
    WHILE i < 10000 DO
        INSERT INTO test_blackhole VALUES (i, REPEAT('X', 200));
        SET i = i + 1;
    END WHILE;
    
    SET end_time = UNIX_TIMESTAMP(NOW(6));
    SELECT CONCAT('BLACKHOLE引擎耗時: ', (end_time - start_time), ' 秒') AS result;
    
    -- 測試InnoDB引擎性能
    SET i = 0;
    DROP TABLE IF EXISTS test_innodb;
    CREATE TABLE test_innodb (
        id INT,
        data VARCHAR(255)
    ) ENGINE = InnoDB;
    
    SET start_time = UNIX_TIMESTAMP(NOW(6));
    
    WHILE i < 10000 DO
        INSERT INTO test_innodb VALUES (i, REPEAT('X', 200));
        SET i = i + 1;
    END WHILE;
    
    SET end_time = UNIX_TIMESTAMP(NOW(6));
    SELECT CONCAT('InnoDB引擎耗時: ', (end_time - start_time), ' 秒') AS result;
END$$

DELIMITER ;

CALL performance_benchmark();

-- 使用BLACKHOLE引擎處理觸發(fā)器邏輯
CREATE TABLE user_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action_type VARCHAR(50),
    action_time DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB;

-- BLACKHOLE表用于觸發(fā)器處理
CREATE TABLE action_statistics (
    user_id INT,
    action_count INT,
    last_action_time DATETIME
) ENGINE = BLACKHOLE;

DELIMITER $$

CREATE TRIGGER after_user_action
AFTER INSERT ON user_actions
FOR EACH ROW
BEGIN
    -- 復(fù)雜的業(yè)務(wù)邏輯處理
    DECLARE current_count INT DEFAULT 0;
    
    -- 統(tǒng)計用戶操作次數(shù)（實際不存儲）
    INSERT INTO action_statistics 
    VALUES (NEW.user_id, 1, NEW.action_time)
    ON DUPLICATE KEY UPDATE 
        action_count = action_count + 1,
        last_action_time = NEW.action_time;
        
    -- 其他業(yè)務(wù)邏輯...
    IF NEW.action_type = 'login' THEN
        -- 記錄登錄特殊處理
        INSERT INTO action_statistics VALUES (NEW.user_id, -1, NEW.action_time);
    END IF;
END$$

DELIMITER ;

-- 單表引擎轉(zhuǎn)換
ALTER TABLE audit_logs ENGINE = InnoDB;

-- 批量轉(zhuǎn)換腳本
SET @database_name = 'your_database';

SELECT 
    CONCAT('ALTER TABLE `', TABLE_NAME, '` ENGINE = InnoDB;') AS alter_statement,
    TABLE_NAME,
    TABLE_ROWS
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = @database_name 
AND ENGINE = 'BLACKHOLE'
ORDER BY TABLE_NAME;

-- 執(zhí)行生成的ALTER語句
-- ALTER TABLE `audit_logs` ENGINE = InnoDB;
-- ALTER TABLE `temporary_metrics` ENGINE = InnoDB;

-- 1. 檢查表結(jié)構(gòu)和依賴關(guān)系
SHOW CREATE TABLE problematic_table;

-- 2. 檢查相關(guān)觸發(fā)器
SHOW TRIGGERS WHERE `Table` = 'problematic_table';

-- 3. 創(chuàng)建備份表
CREATE TABLE problematic_table_backup LIKE problematic_table;
ALTER TABLE problematic_table_backup ENGINE = InnoDB;

-- 4. 遷移數(shù)據(jù)（如果BLACKHOLE表有特殊數(shù)據(jù)來源）
-- 注意：標(biāo)準(zhǔn)的BLACKHOLE表沒有數(shù)據(jù)，但可能有其他數(shù)據(jù)源
INSERT INTO problematic_table_backup 
SELECT * FROM problematic_table;

-- 5. 重命名表完成切換
RENAME TABLE 
    problematic_table TO problematic_table_old,
    problematic_table_backup TO problematic_table;

-- 6. 驗證后清理
-- DROP TABLE problematic_table_old;

-- 自動化遷移存儲過程
DELIMITER $$

CREATE PROCEDURE migrate_blackhole_tables(IN db_name VARCHAR(64))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE table_name VARCHAR(64);
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = db_name 
        AND ENGINE = 'BLACKHOLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE;
        SELECT CONCAT('遷移失敗: ', @sqlstate) AS error;
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 執(zhí)行引擎轉(zhuǎn)換
        SET @sql = CONCAT('ALTER TABLE `', db_name, '`.`', table_name, '` ENGINE = InnoDB');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SELECT CONCAT('已轉(zhuǎn)換: ', table_name) AS progress;
    END LOOP;
    
    CLOSE cur;
    COMMIT;
    
    SELECT '所有BLACKHOLE表轉(zhuǎn)換完成' AS result;
END$$

DELIMITER ;

-- 執(zhí)行批量遷移
CALL migrate_blackhole_tables('your_production_db');

-- 1. 識別所有BLACKHOLE表
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    CREATE_TIME
FROM information_schema.TABLES 
WHERE ENGINE = 'BLACKHOLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- 2. 檢查表依賴關(guān)系
SELECT 
    TABLE_NAME,
    TRIGGER_NAME,
    ACTION_TIMING,
    EVENT_MANIPULATION
FROM information_schema.TRIGGERS 
WHERE EVENT_OBJECT_SCHEMA = 'your_database';

-- 3. 驗證外鍵約束
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database'
AND REFERENCED_TABLE_NAME IS NOT NULL;

-- 1. 確認引擎轉(zhuǎn)換成功
SELECT 
    TABLE_NAME,
    ENGINE,
    TABLE_ROWS
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME IN ('previously_blackhole_table1', 'previously_blackhole_table2');

-- 2. 功能測試
-- 插入測試數(shù)據(jù)
INSERT INTO converted_table (test_column) VALUES ('功能測試數(shù)據(jù)');

-- 驗證數(shù)據(jù)持久化
SELECT * FROM converted_table WHERE test_column = '功能測試數(shù)據(jù)';

-- 3. 性能基準(zhǔn)測試
-- 比較轉(zhuǎn)換前后的性能表現(xiàn)

-- 設(shè)置默認存儲引擎為InnoDB
SET GLOBAL default_storage_engine = InnoDB;

-- 在my.cnf中永久配置
/*
[mysqld]
default-storage-engine = InnoDB
*/

-- 創(chuàng)建用戶時限制引擎使用
CREATE USER 'app_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON your_database.* TO 'app_user'@'%';
REVOKE CREATE TEMPORARY TABLES, CREATE ROUTINE, ALTER ROUTINE 
ON *.* FROM 'app_user'@'%';

-- 代碼審查中檢查存儲引擎使用
SELECT 
    ROUTINE_NAME,
    ROUTINE_DEFINITION
FROM information_schema.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%ENGINE=BLACKHOLE%'
   OR ROUTINE_DEFINITION LIKE '%BLACKHOLE%';

-- 監(jiān)控新創(chuàng)建的BLACKHOLE表
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    CREATE_TIME
FROM information_schema.TABLES 
WHERE ENGINE = 'BLACKHOLE'
AND CREATE_TIME > DATE_SUB(NOW(), INTERVAL 1 DAY);

-- 方案1: 使用分區(qū)表模擬BLACKHOLE行為
CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT,
    log_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    partition_flag ENUM('keep', 'discard') DEFAULT 'discard'
) ENGINE = InnoDB
PARTITION BY LIST COLUMNS(partition_flag) (
    PARTITION p_keep VALUES IN ('keep'),
    PARTITION p_discard VALUES IN ('discard')
);

-- 定期清理"丟棄"分區(qū)的數(shù)據(jù)
ALTER TABLE audit_logs TRUNCATE PARTITION p_discard;

-- 方案2: 使用內(nèi)存表+定期清理
CREATE TABLE temporary_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_data TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = MEMORY;

-- 定期清理腳本
CREATE EVENT cleanup_temporary_data
ON SCHEDULE EVERY 1 HOUR
DO
    DELETE FROM temporary_data WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR);