代码之家 › 专栏 › 技术社区 › charlie

在laravel 5.5中创建mysql函数

laravel database mysql php

charlie · 技术社区 · 7 年前

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

1 回复 | 直到 7 年前

charlie 7 年前

我回答自己的问题

似乎我没有权限删除MySQL函数…

所以我换了 DROP FUNCTION IF EXISTS `ST_Distance_Sphere` 通过mysql版本检查:

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

}

它有点难看,但看起来很管用…

编辑

实际上,它只会执行第一次。这个函数似乎可以将mysql函数保存到mysql数据库中。下一次“MySQL函数已经存在”时,您将有一个错误。你只需要评论上面的方块。