MySQL导出/导入时Polygon的SRID未保留致空间查询失败问题问询

问题根源 #

所有主流导出工具（PhpMyAdmin、TablePlus、原生mysqldump默认模式）导出空间数据时，会将Polygon转为WKT文本格式，而WKT不包含SRID元数据，导致导入后几何字段的SRID被重置为默认值0，进而使依赖SRID的ST_Intersects等空间函数无法正常工作。

紧急修复方案（已导入错误数据时） #

如果已经完成导入且SRID丢失，按以下步骤修复：

1. 检查当前SRID状态
   先确认字段的SRID是否确实为0：

   SELECT ST_SRID(coordinates_polygon) FROM airspaces LIMIT 1;
   

2. 删除空间索引（若存在）
   空间索引依赖SRID，直接修改SRID会导致索引失效，需先删除：

   ALTER TABLE airspaces DROP INDEX idx_coordinates_polygon;
   

   （替换idx_coordinates_polygon为你的实际空间索引名）

3. 重置SRID元数据
   使用ST_SetSRID直接修改几何对象的SRID，避免通过WKT转换丢失精度：

   UPDATE airspaces SET coordinates_polygon = ST_SetSRID(coordinates_polygon, 4326);
   

   （4326为WGS84对应的SRID，按需替换）

4. 重建空间索引
   修复完成后重新创建空间索引：

   ALTER TABLE airspaces ADD SPATIAL INDEX idx_coordinates_polygon (coordinates_polygon);
   

5. 验证修复效果
   再次检查SRID，并测试ST_Intersects：

   SELECT ST_SRID(coordinates_polygon) FROM airspaces LIMIT 1;
   -- 测试匹配
   SELECT * FROM airspaces WHERE ST_Intersects(coordinates_polygon, ST_GeomFromText('POINT(XXX XXX)', 4326));
   

永久解决方法（正确导出导入流程） #

为避免后续再出现SRID丢失问题，使用mysqldump时添加--hex-blob参数，导出EWKB格式的空间数据（EWKB包含SRID元数据）：

导出命令 #

mysqldump -u [用户名] -p --hex-blob [数据库名] airspaces > airspaces_dump.sql

导入命令 #

直接用mysql命令导入即可，EWKB格式会自动保留SRID：

mysql -u [用户名] -p [目标数据库名] < airspaces_dump.sql

为什么之前的UPDATE语句无效？ #

你之前使用的ST_GeomFromText(ST_AsText(...), 4326)存在两个问题：

1. ST_AsText会将几何对象转为文本格式，可能丢失高精度坐标信息；
2. 若字段存在空间索引，修改后的几何对象无法触发索引更新，导致ST_Intersects仍使用旧索引失效的逻辑。
   而ST_SetSRID直接修改几何对象的SRID元数据，无需转换格式，更高效可靠。

内容的提问来源于stack exchange，提问作者Matt Welander