码厩开发者笔记

#15 Mongo 基础

版本

以下是几个大版本和发布时间，作为一个大概的时间线吧：

2009/12 1.2
2010/03 1.4
2010/08 1.6 分片（支持水平拓展）
2011/03 1.8
2011/09 2.0 GridFS
2012/08 2.2 Aggregation Framework
2013/03 2.4 全文搜索
2014/04 2.6 WiredTiger 存储引擎
2015/03 3.0
2015/12 3.2 Change Streams
2016/11 3.4 多文档事务
2017/11 3.6
2018/06 4.0
2019/08 4.2
2020/07 4.4
2021/07 5.0 时间序列 + 聚簇索引（Clustered Indexing）+ 实时重分片 (Live Resharding) + 版本化 API (Versioned API)
2022/07 6.0
2023/08 7.0 可查询加密技术（Queryable Encryption）
2024/10 8.0
2025/09 8.2 功能完善与性能提升

当前最新版本 8 月发布的 4.0.2

Update @ 2021/06/07:
之后主版本号就一直停在了 4，2020 年之后甚至一直停在了 4.4（2019 年 4.2，2020 年 4.4），这也意味着功能组件稳定下来了。

#14 MySQL 笔记

几处小的知识点

#13 MySQL Boolean

我们通常使用以下几种方式表示布尔值：

1. tinyint(1)
2. bit(1)
3. enum('Y','N')

在 MySQL 中 bool, boolean 是 tinyint(1) 的别名。

如果只是一个 True OR False 的布尔型，没有比 bit(1) 更合适的了。
但是也有些时候，我们有好几个 bool 型用一个字段表示，最好用 bit(m)，我也用过 int 型。

附：不同值的长度

PS: blob 如果指定长度 m，MySQL 会选择足够存储数据的最小长度。
PS: MySQL set 类型

参考资料与拓展阅读

• MySQL 文档 (v8.0), 11.9 Using Data Types from Other Database Engines
• MySQL 文档 (v5.7), 11.9 Using Data Types from Other Database Engines
• MySQL 文档 (v5.6), 11.8 Using Data Types from Other Database Engines

#12 数据库：应该怎么存储 IP 地址

IP 地址的存储方式就两种：字符串，整型数。

表示方法

ipv4 VARCHAR(15) -- xx.xx.xx.xx
ipv6 VARCHAR(39) -- xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx

ipv4 INT UNSIGNED
ipv6 BINARY(16)

-- 如果同时需要表达 IPv4 和 IPv6
ip VARCHAR(39)
ip BINARY(16)

PS：我还在有些地方看到过这种采用 12 位整数表示 IPv4 的方法：'192.168.0.1' => 192168000001, 如果存到 MySQL 的话，只能采用 BIGINT 或 VARCHAR(12) 了。
如果省略中间的分号，ipv6 只需要 VARCHAR(32) 就行了。

整型和字符串的转换

• INET_ATON / INET_NTOA
• INET6_ATON / INET6_NTOA

#11 MongoDB 聚集（数据表）中的 ID

默认是一个 ObjectId 对象，也可以手动设置。

举个栗子

使用 PyMongo：

# -*- coding: utf-8 -*-

from pymongo import MongoClient

client = MongoClient()  # 连接到默认主机的默认端口：localhost:27017
db = client.test_db
collection = db.test_collection
collection.insert({"Hu" : "Ang", "Love" : [5, 'Sun', 'Xiu']})
collection.insert({"And" : 20, "Daughter" : True})
collection.insert({"GIRL": ',', "IS": "A GIRL", '_id': 123})

如果是 MongoDB 数据库操作，就应该是这样：

$ mongo
> use test_db
> db.test_collection.insert({"Hu" : "Ang", "Love" : [5, 'Sun', 'Xiu']})
> db.test_collection.insert({"And" : 20, "Daughter" : True})
> db.test_collection.insert({"GIRL": ',', "IS": "A GIRL", '_id': 123})

最后查到的结果显示如下：

> db.test_collection.find()
{ "_id" : ObjectId("5746c0f900e0990cfc600938"), "Love" : [ 5, "Sun", "Xiu" ], "Hu" : "Ang" }
{ "_id" : ObjectId("5746c0f900e0990cfc600939"), "And" : 20, "Daughter" : true }
{ "_id" : 123, "GIRL" : ",", "IS" : "A GIRL" }

_id

如果自己往里面传 _id 的话，要注意唯一性约束，如果里面存在这个 _id 值，那么就会报错：E11000 duplicate key error index

为什么没有采用像其他数据库一样的主键自增机制？

可能是因为 MongoDB 天生的分布式属性，导致其不愿耗费精力来处理自增主键的同步问题。

ObjectId

关于 ObjectId 字段，官方文档中对每个字节所表示内容的说明：

ObjectId is a 12-byte BSON type, constructed using:

• a 4-byte value representing the seconds since the Unix epoch,
• a 3-byte machine identifier,
• a 2-byte process id, and
• a 3-byte counter, starting with a random value.

ObjectId 占 12 个字节，其中：

• 第 1、2、3、4 个字节用来存 Unix 时间戳
• 第 5、6、7 个字节用来存机器标识
• 第 8、9 个字节用来存客户端进程编号
  时间戳 + 机器标识 + 客户端进程编号 保证 “机器 + 进程 + 时间” 的一致性。
• 第 10、11、12 个字节用来存随机字符串
  保证同一台机器，同一个客户端进程，在一秒种之内创建的记录的一致性。
  2 *_ (8 _ 3) = 16777216，也就是说，理论上，同一台机器，同一个客户端进程，在一秒种之内可以创建 1677 万多条记录。

举个例子，比如在 ObjectId("5746c0f900e0990cfc600939") 中 5746c0f9 就是时间戳，00e099 就是机器标识，0cfc 就是客户端进程编号，600939 就是随机字符串。

通过这个设计，保证不同机器的 mongod 服务、同一个机器上的不同 mongod 服务进程之间都不出现重复值的情况（可能性极低，如果出现，可能也有后续的处理办法）。

重点：ObjectId 在客户端生成！！！

我个人也觉得 ObjectId 在客户端生成比服务器端要好：

1. 更加容易根据机器标识 + 进程编号保证记录的唯一性
2. 将生成 ObjectId 的这一部分计算转移出去，也能略微减轻 MongoDB 服务的计算压力。
3. 客户端插入记录的时候，自己就知道 ID，不需要服务器端的反馈，针对这个设计可以设计出一些不需要返回的 insert 方法，给服务器减少一些查询带来的压力。

PyMongo 中就是使用 bson.objectid.ObjectId 生成的。可以阅读一下相关代码，了解这个 ID 的生成方法。
PS：比如，在我的 Ubuntu 环境中，代码文件就是 /usr/local/lib/python2.7/dist-packages/bson/objectid.py。

参考

• MongoDB 官方文档（v2.6），Data Models > Data Model Reference > ObjectId

#10 SQL 的历史

历史

1. 70 年代初，IBM 公司开发了 SEQUEL 语言 (Structured English Query Language，结构化英语查询语言)，用于管理 RDB。
2. 70 年代末，IBM 和甲骨文分别开始开发基于 SQL 的 RDBMS。
   PS: IBM 的产品就包括大名鼎鼎的 DB2，世界上最早的 SQL 数据库。
   PS: 甲骨文当时还叫做 Relational Software, Inc
3. 1980 年，由于商标问题，SEQUEL 改名 SQL。
   虽然官方发音是 ess-cue-el, 但至今为止，不少人还是将其读做 /ˈsiːkwəl/。
4. 1986 年被美国国家标准学会标准化（ANSI X3.135-1986）
5. 1987 年，ISO 采纳 ANSI SQL (ISO 9075:1987)，所以这个版本也被称之为 SQL87。
6. 后来，SQL 陆续推出 89，92，1999, 2003 .... 多个版本。
   应该是 ISO 负责制定和维护吧，也无所谓啦。

版本

SQL-86 (or SQL-87) is the ISO 9075:1987 standard of 1987
SQL-89 is the ISO/IEC 9075:1989 standard of 1989
SQL-92 is the ISO/IEC 9075:1992 standard of 1992
SQL:1999 is the ISO/IEC 9075:1999 standard of 1999
SQL:2003 is the ISO/IEC 9075:2003 standard of 2003
SQL:2006 is the ISO/IEC 9075:2006 standard of 2006
SQL:2008 is the ISO/IEC 9075:2008 standard of 2008
SQL:2011 is the ISO/IEC 9075:2011 standard of 2011
SQL:2016 is the ISO/IEC 9075:2016 standard of 2016

SQL:1999
Added regular expression matching, recursive queries (e.g. transitive closure), triggers, support for procedural and control-of-flow statements, nonscalar types (arrays), and some object-oriented features (e.g. structured types), support for embedding SQL in Java (SQL/OLB) and vice versa (SQL/JRT)

2003

Introduced XML-related features (SQL/XML), window functions, standardized sequences, and columns with autogenerated values (including identity columns)

2006
ISO/IEC 9075-14:2006 defines ways that SQL can be used with XML. It defines ways of importing and storing XML data in an SQL database, manipulating it within the database, and publishing both XML and conventional SQL-data in XML form. In addition, it lets applications integrate queries into their SQL code with XQuery, the XML Query Language published by the World Wide Web Consortium (W3C), to concurrently access ordinary SQL-data and XML documents.

2008
Legalizes ORDER BY outside cursor definitions. Adds INSTEAD OF triggers, TRUNCATE statement,[34] FETCH clause

2011
Adds temporal data (PERIOD FOR)[35] (more information at: Temporal database#History). Enhancements for window functions and FETCH clause.

2016
Adds row pattern matching, polymorphic table functions, JSON

2019
Adds Part 15, multidimensional arrays (MDarray type and operators)

https://en.wikibooks.org/wiki/Structured_Query_Language
https://en.wikibooks.org/wiki/MySQL
https://en.wikibooks.org/wiki/PostgreSQL
https://en.wikibooks.org/wiki/SQLite
https://en.wikipedia.org/wiki/SQL_reserved_words

ISO 9075

最新的 SQL 标准一共分成 9 个部分（Part 5,6,7,8,12 可能是被废弃了）：

1. Part 1: Framework (SQL/Framework)
   基本概念
2. Part 2: Foundation (SQL/Foundation)
   基础语法
3. Part 3: Call-Level Interface (SQL/CLI)
   应该是编程语言方面的接口
4. Part 4: Persistent stored modules (SQL/PSM)
   SQL 面向过程编程
5. Part 9: Management of External Data (SQL/MED)
6. Part 10: Object language bindings (SQL/OLB)
   Java SQLJ 相关内容
7. Part 11: Information and definition schemas (SQL/Schemata)
8. Part 13: SQL Routines and types using the Java TM programming language (SQL/JRT)
   又是 Java 相关
9. Part 14: XML-Related Specifications (SQL/XML)
   XML 相关

PS: 前缀 ISO/IEC 9075-<n>:2016 – Information technology – Database languages – SQL – 省略。

PS: 还有一个拓展标准：ISO/IEC 13249 SQL Multimedia and Application Packages

变种

多数数据库没有严格按照标准来实现，导致不通平台上的 SQL 语句是不能跨平台的。

以下是两种主要的 SQL 方言：

• T-SQL(Transact-SQL): SQLServer
• PL/SQL: Oracle

#9 MySQL 编码字符集

各种类型的编码

mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | gbk                        |
| character_set_connection | gbk                        |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | gbk                        |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

5.7 及更早版本默认字符集和 Collation 是 latin1 和 latin1_swedish_ci
8.0 之后改成 utf8mb4 和 utf8mb4_0900_ai_ci

上面就有 6 种 全局的编码：

• client 客户端编码
• connection 连接编码
• database 数据库编码，创建表时的默认编码
  load data, 以及创建
• filesystem 文件系统编码
• results 结果编码
• server 服务端编码，创建数据库时的默认编码
• system 系统编码

还有两种局部的编码：表的编码和列（字段）的编码。
列主要是 char, varchar, text 类型。

字符集

# 都可以 LIKE 搜索
SHOW CHARACTER SET;
SHOW CHARSET;
SHOW CHAR SET;

SELECT * FROM information_schema.character_sets;
SELECT * FROM information_schema.character_sets WHERE CHARACTER_SET_NAME LIKE "%utf%";

排序规则

SHOW COLLATION;

这就多了，两百多。

SHOW COLLATION LIKE "%ascii%";

SELECT * FROM information_schema.collations WHERE CHARACTER_SET_NAME = "utf8mb4" AND COLLATION_NAME LIKE "%zh%";

命名规则

字符集名称，语言，通用后缀

• ai Accent-insensitive 重音不敏感
• as Accent-sensitive 重音敏感
• ci Case-insensitive 大小写不敏感
• cs Case-sensitive 大小写敏感
• ks Kana-sensitive 假名敏感（日语）
• bin 二进制

8.0 之后，很多编码中多了 0900 字样，表示 Unicode 9.0 规范。

参考资料与拓展阅读

• 博客园，2021/07/10，mysql 设置了 utf8mb4，为什么还有 utf8mb4_general_ci 和 utf8mb4_0900_ai_ci？
• CSDN 博客，2023-07-07，数据库之 MySQL 字符集与数据库操作

#8 SQL Join

#7 SQLAlchemy LIKE

例子：搜索用户表 user 中字段 phone 包含 520 的行。

SQL

和 pymysql 等库一样的用：

keyword = '520'
conn.execute('select * from user where phone like "%%%s%%";' % keyword)
conn.execute('select * from user where phone like "%%%s%%";', keyword)

.like 方法

q = session.query(model.User.id, model.User.phone).filter(model.User.phone.like(f'%{keyword}%'))
qs = qs.all()
# print(qs.statement)
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE "user".phone LIKE :phone_1

对应的大小写不敏感方法有 ilike (lower("user".phone) LIKE lower(:phone_1))
还有：not_like, not_ilike

.contains 方法

print(session.query(model.User.id, model.User.phone).filter(model.User.phone.contains(keyword)).statement)
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE ("user".phone LIKE '%' || :phone_1 || '%')

.regexp_match 方法（1.4 新增）

对应的是 MySQL 支持的 REGEXP 操作符。

print(session.query(model.User.id, model.User.phone).filter(model.User.phone.regexp_match(keyword)).statement)
session.query(model.User.id, model.User.phone).filter(model.User.phone.regexp_match(keyword)).all()
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE "user".phone <regexp> :phone_1

.startswith 和 .endswith

print(session.query(model.User.id, model.User.phone).filter(model.User.phone.startswith(keyword)).statement)
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE ("user".phone LIKE :phone_1 || '%')
print(session.query(model.User.id, model.User.phone).filter(model.User.phone.endswith(keyword)).statement)
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE ("user".phone LIKE '%' || :phone_1)

.match 方法

对应的是数据库的 MATCH (col1,col2,...) AGAINST (expr [search_modifier]) 全文索引方法。
对单字段同样可用，不过需要先建立 FULLTEXT 索引。

print(session.query(model.User.id, model.User.phone).filter(model.User.phone.match(keyword)).statement)
session.query(model.User.id, model.User.phone).filter(model.User.phone.match(keyword)).all()
# SELECT "user".id, "user".phone
# FROM "user"
# WHERE "user".phone MATCH :phone_1

参考资料与拓展阅读

• https://docs.sqlalchemy.org/en/14/core/sqlelement.html
• https://dev.mysql.com/doc/refman/8.0/en/regexp.html

#6 MySQL set 类型

顾名思义，就是集合类型。

set('a', 'b', ...)

每个字段可以是指定选项中的若干个（包含 0 个）。