如何在MariaDB的WordPress postmeta表中统计关联数组数据？

我的MariaDB WordPress的postmeta表中有如下条目：

以此类推，我需要完成两个统计：

1. 统计所有meta_key为profile_star_rating的行数
2. 求和meta_value字段里的数值（该字段是PHP序列化的关联数组）

我编程基础很差，不知道怎么在SQL里处理这种序列化数组。我需要得到的结果是行数2，数值总和7。

我能通过下面的PHP代码获取单个单元格的值，但不知道怎么整合到SQL里：

add_action('wp_trash_post', 'my_deleted_post');
function my_deleted_post($post_id) {
    global $post;
    $author_id=$post->post_author;
    $rating=$post->profile_star_rating;
    $value=$rating['profile_star_rating']
}

方法一：纯MariaDB SQL语句处理 #

直接用SQL字符串函数提取序列化数组里的数值，完成统计：

SELECT
    COUNT(*) AS total_rows,
    SUM(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(meta_value, '"skills";s:1:"', -1), '"', 1) AS UNSIGNED)) AS total_rating
FROM
    wp_postmeta
WHERE
    meta_key = 'profile_star_rating';

逻辑说明： #

• SUBSTRING_INDEX(meta_value, '"skills";s:1:"', -1)：截取"skills";s:1:"之后的内容，得到4";}这类字符串
• SUBSTRING_INDEX(..., '"', 1)：截取第一个双引号前的内容，得到纯数字字符串
• CAST(... AS UNSIGNED)：把字符串转为数值类型，方便求和
• COUNT(*)直接统计符合条件的行数

方法二：WordPress PHP内置函数处理（更安全可靠） #

借助WordPress自带函数自动处理序列化数据，逻辑更直观：

// 获取所有包含profile_star_rating元数据的文章ID
$post_ids = get_posts(array(
    'meta_key' => 'profile_star_rating',
    'posts_per_page' => -1,
    'fields' => 'ids'
));

$total_rows = count($post_ids);
$total_rating = 0;

foreach ($post_ids as $post_id) {
    // 自动反序列化获取数组
    $rating_data = get_post_meta($post_id, 'profile_star_rating', true);
    // 累加skills对应的数值
    if (isset($rating_data['skills'])) {
        $total_rating += (int)$rating_data['skills'];
    }
}

// 输出结果
echo "总行数：{$total_rows}，总评分：{$total_rating}";

逻辑说明： #

• get_posts批量获取符合条件的文章ID，减少数据传输
• get_post_meta会自动完成PHP序列化数组的反序列化，不用手动处理字符串
• 遍历累加数值，避免SQL字符串处理可能出现的兼容性问题

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