城市id应该如何存储?

目前的做法是城市三个id分开存:省、市、县区

开始想着存三个字段,方便搜索,好比搜索某个省的,或某个县区的
但最近发现个情况
如果要查询这个人的家乡的省市县,就会关联[china_area]查询3次表,如果再加个所在地省市县,就会查6次表,这样貌似太浪费了吧!
可是合并在一起,又会影响搜索,大家有什么解决办法吗?

《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
讨论数量: 70

没看明白,区/县编号不是包含了省市编号了吗,为什么还要查三次

1年前 评论
臭鼬 (楼主) 1年前
MArtian 1年前

省市区县放在一行,就是最小颗粒度是区/县,搜索?这么少的数据,你太小看数据库的性能了

1年前 评论
臭鼬 (楼主) 1年前
liuyibao (作者) 1年前

存 省份ID,城市ID,县区ID,同时在修改时把省份城市县区名称做冗余字段{"province_name":"北京","city_name":"北京市","district_name":"朝阳区"}用于展示,既方便检索,有在展示的时候不用关联表获取区域名称,唯一缺点就是区域名称发生变更无法及时更新

1年前 评论
臭鼬 (楼主) 1年前

用一张表加个字段parent_id父级id

1年前 评论
臭鼬 (楼主) 1年前

用国家统一行政区域划分代码

110110113 每3位拆开。 110北京市110北京市113顺义区

中华人民共和国民政部发布的行政区划代码: www.mca.gov.cn/article/sj/xzqh/202...

1年前 评论
臭鼬 (楼主) 1年前
kis龍 1年前
臭鼬 (楼主) 1年前
ShanHai1998 1年前
Junwind

uid , uname, 省, 市 ,县

1年前 评论

前端筛选的时候,后端把区域数据做个级联类型返回,这样前端筛选时,传过来的就是id

1年前 评论
臭鼬 (楼主) 1年前
helloStar (作者) 1年前

e.g.:

code: 10010
name: 朝阳区
fullname: 北京市朝阳区
parent_code: 100
-----
code: 1001010
name: XXX 街道
fullname: 北京市朝阳区XXX 街道
parent_code: 10010

fullname 存 string 或者是结构化数据,取决于你在前端怎么用,如果不需要对每个区域丛单独的处理,例如超链接等,那么直接存 string。

1年前 评论
臭鼬 (楼主) 1年前
GeorgeKing (作者) 1年前
臭鼬 (楼主) 1年前
GeorgeKing (作者) 1年前
臭鼬 (楼主) 1年前
GeorgeKing (作者) 1年前
liuyibao 1年前
ShanHai1998 1年前

我们用的每个字行政区都是有拼上上级行政区的, 比如 朝阳区冗余了北京北京市朝阳区 北京市冗余了北京北京市 存三个字段 但楼上说的国家统一行政区域划分代码也挺不错的

1年前 评论
臭鼬 (楼主) 1年前
ysxpark (作者) 1年前
臭鼬 (楼主) 1年前
ysxpark (作者) 1年前

file

1年前 评论
kis龍 1年前

前端、数据库新手,好奇问一下:

1. 这种表结构不行嘛?

id level name parent_id child_id_begin child_id_end
110000000000 0 北京市 0 110000000001 119999999999
110100000000 1 市辖区 110000000000 110100000001 110199999999
110101000000 2 东城区 110100000000 110101000001 110101999999
110102000000 2 西城区 110100000000 110102000001 110102999999
110105000000 2 朝阳区 110100000000 110105000001 110105999999

其中,parent_idchild_id_beginchild_id_end 都可自动生成

1.1 查询广东深圳下的所有区

SQLite 代码

SELECT c.id, c.name
  FROM cn_area a
  JOIN cn_area b ON b.id BETWEEN a.cid_begin AND a.cid_end AND b.level = 1 AND b.name LIKE '%深圳%'
  JOIN cn_area c ON c.id BETWEEN b.cid_begin AND b.cid_end AND c.level = 2
 WHERE a.level = 0 AND a.name LIKE '%广东%';

速度

数据库 表大小 用时
SQLite 3640 行,无索引,84 KB 0.006 秒

结果

id name
440301000000 市辖区
440303000000 罗湖区
440304000000 福田区
440305000000 南山区
440306000000 宝安区
440307000000 龙岗区
440308000000 盐田区
440309000000 龙华区
440310000000 坪山区
440311000000 光明区

2. 不能在前端查询数据嘛?

2.1 作为数据库提供

SQLite 有提供 wasm 版,可不依赖后端就能完成查询

整个数据库大小才 84 KB,gzip 压缩后 42 KB,感觉代价不算大?

2.2 作为 json 提供

使用以下 SQL

SELECT json_group_object(format('%d,%s', a.id, a.name),
       (SELECT json_group_object(format('%d,%s', b.id, b.name),
               (SELECT json_group_object(format('%d,%s', c.id, c.name), json('{}'))
                  FROM cn_area c
                 WHERE c.level = 2
                   AND c.id BETWEEN b.cid_begin AND b.cid_end))
          FROM cn_area b
         WHERE b.level = 1
           AND b.id BETWEEN a.cid_begin AND a.cid_end))
  FROM cn_area a
 WHERE a.level = 0;

输出格式化后的 json(大小:106 KB,gzip 后 27 KB)

{
  "110000000000,北京市": {
    "110100000000,市辖区": {
      "110101000000,东城区": {},
      "110102000000,西城区": {},
      "110105000000,朝阳区": {},

js 查询

Object.entries(data)
    .filter(([k]) => k.includes('广东')).flatMap(i => Object.entries(i[1]))
    .filter(([k]) => k.includes('深圳')).flatMap(i => Object.entries(i[1]))
    .map(([k]) => k.split(','))

结果

[
  [ '440301000000', '市辖区' ],
  [ '440303000000', '罗湖区' ],
  [ '440304000000', '福田区' ],
  [ '440305000000', '南山区' ],
  [ '440306000000', '宝安区' ],
  [ '440307000000', '龙岗区' ],
  [ '440308000000', '盐田区' ],
  [ '440309000000', '龙华区' ],
  [ '440310000000', '坪山区' ],
  [ '440311000000', '光明区' ]
]
1年前 评论
臭鼬 (楼主) 1年前
wxf666 (作者) 1年前
wxf666 (作者) 1年前
臭鼬 (楼主) 1年前
臭鼬 (楼主) 1年前
wxf666 (作者) 1年前
wxf666 (作者) 1年前
wxf666 (作者) 1年前
wxf666 (作者) 1年前
wxf666 (作者) 1年前
臭鼬 (楼主) 1年前
wxf666 (作者) 1年前
臭鼬 (楼主) 1年前
wxf666 (作者) 1年前

一般存IP在需要的时候再查

1年前 评论

@臭鼬 噢,我贴一下如何生成这个 66W 行数据库的备忘。(若你感兴趣,也可花一两分钟试一试)

  1. 下载并解压大佬爬好的数据:raw.githubusercontent.com/zhiguang...

  2. 运行下列 SQL,等待几秒钟,即可生成 new.db(3 级数据为 84 KB,5 级数据为 16.8 MB)

    (下面是 bash 脚本示例)

#!/bin/bash

sqlite3 :memory: <<'EOF'

-- 采用 UTF-16 存储中文,体积较小
PRAGMA encoding = 'UTF-16';

-- 导入 csv 文件至临时表 t,第一行作为列名
.import --csv 2021-flatten-VILLAGE-data.csv t

-- 建表
CREATE TABLE cn_area (
    id          INTEGER  PRIMARY KEY,
    level       INT      NOT NULL,
    name        TEXT     NOT NULL,
    _MASK       JSON     NOT NULL AS ('[1000000000000,10000000000,100000000,1000000,1000]'),
    pid         INT      NOT NULL AS (id - id % _MASK ->> level),
    cid_end     INT      AS (id + _MASK ->> (level + 1) - 1),
    cid_begin   INT      AS (id + 1)
);

-- 按格式抽取并添加需要的数据
INSERT INTO cn_area (id, level, name)
SELECT CASE WHEN code + 0 > 99 THEN code ELSE code * 10000000000 END,
        '{
            "PROVINCE": 0,
            "CITY": 1,
            "COUNTY": 2,
            "TOWN": 3,
            "VILLAGE": 4
        }' ->> ('$.' || level),
        name
FROM t
-- 如果只需要 3 级,可以过滤:
-- WHERE level IN ('PROVINCE', 'CITY', 'COUNTY')
;

-- 丢掉临时表
DROP TABLE t;

-- 整理紧实数据库后,写入磁盘
VACUUM INTO 'new.db';
EOF
1年前 评论

用户表字段。只存一个字段较好,因为北京直辖是没有第三级区级 code 的。

地区文字显示及地名模糊搜索。地区代码表增加全称冗余,如某省某市、某省某市某县。

三级联动。1/不查库,直接缓存全三级数据,或缓存前两级省级地级。2/查库,地区代码表加 level 字段省级1地级2区级3,由上一级向下一级查时,去掉各级的数字位,如省查市以河北为例,前两位13为省级位,like 13% and level = 2,地级查区级以石家庄市为例,前四位为地级,like 1301% and level=3

由下一级查上一级,如 130102 为例(石家庄市长安区),那么省级 code 为 13+0000,地级 code 为 1301+00,select id,code,level from areas where code in (130102, 130100, 130000) ,再以 level 字段区别三级。

1年前 评论
wxf666 1年前
CREATE TABLE `areas` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `area_id` INT(11) NOT NULL COMMENT '地区id',
    `name` VARCHAR(30) NOT NULL COMMENT '地区名称',
    `short_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '地区简称',
    `path` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '路径,省1 市2 区3',
    `area_code` VARCHAR(4) NOT NULL COMMENT '区号',
    `spell` VARCHAR(50) NOT NULL COMMENT '拼音',
    `letter` VARCHAR(15) NOT NULL COMMENT '简拼',
    `first_letter` VARCHAR(1) NOT NULL COMMENT '首字母',
    `status` TINYINT(4) NOT NULL DEFAULT '1' COMMENT '状态 1-正常 2-禁用',
    `parent_id` INT(11) NOT NULL DEFAULT '0' COMMENT '父ID,如果是省份,则父ID为0',
    PRIMARY KEY (`id`) USING BTREE,
)
COMMENT='地区表'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB;
1年前 评论

太多的简单问题复杂化

1年前 评论
wxf666 1年前
yjrowe

用MySQL视图可以试试,比如区县视图,包含省市区

1年前 评论
zds

懒加载

1年前 评论

这个不就是无限分级么 一个表就行 有个关键的uuid与pid就行 uuid pid name

1年前 评论
wxf666 1年前
markyanggang (作者) 1年前
markyanggang (作者) 1年前
wxf666 1年前
wxf666 1年前

id(自增) code(非重) name(北京) alias_name(京) level (province,city,county,town,) 四级 pid(省默认为0), province_id, province_name, city_id, city_name, county_id, county_name, town_id, town_name, area_path(北京市--北京市--xx区--xx街道--xx)

最后输出的时候 Transformer 遇到 北京市--北京市的这种情况 直接删除一个

1年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!