Detailed explanation of the pitfalls of mixing MySQL order by and limit

In MySQL, we often use order by for sorting and limit for paging. When we need to sort first and then paging, we often use a similar expression: select * from table name order by sort field limt M,N. However, this writing style hides deeper usage traps. When there is duplicate data in the sorting field, it is easy for the sorting results to be inconsistent with expectations.
As shown in the table:

insert image description here

When querying the first and last pages:

insert image description here

Solution:

SELECT * FROM purchaseinfo ORDER BY actiontime,id LIMIT 0,2;

The actual execution results above have proved that there is often a gap between reality and imagination. The actual SQL execution is not executed in the above way. In fact, MySQL will optimize Limit here. For specific optimization methods, see the official document: https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

/*
Navicat MySQL Data Transfer

Source Server: Local mysql8.0
Source Server Version : 80018
Source Host : localhost:3308
Source Database : baihe

Target Server Type : MYSQL
Target Server Version : 80018
File Encoding: 65001

Date: 2020-06-09 14:47:37
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for purchaseinfo
-- ----------------------------
DROP TABLE IF EXISTS `purchaseinfo`;
CREATE TABLE `purchaseinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT '0',
  `inout` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `money` int(11) DEFAULT NULL,
  `actiontime` datetime DEFAULT NULL COMMENT 'jiaoyi',
  PRIMARY KEY (`id`),
  KEY `UserId` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ----------------------------
-- Records of purchaseinfo
-- ----------------------------
INSERT INTO `purchaseinfo` VALUES ('7', '1', 'I', '10000', '2020-06-09 18:05:41');
INSERT INTO `purchaseinfo` VALUES ('8', '7788', 'O', '20000', '2020-06-09 18:05:15');
INSERT INTO `purchaseinfo` VALUES ('9', '7788', 'I', '20000', '2020-06-09 18:05:15');
INSERT INTO `purchaseinfo` VALUES ('10', '7788', 'O', '50000', '2020-06-09 18:05:15');
INSERT INTO `purchaseinfo` VALUES ('11', '1', 'O', '50000', '2020-06-09 18:05:15');
INSERT INTO `purchaseinfo` VALUES ('12', '1', 'O', '50000', '2020-06-09 18:05:15');
INSERT INTO `purchaseinfo` VALUES ('13', '1', 'O', '50000', '2020-06-09 18:05:15');
INSERT INTO `purchaseinfo` VALUES ('14', '1', 'O', '50000', '2020-06-09 18:05:15');
INSERT INTO `purchaseinfo` VALUES ('15', '1', 'O', '50000', '2020-06-09 18:05:15');
INSERT INTO `purchaseinfo` VALUES ('16', '1', 'O', '50000', '2020-06-09 18:05:15');

This is the end of this article about the pitfalls of mixing MySQL order by and limit. For more information about mixing MySQL order by and limit, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in: