MySQL【bug】- spatial key

【bug1】

MySQL建Spatial索引的前提条件是列定义NOT NULL，而当location列中有**’GEOMETRYCOLLECTION EMPTY ‘的值时，这里‘GEOMETRYCOLLECTION EMPTY’**变相绕过了这个限制，会导致报错。

插入空集合 GEOMETRYCOLLECTION EMPTY，空集合占一行空间，语义上“我知道这里该有几何，但当前没有数据”。

与 NULL 的区别

场景 存储值 ST_IsEmpty ST_AsText
NULL 无 NULL NULL
GEOMETRYCOLLECTION EMPTY 有 1 GEOMETRYCOLLECTION EMPTY

重建表也无法进行修复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `spatial_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `location` geometry NOT NULL SRID 4326,
  `number` varchar(20) NOT NULL DEFAULT '7425346',
  PRIMARY KEY (`id`),
  UNIQUE KEY `number` (`number`),
  SPATIAL KEY `location_index` (`location`)
);

INSERT INTO spatial_table (id, name, location, number)
VALUES
(1, 'Place A', ST_GeomFromText('POINT(1 1)', 4326), '7425341'),
(2, 'Place B', ST_GeomFromText('LINESTRING(1 1,2 2,3 3)', 4326), '7425342'),
(3, 'Place C', ST_GeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))', 4326), '7425343'),
(4, 'Place D', ST_GeomFromText('MULTIPOINT((0 0),(20 20),(60 60))', 4326), '7425344'),
(5, 'Place E', ST_GeomFromText('GEOMETRYCOLLECTION(POINT(10 10),POINT(30 30),LINESTRING(15 15,20 20))', 4326), '7425345'),
(6, 'Place F', ST_GeomFromText('GEOMETRYCOLLECTION(POINT(10 10),POINT(30 30),LINESTRING(15 15,20 20))', 4326), '7425348'),
(7, 'Beijing', ST_GeomFromText('POINT(39.9042 116.4074)', 4326), '7425347'),
(8, '<null>', ST_GeomFromText('GEOMETRYCOLLECTION EMPTY', 4326), '7425346');  

SELECT id,name,st_astext(location),number From spatial_table;

解决方法：删除这些行并重建表

【bug2】

先对含** SPATIAL KEY** 的表做过 ALTER TABLE → R-tree 根页指针错乱，后续 UPDATE/DELETE走空间索引时找不到记录 → 报** Record in index … not found**。在MySQL Server 5.7.49、8.0.41、8.4.4和9.2.0版本中修复。

官方文档：https://bugs.mysql.com/bug.php?id=93728

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE tab (
  c1 INT NOT NULL PRIMARY KEY,
  c2 POINT NOT NULL SRID 4326,
  c3 LINESTRING NOT NULL SRID 4326,
  c4 POLYGON NOT NULL SRID 4326,
  c5 GEOMETRY NOT NULL SRID 4326,
  SPATIAL KEY idx2 (c2),
  SPATIAL KEY idx3 (c3),
  SPATIAL KEY idx4 (c4),
  SPATIAL KEY idx5 (c5)
) ENGINE = InnoDB;

INSERT INTO tab(c1,c2,c3,c4,c5) VALUES
(1,
 ST_GeomFromText('POINT(10 10)', 4326),
 ST_GeomFromText('LINESTRING(5 5,20 20,30 30)', 4326),
 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))', 4326),
 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))', 4326));

INSERT INTO tab(c1,c2,c3,c4,c5) VALUES
(2,
 ST_GeomFromText('POINT(20 20)', 4326),
 ST_GeomFromText('LINESTRING(20 20,30 30,40 40)', 4326),
 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))', 4326),
 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))', 4326));

ALTER TABLE tab
  ADD COLUMN new_uniq_col INT NOT NULL AUTO_INCREMENT FIRST,
  ADD UNIQUE INDEX uniq_idx(new_uniq_col),
  DROP PRIMARY KEY;

DELETE From tab;

解决方法：

  • 升级版本
  • 重建表再重新导入数据