mysql5.5 创建函数_在laravel 5.5中创建mysql函数

我有mysql 5.6,我需要使用mysql 5.7函数

ST_Distance_Sphere

(用于地理定位目的)。

我发现

this hack

在我看来是正确的。.

现在我试着用在拉弗5.5。

我把它放进去了

app\Providers\AppServiceProvider.php

在里面

boot()

功能。

我尝试过

DB::statement

,

DB::raw

,

DB::unprepared

.

我试着移除

DELIMITER

和

$$

.

$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);

我每次都有语法错误。例如:

``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 ; )``