MySQL 拆分地址並分別加到欄位中

目前要寫stored procedure 所以必須使用到mysql來解決資料的問題

問題:

地址                         計算地址                       縣市     區   ...
台中市大甲區中山路30段8號     台中市,大甲區,中山路,30段,8號,   
需求:
地址                         計算地址                       縣市     區   ...
台中市大甲區中山路30段8號     台中市,大甲區,中山路,30段,8號,   台中市   大甲區

需求是需要將地址欄位拆分並將拆分完的資料放到各個欄位
有嘗試先將地址資料加入字串符但是後續不知道有什麼function可以達到我的要求
目前有用到REGEXP_SUBSTR,substring_index,replace

額外問題

因為有可能會遇到有大里路這種路名
如果依照我自己的寫法可能會拆成大里跟路
所以不知道有什麼方式能解決這種問題
如果有大神能幫忙解決我會很感激沒有也沒關係

mysql

RDan

30 声望

暂无个人描述~

0 人点赞

wxf666

68 声望

最佳答案

数据库新手，来参与讨论一下。

之前拿一个 66W 行数据的 5级地区库，来练习 SQL 的时候，有做过类似的功能。

并在浏览器上用 wasm 实现的 SQLite 进行测试时，性能也表现的不错：

每秒有 100W ~ 160W 次的 LEFT JOIN （使得快速拼接路径全名，以及子路径模糊匹配成为可能）
5~6 毫秒识别出 疆维阿克苏温宿县博孜墩柯尔克孜族乡吾斯塘博村一组306号tom800-8585222 的省/市/区/街/村 （新疆维吾尔自治区、阿克苏地区、温宿县、博孜墩柯尔克孜族乡、吾斯塘博依村）

但没继续做后续的 xx路、xx号 识别了 （功力不够，没法用纯 SQL 实现了）

file

3年前评论

RDan （楼主）

可否提供個思路呢
目前遇到的困難是拆分下來的數據
不一定第一格就是縣市第二格就是區

wxf666 （作者）

@a1016233 我是模仿地址智能识别 js 库的思路写的。

拆分下来的数据，不一定第一格就是县市第二格就是区

你是说，有可能要识别 大甲區中山路30段8號 为 台中市、大甲區、中山路、30段、8號？

RDan （楼主）

您說的對數據不一定每一段就固定是什麼
我需要將地址拆分然後分別放到各個欄位
我使用python 有順利解決
但是目前會使用Mysql 是因為使用到stored procedure
因為有可能
中山路1000號
也有可能
中山路xx巷80號
目前作法是將路巷號樓等都加個字串符再用substring_index來拆分
再用right判別最右邊的字是縣市路巷等其中一個
目前的思路是
case when 最右邊的字=縣 then table = update 縣市
但是mysql似乎不能這樣子做

wxf666 （作者）

@a1016233 既然 Python 能解决，为啥还要用 stored procedure 呢。。

我上面是存了所有地区名，再逐级模糊匹配的。

如果没有地区数据库，我没有把握能准确拆分某个地址中的省/市/区/街/村。

除非所有地址都非常规矩，老老实实写成 xx 市 xx 区 xx 街道 xx 路 xx 号。

那一个正则 ^(.+省)?(.+市)?(.+区)?(.+街道?)?(.+路)?(.+号)?$ 捕获出来就可以了。

但我觉得不太现实。

如果只给部分地址 （且不要求各个部分必须完整。如，可以只给出“新疆”，而不是“新疆维吾尔自治区”），要求推测出完整路径。我这个数据库新手能想到的是：

对地区数据库的地区名全文索引。
初始化队列，放入【根节点】地区。
取出一个地区，在该地区所有后代范围内，模糊搜索【包含给定地址前两个字】的所有地区，并为每个地区截掉【给定地址出现在该地区的最长前缀】，作为该地区后续匹配的【给定地址】。最后全部添加进队列中。

（如，给定地址“新疆维吾尔阿克苏温宿县……”出现在“新疆维吾尔自治区”的最长前缀为“新疆维吾尔”。截掉后，给定地址为“阿克苏温宿县……”，作为后续在“新疆维吾尔自治区”范围内继续搜索的【给定地址】）
队列若不为空，则跳转回第 3 步。
检查所有曾经匹配出的结果，取匹配层级最深、截掉【给定地址】最多、…… 的地区，作为结果。

第 2 ~ 5 步，可以用数据库的 (Recursive) CTE 做到 （1 MB 的 SQLite 也支持）。

RDan （楼主）

我目前大致上有成功解決還是謝謝您
stored procedure主要是主管要求緣故
您要不要發在一般言論我幫你設定最佳解答

wxf666 （作者）

@a1016233 没事，都行。主要是来参与讨论的。。

RDan （楼主）

還是很感謝你
我地址的部分包括號
是將地址要切割的部分替換
例如省–>省|
在用substring_index取得”|”並分別放到對應的欄位

讨论数量: 8

wxf666

68 声望

数据库新手，来参与讨论一下。

之前拿一个 66W 行数据的 5级地区库，来练习 SQL 的时候，有做过类似的功能。

并在浏览器上用 wasm 实现的 SQLite 进行测试时，性能也表现的不错：

每秒有 100W ~ 160W 次的 LEFT JOIN （使得快速拼接路径全名，以及子路径模糊匹配成为可能）
5~6 毫秒识别出 疆维阿克苏温宿县博孜墩柯尔克孜族乡吾斯塘博村一组306号tom800-8585222 的省/市/区/街/村 （新疆维吾尔自治区、阿克苏地区、温宿县、博孜墩柯尔克孜族乡、吾斯塘博依村）

但没继续做后续的 xx路、xx号 识别了 （功力不够，没法用纯 SQL 实现了）

file

3年前评论

RDan （楼主）

可否提供個思路呢
目前遇到的困難是拆分下來的數據
不一定第一格就是縣市第二格就是區

wxf666 （作者）

@a1016233 我是模仿地址智能识别 js 库的思路写的。

拆分下来的数据，不一定第一格就是县市第二格就是区

你是说，有可能要识别 大甲區中山路30段8號 为 台中市、大甲區、中山路、30段、8號？

RDan （楼主）

wxf666 （作者）

@a1016233 既然 Python 能解决，为啥还要用 stored procedure 呢。。

我上面是存了所有地区名，再逐级模糊匹配的。

如果没有地区数据库，我没有把握能准确拆分某个地址中的省/市/区/街/村。

除非所有地址都非常规矩，老老实实写成 xx 市 xx 区 xx 街道 xx 路 xx 号。

那一个正则 ^(.+省)?(.+市)?(.+区)?(.+街道?)?(.+路)?(.+号)?$ 捕获出来就可以了。

但我觉得不太现实。

对地区数据库的地区名全文索引。
初始化队列，放入【根节点】地区。
取出一个地区，在该地区所有后代范围内，模糊搜索【包含给定地址前两个字】的所有地区，并为每个地区截掉【给定地址出现在该地区的最长前缀】，作为该地区后续匹配的【给定地址】。最后全部添加进队列中。

（如，给定地址“新疆维吾尔阿克苏温宿县……”出现在“新疆维吾尔自治区”的最长前缀为“新疆维吾尔”。截掉后，给定地址为“阿克苏温宿县……”，作为后续在“新疆维吾尔自治区”范围内继续搜索的【给定地址】）
队列若不为空，则跳转回第 3 步。
检查所有曾经匹配出的结果，取匹配层级最深、截掉【给定地址】最多、…… 的地区，作为结果。

第 2 ~ 5 步，可以用数据库的 (Recursive) CTE 做到 （1 MB 的 SQLite 也支持）。

RDan （楼主）

我目前大致上有成功解決還是謝謝您
stored procedure主要是主管要求緣故
您要不要發在一般言論我幫你設定最佳解答

wxf666 （作者）

@a1016233 没事，都行。主要是来参与讨论的。。

RDan （楼主）

還是很感謝你
我地址的部分包括號
是將地址要切割的部分替換
例如省–>省|
在用substring_index取得”|”並分別放到對應的欄位

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

帮助

MySQL 拆分地址並分別加到欄位中

額外問題

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

MySQL 拆分地址並分別加到欄位中

額外問題

社区赞助商

关于 LearnKu

资源推荐

服务提供商

其他信息

请登录