码厩技术博客

#36 MySQL 的浮点型精度问题

#35 MySQL --login-path 参数

#34 我也想知道为什么 PG 不如 MySQL

#33 MySQL sort limit 的一个小问题

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;

参考资料与拓展阅读

• 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

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

#31 MySQL 时间相关函数汇总

• 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

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

TLDR：

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

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

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

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

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

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

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})")

#29 《高性能 MySQL》阅读笔记 2: 基准测试 [编辑中]

#28 《高性能 MySQL》阅读笔记 1

这本书买了都两年了，一直没有认真看，真是... 这次，我想花点时间把它从头到尾看一遍。

#27 Golang MySQL

测试表：

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 查