PHP & MySQL 「数据关联一对一」的最佳实践

php框架

浏览数:57

2019-3-31

前言

在开发过程中,通常会遇到很多 一对一 数据的处理情况。而很多时候我们会要取到的是一个列表,然后列表的单条记录的对应另外一张表,来实现业务。比如下面的商品信息商品详情 两个表,这里为了演示只是使用了基础字段,实际开发中可能会复杂的多,下方演示代码中数据库连接使用 PDO 进行处理。

表结构

goods

类型 注释
id int(11) 自动增量 主键ID
title varchar(100) 商品名称
price decimal(10,2) 商品价格
cover varchar(100) 商品封面

goods_detail

类型 注释
id int(11) 自动增量 主键ID
goods_id int(11) 商品ID
content varchar(5000) 商品图文介绍

初级

坦言,无论是在公司,还是在一些开源项目上,我都看到过如下的代码。

$query = $db->query('select * from goods');
$result = $query->fetchAll();
// 方案一
foreach($result as $key => $item){
    $query = $db->query('select * from goods_detail where goods_id=' . $item['id']);
    $result[$key]['goods_detail'] = $query->fetch();
}
var_dump($result);
// 方案二
foreach($result as &$item){
    $query = $db->query('select * from goods_detail where goods_id=' . $item['id']);
    $item['goods_detail'] = $query->fetch();
}
unset($item);
var_dump($result);
// 方案三
$result = array_map(function($item){
    $query = $db->query('select * from goods_detail where goods_id=' . $item['id']);
    $item['goods_detail'] = $query->fetch();
    return $item;
},$result);
var_dump($result);

这是最暴力的方式,也是立杆见影,而且方案一看起来代码貌似还很繁琐,不是吗?如果学过 引用这一节的朋友,应该知道第二种用法,直接用引用去操作源数据,当然最后最好别忘了 unset 掉 $item,除了第二种,我们还可以用第三种方式,使用 array_map,诚然,这和第二种方式没什么区别,但是这其中有着一个非常大的问题:数据库查询的N+1 。从执行中我们就可以看到,除了查询列表的一条 SQL 外,每查询一条记录对应的都需要执行一条 SQL ,导致了额外的查询,想想一下如果查询没有 limit 限制。会是什么样子的情况?

进阶

看到这里,有人可能会想到了另一种方案来,先查询列表,然后取出列表里面的 goods_id 之后使用 in 查询,然后再循环分配给列表,看代码。

$goods_id = array_column($result,'id');
$goods_id_str = implode(',',$goods_id);
$query = $db->query(sprintf('select * from goods_detail where goods_id in (%s)',$goods_id_str));
$goods_detail_list = $query->fetchAll();
foreach($result as &$item){
    $item['goods_detail'] = array_first($goods_detail_list,function($item1){
        return $item['id'] == $item1['goods_id'];
    });
}
unset($item);
var_dump($result);
/**
 * 来自 Laravel
 */
if (!function_exists('value')) {
    function value($value)
    {
        return $value instanceof Closure ? $value() : $value;
    }
}
/**
 * 来自 Laravel
 */
if (!function_exists('array_first')) {
    /**
     * @param               $array
     * @param callable|null $callback
     * @param null          $default
     * @return mixed
     */
    function array_first($array, callable $callback = null, $default = null)
    {
        if (is_null($callback)) {
            if (empty($array)) {
                return value($default);
            }

            foreach ($array as $item) {
                return $item;
            }
        }

        foreach ($array as $key => $value) {
            if (call_user_func($callback, $value, $key)) {
                return $value;
            }
        }

        return value($default);
    }
}

在这个代码中,我们完美避开了 N+1 的窘境,使用了in查询,然后遍历数组,再使用 array_first 方法来查找后传递给 goods_detail 索引,虽然这样的效率相比第一次的要高了很多,但是并不完美,接下来来看最后一种方案。关于 array_first 可以看我的另一篇文章 『PHP 多维数组中的 array_find』。

最佳实践

$goods_detail_list_by_keys = array_column($goods_detail_list,null,'goods_id');
foreach($result as &$item){
    $item['goods_detail'] = array_key_exists($goods_detail_list_by_keys,$item['id']) ? $goods_detail_list_by_keys[$item['id']] : null ;
    // php 7.1+
    // $item['goods_detail'] = $goods_detail_list_by_keys[$item['id']] ?? null;
}
unset($item);
var_dump($result);

这一次,我们用到了其他两个函数。array_columnarray_key_exists,接下里一一道来,其实在array_column的官方手册中的我们就能 Example #2 中就介绍了我们想要的方法。套用在这里就是重置goods_detail_list 里面元素的 key 为 单个元素下的 goods_id。在后面我们直接用 array_key_exists 判断是否存在,然后做出相应的处理就好了。在这里我们还可以做另外一个操作,那就是默认值,因为有时候,数据有可能会因对不上,如果查出来直接返回给前端,前端没有预料到这种情况没有做容错处理就会导致前端页面崩溃,下面来改写一下代码

// 在 「进阶」 板块中,我们用到了 「array_first」 函数,该函数第三个参数可以直接设置默认值,我们就不多讲了,主要讲讲最后一个
$goods_detail_default = [
    'content' => '默认内容',
    'id'      => null,
    'goods_id'=> null,
];
foreach($result as &$item){
    $tmp = array_key_exists($goods_detail_list_by_keys,$item['id']) ? $goods_detail_list_by_keys[$item['id']] : [] ;
    // php 7.1+
    // $tmp = $goods_detail_list_by_keys[$item['id']] ?? [];
    $item['goods_detail'] = array_merge($goods_detail_default,$tmp);
}
unset($item);
var_dump($result);

结束

看到这里就算是完结了但是有的朋友会说,为什么不用 leftJoin 来处理?确实,在处理一对一关系中很多时候我们都会选择 innerJoin 或者 leftJoin 来进行处理,一条 SQL 就能搞定,很少会用到类似于这种方案,其实不然,在主流的框架中,默认的解决方案几乎都是这样处理的,比如LaravelThinkPHP,考虑到的场景会有很多,比如有的时候我只是需要按需取一部分的,或者我需要根据我后面的业务结果来决定是不是要加载一对一,然而这种情况下 join 似乎就不太适合。