mysql分表join

-- 用户表分表
CREATE TABLE user_1 (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

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

-- 订单表分表
CREATE TABLE order_1 (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2)
);

CREATE TABLE order_2 (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2)
);

# 假设使用Python和SQLAlchemy
from sqlalchemy import create_engine, Table, MetaData, select

engine = create_engine('mysql://user:password@host/dbname')

metadata = MetaData()

user_1 = Table('user_1', metadata, autoload_with=engine)
user_2 = Table('user_2', metadata, autoload_with=engine)
order_1 = Table('order_1', metadata, autoload_with=engine)
order_2 = Table('order_2', metadata, autoload_with=engine)

# 查询用户ID为1的所有订单
user_id = 1

# 查询user_1表
stmt_user_1 = select([user_1]).where(user_1.c.id == user_id)
result_user_1 = engine.execute(stmt_user_1).fetchone()

if result_user_1:
    # 查询order_1表
    stmt_order_1 = select([order_1]).where(order_1.c.user_id == user_id)
    result_order_1 = engine.execute(stmt_order_1).fetchall()
else:
    # 查询user_2表
    stmt_user_2 = select([user_2]).where(user_2.c.id == user_id)
    result_user_2 = engine.execute(stmt_user_2).fetchone()

    if result_user_2:
        # 查询order_2表
        stmt_order_2 = select([order_2]).where(order_2.c.user_id == user_id)
        result_order_2 = engine.execute(stmt_order_2).fetchall()

# 合并结果
orders = result_order_1 + result_order_2