mysql地区表

CREATE TABLE regions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT,
    code VARCHAR(20),
    level ENUM('country', 'state', 'city') NOT NULL,
    INDEX idx_parent_id (parent_id)
);

SELECT * FROM regions WHERE parent_id = ?;

WITH RECURSIVE region_path AS (
    SELECT * FROM regions WHERE id = ?
    UNION ALL
    SELECT r.* FROM regions r INNER JOIN region_path rp ON r.id = rp.parent_id
)
SELECT * FROM region_path;

INSERT INTO regions (name, parent_id, code, level)
SELECT 'New City', 1, 'NC123', 'city'
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1 FROM regions WHERE name = 'New City' AND parent_id = 1
);