ThinkPHP3.2数据库操作

Published on 2016 - 11 - 25

连贯操作

ThinkPHP模型基础类提供的连贯操作方法(也有些框架称之为链式操作),可以有效的提高数据存取的代码清晰度和开发效率,并且支持所有的CURD操作。

使用也比较简单, 假如我们现在要查询一个User表的满足状态为1的前10条记录,并希望按照用户的创建时间排序 ,代码如下:

$User->where('status=1')->order('create_time')->limit(10)->select();

这里的where、order和limit方法就被称之为连贯操作方法,除了select方法必须放到最后一个外(因为select方法并不是连贯操作方法),连贯操作的方法调用顺序没有先后,例如,下面的代码和上面的等效:

$User->order('create_time')->limit(10)->where('status=1')->select();

如果不习惯使用连贯操作的话,还支持直接使用参数进行查询的方式。例如上面的代码可以改写为:

$User->select(array('order'=>'create_time','where'=>'status=1','limit'=>'10'));

使用数组参数方式的话,索引的名称就是连贯操作的方法名称。其实不仅仅是查询方法可以使用连贯操作,包括所有的CURD方法都可以使用,例如:

$User->where('id=1')->field('id,name,email')->find(); 
$User->where('status=1 and id=1')->delete();

连贯操作通常只有一个参数,并且仅在当此查询或者操作有效,完成后会自动清空连贯操作的所有传值(有个别特殊的连贯操作有多个参数,并且会记录当前的传值)。简而言之,连贯操作的结果不会带入以后的查询。

系统支持的连贯操作方法有:

连贯操作 作用 支持的参数类型
where* 用于查询或者更新条件的定义 字符串、数组和对象
table 用于定义要操作的数据表名称 字符串和数组
alias 用于给当前数据表定义别名 字符串
data 用于新增或者更新数据之前的数据对象赋值 数组和对象
field 用于定义要查询的字段(支持字段排除) 字符串和数组
order 用于对结果排序 字符串和数组
limit 用于限制查询结果数量 字符串和数字
page 用于查询分页(内部会转换成limit) 字符串和数字
group 用于对查询的group支持 字符串
having 用于对查询的having支持 字符串
join* 用于对查询的join支持 字符串和数组
union* 用于对查询的union支持 字符串、数组和对象
distinct 用于查询的distinct支持 布尔值
lock 用于数据库的锁机制 布尔值
cache 用于查询缓存 支持多个参数
relation 用于关联查询(需要关联模型支持) 字符串
result 用于返回数据转换 字符串
validate 用于数据自动验证 数组
auto 用于数据自动完成 数组
filter 用于数据过滤 字符串
scope* 用于命名范围 字符串、数组
bind* 用于数据绑定操作 数组或多个参数
token 用于令牌验证 布尔值
comment 用于SQL注释 字符串
index 用于数据集的强制索引(3.2.3新增) 字符串
strict 用于数据入库的严格检测(3.2.3新增) 布尔值

所有的连贯操作都返回当前的模型实例对象(this),其中带*标识的表示支持多次调用。

WHERE

where方法的用法是ThinkPHP查询语言的精髓,也是ThinkPHP ORM的重要组成部分和亮点所在,可以完成包括普通查询、表达式查询、快捷查询、区间查询、组合查询在内的查询操作。where方法的参数支持字符串和数组,虽然也可以使用对象但并不建议。

字符串条件

使用字符串条件直接查询和操作,例如:

$User = M("User"); 
$User->where('type=1 AND status=1')->select(); 

使用字符串条件的时候,建议配合预处理机制,确保更加安全,例如:

$Model->where("id=%d and username='%s' and xx='%f'",array($id,$username,$xx))->select();

或者使用:

$Model->where("id=%d and username='%s' and xx='%f'",$id,$username,$xx)->select();

如果$id变量来自用户提交或者URL地址的话,如果传入的是非数字类型,则会强制格式化为数字格式后进行查询操作。字符串预处理格式类型支持指定数字、字符串等,具体可以参考vsprintf方法的参数说明。

数组条件

数组条件的where用法是ThinkPHP推荐的用法。

普通查询

最简单的数组查询方式如下:

$User = M("User"); 
$map['name'] = 'thinkphp';
$map['status'] = 1;
// 把查询条件传入查询方法
$User->where($map)->select(); 

表达式查询

上面的查询条件仅仅是一个简单的相等判断,可以使用查询表达式支持更多的SQL查询语法,查询表达式的使用格式:

$map['字段1']  = array('表达式','查询条件1');
$map['字段2']  = array('表达式','查询条件2');
$Model->where($map)->select(); // 也支持

表达式不分大小写,支持的查询表达式有下面几种,分别表示的含义是:

表达式 含义
EQ 等于(=)
NEQ 不等于()
GT 大于(>)
EGT 大于等于(>=)
LT 小于(<)
ELT 小于等于(<=)
LIKE 模糊查询
[NOT] BETWEEN (不在)区间查询
[NOT] IN (不在)IN 查询
EXP 表达式查询,支持SQL语法

多次调用

where方法支持多次调用,但字符串条件只能出现一次,例如:

$map['a'] = array('gt',1);
$where['b'] = 1;
$Model->where($map)->where($where)->where('status=1')->select();

多次的数组条件表达式会最终合并,但字符串条件则只支持一次。

TABLE

table方法也属于模型类的连贯操作方法之一,主要用于指定操作的数据表。

用法

一般情况下,操作模型的时候系统能够自动识别当前对应的数据表,所以,使用table方法的情况通常是为了:

  • 切换操作的数据表;
  • 对多表进行操作;

例如:

$Model->table('think_user')->where('status>1')->select();

也可以在table方法中指定数据库,例如:

$Model->table('db_name.think_user')->where('status>1')->select();

table方法指定的数据表需要完整的表名,但可以采用下面的方式简化数据表前缀的传入,例如:

$Model->table('__USER__')->where('status>1')->select();

会自动获取当前模型对应的数据表前缀来生成 think_user 数据表名称。

需要注意的是table方法不会改变数据库的连接,所以你要确保当前连接的用户有权限操作相应的数据库和数据表。 切换数据表后,系统会自动重新获取切换后的数据表的字段缓存信息。

如果需要对多表进行操作,可以这样使用:

$Model->field('user.name,role.title')
->table('think_user user,think_role role')
->limit(10)->select();

为了尽量避免和mysql的关键字冲突,可以建议使用数组方式定义,例如:

$Model->field('user.name,role.title')
->table(array('think_user'=>'user','think_role'=>'role'))
->limit(10)->select();

使用数组方式定义的优势是可以避免因为表名和关键字冲突而出错的情况。

一般情况下,无需调用table方法,默认会自动获取当前模型对应或者定义的数据表。

ALIAS

alias用于设置当前数据表的别名,便于使用其他的连贯操作例如join方法等。

示例:

$Model = M('User');
$Model->alias('a')->join('__DEPT__ b ON b.user_id= a.id')->select();

最终生成的SQL语句类似于:

SELECT * FROM think_user a INNER JOIN think_dept b ON b.user_id= a.id

DATA

data方法也是模型类的连贯操作方法之一,用于设置当前要操作的数据对象的值。

写操作

通常情况下我们都是通过create方法或者赋值的方式生成数据对象,然后写入数据库,例如:

$Model = D('User');
$Model->create();
// 这里略过具体的自动生成和验证判断
$Model->add();

又或者直接对数据对象赋值,例如:

$Model = M('User');
$Model->name = '流年';
$Model->email = 'thinkphp@qq.com';
$Model->add();

那么data方法则是直接生成要操作的数据对象,例如:

$Model = M('User');
$data['name'] = '流年';
$data['email'] = 'thinkphp@qq.com';
$Model->data($data)->add();

注意:如果我们同时使用create方法和data创建数据对象的话,则最后调用的方法有效。

data方法支持数组、对象和字符串,对象方式如下:

$Model = M('User');
$obj = new \stdClass;
$obj->name = '流年';
$obj->email = 'thinkphp@qq.com';
$Model->data($obj)->add();

字符串方式用法如下:

$Model = M('User');
$data = 'name=流年&email=thinkphp@qq.com';
$Model->data($data)->add();

也可以直接在add方法中传入数据对象来新增数据,例如:

$Model = M('User');
$data['name'] = '流年';
$data['email'] = 'thinkphp@qq.com';
$Model->add($data);

但是这种方式data参数只能使用数组。

当然data方法也可以用于更新数据,例如

$Model = M('User');
$data['id'] = 8;
$data['name'] = '流年';
$data['email'] = 'thinkphp@qq.com';
$Model->data($data)->save();

当然我们也可以直接这样用:

$Model = M('User');
$data['id'] = 8;
$data['name'] = '流年';
$data['email'] = 'thinkphp@qq.com';
$Model->save($data);

同样,此时data参数只能传入数组。

在调用save方法更新数据的时候 会自动判断当前的数据对象里面是否有主键值存在,如果有的话会自动作为更新条件。也就是说,下面的用法和上面等效:

$Model = M('User');
$data['name'] = '流年';
$data['email'] = 'thinkphp@qq.com';
$Model->data($data)->where('id=8')->save();

读操作

除了写操作外,data方法还可以用于读取当前的数据对象,例如:

$User = M('User');
$map['name'] = '流年';
$User->where($map)->find();
// 读取当前数据对象
$data = $User->data(); 

FIELD

field方法属于模型的连贯操作方法之一,主要目的是标识要返回或者操作的字段,可以用于查询和写入操作。

用于查询

指定字段

在查询操作中field方法是使用最频繁的。

$Model->field('id,title,content')->select();

这里使用field方法指定了查询的结果集中包含id,title,content三个字段的值。

可以给某个字段设置别名,例如:

$Model->field('id,nickname as name')->select();

使用SQL函数

可以在field方法中直接使用函数,例如:

$Model->field('id,SUM(score)')->select();

除了select方法之外,所有的查询方法,包括find等都可以使用field方法。

使用数组参数

field方法的参数可以支持数组,例如:

$Model->field(array('id','title','content'))->select();

最终执行的SQL和前面用字符串方式是等效的。

数组方式的定义可以为某些字段定义别名,例如:

$Model->field(array('id','nickname'=>'name'))->select();

对于一些更复杂的字段要求,数组的优势则更加明显,例如:

$Model->field(array('id','concat(name,'-',id)'=>'truename','LEFT(title,7)'=>'sub_title'))->select();

获取所有字段

如果有一个表有非常多的字段,需要获取所有的字段(这个也许很简单,因为不调用field方法或者直接使用空的field方法都能做到):

$Model->select();
$Model->field()->select();
$Model->field('*')->select();

上面三个用法是等效的,都相当于执行SQL:

SELECT * FROM table

但是这并不是我说的获取所有字段,我希望显式的调用所有字段(对于对性能要求比较高的系统,这个要求并不过分,起码是一个比较好的习惯),那么OK,仍然很简单,下面的用法可以完成预期的作用:

$Model->field(true)->select();

field(true)的用法会显式的获取数据表的所有字段列表,哪怕你的数据表有100个字段。

字段排除

如果我希望获取排除数据表中的content字段(文本字段的值非常耗内存)之外的所有字段值,我们就可以使用field方法的排除功能,例如下面的方式就可以实现所说的功能:

$Model->field('content',true)->select();

则表示获取除了content之外的所有字段,要排除更多的字段也可以:

$Model->field('user_id,content',true)->select();
//或者用
$Model->field(array('user_id','content'),true)->select();

用于写入

除了查询操作之外,field方法还有一个非常重要的安全功能--字段合法性检测。field方法结合create方法使用就可以完成表单提交的字段合法性检测,如果我们在表单提交的处理方法中使用了:

$Model->field('title,email,content')->create();

即表示表单中的合法字段只有title,email和content字段,无论用户通过什么手段更改或者添加了浏览器的提交字段,都会直接屏蔽。因为,其他是所有字段我们都不希望由用户提交来决定,你可以通过自动完成功能定义额外的字段写入。

同样的,field也可以结合add和save方法,进行字段过滤,例如:

$Model->field('title,email,content')->save($data);

如果data数据中包含有title,email,content之外的字段数据的话,也会过滤掉。

ORDER

order方法属于模型的连贯操作方法之一,用于对操作的结果排序。用法如下:

$Model->where('status=1')->order('id desc')->limit(5)->select();

注意:连贯操作方法没有顺序,可以在select方法调用之前随便改变调用顺序。

支持对多个字段的排序,例如:

$Model->where('status=1')->order('id desc,status')->limit(5)->select();

如果没有指定desc或者asc排序规则的话,默认为asc。

如果你的字段和mysql关键字有冲突,那么建议采用数组方式调用,例如:

$Model->where('status=1')->order(array('order','id'=>'desc'))->limit(5)->select(); 

LIMIT

limit方法也是模型类的连贯操作方法之一,主要用于指定查询和操作的数量,特别在分页查询的时候使用较多。ThinkPHP的limit方法可以兼容所有的数据库驱动类的。

限制结果数量

例如获取满足要求的10个用户,如下调用即可:

$User = M('User');
$User->where('status=1')->field('id,name')->limit(10)->select();

limit方法也可以用于写操作,例如更新满足要求的3条数据:

$User = M('User');
$User->where('score=100')->limit(3)->save(array('level'=>'A'));

分页查询

用于文章分页查询是limit方法比较常用的场合,例如:

$Article = M('Article');
$Article->limit('10,25')->select();

表示查询文章数据,从第10行开始的25条数据(可能还取决于where条件和order排序的影响 这个暂且不提)。

你也可以这样使用,作用是一样的:

$Article = M('Article');
$Article->limit(10,25)->select();

对于大数据表,尽量使用limit限制查询结果,否则会导致很大的内存开销和性能问题。

PAGE

page方法也是模型的连贯操作方法之一,是完全为分页查询而诞生的一个人性化操作方法。

我们在前面已经了解了关于limit方法用于分页查询的情况,而page方法则是更人性化的进行分页查询的方法,例如还是以文章列表分页为例来说,如果使用limit方法,我们要查询第一页和第二页(假设我们每页输出10条数据)写法如下:

$Article = M('Article');
$Article->limit('0,10')->select(); // 查询第一页数据
$Article->limit('10,10')->select(); // 查询第二页数据

虽然利用扩展类库中的分页类Page可以自动计算出每个分页的limit参数,但是如果要自己写就比较费力了,如果用page方法来写则简单多了,例如:

$Article = M('Article');
$Article->page('1,10')->select(); // 查询第一页数据
$Article->page('2,10')->select(); // 查询第二页数据

显而易见的是,使用page方法你不需要计算每个分页数据的起始位置,page方法内部会自动计算。

和limit方法一样,page方法也支持2个参数的写法,例如:

$Article->page(1,10)->select();
// 和下面的用法等效
$Article->page('1,10')->select();

page方法还可以和limit方法配合使用,例如:

$Article->limit(25)->page(3)->select();

当page方法只有一个值传入的时候,表示第几页,而limit方法则用于设置每页显示的数量,也就是说上面的写法等同于:

$Article->page('3,25')->select(); 

GROUP

GROUP方法也是连贯操作方法之一,通常用于结合合计函数,根据一个或多个列对结果集进行分组 。

group方法只有一个参数,并且只能使用字符串。

例如,我们都查询结果按照用户id进行分组统计:

$this->field('user_id,username,max(score)')->group('user_id')->select();

生成的SQL语句是:

SELECT user_id,username,max(score) FROM think_score GROUP BY user_id

也支持对多个字段进行分组,例如:

$this->field('user_id,test_time,username,max(score)')->group('user_id,test_time')->select();

生成的SQL语句是:

SELECT user_id,test_time,username,max(score) FROM think_score GROUP BY user_id,test_time

HAVING

HAVING方法也是连贯操作之一,用于配合group方法完成从分组的结果中筛选(通常是聚合条件)数据。

having方法只有一个参数,并且只能使用字符串,例如:

$this->field('username,max(score)')->group('user_id')->having('count(test_time)>3')->select(); 

生成的SQL语句是:

SELECT username,max(score) FROM think_score GROUP BY user_id HAVING count(test_time)>3

JOIN

JOIN方法也是连贯操作方法之一,用于根据两个或多个表中的列之间的关系,从这些表中查询数据。

join通常有下面几种类型,不同类型的join操作会影响返回的数据结果。

  • INNER JOIN: 等同于 JOIN(默认的JOIN类型),如果表中有至少一个匹配,则返回行
  • LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
  • RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
  • FULL JOIN: 只要其中一个表中存在匹配,就返回行

join方法可以支持以上四种类型,例如:

$Model = M('Artist');
$Model
->join('think_work ON think_artist.id = think_work.artist_id')
->join('think_card ON think_artist.card_id = think_card.id')
->select();

join方法支持多次调用,但指定的数据表必须是全称,但我们可以这样来定义:

$Model
->join('__WORK__ ON __ARTIST__.id = __WORK__.artist_id')
->join('__CARD__ ON __ARTIST__.card_id = __CARD__.id')
->select();

WORKCARD在最终解析的时候会转换为 think_work和 think_card。

默认采用INNER JOIN 方式,如果需要用其他的JOIN方式,可以改成

$Model->join('RIGHT JOIN __WORK__ ON __ARTIST__.id = __WORK__.artist_id')->select();

或者使用:

$Model->join('__WORK__ ON __ARTIST__.id = __WORK__.artist_id','RIGHT')->select();

join方法的第二个参数支持的类型包括:INNER LEFT RIGHT FULL。

如果join方法的参数用数组的话,只能使用一次join方法,并且不能和字符串方式混合使用。 例如:

join(array(' __WORK__ ON __ARTIST__.id = __WORK__.artist_id','__CARD__ ON __ARTIST__.card_id = __CARD__.id'))

使用数组方式的情况下,第二个参数无效。因此必须在字符串中显式定义join类型,例如:

join(array(' LEFT JOIN __WORK__ ON __ARTIST__.id = __WORK__.artist_id','RIGHT JOIN __CARD__ ON __ARTIST__.card_id = __CARD__.id'))

UNION

UNION操作用于合并两个或多个 SELECT 语句的结果集。

使用示例:

$Model->field('name')
      ->table('think_user_0')
      ->union('SELECT name FROM think_user_1')
      ->union('SELECT name FROM think_user_2')
      ->select();

数组用法:

$Model->field('name')
      ->table('think_user_0')
      ->union(array('field'=>'name','table'=>'think_user_1'))
      ->union(array('field'=>'name','table'=>'think_user_2'))
      ->select();

或者

$Model->field('name')
      ->table('think_user_0')
      ->union(array('SELECT name FROM think_user_1','SELECT name FROM think_user_2'))
      ->select();

支持UNION ALL 操作,例如:

$Model->field('name')
      ->table('think_user_0')
      ->union('SELECT name FROM think_user_1',true)
      ->union('SELECT name FROM think_user_2',true)
      ->select();

或者

$Model->field('name')
      ->table('think_user_0')
      ->union(array('SELECT name FROM think_user_1','SELECT name FROM think_user_2'),true)
      ->select();

每个union方法相当于一个独立的SELECT语句。

注意:UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。

DISTINCT

DISTINCT 方法用于返回唯一不同的值 。

例如:

$Model->distinct(true)->field('name')->select();

生成的SQL语句是: SELECT DISTINCT name FROM think_user

distinct方法的参数是一个布尔值

LOCK

Lock方法是用于数据库的锁机制,如果在查询或者执行操作的时候使用:

lock(true);

就会自动在生成的SQL语句最后加上 FOR UPDATE或者FOR UPDATE NOWAIT(Oracle数据库)。

CACHE

cache方法用于查询缓存操作,也是连贯操作方法之一。

cache可以用于select、find和getField方法,以及其衍生方法,使用cache方法后,在缓存有效期之内不会再次进行数据库查询操作,而是直接获取缓存中的数据,关于数据缓存的类型和设置可以参考缓存部分。

下面举例说明,例如,我们对find方法使用cache方法如下:

$Model = M('User');
$Model->where('id=5')->cache(true)->find();

第一次查询结果会被缓存,第二次查询相同的数据的时候就会直接返回缓存中的内容,而不需要再次进行数据库查询操作。

默认情况下, 缓存有效期和缓存类型是由DATA_CACHE_TIME和DATA_CACHE_TYPE配置参数决定的,但cache方法可以单独指定,例如:

$Model = M('User');
$Model->cache(true,60,'xcache')->find();

表示对查询结果使用xcache缓存,缓存有效期60秒。

cache方法可以指定缓存标识:

$Model = M('User');
$Model->cache('key',60)->find();

指定查询缓存的标识可以使得查询缓存更有效率。

这样,在外部就可以通过S方法直接获取查询缓存的数据,例如:

$Model = M('User');
$result = $Model->cache('key',60)->find();
$data = S('key');

COMMENT

COMMENT方法 用于在生成的SQL语句中添加注释内容,例如:

$this->comment('查询考试前十名分数')
->field('username,score')
->limit(10)
->order('score desc')
->select();

最终生成的SQL语句是:

SELECT username,score FROM think_score ORDER BY score desc LIMIT 10 /* 查询考试前十名分数 */

FETCHSQL

fetchSql用于直接返回SQL而不是执行查询,适用于任何的CURD操作方法。 例如:

$result = M('User')->fetchSql(true)->find(1);

输出result结果为:

SELECT * FROM think_user where id = 1

TOKEN

token方法可用于临时关闭令牌验证,例如:

$model->token(false)->create();

即可在提交表单的时候临时关闭令牌验证(即使开启了TOKEN_ON参数)。

STRICT

strict为3.2.3新增连贯操作,用于设置数据写入和查询是否严格检查是否存在字段。默认情况下不合法数据字段自动删除,如果设置了严格检查则会抛出异常。 例如:

$model->strict(true)->add($data);

INDEX

index方法用于数据集的强制索引操作,例如:

$Model->index('user')->select();

对查询强制使用user索引,user必须是数据表实际创建的索引名称。

参考文档