Laravel—数据库操作
数据库操作
Laravel支持的数据库系统:
- MySQL
- Postgres
- SQLite
- SQL Server
配置
应用的数据库配置位于config/database.php
(但数据库用户及密码等敏感信息位于.env
文件)
<!-- 位于.env文件中关于数据库的配置 -->
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret
config/database.php
文件可以根据需要配置要使用的数据库,env函数读取.env配置,第二个参数表示读取不到则采用默认的方式
<!-- 位于database.php中关于MySQL数据库的配置 -->
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
运行原生的SQL查询
DB
门面为每种操作提供了相应的方法:select
,update
,insert
,delete
和statement
使用之前要先引用DB类
//要先引入!!!
use Illuminate\Support\Facades\DB;
<!-- 查询语句 -->
public function dbSelect()
{
//该方法以数组的形式返回结果集
//?表示通配符,这样做可以防止SQL注入攻击
$res = DB::select('select * from goods where id = ?', [20]);
//还可以使用命名绑定的方式执行查询
$res = DB::select('select * from goods where id = :id',['id'=>1]);
}
<!-- 插入语句 -->
public function dbInsert()
{
$res = DB::insert('insert into goods (id,names) values (?,?)',[1,'xiaoming']);
}
<!-- 更新语句 -->
public function dbUpdate()
{
//该方法返回更新语句影响的行数
$res = DB::update('update goods set name = ? where id = ?',['xiaoming',2]);
}
<!-- 删除语句 -->
public function dbDelete()
{
//该方法返回删除的行数
$res = DB::delete('delete from goods where id < ?',[3]);
}
运行一个通用语句DB
门面的statement
方法可以用于新增表、修改表、删除表等操作
DB::statement(‘drop table goods’);
事务操作
事务操作指在数据库中运行一系列的操作,只有当全部操作都成功时才生效,否则就自动还原为原来的状态
手动使用事务
使用DB
门面的beginTransaction
开始一个事务,rollBack
表示出错时回滚事务,commit
用于提交事务
//开始事务
DB::beginTransaction();
//数据库执行语句
$res = DB::update("update user set account = account - 2000 where id = 1000");
$res2 = DB::update('update user set account = account + 2000 where id = 2');
if($res && $res2) {
//事务提交
DB::commit();
}
else {
//回滚事务
DB::rollback();
}
自动使用事务
DB
门面的transaction
方法能够自动在一个数据库事务中运行一连串操作,不需要手动回滚或提交
DB::transaction(function(){
//数据库操作的各种函数
...
});
处理死锁
数据库死锁是指有两个或者两个以上的数据库操作相互依赖,一方需要等待另一方退出才能获取资源,但是没有一方提前退出,就会造成死锁transaction
方法接受一个可选参数作为第二个参数,用于定义死锁发生时事务的最大重试次数,如果茶尝试次数超过指定值,就抛出异常:
DB::transaction(function(){
//数据库操作的各种函数
},5);//指定次数在函数之后
操作多个数据库(读写分离)
配置方法
当需要操作多个数据库时,可以在config/database.php
配置中多添加三个数组read
、write
、sticky
(可选),read
和write
两个键对应值都有一个包含单个键host的数组,分别表示读链接和写链接,读/写链接的其余配置共用mysql主数组配置
- 读链接可以以数组形式配置多个
- 目前Laravel只支持的单个写链接
- sticky项用于在当前请求生命周期内允许立即读取写入数据库的记录,使用写链接读取
'mysql' => [
'read' => [
'host' => ['193.168.1.1','193.168.1.2']
],
'write' => [
'host' => ['193.168.1.3']
],
'sticky' => true,
'driver' => 'mysql',
// 'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => true,
'engine' => null,
],
使用不同数据库连接
DB
门面上的connection
方法可以访问不同的链接,参数为配置文件中对应的某个链接的名字,有几种不同的使用方法:
$users = DB::connection(‘read’)->select(…);
$users = DB::connection(‘mysql::read’)->select(…);
查询构建器
Laravel提供的用于创建和执行数据库查询的方便流接口,使用DB
门面的table
方法,参数为表名
查询
查询所有行
get
方法获取表中所有的记录
$users = DB::table(‘users’)->get();
查询单条
first
方法从数据表中获取单行数据
$users = DB::table(‘users’)->where(‘name’,’=’,’xiaoming’)->first()
value
方法从结果中获取某一字段的单个值
$users = DB::table(‘users’)->where(‘id’,’=’,’1’)->value(‘name’)
查询一列
pluck
方法获取指定某个列的所有值
$users = DB::table(‘users’)->pluck(‘name’)
可以为pluck指定第二个可选参数作为列值的自定义键,键名必须是该表其它字段名,否则会报错
//无参数
$title = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
echo $title;
}
//有参数
$roles = DB::table('roles')->pluck('title','name');
foreach ($roles as $name => $title)
{
echo $title;
}
组块结果集
chunk
方法用于处理当数据库记录很多时,一次获取结果集的一小块
//把全部uses表数据分割成一次处理100条记录的小组块
DB::table('users')->orderBy('id')->chunk(100,function($users){
foreach ($users as $user)
});
指定查询子句
select
方法可以为查询自定义select子句
$users = DB::table(‘users’)->select(‘name’,’email as user_email’)->get();
distinct
方法允许强制查询返回不重复的结果集
$users = DB::table(‘users)->distinct()->get();
addSelect
方法能够在已有的查询子句的基础上添加一个查询字段
$query = DB::table(‘users’)->select(‘name’);
$users = $query->addSelect(‘age’)->get();
原生表达式
有时候在查询中需要使用到原生表达式,DB::raw
方法或者selectRaw
方法可以创建原生表达式
//第一种方法
$users = DB::table('users')
->select(DB::raw('count(*) as user_count,stasus'))
->where('status','<>',1)
->groupBy('status')
->get();
//第二种方法
$orders = DB::table('orders')
->selectRaw('price * ? as price_with_tax', [1.0825])
->get();
whereRaw
和orWhereRaw
方法可用于注入原生where
子句到查询,这两个方法接收一个可选的绑定数组作为第二个参数:
$orders = DB::table('orders')
->whereRaw('price > IF(state = "TX", ?, 100)', [200])
->get();
havingRaw
和orHavingRaw
方法可用于设置原生字符串作为having
子句的值:
$orders = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > 2500')
->get();
orderByRaw
方法可用于设置原生字符串作为order by
子句的值:
$orders = DB::table('orders')
->orderByRaw('updated_at - created_at DESC')
->get();
插入
insert
方法用于插入记录到数据表,接受数组形式的字段名和字段值作为参数进行插入操作
DB::table('users')->insert([
['name' => 'xiaoming', 'years' => 8],
['name' =>'xzm', 'years' => 6]
]);
如果数据表带有自增ID,可以使用insertGetId
方法来插入并记录返回的ID值
$id = DB::table('users')->insetGetId([
['name' => 'xiaoming', 'years' => 8]
]);
更新
update
方法用于更新已有记录
DB::table('users')
-> where('id','=',1)
-> update(['name' => 'xioaming']);
删除
delete
方法用于删除记录
DB::table('users')
->where(['name'=>'xiaoming'])
->delete();
truncate
方法可以用于清除整张表
DB::table(‘users’)->truncate();
自增与自减
increment
和decrement
方法用于为给定字段递增或递减,第二个可选参数用于控制递增或递减的量
DB::table('users')->increment('point');
DB::teble('users')->increment('point',5);
DB::table('users')->decrement('point');
DB::teble('users')->decrement('point',5);
也可以在操作过程中指定更新的字段:
DB::table(‘users’)->increment(‘point’,1,[‘name’ => ‘xiaoming’]);
连贯操作
条件
where
where
方法需要传递三个参数,第一个参数是列名,第二个参数是任意一个数据库系统支持的操作符(默认是=),第三个参数是该列要比较的值。
还可以传递条件数组到where
函数
$users = DB::table('users')->where
([
['status','=','1'],
['subscribed','<>','1'],
])->get();
orWhere
相当于或,即只要有一个条件满足即可
$users = DB::table('users')
->where('votes', '>', 100)
->orWhere('name', 'John')
->get();
whereBetween
,whereNotBetween
方法验证列值是否在给定值之间:
$users = DB::table('users')
->whereBetween('votes', [1, 100])->get();
$users = DB::table('users')
->whereNotBetween('votes', [1, 100])
->get();
whereIn
,whereNotIn
方法验证给定列的值是否在给定数组中:
$users = DB::table('users')
->whereIn('id', [1, 2, 3])
->get();
$users = DB::table('users')
->whereNotIn('id', [1, 2, 3])
->get();
其它类型:
whereNull
,whereNotNull
方法验证给定列的值是否为 NULLwhereDate
方法用于比较字段值和日期whereMonth
方法用于比较字段值和一年中的指定月份whereDay
方法用于比较字段值和一月中的指定日期whereYear
方法用于比较字段值和指定年whereTime
方法用于比较字段值和指定时间whereColumn
方法用于验证两个字段是否相等:
$users = DB::table('users')
->whereColumn('first_name', 'last_name')
->get();
还可以传递一个比较运算符到该方法:
$users = DB::table('users')
->whereColumn('updated_at', '>', 'created_at')
->get();
还可以传递多条件数组到 whereColumn 方法,这些条件通过 and 操作符进行连接:
$users = DB::table('users')
->whereColumn([
['first_name', '=', 'last_name'],
['updated_at', '>', 'created_at']
])->get();
whereExists
方法允许编写where exists
SQL语句,接受一个函数作为参数,该函数获取一个查询构建器实例
//其中$query=DB::table('orders')
DB::table('users')
->whereExists(function($query){
$query ->select(DB::raw(1))
->from('orders')
->whereRaw('orders.user_id = users.id');
})
->get();
//等价于以下SQL语句:
select * from users
where exists (
select 1 from orders where orders.user_id = users.id
)
when
when
方法只有在第一个参数为true的时候才执行给定闭包,如果第一个参数为false,则闭包不执行。
可以传递另一个闭包作为when
方法的第三个参数,该闭包会在第一个参数为 false 的情况下执行
$sortBy = null;
//闭包的基本语法,use表示引入外部变量
$users = DB::table('users')
->when($sortBy, function ($query) use ($sortBy) {
return $query->orderBy($sortBy);
}, function ($query) {
return $query->orderBy('name');
})
->get();
排序
orderBy
方法对给定结果集进行排序,第一个参数为排序字段,第二个是排序法方向,asc
或desc
$users = DB::table('users')
->orderBy('name','desc')
->get();
groupBy/having
方法对查询结果进行分组
//可以将多个参数作为数组传递给groupBy方法
$users = DB::table('users')
->groupBy('account_id','first_name')
->having('account_id','>','100')
->get();
latest/oldest
方法通过日期对结果进行排序,默认情况下根据created_at
进行排序,也可以把字段名作为参数传入
$user = DB::table('users')
->latest()
->first();
inRandomOrder
方法可用于对查询结果集进行随机排序,比如,你可以用该方法获取一个随机用户:
$randomUser = DB::table('users')
->inRandomOrder()
->first();
分页
skip
和take
方法用于限定查询跳过的数目和返回的数目,一般用于分页,还可以用limit
和offset
方法:
$user = DB::table('users')->skip(10)->take(5)->get();
$user = DB::table('users')->offset(10)->limit(5)->get();
连接表
关于SQL的几种连接类型:
join
方法为内连接,第一个参数为连接的表名,其他的参数为连接指定的列约束
$users =DB::table('users')
->join('contacts','users.id','=','contacts.user_id')
->join('orders','users.id','=','orders.user_id')
->select('users.*','contacts.phone','orders.price')
->get();
leftJoin
为左连接方法
“交叉连接”可以使用crossJoin
方法,传递想要交叉连接的表名到该方法即可。交叉连接在第一张表和被连接表之间生成一个笛卡尔积:
$users = DB::table('sizes')
->crossJoin('colours')
->get();
联合(Union)
union
方法将两个查询联合
$first = DB::table('users')
->whereNull('first_name');
$users = DB::table('users')
->whereNull('last_name')
->union($first)
->get();
计算(聚合函数)
查询构建器还提供了多个聚合方法,如count
, max
, min
, avg
和 sum
,可以在构造查询之后调用这些方法:
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
还可以联合其它查询子句和聚合函数来构建查询:
$price = DB::table('orders')
->where('finalized', 1)
->avg('price');
除了通过 count 方法来判断匹配查询条件的结果是否存在外,还可以使用 exists 或 doesntExist 方法:
return DB::table('orders')->where('finalized', 1)->exists();
return DB::table('orders')->where('finalized', 1)->doesntExist();
sql语句记录(监听查询事件)
获取应用中每次 SQL 语句的执行,可以使用listen
方法,该方法对查询日志和调试非常有用,可以在服务提供者中注册查询监听器:
<?php
namespace App\Providers;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
DB::listen(function ($query) {
$query->sql
$query->bindings
$query->time
});
}
/**
* Register the service provider.
*
* @return void
*/
public function register()
{
//
}
}
悲观锁 & 乐观锁
悲观锁(Pessimistic Lock),顾名思义,就是很悲观,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁、表锁、读锁、写锁等,都是在做操作之前先上锁。
乐观锁(Optimistic Lock),顾名思义,就是很乐观,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号等机制实现。乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库如果提供类似于 write_condition 机制的其实都是提供的乐观锁。
悲观锁和乐观锁在 Laravel 中的使用:
悲观锁使用
Laravel 查询构建器提供了一些方法在select
语句中实现“悲观锁”。可以在查询中使用sharedLock
方法从而在运行语句时带一把”共享锁“。共享锁可以避免被选择的行被修改直到事务提交:
DB::table(‘users’)->where(‘votes’, ‘>’, 100)->sharedLock()->get();
上面这个查询等价于下面这条 SQL 语句:
select * from
users
wherevotes
> ‘100’ lock in share mode
此外还可以使用lockForUpdate
方法。“for update”锁避免选择行被其它共享锁修改或删除:
DB::table(‘users’)->where(‘votes’, ‘>’, 100)->lockForUpdate()->get();
上面这个查询等价于下面这条 SQL 语句:
select * from
users
wherevotes
> ‘100’ for update
for update
与lock in share mode
都是用于确保被选中的记录值不能被其它事务更新(上锁),两者的区别在于lock in share mode
不会阻塞其它事务读取被锁定行记录的值,而for update
会阻塞其他锁定性读对锁定行的读取(非锁定性读仍然可以读取这些记录,lock in share mode 和for update 都是锁定性读)。
举个计数器的例子:在一条语句中读取一个值,然后在另一条语句中更新这个值。使用 lock in share mode 的话可以允许两个事务读取相同的初始化值,所以执行两个事务之后最终计数器的值+1;而如果使用 for update 的话,会锁定第二个事务对记录值的读取直到第一个事务执行完成,这样计数器的最终结果就是+2了。
乐观锁使用
乐观锁,大多是基于数据版本 ( Version )记录机制实现。何谓数据版本?即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。
读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据