MySQL - 码厩

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

参考资料与拓展阅读

MySQL 8.0, 8.2.1.19 LIMIT Query Optimization
MySQL 5.7, 8.2.1.17 LIMIT Query Optimization
MySQL 5.7 中文文档, 8.2.1.17 LIMIT 查询优化
MySQL 5.1 中文文档, 7.2.14. MySQL如何优化LIMIT

#33 《高性能 MySQL》阅读笔记 3: 服务器性能剖析 [编辑中]

MySQL HighPerformanceMySQL 2021-11-17

BOOK

#32 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

#31 周数问题：今天是今年的第几周

软件设计 Java MySQL 2021-11-16

2021-01-01

TLDR：

ISO 8601 标准：国际标准规定周一为一周的开始，包含 1 月 4 日的那一周为第一周
Java: 使用 WeekFields.ISO 获取 ISO 标准周数，或使用本地化设置
Python: 使用 strftime 的 %W (本地化) 或 %V (ISO) 参数获取周数
Go: 使用 ISOWeek() 方法获取 ISO 标准周数
MySQL: 使用 WEEK() 函数的 mode 3 或 7 获取 ISO 标准周数

Java

import java.time.LocalDate;
import java.time.temporal.WeekFields;
import java.util.Locale;

public class WeekNumberExample {
    public static void main(String[] args) {
        LocalDate date = LocalDate.of(2021, 1, 1);
        // 使用 ISO 标准计算周数（周一为一周开始）
        int weekNumber = date.get(WeekFields.ISO.weekOfYear());
        System.out.println("ISO week number: " + weekNumber);

        // 使用本地化周数计算
        WeekFields weekFields = WeekFields.of(Locale.getDefault());
        int localizedWeek = date.get(weekFields.weekOfYear());
        System.out.println("Localized week number: " + localizedWeek);
    }
}

Python

import datetime

# 说明不同格式化参数的含义：
# %Y - 年份 (基于日历年度)
# %W - 周数 (周一作为一周的开始，第一周是包含第一个周一的周)
# %G - 年份 (基于ISO周数)
# %V - 周数 (ISO周数，周一作为一周开始，第一周是包含1月4日的那一周)

for i in range(10):
    d = datetime.date(2021 - i, 1, 1)
    print((str(d), d.weekday(), d.strftime('%Y %W / %G %V')))
('2021-01-01', 4, '2021 00 / 2020 53')
('2020-01-01', 2, '2020 00 / 2020 01')
('2019-01-01', 1, '2019 00 / 2019 01')
('2018-01-01', 0, '2018 01 / 2018 01')
('2017-01-01', 6, '2017 00 / 2016 52')
('2016-01-01', 4, '2016 00 / 2015 53')
('2015-01-01', 3, '2015 00 / 2015 01')
('2014-01-01', 2, '2014 00 / 2014 01')
('2013-01-01', 1, '2013 00 / 2013 01')
('2012-01-01', 6, '2012 00 / 2011 52')

Go

package main

import (
    "fmt"
    "time"
)

func main() {
    // Go 中计算周数
    t := time.Date(2021, 1, 1, 0, 0, 0, 0, time.UTC)

    // 使用 ISO 标准计算周数
    _, week := t.ISOWeek()
    fmt.Printf("ISO week number: %d\n", week)

    // 使用 YearDay 计算基于年的周数
    yearDay := t.YearDay()
    weekFromYearDay := (yearDay + 6) / 7
    fmt.Printf("Week from year day: %d\n", weekFromYearDay)
}

MySQL 中的实现

WEEK(date[, mode]) 函数：

Mode	First day of week	Range	Week 1 is the first week …
0	Sunday	0-53	with a Sunday in this year
1	Monday	0-53	with 4 or more days this year
2	Sunday	1-53	with a Sunday in this year
3	Monday	1-53	with 4 or more days this year
4	Sunday	0-53	with 4 or more days this year
5	Monday	0-53	with a Monday in this year
6	Sunday	1-53	with 4 or more days this year
7	Monday	1-53	with a Monday in this year

总结一下，就是三个维度：

周数从 0 开始还是从 1 开始
以周一算每周的第一天还是周日
按每周第一天开始算，还是按每周四天开始算

0 周日 每周第一天 0
2 周日 每周第一天 1
4 周日 每周四天 0
6 周日 每周四天 1

1 周一 每周四天 0
3 周一 每周四天 1
5 周一 每周第一天 0
7 周一 每周第一天 1

SELECT NULL AS Mode, WEEK('2021-01-01'), WEEK('2021-01-04')
UNION SELECT 0 AS Mode, WEEK('2021-01-01', 0), WEEK('2021-01-04', 0)
UNION SELECT 1 AS Mode, WEEK('2021-01-01', 1), WEEK('2021-01-04', 1)
UNION SELECT 2 AS Mode, WEEK('2021-01-01', 2), WEEK('2021-01-04', 2)
UNION SELECT 3 AS Mode, WEEK('2021-01-01', 3), WEEK('2021-01-04', 3)
UNION SELECT 4 AS Mode, WEEK('2021-01-01', 4), WEEK('2021-01-04', 4)
UNION SELECT 5 AS Mode, WEEK('2021-01-01', 5), WEEK('2021-01-04', 5)
UNION SELECT 6 AS Mode, WEEK('2021-01-01', 6), WEEK('2021-01-04', 6)
UNION SELECT 7 AS Mode, WEEK('2021-01-01', 7), WEEK('2021-01-04', 7);

Mode	WEEK('2021-01-01')	WEEK('2021-01-04')
NULL	0	1
0	0	1
1	0	1
2	52	1
3	53	1
4	0	1
5	0	1
6	53	1
7	52	1

我定义的每周迭代名称（例如：2020-01A）

每周第一天为周一。
按照周一属于哪个月计算。

import datetime
from datetime import timedelta

def get_week_iteration_name(date):
    """
    根据周一所属月份计算周迭代名称
    格式：YYYY-MM{A-Z}
    返回：str, date, date (迭代名称，周一日期，周日日期)
    """
    weekday = date.weekday()
    monday = date - timedelta(days=weekday)
    sunday = monday + timedelta(days=6)

    year = monday.year
    month = monday.month

    # 计算该月第几个周一
    # 找到该月第一个周一
    first_day = datetime.date(year, month, 1)
    first_monday_offset = (7 - first_day.weekday()) % 7
    first_monday = first_day + timedelta(days=first_monday_offset)

    # 如果第一个周一不在本月，说明该月第一天就是周一
    if first_monday.month != month:
        first_monday = first_day

    # 计算是第几个周一
    week_index = ((monday - first_monday).days // 7) + 1
    # 转换为字母标识 (A=1, B=2, ...)
    week_letter = chr(ord('A') + week_index - 1)

    return f"{year}-{month:02d}{week_letter}", monday, sunday

test_dates = [
    datetime.date(2021, 1, 1),   # Friday
    datetime.date(2021, 1, 4),   # Monday
    datetime.date(2021, 1, 8),   # Friday
    datetime.date(2020, 12, 28), # Monday
]
for d in test_dates:
    weekday = d.weekday()
    name, monday, sunday = get_week_iteration_name(d)
    print(f"{d} -> {name} {weekday + 1} (Mon: {monday}, Sun: {sunday})")

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
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
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