mysql 树状表创建

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    path VARCHAR(255) NOT NULL
);

WITH RECURSIVE category_tree AS (
    SELECT * FROM categories WHERE id = ?
    UNION ALL
    SELECT c.* FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

SELECT * FROM categories WHERE path LIKE CONCAT('%', ?, '%');

INSERT INTO categories (name, parent_id) VALUES (?, ?);

INSERT INTO categories (name, path) VALUES (?, CONCAT((SELECT path FROM categories WHERE id = ?), '/', ?));

UPDATE categories SET parent_id = ? WHERE id = ?;

UPDATE categories SET path = CONCAT((SELECT path FROM categories WHERE id = ?), '/', ?) WHERE id = ?;