php - mysql表绑定(bind)下一行和当前行

Question

我需要将下一行也绑定(bind)到我的查询响应。例如，

SELECT * FROM `ActivityLog` WHERE data like '%865814071038085%'

这将返回我需要的记录，我还需要下一行出现在结果集中，

我通过此查询获得的表记录 ID 是，

1,
3,
5,
7

所以我需要一种方法来获取下一行 2、4、6、8 并将查询输出

1,
2,
3,
4,
5,
6,
7,
8 
records.

如何使用 mysql 获取此处解释的下一行？它始终是当前行和下一个第一行。

CREATE TABLE IF NOT EXISTS `ActivityLog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `activityAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `userId` int(11) DEFAULT NULL,
  `module` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `controller` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `action` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `data` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idxUserName` (`username`),
  KEY `idxUserId` (`userId`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Application activity log' AUTO_INCREMENT=25390 ;

数据集，

INSERT INTO `ActivityLog` (`id`, `activityAt`, `username`, `userId`, `module`, `controller`, `action`, `data`) VALUES
(1, '2015-03-03 13:02:03', 'guest', NULL, '', 'Site', 'Login', 'Login access. IP:220.247.236.99'),
(2, '2015-03-03 13:02:08', 'QelasySuperAdmin', -1, '', 'Site', 'Login', 'Login success. IP: 220.247.236.99'),
(3, '2015-03-03 13:02:10', 'QelasySuperAdmin', -1, 'Log', 'Activity', 'Admin', 'Manage Activity Logs.'),
(4, '2015-03-03 13:02:17', 'QelasySuperAdmin', -1, 'Log', 'Activity', 'Admin', 'Manage Activity Logs.'),
(5, '2015-03-03 13:06:10', 'guest', NULL, 'Api', 'Default', 'DeviceUsage', ' Data:{"url":"http:\\/\\/www.googleadservices.com\\/pagead\\/aclk?sa=L&ai=CpOAR1LH1VPbZI4GOjAbY2ILQCeikiaQGwIKYmM8Bvtf9EwgAEAEgs_j-GCgDYIEDoAGwhoDQw&val=ChBhOTg3YjE3Y2E3MzM3NWYwEJC5jKcFGggTkt0OPC8P_iABKAAwwZ6E452z_YSAATiQuYynBUDMw9OnBQ&:","id":"1745"}'),
(6, '2015-03-03 13:06:10', 'guest', NULL, 'Api', 'Default', 'DeviceUsage', ' Response:{"code":200,"status":true,"data":[],"serverTime":"2015-03-03 13:06:10"}'),
(7, '2015-03-03 13:06:11', 'guest', NULL, 'Api', 'Default', 'DeviceUsage', ' Data:{"url":"about:blank","id":"1745"}'),
(8, '2015-03-03 13:06:11', 'guest', NULL, 'Api', 'Default', 'DeviceUsage', ' Response:{"code":200,"status":true,"data":[],"serverTime":"2015-03-03 13:06:11"}'),
(9, '2015-03-03 13:06:15', 'guest', NULL, 'Api', 'Default', 'DeviceUsage', ' Data:{"url":"http:\\/\\/clickserve","id":"1745"}'),
(10, '2015-03-03 13:06:15', 'guest', NULL, 'Api', 'Default', 'DeviceUsage', ' Response:{"code":200,"status":true,"data":[],"serverTime":"2015-03-03 13:06:15"}'))

更新

请检查这个 sql fiddle， http://sqlfiddle.com/#!9/b218a/1

到目前为止，没有一个答案对我有帮助。

EXPLAIN 命令的输出。

Answer 1

试试这个。应该很接近。

select * from
  (
    SELECT * FROM `ActivityLog` WHERE data like '%865814071038085%'
    union
    SELECT * FROM `ActivityLog` where id in (SELECT id + 1 FROM `ActivityLog` WHERE data like '%865814071038085%') 
  ) q

此处演示:http://sqlfiddle.com/#!9/2b406/5

以防 id 不连续，这是另一个:

select * from
  (
    SELECT * FROM `ActivityLog` WHERE data like '%FOO%'
    union
    SELECT * FROM `ActivityLog` where id in (SELECT min(a2.id)  FROM `ActivityLog` a1 inner join `activitylog` a2 on a1.id < a2.id WHERE a1.data like '%FOO%' group by a1.id) 
  ) q

使用更新的 fiddle :http://sqlfiddle.com/#!9/2b406/9

使用与来自另一个答案的 Anirban N 相同的技术，但修改为仅在 @pid 不是下一个顺序 id 时更新其值，并且仅在其搜索值时才匹配，或 IS 下一个顺序 ID。 (如果id序列有空隙，这个就拿不到下一行)

select if(@pid + 1 = id, id, @pid := id) id, 
       activityAt,
       username , 
       userId, 
       module,
       controller,
       action,
       data  
  from `ActivityLog` as a 
  where a.data like '%865814071038085%' 
    or id = @pid + 1;

final fiddle