MySQL中如何将坐标字符串拆分更新至lat、lon字段？

别担心，你完全可以用一条UPDATE语句结合SUBSTRING_INDEX函数一次性完成两个字段的更新，不用分开执行两次。假设你的表名叫your_table，存储原始坐标的字段叫coordinate，那具体的SQL语句是这样的：

UPDATE your_table
SET 
  lat = SUBSTRING_INDEX(SUBSTRING_INDEX(coordinate, ' ', 1), 'N', -1),
  lon = SUBSTRING_INDEX(SUBSTRING_INDEX(coordinate, ' ', -1), 'E', -1);

代码逻辑拆解 #

• 提取纬度（lat）：
  1. 先用SUBSTRING_INDEX(coordinate, ' ', 1)把坐标按空格拆分，取出第一个部分（也就是N52.27337）
  2. 再用SUBSTRING_INDEX(..., 'N', -1)把这个部分按N拆分，取最后一段，就能得到纯数字的纬度值52.27337
• 提取经度（lon）：
  1. 先用SUBSTRING_INDEX(coordinate, ' ', -1)按空格拆分坐标，取出最后一个部分（也就是E4.98162）
  2. 再用SUBSTRING_INDEX(..., 'E', -1)按E拆分，取最后一段，得到纯数字的经度值4.98162

额外实用提醒 #

• 如果你的坐标包含南纬（S）或西经（W），记得处理符号，比如南纬要加负号：

  lat = CASE 
          WHEN SUBSTRING_INDEX(coordinate, ' ', 1) LIKE 'S%' 
          THEN -SUBSTRING_INDEX(SUBSTRING_INDEX(coordinate, ' ', 1), 'S', -1)
          ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(coordinate, ' ', 1), 'N', -1)
        END
  

• 执行更新前，建议先用SELECT测试结果，避免误操作：

  SELECT 
    coordinate,
    SUBSTRING_INDEX(SUBSTRING_INDEX(coordinate, ' ', 1), 'N', -1) AS test_lat,
    SUBSTRING_INDEX(SUBSTRING_INDEX(coordinate, ' ', -1), 'E', -1) AS test_lon
  FROM your_table;
  

内容的提问来源于stack exchange，提问作者Jack Vonderberg