H5W3
当前位置:H5W3 > 其他技术问题 > 正文

请问一下mysql分页,在添加一个序号字段,怎么解决下一页后,序号又从1开始了问题

建表语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


— Table structure for system_log


DROP TABLE IF EXISTS system_log;
CREATE TABLE system_log (
id bigint(20) NOT NULL AUTO_INCREMENT,
creat_time_str varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
create_time datetime(0) DEFAULT NULL,
ip varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
method varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
rownumber int(11) DEFAULT NULL,
sdf tinyblob,
status_desc varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
user_nickname varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 112 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


— Records of system_log


INSERT INTO system_log VALUES (1, NULL, ‘2020-09-15 13:48:38’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.UserController.loginVerify’, NULL, NULL, ‘用户登录’, NULL);
INSERT INTO system_log VALUES (2, NULL, ‘2020-09-15 13:48:40’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (3, NULL, ‘2020-09-15 13:48:43’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (4, NULL, ‘2020-09-15 13:48:44’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (5, NULL, ‘2020-09-15 13:48:44’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (6, NULL, ‘2020-09-15 13:48:45’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (7, NULL, ‘2020-09-15 13:49:01’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (8, NULL, ‘2020-09-15 13:49:02’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (9, NULL, ‘2020-09-15 13:49:02’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (10, NULL, ‘2020-09-15 13:49:02’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (11, NULL, ‘2020-09-15 13:49:02’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (12, NULL, ‘2020-09-15 13:49:02’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (13, NULL, ‘2020-09-15 13:49:03’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (14, NULL, ‘2020-09-15 13:51:14’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.UserController.logout’, NULL, NULL, ‘用户退出’, ‘admin’);
INSERT INTO system_log VALUES (15, NULL, ‘2020-09-15 13:51:15’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.UserController.loginVerify’, NULL, NULL, ‘用户登录’, NULL);
INSERT INTO system_log VALUES (16, NULL, ‘2020-09-15 13:51:18’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (17, NULL, ‘2020-09-15 13:51:20’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (18, NULL, ‘2020-09-15 13:51:22’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (19, NULL, ‘2020-09-15 13:51:22’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);
INSERT INTO system_log VALUES (20, NULL, ‘2020-09-15 13:51:24’, ‘127.0.0.1’, ‘com.hkv.nvr.login.controller.SystemLogController.getSysSystemLog’, NULL, NULL, ‘查询事件记录’, ‘admin’);

SET FOREIGN_KEY_CHECKS = 1;
`

SELECT

( @i := @i + 1 ) AS rownumber,
method,
status_desc,
user_nickname,
ip,
create_time 

FROM

system_log,
( SELECT @i := 0 ) AS it 

WHERE

create_time >= '2020-09-15 00:00:00'
AND create_time <= '2020-09-15 23:59:59'
LIMIT 1,
5

回答

SELECT @i := pageSize*page啊,这东西直接前端实现就好了

本文地址:H5W3 » 请问一下mysql分页,在添加一个序号字段,怎么解决下一页后,序号又从1开始了问题

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址