Create View on MySQL似乎在视图中包含地理空间函数时生成无效的函数名(MySQL运行在Amazon EC2/Amazon Linux上);
mysql> create view test as select point(1,1);
Query OK, 0 rows affected (0.01 sec)
mysql> select * from test;
ERROR 1356 (HY000): View 'wordpress.test' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
mysql> show create view test
-> ;
+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| test | CREATE ALGORITHM=UNDEFINED DEFINER=`eric`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select `st_point`(1,1) AS `point(1,1)` | utf8 | utf8_general_ci |
+------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> drop view test;
Query OK, 0 rows affected (0.00 sec)
问题似乎是st_point不是一个有效的MySQL函数;
mysql> select st_point(1,1);
ERROR 1305 (42000): FUNCTION wordpress.st_point does not exist
问题是,为什么会发生这种情况,我如何阻止它?
下面是遇到问题的计算机上安装的软件包列表;
sudo yum list installed | grep mysql
mysql-config.x86_64 5.5.51-1.11.amzn1 @amzn-updates
mysql56.x86_64 5.6.32-1.16.amzn1 @amzn-updates
mysql56-common.x86_64 5.6.32-1.16.amzn1 @amzn-updates
mysql56-devel.x86_64 5.6.32-1.16.amzn1 @amzn-updates
mysql56-embedded.x86_64 5.6.32-1.16.amzn1 @amzn-updates
mysql56-errmsg.x86_64 5.6.32-1.16.amzn1 @amzn-updates
mysql56-libs.x86_64 5.6.32-1.16.amzn1 @amzn-updates
mysql56-server.x86_64 5.6.32-1.16.amzn1 @amzn-updates
php70-mysqlnd.x86_64 7.0.9-1.14.amzn1 @amzn-updates
一个类似的电脑使用旧的mysql包不会出现这个问题-但我需要新版本的mysql来访问新的地理空间功能。
在旧电脑上进行相同的测试;
mysql> create view test as select point(1,1);
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+---------------------------+
| point(1,1) |
+---------------------------+
| ?? ?? |
+---------------------------+
1 row in set (0.00 sec)
mysql> show create view test;
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| test | CREATE ALGORITHM=UNDEFINED DEFINER=`eric`@`localhost` SQL SECURITY DEFINER VIEW `test` AS select point(1,1) AS `point(1,1)` | utf8 | utf8_general_ci |
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)
mysql> drop view test;
Query OK, 0 rows affected (0.00 sec)
旧电脑上的包;
sudo yum list installed | grep mysql;
mysql.noarch 5.5-1.6.amzn1 @amzn-main
mysql-config.x86_64 5.5.51-1.11.amzn1 @amzn-updates
mysql-devel.noarch 5.5-1.6.amzn1 @amzn-main
mysql-server.noarch 5.5-1.6.amzn1 @amzn-main
mysql55.x86_64 5.5.51-1.11.amzn1 @amzn-updates
mysql55-devel.x86_64 5.5.51-1.11.amzn1 @amzn-updates
mysql55-libs.x86_64 5.5.51-1.11.amzn1 @amzn-updates
mysql55-server.x86_64 5.5.51-1.11.amzn1 @amzn-updates
php-mysql.x86_64 5.3.29-1.8.amzn1 @amzn-updates
我错过了什么?
更新:此问题已被验证为MySQL版本5.6.10+
是的,正如@Michael提到的GeomFromText
在5.6.27工作。
如果您需要将后期/lng列插入Point()
中,则:
CREATE OR REPLACE VIEW `vw_some_table` AS
SELECT
id,
name,
lat,
lng,
status,
GeomFromText(concat('point(',lng,' ', lat,')'))
FROM vw_some_table;
注意concat的用法。
或者像你的例子:
CREATE OR REPLACE VIEW `vw_some_table` AS
SELECT
id,
name,
lat,
lng,
status,
GeomFromText('point(1 1')
FROM vw_some_table;
很惊讶他们还没有修复这样一个严重的错误…不管它在5.7.x中工作
https://dev.mysql.com/doc/refman/5.6/en/gis-point-property-functions.html