40 lines
1.5 KiB
MySQL
40 lines
1.5 KiB
MySQL
|
begin;
|
||
|
|
|
||
|
|
set search_path to public;
|
||
|
|
|
||
|
|
create table if not exists cnarea (
|
||
|
|
id integer generated by default as identity primary key,
|
||
|
|
level smallint not null check (level >= 0),
|
||
|
|
parent_code varchar(16) not null default '0',
|
||
|
|
area_code varchar(16) not null default '0',
|
||
|
|
zip_code varchar(6) not null default '000000',
|
||
|
|
city_code char(6) not null default '',
|
||
|
|
name varchar(50) not null default '',
|
||
|
|
short_name varchar(50) not null default '',
|
||
|
|
merger_name varchar(50) not null default '',
|
||
|
|
pinyin varchar(30) not null default '',
|
||
|
|
lng numeric(10, 6) not null default 0.000000,
|
||
|
|
lat numeric(10, 6) not null default 0.000000,
|
||
|
|
short_code varchar(8)
|
||
|
|
);
|
||
|
|
|
||
|
|
create unique index if not exists uk_cnarea_code on cnarea(area_code);
|
||
|
|
create index if not exists idx_cnarea_parent_code on cnarea(parent_code);
|
||
|
|
|
||
|
|
comment on table cnarea is '中国行政地区表';
|
||
|
|
comment on column cnarea.id is '主键';
|
||
|
|
comment on column cnarea.level is '层级';
|
||
|
|
comment on column cnarea.parent_code is '父级行政代码';
|
||
|
|
comment on column cnarea.area_code is '行政代码';
|
||
|
|
comment on column cnarea.zip_code is '邮政编码';
|
||
|
|
comment on column cnarea.city_code is '区号';
|
||
|
|
comment on column cnarea.name is '名称';
|
||
|
|
comment on column cnarea.short_name is '简称';
|
||
|
|
comment on column cnarea.merger_name is '组合名';
|
||
|
|
comment on column cnarea.pinyin is '拼音';
|
||
|
|
comment on column cnarea.lng is '经度';
|
||
|
|
comment on column cnarea.lat is '纬度';
|
||
|
|
comment on column cnarea.short_code is '简短code';
|
||
|
|
|
||
|
|
commit;
|