MySQL 5.7升级8.0：含GEOMETRYCOLLECTION的空间列SRID设置问题

核心问题分析 #

MySQL 8.0对空间列的SRID约束更严格：若要设置列级SRID，要求所有行的几何对象类型统一且SRID一致。当前cuatom_shapes表的shape列混合了GEOMETRYCOLLECTION、POLYGON、MULTIPOLYGON等类型，且SRID均为0，直接设置列级SRID会因类型不兼容失败。以下是两种可行的处理方案：

方案1：拆分表（推荐，符合空间数据最佳实践） #

将不同几何类型的数据拆分到独立表中，既能满足SRID约束，也便于后续数据维护。

步骤1：备份原表（必做） #

CREATE TABLE cuatom_shapes_backup LIKE cuatom_shapes;
INSERT INTO cuatom_shapes_backup SELECT * FROM cuatom_shapes;

步骤2：创建POLYGON专用表（指定SRID 4326） #

根据原表结构复制字段，仅保留POLYGON类型并绑定SRID：

CREATE TABLE cuatom_shapes_polygons (
    id INT PRIMARY KEY, -- 替换为原表实际主键
    shape POLYGON SRID 4326,
    -- 复制原表其他业务字段
    name VARCHAR(255),
    create_time DATETIME,
    ...
);

步骤3：导入POLYGON数据并设置SRID #

INSERT INTO cuatom_shapes_polygons (id, shape, name, create_time, ...)
SELECT 
    id,
    ST_SetSRID(shape, 4326) AS shape,
    name,
    create_time,
    ...
FROM cuatom_shapes
WHERE ST_GeometryType(shape) = 'POLYGON';

步骤4：创建其他几何类型专用表 #

针对GEOMETRYCOLLECTION、MULTIPOLYGON等数据，创建通用GEOMETRY类型表（可根据需求设置统一SRID）：

CREATE TABLE cuatom_shapes_collections (
    id INT PRIMARY KEY,
    shape GEOMETRY SRID 4326, -- 若这些数据的SRID也应为4326则保留，否则去掉SRID约束
    name VARCHAR(255),
    create_time DATETIME,
    ...
);

步骤5：导入其他类型数据 #

INSERT INTO cuatom_shapes_collections (id, shape, name, create_time, ...)
SELECT 
    id,
    ST_SetSRID(shape, 4326) AS shape, -- 需统一SRID时执行，否则直接用shape
    name,
    create_time,
    ...
FROM cuatom_shapes
WHERE ST_GeometryType(shape) != 'POLYGON';

步骤6：验证与切换 #

确认新表数据无误后，可重命名原表为历史表，将新表重命名为原表名，或直接使用新表开展业务。

方案2：保留原表，统一SRID并使用通用GEOMETRY类型 #

若不想拆分表，需先将所有几何对象的SRID统一为4326，再修改列约束。

步骤1：备份原表（必做） #

CREATE TABLE cuatom_shapes_backup LIKE cuatom_shapes;
INSERT INTO cuatom_shapes_backup SELECT * FROM cuatom_shapes;

步骤2：更新POLYGON行的SRID #

UPDATE cuatom_shapes 
SET shape = ST_SetSRID(shape, 4326) 
WHERE ST_GeometryType(shape) = 'POLYGON';

步骤3：更新其他几何类型的SRID #

情况A：GEOMETRYCOLLECTION/MULTIPOLYGON的SRID也应为4326

直接统一设置SRID：

UPDATE cuatom_shapes 
SET shape = ST_SetSRID(shape, 4326) 
WHERE ST_GeometryType(shape) IN ('GEOMETRYCOLLECTION', 'MULTIPOLYGON');

情况B：GEOMETRYCOLLECTION内部子对象SRID不一致

需逐个提取子对象设置SRID后重新组合：

UPDATE cuatom_shapes 
SET shape = ST_Collect(
    ST_SetSRID(ST_CollectionExtract(shape, 3), 4326), -- 提取POLYGON（类型码3）并设SRID
    ST_SetSRID(ST_CollectionExtract(shape, 2), 4326)  -- 提取LINESTRING（类型码2）并设SRID
)
WHERE ST_GeometryType(shape) = 'GEOMETRYCOLLECTION';

步骤4：修改列级SRID约束 #

所有数据SRID统一为4326后，修改列类型：

-- 若原表有空间索引，先删除
DROP INDEX idx_shape ON cuatom_shapes;

ALTER TABLE cuatom_shapes 
MODIFY COLUMN shape GEOMETRY SRID 4326;

-- 重新创建空间索引
CREATE SPATIAL INDEX idx_shape ON cuatom_shapes(shape);

关键注意事项 #

1. 数据有效性校验：MySQL 8.0对空间数据合规性要求更高，可通过ST_IsValid检查无效数据：

   SELECT id, ST_IsValid(shape) AS is_valid FROM cuatom_shapes WHERE ST_IsValid(shape) = 0;
   

2. 大表更新优化：若表数据量较大，建议分批执行UPDATE语句，避免锁表影响业务。

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