mysql将查询结果插入新表

-- 创建新表
CREATE TABLE new_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

-- 将查询结果插入新表
INSERT INTO new_table (id, name, age)
SELECT id, name, age FROM original_table WHERE age > 25;

-- 创建新表并将查询结果插入
CREATE TABLE new_table AS
SELECT id, name, age FROM original_table WHERE age > 25;

-- 检查新表结构
DESCRIBE new_table;

-- 确保查询结果的列数和数据类型与新表一致
INSERT INTO new_table (id, name, age)
SELECT id, name, age FROM original_table WHERE age > 25;

-- 使用INSERT IGNORE忽略主键冲突
INSERT IGNORE INTO new_table (id, name, age)
SELECT id, name, age FROM original_table WHERE age > 25;

-- 或者使用ON DUPLICATE KEY UPDATE处理冲突
INSERT INTO new_table (id, name, age)
SELECT id, name, age FROM original_table WHERE age > 25
ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

-- 分批插入数据
INSERT INTO new_table (id, name, age)
SELECT id, name, age FROM original_table WHERE age > 25
LIMIT 1000;

-- 或者使用事务提高性能
START TRANSACTION;
INSERT INTO new_table (id, name, age)
SELECT id, name, age FROM original_table WHERE age > 25
LIMIT 1000;
COMMIT;