在 Laravel 4 迁移中创建 MYSQL 过程 [英] Creating MYSQL Procedure in Laravel 4 Migrations

问题描述

有没有办法在 Laravel 4 迁移中生成存储的 MYSQL 过程?

例如，这是一个存储为字符串的简单过程生成查询(通过 的源代码.您可以使用 PDO exec() DB::connection()->getPdo()->exec() 代替

也就是说，虚拟tags 表的示例迁移可能如下所示

class CreateTagsTable extends Migration {/*** 运行迁移.** @return 无效*/公共函数 up(){Schema::create('tags', function($table){$table->increments('id');$table->string('name')->unique();});$sql = <<<SQL如果存在则删除程序 sp_insert_tag;创建程序 sp_insert_tag(IN _name VARCHAR(32))开始INSERT INTO `tags`(`name`) VALUES(_name);结尾SQL;DB::connection()->getPdo()->exec($sql);}/*** 反转迁移.** @return 无效*/公共函数 down(){$sql = "DROP PROCEDURE IF EXISTS sp_insert_tag";DB::connection()->getPdo()->exec($sql);架构::drop('标签');}}

Is there a way to generate stored MYSQL procedures in a Laravel 4 migration?

For example, here's a simple procedure generation query stored as a string (via a Heredoc)

    $query = <<<SQL
DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test()
BEGIN
    INSERT INTO `test_table`(`name`) VALUES('test');
END$$
DELIMITER ;
SQL;

    DB:statement(DB::RAW($query));

---

When Running this in a migration's up() function I get this error:

解决方案

There are two major problems with your code

1. DELIMITER is not a valid sql statement. It's just a MySql client command. So just don't use it. BTW the error you get tells you exactly that.
2. You can't use DB::statement to execute CREATE PROCEDURE code because it uses prepared statement source code for Connection. You can use PDO exec() DB::connection()->getPdo()->exec() instead

That being said a sample migration for imaginary tags table might look like this

class CreateTagsTable extends Migration {

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('tags', function($table){
            $table->increments('id');
            $table->string('name')->unique();
        });
$sql = <<<SQL
DROP PROCEDURE IF EXISTS sp_insert_tag;
CREATE PROCEDURE sp_insert_tag(IN _name VARCHAR(32))
BEGIN
    INSERT INTO `tags`(`name`) VALUES(_name);
END
SQL;
        DB::connection()->getPdo()->exec($sql);
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $sql = "DROP PROCEDURE IF EXISTS sp_insert_tag";
        DB::connection()->getPdo()->exec($sql);
        Schema::drop('tags');
    }
}

这篇关于在 Laravel 4 迁移中创建 MYSQL 过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！