Create MySQL function in Laravel 5.5

Question

I have MySQL 5.6 and I need to use MySQL 5.7 function ST_Distance_Sphere (for geolocalization purposes).

I found this hack and it looks correct to me (?).

Now I'm trying to use it in Laravel 5.5.

I put it in app\Providers\AppServiceProvider.php in boot() function.

I tried with DB::statement, DB::raw, DB::unprepared.

I tried removing DELIMITER and $$.

$sql = '
        DELIMITER $$

        DROP FUNCTION IF EXISTS `ST_Distance_Sphere`$$

        CREATE FUNCTION `ST_Distance_Sphere` (point1 POINT, point2 POINT)

            RETURNS FLOAT
            no sql deterministic
            BEGIN
                declare R INTEGER DEFAULT 6371000;
                declare `φ1` float;
                declare `φ2` float;
                declare `Δφ` float;
                declare `Δλ` float;
                declare a float;
                declare c float;
                set `φ1` = radians(y(point1));
                set `φ2` = radians(y(point2));
                set `Δφ` = radians(y(point2) - y(point1));
                set `Δλ` = radians(x(point2) - x(point1));

                set a = sin(`Δφ` / 2) * sin(`Δφ` / 2) + cos(`φ1`) * cos(`φ2`) * sin(`Δλ` / 2) * sin(`Δλ` / 2);
                set c = 2 * atan2(sqrt(a), sqrt(1-a));

                return R * c;
            END$$

        DELIMITER ;
    ';

DB::statement($sql);

I have a syntax error everytime. For example:

``Illuminate \ Database \ QueryException (42000)SQLSTATE[42000]: Syntax error or access violation: 1064 Erreur de syntaxe près de 'DELIMITER $$ DROP FUNCTION IF EXISTS `ST_Distance_Sphere`$$ ' à la ligne 1 (SQL: DELIMITER $$ DROP FUNCTION IF EXISTS `ST_Distance_Sphere`$$ CREATE FUNCTION `ST_Distance_Sphere` (point1 POINT, point2 POINT) RETURNS FLOAT no sql deterministic BEGIN declare R INTEGER DEFAULT 6371000; declare `φ1` float; declare `φ2` float; declare `Δφ` float; declare `Δλ` float; declare a float; declare c float; set `φ1` = radians(y(point1)); set `φ2` = radians(y(point2)); set `Δφ` = radians(y(point2) - y(point1)); set `Δλ` = radians(x(point2) - x(point1)); set a = sin(`Δφ` / 2) * sin(`Δφ` / 2) + cos(`φ1`) * cos(`φ2`) * sin(`Δλ` / 2) * sin(`Δλ` / 2); set c = 2 * atan2(sqrt(a), sqrt(1-a)); return R * c; END$$ DELIMITER ; )``

Answer 1

I answer my own question

It seems I didn't have access to drop a MySQL function...

So I replaced DROP FUNCTION IF EXISTS `ST_Distance_Sphere` by a MySQL version check:

$mysql_version_check = DB::select(DB::raw('SHOW VARIABLES LIKE "version";'));
$mysql_version = $mysql_version_check[0]->Value;
if (substr($mysql_version,2, 1) < '7' AND substr($mysql_version,4, 1) < '6') {
    $sql = '
        CREATE FUNCTION `ST_Distance_Sphere` (point1 POINT, point2 POINT)

            RETURNS FLOAT
            no sql deterministic
            BEGIN
                declare R INTEGER DEFAULT 6371000;
                declare `φ1` float;
                declare `φ2` float;
                declare `Δφ` float;
                declare `Δλ` float;
                declare a float;
                declare c float;
                set `φ1` = radians(y(point1));
                set `φ2` = radians(y(point2));
                set `Δφ` = radians(y(point2) - y(point1));
                set `Δλ` = radians(x(point2) - x(point1));

                set a = sin(`Δφ` / 2) * sin(`Δφ` / 2) + cos(`φ1`) * cos(`φ2`) * sin(`Δλ` / 2) * sin(`Δλ` / 2);
                set c = 2 * atan2(sqrt(a), sqrt(1-a));

                return R * c;
            END;
    ';

    DB::unprepared($sql);

}

It's a bit ugly, but it appears to work...

EDIT

Actually, it will work only the 1st time it is executed. It appears this function could save the mysql function to the mysql database. You will have an error the next times like "Mysql function already exists". You just have to comment the block above.