mysql数据库拆分字段

-- 原始用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    avatar BLOB -- 大字段
);

-- 拆分后的用户表
CREATE TABLE users_basic (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100)
);

-- 拆分后的头像表
CREATE TABLE user_avatars (
    user_id INT,
    avatar BLOB,
    FOREIGN KEY (user_id) REFERENCES users_basic(id)
);

SELECT u.id, u.username, u.email, a.avatar
FROM users_basic u
LEFT JOIN user_avatars a ON u.id = a.user_id
WHERE u.id = 1;

START TRANSACTION;
INSERT INTO users_basic (username, email) VALUES ('john_doe', 'john@example.com');
SET @user_id = LAST_INSERT_ID();
INSERT INTO user_avatars (user_id, avatar) VALUES (@user_id, LOAD_FILE('/path/to/avatar.jpg'));
COMMIT;

CREATE INDEX idx_user_id ON user_avatars(user_id);