mysql - mysql st_within不适用于虚拟列

我的桌子是这样的：

CREATE TABLE `jovan_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `properties__name` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.name'))) VIRTUAL,
  `properties__age` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.age'))) VIRTUAL,
  `properties__height` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.height'))) VIRTUAL,
  `properties__address__city` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.city'))) VIRTUAL,
  `properties__address__zip_code` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.zip_code'))) VIRTUAL,
  `properties__address__state` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.state'))) VIRTUAL,
  `properties__address__country` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.country'))) VIRTUAL,
  `properties__address__home_coordinate` point GENERATED ALWAYS AS (st_geomfromgeojson(json_unquote(json_extract(`__docs`,'$.properties.address.home_coordinate')))) VIRTUAL,
  `properties__address__routes` geometry GENERATED ALWAYS AS (st_geomfromgeojson(json_unquote(json_extract(`__docs`,'$.properties.address.routes')))) VIRTUAL,
  `__docs` json NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `jovan_test_properties__name_index` (`properties__name`),
  KEY `jovan_test_properties__age_index` (`properties__age`),
  KEY `jovan_test_properties__height_index` (`properties__height`),
  KEY `jovan_test_properties__address__city_index` (`properties__address__city`),
  KEY `jovan_test_properties__address__zip_code_index` (`properties__address__zip_code`),
  KEY `jovan_test_properties__address__state_index` (`properties__address__state`),
  KEY `jovan_test_properties__address__country_index` (`properties__address__country`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

像这样插入新值：

insert into jovan_test set __docs = '{"properties": {"age": 29, "name": "Rahmat Awaludin", "height": 160, "address": {"city": "Bandung", "state": "Jawa Barat", "routes": {"type": "LineString", "coordinates": [[102, 0], [103, 1], [104, 0], [105, 1]]}, "country": "Indonesia", "zip_code": "43193", "home_coordinate": {"type": "Point", "coordinates": [30, 10]}}}}'

结果如下：

mysql root@localhost:intelligence> select id, st_astext(properties__address__home_coordinate) from jovan_test;
+----+-------------------------------------------------+
| id | st_astext(properties__address__home_coordinate) |
+----+-------------------------------------------------+
| 2  | POINT(30 10)                                    |
+----+-------------------------------------------------+

我想使用st_within查询该记录。我这样做是这样的：

select * from `jovan_test` where st_within(`properties__address__home_coordinate`, ST_GeomFromText('POLYGON((30 0,0 0,0 30,30 30,30 0))',4326))
+----+------------------+-----------------+--------------------+---------------------------+-------------------------------+----------------------------+------------------------------+--------------------------------------+-----------------------------+--------+------------+------------+
| id | properties__name | properties__age | properties__height | properties__address__city | properties__address__zip_code | properties__address__state | properties__address__country | properties__address__home_coordinate | properties__address__routes | __docs | created_at | updated_at |
+----+------------------+-----------------+--------------------+---------------------------+-------------------------------+----------------------------+------------------------------+--------------------------------------+-----------------------------+--------+------------+------------+
0 rows in set

如您所见，我在那里得到0结果。我究竟做错了什么？

最佳答案

找到了！如果该点在其边界内，则st_within将返回false。

该查询有效：

select * from `jovan_test` where st_within(`properties__address__home_coordinate`, ST_GeomFromText('POLYGON((40 -10,-10 -10,-10 40,40 40,40 -10))',4326))

关于mysql - mysql st_within不适用于虚拟列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51274958/