前言

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

数据库

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

查询分页

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

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 自带批量更新。官方文档

//需求,已知 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');

分组统计

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

/* 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');

Dorad's Life

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

前言

数据库

查询分页

批量更新

分组统计

总结