在 Laravel 4 迁移中创建 MYSQL 过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18379137/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating MYSQL Procedure in Laravel 4 Migrations
提问by Johannes
Is there a way to generate stored MYSQL procedures in a Laravel 4 migration?
有没有办法在 Laravel 4 迁移中生成存储的 MYSQL 过程？
For example, here's a simple procedure generation query stored as a string (via a Heredoc)
例如，这是一个存储为字符串的简单过程生成查询（通过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:
在迁移的up()函数中运行它时，我收到此错误：
回答by peterm
There are two major problems with your code
您的代码有两个主要问题
DELIMITERis not a valid sql statement. It's just a MySql client command. So just don't use it. BTWthe error you get tells you exactly that.- You can't use
DB::statementto executeCREATE PROCEDUREcode because it uses prepared statement source code forConnection. You can use PDOexec()DB::connection()->getPdo()->exec()instead
DELIMITER不是有效的 sql 语句。这只是一个 MySql 客户端命令。所以只是不要使用它。顺便说一句，你得到的错误正好告诉你这一点。- 您不能用于
DB::statement执行CREATE PROCEDURE代码，因为它使用了准备好的语句源代码Connection。您可以使用PDO来代替exec()DB::connection()->getPdo()->exec()
That being said a sample migration for imaginary tagstable might look like this
也就是说，虚tags表的示例迁移可能如下所示
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');
}
}
回答by Vainglory07
For fellow dev looking for the link in laravel mentioned by @Johannes.
对于在 @Johannes 提到的 laravel 中寻找链接的开发人员。
Any way to create MYSQL Procedures in Migrations?answered by @aheissenberger.
有什么方法可以在迁移中创建 MYSQL 过程？由@aheissenberger 回答。
I use this and it works well for me:
我使用这个，它对我很有效：
public function up() {
DB::unprepared('CREATE PROCEDURE get_highscore() BEGIN SET time_zone = \'Europe/Berlin\'; SET @refscore :=0; SELECT * FROM test; END');
}
public function down() {
DB::unprepared('DROP PROCEDURE IF EXISTS get_highscore');
}
To call a procedure in your code:
要在代码中调用过程：
DB::unprepared('CALL get_highscore()');
if you expect a resulting table:
如果您希望得到一个结果表：
DB::statement('CALL update_highscore()');
if you expect variables:
如果您期望变量：
DB::statement('CALL update_ranking(3,10,@olduser,@newuser)');
$dberg = DB::select('select @olduser as old, @newuser as new');
