mysql函数用法

1、修改ar_slider表中的img字段，统一在第二位插入字母"A"，若img字段中有中文也能在第二位中文后正常插入毫不影响

update ar_slider set img=CONCAT(left(img,2),'A',right(img,LENGTH(img)-2));

2、查找用户的两个常去的店，按照常去的次数排序，若次数相同，则按照最近一次到店时间排序

$uid = 128;
$field = "sh.id,sh.name,sh.address,sh.long,sh.lat,sh.is_default,count(su.shop_id) as count,sr.last_subscribe_time";
$res = Db::name('subscribe su')->join('shop sh','su.shop_id = sh.id')->leftjoin('shopuser sr','sr.user_id = su.user_id and sr.shop_id = sh.id')->whereNotNull('su.bake_start_time')->where('su.user_id',$uid)->field($field)->group('su.shop_id')->order('count desc,sr.last_subscribe_time desc')->limit(0,2)->select();

3、groupconcat的用法，可以把字段以逗号方式列出来

$res = Db::name('subscribe')->whereNotNull('bake_start_time')->where('user_id',$uid)->whereIn('shop_id',$shopids)->field('shop_id,bake_start_time,GROUP_CONCAT(shop_id) as all_obj_id')->group('shop_id')->order('bake_start_time asc')->select();

4、orderby后再group

$a = Db::name('healthbloodpressure')->field('id,DATE(create_time) as date,group_concat(id order by `create_time` desc) as id,group_concat(relax_pressure order by `create_time` desc) as relax_pressure, group_concat(shrink_pressure order by `create_time` desc) as shrink_pressure')->group('date')->order('create_time asc')->select();
foreach ($a as $k => $v) {
    $id = explode(',',$v['id']);
    $relax_pressure = explode(',',$v['relax_pressure']);
    $shrink_pressure = explode(',',$v['shrink_pressure']);
    $a[$k]['id'] = $id[0];
    $a[$k]['relax_pressure'] = $relax_pressure[0];
    $a[$k]['shrink_pressure'] = $shrink_pressure[0];
}
// FROM_UNIXTIME
print_dump($a);die;

5、时间戳转换为日期，以及时分秒日期保留到日，使用的是FROM_UNIXTIME和DATE函数（注：record_time的格式为时间戳秒）

$re1 = Db::name('healthbloodsugar')->where($where)->field('id,FROM_UNIXTIME(record_time,"%Y-%m-%d") as record_date,DATE(create_time) as date,group_concat(id order by `create_time` desc) as id,group_concat(sugar order by `create_time` desc) as sugar')->group('date')->order('create_time asc')->select();

6、以上的第5种方法中，若record_time的时间戳在1970年以前，record_time为负数时，会有bug，因此可以改进为

$re1 = Db::name('healthbloodsugar')->where($where)->field('id,DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), INTERVAL record_time SECOND),"%Y-%m-%d") as date,group_concat(id order by `record_time` desc) as id,group_concat(sugar order by `record_time` desc) as sugar')->group('date')->order('record_time asc')->select();