如何逐键对比两行数据差异？PHP array_diff()失效问题求助

你踩的这个坑我之前也遇到过！array_diff()的逻辑其实是找值的存在性差异，它只会返回在第一个数组里出现、但第二个数组里完全没有的值——但像featured、on_sale这种列，两个数组里都有yes和no，所以它根本不会把这些键值对的变化当成差异。

要实现你需要的逐键逐值精准对比，可以从PHP和SQL两个层面解决：

一、PHP层面：用逐键对比替代值匹配 #

最直接的方案是用PHP内置的array_diff_assoc()函数，它会同时对比键和值的一致性，完全符合你的需求：

// 假设你已经通过查询得到了$data1和$data2两个关联数组
$theDifferences = array_diff_assoc($data1, $data2);

// 遍历输出变更详情
foreach($theDifferences as $field => $newValue){
    $oldValue = $data1[$field];
    echo "字段 *{$field}* 从 `{$oldValue}` 变更为 `{$newValue}`!";
}

如果需要更灵活的自定义判断（比如忽略大小写、处理NULL值），也可以手动循环遍历每一个键：

$differences = [];
// 遍历所有列（假设两个数组的键完全一致，因为是同一张表的查询结果）
foreach($data1 as $field => $oldVal){
    $newVal = $data2[$field];
    // 这里可以加自定义规则，比如NULL-safe对比、忽略大小写
    if($oldVal !== $newVal){
        $differences[$field] = [
            'old_value' => $oldVal,
            'new_value' => $newVal
        ];
    }
}

// 输出差异
foreach($differences as $field => $change){
    echo "字段 *{$field}* 变更：`{$change['old_value']}` → `{$change['new_value']}`";
}

二、SQL层面：直接查询出差异列 #

如果不想在PHP里处理，也可以让数据库直接返回变更结果，减少后端逻辑。这里分两种场景：

1. 列数固定时的简洁写法 #

用CASE和UNION ALL拼接每一列的对比结果：

SELECT
    'height' AS changed_field,
    t1.height AS old_value,
    t2.height AS new_value
FROM your_table t1
JOIN your_table t2 ON t1.id = 1 AND t2.id = 2
WHERE t1.height != t2.height

UNION ALL

SELECT
    'width' AS changed_field,
    t1.width AS old_value,
    t2.width AS new_value
FROM your_table t1
JOIN your_table t2 ON t1.id = 1 AND t2.id = 2
WHERE t1.width != t2.width

UNION ALL

-- 把所有需要对比的列都按这个格式拼接
SELECT
    'featured' AS changed_field,
    t1.featured AS old_value,
    t2.featured AS new_value
FROM your_table t1
JOIN your_table t2 ON t1.id = 1 AND t2.id = 2
WHERE t1.featured != t2.featured;

2. 列数较多时的JSON方案（MySQL 8.0+支持） #

用JSON函数动态拼接对比结果，不用写大量UNION：

SELECT
    column_name AS changed_field,
    t1.value AS old_value,
    t2.value AS new_value
FROM (
    -- 把第一行的所有列转成键值对
    SELECT 'height' AS column_name, t1.height AS value FROM your_table t1 WHERE t1.id=1
    UNION ALL SELECT 'width', t1.width FROM your_table t1 WHERE t1.id=1
    UNION ALL SELECT 'colour', t1.colour FROM your_table t1 WHERE t1.id=1
    UNION ALL SELECT 'featured', t1.featured FROM your_table t1 WHERE t1.id=1
    UNION ALL SELECT 'on_sale', t1.on_sale FROM your_table t1 WHERE t1.id=1
) t1
JOIN (
    -- 把第二行的所有列转成键值对
    SELECT 'height' AS column_name, t2.height AS value FROM your_table t2 WHERE t2.id=2
    UNION ALL SELECT 'width', t2.width FROM your_table t2 WHERE t2.id=2
    UNION ALL SELECT 'colour', t2.colour FROM your_table t2 WHERE t2.id=2
    UNION ALL SELECT 'featured', t2.featured FROM your_table t2 WHERE t2.id=2
    UNION ALL SELECT 'on_sale', t2.on_sale FROM your_table t2 WHERE t2.id=2
) t2 ON t1.column_name = t2.column_name
-- 用NULL-safe对比，避免NULL值被忽略
WHERE NOT t1.value <=> t2.value;

注意事项 #

• 如果列值可能包含NULL，一定要用<=>（NULL-safe相等运算符）代替!=，否则NULL和任何值的对比结果都是NULL，不会被WHERE条件捕获。
• 字符串类型的列，如果需要忽略大小写，PHP里可以用strcasecmp()代替!==，SQL里可以加COLLATE utf8mb4_general_ci来指定不区分大小写的规则。

内容的提问来源于stack exchange，提问作者mayersdesign