Laravel 4：如何运行原始 SQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14687993/
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
Laravel 4: how to run a raw SQL?
提问by duality_
I want to rename a table in Laravel 4, but don't know how to do that.
我想在 Laravel 4 中重命名一个表，但不知道该怎么做。
The SQL is alter table photos rename to images. If there is an Eloquent solution, I'd also like to know how to run a raw SQL, cause sometimes there's just no alternative.
SQL 是alter table photos rename to images. 如果有 Eloquent 解决方案，我也想知道如何运行原始 SQL，因为有时别无选择。
回答by Laurence
In the Laravel 4 manual- it talks about doing raw commands like this:
在Laravel 4 手册中- 它谈到执行这样的原始命令：
DB::select(DB::raw('RENAME TABLE photos TO images'));
edit:I just found this in the Laravel 4 documentationwhich is probably better:
编辑：我刚刚在Laravel 4 文档中找到了这个，这可能更好：
DB::statement('drop table users');
Update:In Laravel 4.1 (maybe 4.0 - I'm not sure) - you can also do this for a raw Where query:
更新：在 Laravel 4.1（可能是 4.0 - 我不确定） - 你也可以对原始 Where 查询执行此操作：
$users = User::whereRaw('age > ? and votes = 100', array(25))->get();
Further UpdateIf you are specifically looking to do a table rename - there is a schema command for that - see Mike's answer below for that.
进一步更新如果您特别想进行表重命名 - 有一个模式命令 - 请参阅下面的 Mike 回答。
回答by Mike Branski
Actually, Laravel 4 does have a table rename function in Illuminate/Database/Schema/Builder.php, it's just undocumented at the moment: Schema::rename($from, $to);.
实际上，Laravel 4 在Illuminate/Database/Schema/Builder.php 中确实有一个表重命名功能，目前只是没有记录：Schema::rename($from, $to);.
回答by smitrp
You can also use DB::unpreparedfor ALTER TABLEqueries.
您也可以DB::unprepared用于ALTER TABLE查询。
DB::unpreparedis meant to be used for queries like CREATE TRIGGER. But essentially it executes raw sql queries directly. (without using PDO preparedstatements)
DB::unprepared旨在用于诸如CREATE TRIGGER. 但本质上它直接执行原始 sql 查询。（不使用 PDOprepared语句）
回答by Abelgo
The best way to do this I have found so far it to side step Laravel and execute the query directly using the Pdo object.
到目前为止，我发现最好的方法是绕过 Laravel 并直接使用 Pdo 对象执行查询。
Example
例子
DB::connection()->getPdo()->exec( $sql );
I usually find it faster and more efficient for a one time query to simply open my database query tool and type the query with full syntax checking then execute it directly.
我通常发现一次性查询更快、更有效，只需打开我的数据库查询工具并键入带有完整语法检查的查询，然后直接执行它。
This becomes essential if you have to work with stored procedures or need to use any database functions
如果您必须使用存储过程或需要使用任何数据库函数，这将变得至关重要
Example 2setting created_at to a the value you need it to be and side steeping any carbon funkiness
示例 2将 created_at 设置为您需要的值，并在侧面浸泡任何碳元素
$sql = 'UPDATE my_table SET updated_at = FROM_UNIXTIME(nonce) WHERE id = ' . strval($this->id);
DB::statement($sql);
I found this worked in a controller but not in a migration
我发现这在控制器中有效，但在迁移中无效
回答by Sean the Bean
The accepted way to rename a table in Laravel 4 is to use the Schema builder. So you would want to do:
在 Laravel 4 中重命名表的公认方法是使用 Schema 构建器。所以你会想要做：
Schema::rename('photos', 'images');
From http://laravel.com/docs/4.2/schema#creating-and-dropping-tables
来自http://laravel.com/docs/4.2/schema#creating-and-dropping-tables
If you really want to write out a raw SQL query yourself, you can always do:
如果你真的想自己写出一个原始的 SQL 查询，你总是可以这样做：
DB::statement('alter table photos rename to images');
Note: Laravel's DB class also supports running raw SQL select, insert, update, and deletequeries, like:
注：Laravel的DB类还支持运行原始的SQL select，insert，update，和delete查询，如：
$users = DB::select('select id, name from users');
For more info, see http://laravel.com/docs/4.2/database#running-queries.
有关更多信息，请参阅http://laravel.com/docs/4.2/database#running-queries。
回答by Yevgeniy Afanasyev
This is my simplified example of how to run RAW SELECT, get result and access the values.
这是我如何运行 RAW SELECT、获取结果和访问值的简化示例。
$res = DB::select('
select count(id) as c
from prices p
where p.type in (2,3)
');
if ($res[0]->c > 10)
{
throw new Exception('WOW');
}
If you want only run sql script with no return resutl use this
如果你只想运行 sql 脚本而不返回 resutl 使用这个
DB::statement('ALTER TABLE products MODIFY COLUMN physical tinyint(1) AFTER points;');
Tested in laravel 5.1
在 Laravel 5.1 中测试
回答by sobhagya sahu
Laravel raw sql– Insert query:
Laravel 原始 sql– 插入查询：
lets create a get link to insert data which is accessible through url . so our link name is ‘insertintodb' and inside that function we use db class . db class helps us to interact with database . we us db class static function insert . Inside insert function we will write our PDO query to insert data in database . in below query we will insert ‘ my title ‘ and ‘my content' as data in posts table .
让我们创建一个获取链接以插入可通过 url 访问的数据。所以我们的链接名称是 'insertintodb' 并且在该函数中我们使用 db class 。db 类帮助我们与数据库交互。我们使用 db 类静态函数 insert 。在插入函数中，我们将编写 PDO 查询以在数据库中插入数据。在下面的查询中，我们将插入“我的标题”和“我的内容”作为帖子表中的数据。
put below code in your web.php file inside routes directory :
将以下代码放在路由目录中的 web.php 文件中：
Route::get('/insertintodb',function(){
DB::insert('insert into posts(title,content) values (?,?)',['my title','my content']);
});
Now fire above insert query from browser link below :
现在从下面的浏览器链接触发上面的插入查询：
localhost/yourprojectname/insertintodb
You can see output of above insert query by going into your database table .you will find a record with id 1 .
您可以通过进入数据库表来查看上述插入查询的输出。您将找到 id 为 1 的记录。
Laravel raw sql – Read query :
Laravel 原始 sql – 读取查询：
Now , lets create a get link to read data , which is accessible through url . so our link name is ‘readfromdb'. we us db class static function read . Inside read function we will write our PDO query to read data from database . in below query we will read data of id ‘1' from posts table .
现在，让我们创建一个获取链接来读取数据，该链接可通过 url 访问。所以我们的链接名称是“readfromdb”。我们使用 db 类静态函数读取。在 read 函数中，我们将编写 PDO 查询以从数据库读取数据。在下面的查询中，我们将从帖子表中读取 id '1' 的数据。
put below code in your web.php file inside routes directory :
将以下代码放在路由目录中的 web.php 文件中：
Route::get('/readfromdb',function() {
$result = DB::select('select * from posts where id = ?', [1]);
var_dump($result);
});
now fire above read query from browser link below :
现在从下面的浏览器链接触发上面的读取查询：
localhost/yourprojectname/readfromdb
