phpcms 表关联

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    bio TEXT
);

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

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

CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

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

CREATE TABLE article_tags (
    article_id INT,
    tag_id INT,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (tag_id) REFERENCES tags(id)
);

-- 错误的关联条件
SELECT * FROM articles a
JOIN categories c ON a.category_id = c.id;

-- 正确的关联条件
SELECT * FROM articles a
JOIN categories c ON a.category_id = CAST(c.id AS INT);

CREATE INDEX idx_category_id ON articles(category_id);