mysql 内存表初始化

-- 创建内存表
CREATE TABLE IF NOT EXISTS memory_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL
) ENGINE=MEMORY;

-- 插入初始数据
INSERT INTO memory_table (name, age) VALUES
('Alice', 30),
('Bob', 25),
('Charlie', 35);

-- 将内存表数据导出到磁盘表
CREATE TABLE IF NOT EXISTS disk_table LIKE memory_table;
INSERT INTO disk_table SELECT * FROM memory_table;

-- 创建多个内存表并分片存储数据
CREATE TABLE IF NOT EXISTS memory_table_1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL
) ENGINE=MEMORY;

CREATE TABLE IF NOT EXISTS memory_table_2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL
) ENGINE=MEMORY;

-- 插入数据时根据 id 进行分片
DELIMITER $$
CREATE PROCEDURE InsertData(IN p_name VARCHAR(255), IN p_age INT)
BEGIN
    DECLARE table_name VARCHAR(255);
    SET table_name = (SELECT CASE WHEN p_id % 2 = 0 THEN 'memory_table_1' ELSE 'memory_table_2' END);
    SET @sql = CONCAT('INSERT INTO ', table_name, ' (name, age) VALUES (?, ?)');
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING p_name, p_age;
    DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;