在 MySQL 中循环创建串联变量

Question

我有一个 WordPress 网站，其帖子评级存储在 wp_ ratings 表中，每个评级存储为单独的行。我们很快将更改主题，需要将 wp_ ratings 中存储的值转换为元数据，如下所示：

a: 3: {
i: 0;
a: 2: {
    s: 7: "user_id";
    s: 1: "0";
    s: 2: "ip";
    s: 9: "127.0.0.1";
}
i: 1;
a: 2 {
    s: 7: "user_id";
    s: 1: "0";
    s: 2: "ip";
    s: 9: "127.0.0.1";
}
i: 2;
a: 2: {
    s: 7: "user_id";
    s: 1: "0";
    s: 2: "ip";
    s: 9: "127.0.0.1";
}
i: 3;
a: 2: {
    s: 7: "user_id";
    s: 1: "0";
    s: 2: "ip";
    s: 9: "127.0.0.1";
}

总投票值由语句开头的 a:3 显示然后存储的每个评级都有一个单独的条目。

我对存储过程不是特别熟悉，但我想创建一个循环来计算 wp_posts sum( rating_ating) 中每个帖子的总值，然后将元数据创建到一个长字符串中。

我真的不知道从哪里开始，所以如果有人能指出我正确的方向，我将非常感激。

谢谢

Answer 1

设置测试数据

CREATE TABLE `wp_ratings` ( 
  `rating_id` INT(11) NOT NULL AUTO_INCREMENT, 
  `rating_postid` INT(11) NOT NULL, 
  `rating_posttitle` TEXT NOT NULL, 
  `rating_rating` INT(2) NOT NULL, 
  `rating_timestamp` VARCHAR(15) NOT NULL, 
  `rating_ip` VARCHAR(40) NOT NULL, 
  `rating_host` VARCHAR(200) NOT NULL, 
  `rating_username` VARCHAR(50) NOT NULL, 
  `rating_userid` INT(10) NOT NULL DEFAULT '0', 
  PRIMARY KEY  (`rating_id`), 
  KEY `rating_postid` (`rating_postid`) 
); 

INSERT INTO `test`.`wp_ratings` 
  (`rating_id`, `rating_postid`, `rating_posttitle`, 
  `rating_rating`, `rating_timestamp`, `rating_ip`, 
  `rating_host`, `rating_username`, `rating_userid`)
  VALUES
  (1,1,'title',1,'abc','127.0.0.1','a.a.a','user_id',1),
  (2,2,'title',1,'abc','127.0.0.1','a.a.a','user_id',1),
  (3,2,'title',1,'abc','127.0.0.1','a.a.a','user_id',1),
  (4,3,'title',1,'abc','127.0.0.1','a.a.a','user_id',1),
  (5,3,'title',1,'abc','127.0.0.1','a.a.a','user_id',1),
  (6,3,'title',1,'abc','127.0.0.1','a.a.a','user_id',1);

---

查询

SET @post_id = 3;

SELECT CONCAT
(
'a:', COUNT(rating_id), ':{',
    (
        SELECT CONCAT( GROUP_CONCAT(meta_data_vote SEPARATOR ''), '}') FROM
        (
            SELECT CONCAT
            ( 
                'i:',
                @curRow := @curRow + 1,
                ';a:2:{s:7:"', 
                rating_username, 
                '";s:1:"0";s:2:"ip";s:9:"', 
                rating_ip,
                '";}'
            )AS meta_data_vote
            FROM
                wp_ratings
            JOIN (SELECT @curRow := -1 AS j) r
            WHERE rating_postid = @post_id
        )AS meta_data_votes
    )
) AS new_ratings_meta_data
FROM wp_ratings l
WHERE rating_postid = @post_id

---

结果（其中@post_id = 3）

a:3:{i:0;a:2:{s:7:"user_id";s:1:"0";s:2:"ip";s:9:"127.0.0.1";}i:1;a:2:{s:7:"user_id";s:1:"0";s:2:"ip";s:9:"127.0.0.1";}i:2;a:2:{s:7:"user_id";s:1:"0";s:2:"ip";s:9:"127.0.0.1";}}

---

结论

我无法编写一个查询来返回带有post_ids及其新meta_data的结果集。

上面的代码只会在逐个帖子的基础上进行转换。

如果您想批量更新，则需要更多考虑，但我认为这会给您一个良好的开端。

Answer 2

我对存储过程了解不多，但我认为您可以使用 group_concat() 和按帖子 id 分组，只用一个查询来完成此操作。