MySQL test if function is available

functionsMySQLmysql-5.7mysql-8.0

Since MySQL 5.8 the function GeomFromText() is no longer available and one should use ST_GeomFromText() instead, which works exactly the same. Progress, of course 🙁

Ref: https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html#function_geomfromtext

Now I'd like to use this function in a way that is backwards compatible so it can run on very old MySQL server versions as much as possible. What is the best way to check if this function is defined and which of the two (if any) I can use? I'd like to prevent having to check the version number of the server to select the correct query, if at all possible.

Best Answer

You can use the IF() function, VERSION() function and Dynamic SQL:

SET @dynamic_func_name = CONCAT(IF(LEFT(VERSION(),3)>'5.6','ST_',''),'GeomFromText');
SET @sql = CONCAT('CALL ',@dynamic_func_name,'(wkt,srid)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

I used the left 3 character of the VERSION() to get the major release. Why ?

  • If LEFT(VERSION(),3) returns 5.7 or more, @dynamic_func_name will be ST_GeomFromText
  • If LEFT(VERSION(),3) returns 5.6 or less, @dynamic_func_name will be GeomFromText
Related Question