Laravel 5.1 如何使用迁移创建 MySQL 存储过程 - Laravel 5.1 How do you create a MySQL stored procedure with migration

Laravel 5.1 How do you create a MySQL stored procedure with migration

您好，我正在尝试使用 Laravel 5.1 迁移创建一个存储过程。到目前为止，我尝试使用DB：：connection（）->getPdo（）->exec（），DB：：raw（）和DB：：unprepared（），但没有运气。我的代码如下所示：

use Illuminate'Database'Schema'Blueprint;
use Illuminate'Database'Migrations'Migration;
use Wryttnapp'Models'User;

class AddCentroidFieldsToUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::connection()->getPdo()->exec("
            ALTER TABLE `users` 
            ADD COLUMN centroid POINT NULL 
            AFTER `avatar`;");
        DB::unprepared("SET @OLD_SQL_MODE=@@SQL_MODE;
            DELIMITER ;;
            DROP PROCEDURE IF EXISTS UPDATE_USER_CENTROID;;
            CREATE PROCEDURE UPDATE_USER_CENTROID(userId INT)
              NOT DETERMINISTIC
            BEGIN
              DECLARE bDone INT;
              DECLARE tempLatLon VARCHAR(100);
              DECLARE counter INT;
              DECLARE firstLatLon VARCHAR(100);
              DECLARE polygonDefinition TEXT;
              DECLARE geometry POINT;
              DECLARE curs CURSOR FOR SELECT DISTINCT CONCAT(`latitude`, ' ', `longitude`) FROM `user_locations` WHERE `user_id` = userId ORDER BY `id` ASC;
              DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
              OPEN curs;
              SET bDone = 0;
              SET counter = 0;
              SET polygonDefinition = ',';
              FETCH curs INTO tempLatLon;
              WHILE NOT bDone DO
                SET polygonDefinition = CONCAT(polygonDefinition, ',', tempLatLon);
                IF counter = 0 THEN
                      SET firstLatLon = tempLatLon;
                END IF;
                SET counter = counter + 1;
                FETCH curs INTO tempLatLon;
              END WHILE;
              CLOSE curs;
              SET polygonDefinition = CONCAT(polygonDefinition, ',', firstLatLon);
              SET polygonDefinition = SUBSTRING(polygonDefinition, 3);
              SET polygonDefinition = CONCAT('POLYGON((', polygonDefinition, '))');
              SET geometry = ST_Centroid(ST_GeomFromText(polygonDefinition));
              UPDATE `users` SET centroid = geometry WHERE id = userId;
            END;;
            DELIMITER ;
            ");
    }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::connection()->getPdo()->exec("
            ALTER TABLE `users` DROP COLUMN centroid;
            DROP INDEX centroid_index ON `users`;
            DROP FUNCTION IF EXISTS `UPDATE_USER_CENTROID`;");
    }
}

请帮忙！谢谢！

到目前为止，

这就是我在迁移中执行过程的方式......我相信我在设置分隔符时遇到了一些麻烦，所以我放弃了它们并为每个语句使用了不同的unprepared()。

$procedure = "
    CREATE PROCEDURE `procedure_name`(procedure_param_1 TEXT, procedure_param_2 TEXT)
    BEGIN
         // Your SP here
    END
";
DB::unprepared("DROP procedure IF EXISTS procedure_name");
DB::unprepared($procedure);

这似乎与此相关： Laravel迁移原始sql错误