#31 MySQL sort limit 的一个小问题

DB MySQL 2023-04-01

sort limit 中有一个相同 sort 项随机选择的逻辑，比如：
我们 sort 之后得到的结果是 a, b, c, d
加上 limit 3 之后，可能返回的是 b, a, d（a、b 的排序列相同，c、d 的排序列相同）
和预期的不一定是吻合的，这个可能需要注意一下。

官方文档的描述：

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result.
如果将 LIMIT row_count 和 ORDER BY 结合使用，MySQL 会在找到排序结果的前 row_count 行后立即停止排序，而不是对整个结果进行排序。

CREATE TABLE `testSortLimit` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `sort` INT(10) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`) USING BTREE
)
ENGINE=InnoDB;

INSERT INTO testSortLimit (name, sort) VALUES
("aaa", 0), ("eee", 1), ("iii", 1), ("mmm", 1),
("bbb", 0), ("fff", 1), ("jjj", 1), ("nnn", 1),
("ccc", 0), ("ggg", 1), ("kkk", 1), ("ooo", 1),
("ddd", 0), ("hhh", 1), ("lll", 1), ("ppp", 1);

SELECT * FROM testSortLimit;
SELECT * FROM testSortLimit ORDER BY sort ASC;
SELECT * FROM testSortLimit ORDER BY sort ASC LIMIT 6;
id name sort
1 aaa 0
5 bbb 0
9 ccc 0
13 ddd 0
2 eee 1
3 iii 1
4 mmm 1
6 fff 1
7 jjj 1
8 nnn 1
10 ggg 1
11 kkk 1
12 ooo 1
14 hhh 1
15 lll 1
16 ppp 1
id name sort
5 bbb 0
9 ccc 0
13 ddd 0
1 aaa 0
12 ooo 1
11 kkk 1

参考资料与拓展阅读

#29 MySQL 时间相关函数汇总

MySQL 2021-11-16
Name Description
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
CONVERT_TZ() Convert from one time zone to another
CURDATE() Return the current date
CURRENT_DATE(),
CURRENT_DATE		 Synonyms for CURDATE()
CURRENT_TIME(),
CURRENT_TIME		 Synonyms for CURTIME()
CURRENT_TIMESTAMP(),
CURRENT_TIMESTAMP		 Synonyms for NOW()
CURTIME() Return the current time
DATE() Extract the date part of a date or datetime expression
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
EXTRACT() Extract part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format Unix timestamp as a date
GET_FORMAT() Return a date format string
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
LOCALTIME(),
LOCALTIME		 Synonym for NOW()
LOCALTIMESTAMP,
LOCALTIMESTAMP()		 Synonym for NOW()
MAKEDATE() Create a date from the year and day of year
MAKETIME() Create time from hour, minute, second
MICROSECOND() Return the microseconds from argument
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
QUARTER() Return the quarter from a date argument
SEC_TO_TIME() Converts seconds to 'hh:mm:ss' format
SECOND() Return the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
SUBTIME() Subtract times
SYSDATE() Return the time at which the function executes
TIME() Extract the time portion of the expression passed
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the date or datetime expression;
with two arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP() Return a Unix timestamp
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (1-53)
YEAR() Return the year
YEARWEEK() Return the year and week
  • ADDDATE(date, INTERVAL expr unit), ADDDATE(expr, days)
SELECT WEEK(CURRENT_DATE());

SELECT '2021-01-01' AS `Date`, DAYNAME('2021-01-01'), DAYOFWEEK('2021-01-01'), WEEK('2021-01-01'), WEEKDAY('2021-01-01'), WEEKOFYEAR('2021-01-01'), YEARWEEK('2021-01-01')
UNION SELECT '2021-01-06' AS `Date`, DAYNAME('2021-01-04'), DAYOFWEEK('2021-01-04'), WEEK('2021-01-04'), WEEKDAY('2021-01-04'), WEEKOFYEAR('2021-01-04'), YEARWEEK('2021-01-04')
UNION SELECT '2021-01-08' AS `Date`, DAYNAME('2021-01-08'), DAYOFWEEK('2021-01-08'), WEEK('2021-01-08'), WEEKDAY('2021-01-08'), WEEKOFYEAR('2021-01-08'), YEARWEEK('2021-01-08');
-- weekday: monday 0, tuesday 1 ... sunday 6

#26 Golang MySQL

Golang DB MySQL 2021-06-04

测试表：

CREATE TABLE `users` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `password` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `nickname` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `email` VARCHAR(100) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
    `birthday` VARCHAR(10) NOT NULL DEFAULT '0000-00-00' COLLATE 'utf8mb4_unicode_ci',
    `age` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    `level` TINYINT(3) NOT NULL DEFAULT '0',
    `disabled` TINYINT(1) NOT NULL DEFAULT '0',
    `created_at` DATETIME NOT NULL DEFAULT current_timestamp(),
    `updated_at` DATETIME NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `username` (`username`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB;

连接

package main

import (
 "database/sql"
 "fmt"
 "time"

 _ "github.com/go-sql-driver/mysql"
)

var db *sql.DB   //全局变量client

func initMySQL() (err error) {
 dsn := "root:123456@tcp(127.0.0.1:3306)/test"
 db, err = sql.Open("mysql", dsn)
 if err != nil {
  panic(err)
 }
 err = db.Ping() //检测是否连接成功
 if err != nil {
  return
 }
 db.SetMaxOpenConns(200)                 //最大连接数
 db.SetMaxIdleConns(10)                  //连接池里最大空闲连接数。必须要比maxOpenConns小
 db.SetConnMaxLifetime(time.Second * 10) //最大存活保持时间
 db.SetConnMaxIdleTime(time.Second * 10) //最大空闲保持时间
 return
}

func main() {
 if err := initMySQL(); err != nil {
  fmt.Printf("connect to db failed,err:%v\n", err)
 } else {
  fmt.Println("connect to db success")
 }

 sqlStr := "SELECT id, name FROM sys_user WHERE id=?"
 var u user
 //非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
 err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
 if err != nil {
  fmt.Printf("scan failed， err: %v\n", err)
  return
 }
 fmt.Printf("id:%d,name:%s,age:%d\n", u.id, u.name)

 defer db.Close()

}

//user结构体
type user struct {
 id int
 name string
}

Insert 增

Update 改

Delete 删

Select 查