mysql 用户变量 循环_在MySQL中循环创建一个连接变量

设置测试数据 h2>

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);

查询 H2>

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) h2>

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";}}

结论 H2>

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

以上代码仅在逐个帖子的基础上进行转换。

如果你想批量更新，它需要更多的思考，但我认为这将让你有一个良好的开端。