CI框架常用数据库操作技巧

前言

近期在CI框架下开发了多个项目(准确说是接盘哈哈),在增改删查中也学习了不少的小技能~
留下以后备查~

数据库

增改删查作为最基本的数据库操作,写起来代码冗余。
等我琢磨一下看能不能封装一层 Model ,减去这些冗余的操作。

查询分页

在对大量数据进行查询时,经常要用到分页查询操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
public function get($param1 = null, $param2 = null, $param3 = null, $order_by = null, $direction = 'DESC', $page_size = 30, $page_index = 1){
//表名
$table_name='database_table_1';
/*
* 筛选条件,可根据需求进行调整
*/
if ($param1) {
$this->db->where('param1', $param1);
}
if ($param2) {
$this->db->where('param2', $param2);
}
if ($param3) {
$this->db->like('param3', $param3);
}
//排序
if ($order_by) {
if (!$this->db->field_exists($order_by,$table_name)) {
//字段不存在
$this->msg = '该筛选字段不存在';
$this->code = 10022;
return false;
} else {
$this->db->order_by($order_by, $direction == null ? 'DESC' : $direction);
}
}
//统计总条数
$result_nums = $this->db->count_all_results($table_name,false);
$page_size = intval($page_size);
//分页
$page_index = intval($page_index);
$limit = 0;
$offset = 30;
if ($page_index >= 1) {
$offset = ($page_index - 1) * $page_size;
$limit = $page_size;
}
$this->db->limit($limit, $offset);
$query = $this->db->get();
$result = $query->result_array();
//重组返回结果
$result_new = array();
if ($result) {
$this->msg = '查到相关记录';
$this->code = 0;
$result_new['page_index'] = $page_index;
$result_new['page_size'] = $page_size;
$result_new['count'] = $result_nums;
$result_new['records'] = $result;
} else {
$this->msg = '未查到相关记录!';
$this->code = 10023;
return false;
}
return $result_new;
}

只需要稍微修改 $table_name 以及相关参数,就能完成筛选、排序和分页等操作。

批量更新

当有大部分数据需要进行同步时,考虑到效率问题以及相关的操作复杂度。
最初我用的自己构造的sql语句进行操作,但效率很低,而且很繁琐。
原sql语句:

1
SET name= CASE mobile WHEN '16688889999' THEN '小明' WHEN '19966665555' THEN '小红'...END

但是效率低、构造困难。后发现 CI 自带批量更新。官方文档

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//需求,已知 mobile,从其他接口查询到 name 和 department,需要同步到本地。
$data=array(
array(
'name'='小明',
'mobile'='16688889999',
'department'=>'信息部'
),
array(
'name'='小红',
'mobile'='19966665555',
'department'=>'体育部'
),
array(
'name'='小张',
'mobile'='16536556986',
'department'=>'科技部'
)
);
//以 mobile 为条件,当 mobile 与原表中的某行的 mobile 字段数值对应时,更新 name,department 字段。
$re=$this->db->update_batch('database_table_1',$data,'mobile');

分组统计

当需要统计分析每小时/每天/每月/每年的记录时,时常会用到分组统计的功能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/* 24h内,按小时统计 */
$this->db->select('DATE_FORMAT(s.insert_datetime,\'%Y-%m-%d\') as date,HOUR(s.insert_datetime) as hour,s.status,count(1) as total');
$this->db->where('TIMESTAMPDIFF(HOUR,DATE_FORMAT(s.insert_datetime,\'%Y-%m-%d %H:%m:%s\'),CURTIME())<=24');
$this->db->group_by('s.status,DATE_FORMAT(s.insert_datetime,\'%Y-%m-%d %H\')');
$this->db->order_by('date','ASC');
$result=$this->db->get()
->result_array();
/*
* 结果后处理,显示 count 为0
*/
$datetime_now = new DateTime();
$datetime_old = new DateTime();
date_sub($datetime_old, date_interval_create_from_date_string("23 hours"));
for (; $datetime_old->getTimestamp() <= $datetime_now->getTimestamp(); date_add($datetime_old, date_interval_create_from_date_string("1 hours"))) {
$status = array('发送成功', '发送失败');
foreach ($status as $s) {
# 筛选是否存在
$flag = 0;
if ($result) {
foreach ($result as $key => $row) {
if ($row['date'] == $datetime_old->format('Y-m-d') AND $row['hour'] == $datetime_old->format('H') AND $row['status'] == $s) {
$flag = 1;
}
}
}
//若不存在,则插入
if (!$flag) {
array_push($result, array(
'date' => $datetime_old->format('Y-m-d'),
'hour' => strval(intval($datetime_old->format('H'))),
'status' => $s,
'total' => 0
));
}
}
}
$result = arraySequence($result, 'date', 'SORT_ASC');

总结

都是一些经常用到的东西,记录一下,以后备查~