mysql数据库分表迁移

-- 创建新表
CREATE TABLE user_0 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

CREATE TABLE user_1 (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

import mysql.connector

def migrate_data(source_conn, target_conn, table_name, start_id, end_id, new_table_name):
    cursor_source = source_conn.cursor()
    cursor_target = target_conn.cursor()
    
    query = f"SELECT * FROM {table_name} WHERE id BETWEEN %s AND %s"
    cursor_source.execute(query, (start_id, end_id))
    
    rows = cursor_source.fetchall()
    for row in rows:
        insert_query = f"INSERT INTO {new_table_name} VALUES (%s, %s, %s)"
        cursor_target.execute(insert_query, row)
    
    target_conn.commit()

# 连接源数据库和目标数据库
source_conn = mysql.connector.connect(user='source_user', password='source_password', host='source_host', database='source_db')
target_conn = mysql.connector.connect(user='target_user', password='target_password', host='target_host', database='target_db')

# 迁移数据
migrate_data(source_conn, target_conn, 'user', 0, 9999, 'user_0')
migrate_data(source_conn, target_conn, 'user', 10000, 19999, 'user_1')

# 关闭连接
source_conn.close()
target_conn.close()