在Laravel 5.5中创建MySQL函数

问题描述

我有MySQL 5.6，需要使用MySQL 5.7函数ST_Distance_Sphere(用于地理定位).

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

我发现了此hack ，它看起来是正确的给我(?).

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

现在我正在尝试在Laravel 5.5中使用它.

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

我将其放在boot()函数的app\Providers\AppServiceProvider.php中.

我尝试使用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);

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

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

推荐答案

我回答了我自己的问题

似乎我无权删除MySQL函数...

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

所以我用MySQL版本检查替换了DROP FUNCTION IF EXISTS `ST_Distance_Sphere`:

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...

实际上，它将仅在第一次执行时起作用.看来此函数可以将mysql函数保存到mysql数据库中.下次您将出现错误，例如"Mysql函数已经存在".您只需要在上面的方框中添加注释即可.

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.

这篇关于在Laravel 5.5中创建MySQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！