结论:
- mysql使用limit分页查询,当分页临界点的数据等于分页条件时,可能两页内容会查到重复数据,并且会有数据丢失查询不出来;
- 通过时间范围查询数据时,如果只查询到23:59:59,会丢失1s的数据(23:59:59.000~23:59:59.999)
CREATE TABLE `color` (
`color_id` char(24) NOT NULL COMMENT '颜色id',
`biz_code` varchar(20) DEFAULT NULL COMMENT '颜色名',
`color_name` varchar(20) DEFAULT NULL COMMENT '颜色名',
`color_value` varchar(25) DEFAULT NULL COMMENT '颜色值',
`create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
`update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
`is_deleted` bit(1) DEFAULT b'0' COMMENT '删除标志',
`seq` int(2) DEFAULT NULL,
PRIMARY KEY (`color_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `test`.`color`(`color_id`, `biz_code`, `color_name`, `color_value`, `create_time`, `update_time`, `is_deleted`, `seq`) VALUES ('1', '1', '1', '1', '2020-05-11 19:27:00.561', '2020-05-11 19:27:00.561', b'0', NULL);
INSERT INTO `test`.`color`(`color_id`, `biz_code`, `color_name`, `color_value`, `create_time`, `update_time`, `is_deleted`, `seq`) VALUES ('2', '1', '1', '1', '2020-05-11 19:27:10.245', '2020-05-11 19:27:10.245', b'0', NULL);
INSERT INTO `test`.`color`(`color_id`, `biz_code`, `color_name`, `color_value`, `create_time`, `update_time`, `is_deleted`, `seq`) VALUES ('3', '1', '1', '1', '2020-05-11 19:27:23.357', '2020-05-11 19:27:46.726', b'0', NULL);
INSERT INTO `test`.`color`(`color_id`, `biz_code`, `color_name`, `color_value`, `create_time`, `update_time`, `is_deleted`, `seq`) VALUES ('4', '1', '1', '1', '2020-05-11 19:27:23.357', '2020-05-11 19:27:23.357', b'0', NULL);
INSERT INTO `test`.`color`(`color_id`, `biz_code`, `color_name`, `color_value`, `create_time`, `update_time`, `is_deleted`, `seq`) VALUES ('5', '1', '1', '1', '2020-05-11 19:27:29.733', '2020-05-11 19:27:29.733', b'0', NULL);
-- 验证结论1的查询
SELECT * FROM color ORDER BY create_time desc limit 0,2;
SELECT * FROM color ORDER BY create_time desc limit 2,2;
-- 验证结论2的查询
SELECT * FROM color WHERE create_time BETWEEN '2020-05-11 00:00:00' AND '2020-05-11 23:59:59';
SELECT * FROM color WHERE create_time BETWEEN '2020-05-11 00:00:00' AND '2020-05-11 23:59:59.999';
SELECT * FROM color WHERE create_time BETWEEN '2020-05-11 00:00:00' AND '2020-05-12 00:00:00';
附言:虽然这些坑自己踩过,同事也踩过,但总是不能很快识别到,应多加记录以增强记忆,避免以后再踩同样的坑!!!当积累足够多的时候,小小的问题也能展现你的大智慧。