MySQL 在字符串中移动，在逗号之前和之后

Question

我有一个 mysql 行，其坐标保存在纬度/经度中。我需要将其更改为长/纬度。

我知道这需要以某种方式加以界定并进行改变。

在 MySQL 中执行此操作的正确方法是什么？

示例:

 ["26.247798956308,50.657465902192","26.247765257163,50.656210342278","26.24777327998,50.655413265762","26.247772343418,50.655399250007","26.24775833567,50.655206036067","26.247768008419,50.655097864672","26.247779429251,50.654965656351","26.247844499259,50.654613999694","26.247882692272,50.654350538671","26.247886070313,50.654253400286","26.248070301095,50.654265872269","26.24871145246,50.654291010692","26.248712355399,50.65429100802","26.248938997758,50.654292339968","26.248939900696,50.654292337296","26.249167443576,50.654292665245","26.249652369652,50.654311256915","26.24965327259,50.654311254243","26.250527203555,50.65426160469","26.251604232782,50.654185317821","26.251850689062,50.654165562357","26.252226248584,50.654138414997","26.252525072823,50.654117502871","26.252875354859,50.654092432768","26.253260873151,50.654076270464","26.253503640429,50.654024481477","26.253881809743,50.653956269108","26.253918794004,50.653941138898","26.253934151183,50.65394409752","26.254001887777,50.653956645335","26.254066915409,50.653981134594","26.254127534099,50.654016927469","26.254182158719,50.654063086371","26.254229361508,50.654118404871","26.254267909164,50.654181436626","26.254426530564,50.654433314626","26.254430159178,50.654440313548","26.254140446036,50.654870761592","26.253604791621,50.655536254578","26.253060504139,50.655991479569","26.252712653207,50.656277893161","26.252469279111,50.656453847903","26.252138350894,50.65664307698","26.25144296537,50.656971562628","26.250888157852,50.657182473989","26.250417074844,50.657288997819","26.249898133586,50.657394659791","26.248058102136,50.657466143684","26.247798956308,50.657465902192"]

Answer 1

一般规则(称为数据库标准化)是:

切勿在单个列中存储多个值!

因此，如果您可以更改表格设计，您可以这样做(在我的示例中，我们存储用户收藏夹的积分 - 我不知道您的积分有什么用途):

users table
-----------
id
name
....


coordinates table
-----------------
user_id
lng
lat

如果您想检索用户“Tom”的所有记录，则可以运行此查询

select c.lng, c.lat
from coordinates c
join users u on u.id = c.user_id
where u.name = 'Tom'