地区代码表 mysql

CREATE TABLE country (
    country_code CHAR(2) PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL
);

CREATE TABLE province (
    province_code CHAR(2) PRIMARY KEY,
    country_code CHAR(2),
    province_name VARCHAR(100) NOT NULL,
    FOREIGN KEY (country_code) REFERENCES country(country_code)
);

INSERT INTO country (country_code, country_name) VALUES ('CN', 'China')
ON DUPLICATE KEY UPDATE country_name = 'China';

INSERT INTO province (province_code, country_code, province_name) VALUES ('BJ', 'CN', 'Beijing')
ON DUPLICATE KEY UPDATE province_name = 'Beijing';

SELECT country_name, province_name FROM country JOIN province ON country.country_code = province.country_code WHERE province.province_code = 'BJ';

CREATE INDEX idx_country_code ON country(country_code);
CREATE INDEX idx_province_code ON province(province_code);