无法通过Laravel迁移实现MySQL/MariaDB功能

Question

我正在尝试创建一个mysql存储函数

        DROP FUNCTION IF EXISTS getDistance;
        
        DELIMITER $$
            CREATE FUNCTION `getDistance`(`lat1` VARCHAR(255), `lng1` VARCHAR(255), `lat2` VARCHAR(255), `lng2` VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
            BEGIN
                DECLARE distance varchar(255);
           
                SELECT (6371 * acos(cos(radians(lat2)) * cos(radians(lat1)) * cos(radians(lng1) - radians(lng2)) + sin(radians(lat2)) * sin(radians(lat1)))) INTO distance;
                 
                if(distance is null) then
                    return null;
                else 
                    return distance;
                end if;
            END
        $$
        DELIMITER ;

如果我通过phpMyAdmin执行函数，工作就会很好，函数就会在数据库中创建

但是，当我试图运行Laravel(V6.x)迁移来创建存储的函数时，我遇到了错误

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateStoredFunction extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
       DB::unprepared('
            DROP FUNCTION IF EXISTS getDistance;
            
            DELIMITER $$
                CREATE FUNCTION `getDistance`(`lat1` VARCHAR(255), `lng1` VARCHAR(255), `lat2` VARCHAR(255), `lng2` VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
                BEGIN
                    DECLARE distance varchar(255);
               
                    SELECT (6371 * acos(cos(radians(lat2)) * cos(radians(lat1)) * cos(radians(lng1) - radians(lng2)) + sin(radians(lat2)) * sin(radians(lat1)))) INTO distance;
                     
                    if(distance is null) then
                        return null;
                    else 
                        return distance;
                    end if;
                END
            $$
            DELIMITER ;
        ');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::unprepared('DROP FUNCTION IF EXISTS getDistance');
    }
}

错误

条令\dbal\driver\pdo\exception::（“SQLState[42000]:语法错误或访问违规：1064您的SQL语法中有错误；请查看与您的MariaDB服务器版本相对应的手册，以了解在'delimiter$$CREATE FUNCTIONgetdistance(lat1VARCHAR(255)'第1行使用的正确语法”）

Answer 1

delimiter不是有效的SQL语句。这只是一个MySql客户端命令。所以别用了。你得到的错误告诉你这一点。

您可以在Laravel中创建一个存储过程，如下所示，这将使delimiter过时

试试这个

class CreateStoredFunction extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
       DB::unprepared('
            DROP FUNCTION IF EXISTS getDistance;
            
            
                CREATE FUNCTION `getDistance`(`lat1` VARCHAR(255), `lng1` VARCHAR(255), `lat2` VARCHAR(255), `lng2` VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
                BEGIN
                    DECLARE distance varchar(255);
               
                    SELECT (6371 * acos(cos(radians(lat2)) * cos(radians(lat1)) * cos(radians(lng1) - radians(lng2)) + sin(radians(lat2)) * sin(radians(lat1)))) INTO distance;
                     
                    if(distance is null) then
                        return null;
                    else 
                        return distance;
                    end if;
                END
           
        ');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::unprepared('DROP FUNCTION IF EXISTS getDistance');
    }
}