MySQL 地理数据“失忆”？教你一招找回 WKT 格式

问题场景：明明是空间数据，怎么导出后变成了“乱码”？

为什么会出现这种情况？

1. 直接导出二进制数据：MySQL 的空间字段（如 geom）底层是 BLOB。如果你使用普通的导出工具（如 SQLyog 的普通导出或简单的 SELECT *），你得到的是二进制流，而不是 WKT 字符串。
2. 未使用转换函数：MySQL 提供了专门的函数来处理空间数据。如果不显式调用转换函数，数据库就不会主动把二进制转成文本。
3. 字符编码不匹配：即使转换成功，如果导出的文件或终端不支持 UTF-8 或相关字符集，特殊符号或中文字符可能会显示为乱码。
4. 导出工具的限制：某些老旧的数据库管理工具（如旧版 Navicat 或 phpMyAdmin）可能无法正确解析空间数据的二进制流，导致显示为 Hex 代码（一串 16 进制数字）。

解决方案：三步找回你的 WKT 数据

第一步：使用 ST_AsText() 函数（核心方法）

SQL
SELECT id, coord FROM locations;

SQL
SELECT id, ST_AsText(coord) AS wkt_text FROM locations;

第二步：处理导出文件的编码

SQL
SELECT 
    id, 
    name, 
    ST_AsText(coord) AS wkt_geometry 
FROM locations 
INTO OUTFILE '/tmp/export_locations.csv' 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
CHARACTER SET utf8mb4;

注意：执行此命令需要 MySQL 服务器对文件系统有写入权限，且文件不能已存在。

第三步：在应用程序中转换

PYTHON
import pymysql

connection = pymysql.connect(...)
cursor = connection.cursor()

# 关键：在 SQL 中使用 ST_AsText
sql = "SELECT id, ST_AsText(coord) as wkt FROM locations LIMIT 10"
cursor.execute(sql)

for row in cursor:
    print(f"ID: {row[0]}, WKT: {row[1]}")
    # 此时 row[1] 已经是字符串 'POINT(116.4 39.9)'，可以轻松解析或入库其他 GIS 系统

进阶技巧：除了 WKT，你还可以转换成什么？

• 转换为 GeoJSON (适用于 Web 地图)

  SQL
  SELECT 
      id, 
      ST_AsGeoJSON(coord) AS geo_json 
  FROM locations;

  结果：{"type":"Point","coordinates":[116.397,39.909]}
• 转换为 WKB (Well-Known Binary) 如果你需要在不同数据库间迁移（例如 MySQL 到 PostgreSQL），使用 ST_AsBinary 可以保持数据的二进制精度，通常比 WKT 更高效。

常见误区排查清单

1. 空间参考系统 (SRID) 问题：确保你的数据确实是有效的空间数据。可以使用 ST_IsValid(coord) 检查。如果数据本身无效，转换可能会报错或返回 NULL。
2. NULL 值处理：如果某些行的坐标是 NULL，ST_AsText(NULL) 会返回 NULL。在代码中需要做好空值判断。
3. 版本兼容性：MySQL 5.6 及以上版本才开始较好地支持 GIS 功能。如果你还在使用 MySQL 5.5 或更早版本，建议升级。
4. 工具支持：如果你使用的是 GUI 工具（如 Navicat），请确保你查看数据的“网格”或“数据视图”模式支持空间数据可视化。有些工具需要安装额外的插件才能正确显示 WKT。

总结