Key_Value 形式 存储_5级省市城乡划分代码 (mysql 8.0 实例)

CREATE TABLE `china_code` (
  `chinacode` bigint DEFAULT NULL,
  `value` varchar(100) DEFAULT NULL
) ENGINE=MyISAM ;

select * from  china_code

insert into china_code(chinacode,value) values
('310000000000','上海市'),
('310100000000','直辖区'),
('310112000000','闵行区'),
('310112102000','七宝镇'),
('310112102042','华林路第一居委会'),
('310104000000','徐汇区'),
('310104011000','田林街道'),
('310104011033','千鹤第五居委会'),
('110000000000','北京市'),
('110100000000','直辖区'),
('110105000000','朝阳区'),
('110105009000','亚运村街道'),
('110105009057','民族园社区居民委员会'),
('230000000000','黑龙江省'),
('231100000000','黑河市'),
('231124000000','孙吴县'),
('231124100000','孙吴镇'),
('231124100218','镇南村委会'),
('510000000000','四川省'),
('513200000000','阿坝藏族羌族自治州'),
('513201000000','马尔康市'),
('513201100000','马尔康镇'),
('513201100212','查米村村民委员会')

以上2个函数都是余数函数
select *  from  china_code where chinacode%1000000000=0
select *  from  china_code where mod(chinacode,1000000000)=0

select a.chinacode,a.value,b.chinacode,b.value,c.chinacode,c.value,d.chinacode,d.value,e.chinacode,e.value from  china_code a
left join china_code b on left(a.chinacode,2)=left(b.chinacode,2) 
left join china_code c on left(b.chinacode,4)=left(c.chinacode,4) 
left join china_code d on left(c.chinacode,6)=left(d.chinacode,6)
left join china_code e on left(d.chinacode,8)=left(e.chinacode,8) 
where   a.chinacode%1000000000=0 
and b.chinacode%10000000=0 and b.chinacode%1000000000!=0
and c.chinacode%1000000=0 and  c.chinacode%100000000!=0
and d.chinacode%1000=0 and   d.chinacode%1000000!=0
and e.chinacode%1000!=0