Laravel迁移原始SQL错误 [英] Laravel migration raw sql error

问题描述

我想使用Levenshtein距离在Laravel中进行搜索.我发现了SQL函数(另存为app/database/migrations/levenshtain.sql):

I want to use Levenshtein distance for search in Laravel. I found SQL function (saved as app/database/migrations/levenshtain.sql):

    DELIMITER //
    CREATE FUNCTION `LEVENSHTEIN`(s1 VARCHAR(255) CHARACTER SET utf8, s2 VARCHAR(255) CHARACTER SET utf8) RETURNS int(11)
        DETERMINISTIC
    BEGIN
        DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
        DECLARE s1_char CHAR CHARACTER SET utf8;
        -- max strlen=255 for this function
        DECLARE cv0, cv1 VARBINARY(256);

        SET s1_len = CHAR_LENGTH(s1),
            s2_len = CHAR_LENGTH(s2),
            cv1 = 0x00,
            j = 1,
            i = 1,
            c = 0;

        IF (s1 = s2) THEN
          RETURN (0);
        ELSEIF (s1_len = 0) THEN
          RETURN (s2_len);
        ELSEIF (s2_len = 0) THEN
          RETURN (s1_len);
        END IF;

        WHILE (j  c_temp) THEN
              SET c = c_temp;
            END IF;

            SET c_temp = ORD(SUBSTRING(cv1, j+1, 1)) + 1;
            IF (c > c_temp) THEN
              SET c = c_temp;
            END IF;

            SET cv0 = CONCAT(cv0, CHAR(c)),
                j = j + 1;
          END WHILE;

          SET cv1 = cv0,
              i = i + 1;
        END WHILE;

        RETURN (c);
      END//
    DELIMITER ;

在迁移中:

    public function up () {
        DB::statement(file_get_contents(__DIR__.'/levenshtein.sql'));
    }
    public function down () {
        DB::statement('DROP FUNCTION `LEVENSHTEIN`;');
    }

我尝试了DB::statement，DB::unprepared和DB::raw，但是Laravel迁移会引发错误:

I tried DB::statement, DB::unprepared and DB::raw, but Laravel migrations throw error:

    [Illuminate\Database\QueryException]
      SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in yo
      ur SQL syntax; check the manual that corresponds to your MySQL server version f
      or the right syntax to use near 'DELIMITER //
      CREATE FUNCTION `LEVENSHTEIN`(s1 VARCHAR(255) CHARACTER SET utf8, ' at line 1

当我通过HeidiSQL执行它时，它可以工作，什么问题?

It works when I execute it by HeidiSQL, what is the problem?

推荐答案

我在

I found solution at Creating MYSQL Procedure in Laravel 4 Migrations answered by @peterm.

DELIMITER //中的问题，该问题不是有效的sql语句.这只是一个MySql客户端命令.

Problem in DELIMITER // that is not a valid sql statement. It's just a MySql client command.

这篇关于Laravel迁移原始SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！