码厩技术博客

#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 个）。

#5 MySQL 时间

几种形式

1. 字符串，比如：
2. varchar(14)，存：yyyymmddHHMMSS 格式
3. varchar(19)，存：yyyy-mm-dd HH:MM:SS 格式
4. varchar(23)，存：yyyy-mm-dd HH:MM:SS.fff 格式
5. varchar(26)，存：yyyy-mm-dd HH:MM:SS.ffffff 格式
6. varchar(24)，存：yyyy-mm-ddTHH:MM:SS+0800 或 yyyy-mm-dd HH:MM:SS 0800 格式
7. 整形数
8. int 1970 - 2038

   time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime((1 << 31) - 1))
   # 2038-01-19 03:14:07
   

   - int unsigned 1970 - 2106

   time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime((1 << 32) - 1))
   # 2106-02-07 06:28:15
   

   - bigint 可以用到世界毁灭
9. 注意：也可以任意指定一个时间为起点，比如 2020-01-01 00:00:00
10. TIMESTAMP 类型
11. 相当于上面的 int 类型，2038 问题
12. DATETIME 类型

PS: DATE, TIME, YEAR 三种类型：

• DATE
• TIME
• YEAR

比较

1. 存储效率

2. 时间比较

3. 是否方便索引

#4 MySQL Timestamp

优点

Timestamp 是按 UTC 时间进行存储。

2038 问题

MySQL 的 timestamp 类型挺好的，不过有个严重的 2038 问题，我不知道到时候这个类型会如何处理。

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

(1 << 31) / (3600 * 24 * 365)

根据时间范围，这应该是用的一个有符号的 32 位整型数实现的，为什么不用无符号数呢，不是又可以往后续 68 年么？

我预测，MySQL 的某一个新版本，将会增加一个 TIMESTAMP64 类型，采用 64 位有符号整形数存时间戳。
或直接将 TIMESTAMP 拓展到 64 位，先支持配置和命令行参数开启这个特性，然后后续版本将其视作默认设置。

参考资料与拓展阅读

• MySQL 文档 (v5.7), 11.2.2 The DATE, DATETIME, and TIMESTAMP Types

#3 MySQL 插入的长字符串被截断问题

MySQL 出现了一个问题，插入一个长字符串（二十几KB）结果导致被截断。

#2 Python 的 MySQLdb 模块插入数据没有成功与 autocommit（自动提交）的关系

用 MySQLdb 操作数据库，插入数据之后发现数据库中依然为空，不知原因为何。
开启 mysqld 的 log 设置项之后发现日志文档中更有执行 sql 语句，直接复制语句在客户端中执行也没有问题，那么为什么通过 MySQLdb 的插入全部没有结果呢？

#1 SQLite

简介

1. 嵌入式数据库
2. 第一版 2000 年发布
3. 公有领域
4. 实现了大多数 SQL-92 标准，包括事务（ACID）
5. 使用最广泛的 DB 引擎

命令

.help
.databases
.tables
.shema <tableName> # show sql
.fullschema
.quit

.mode list      # 默认，没有头部，竖线隔开
.mode tabs      # tab 隔开
.mode column    # 可读性强些
.mode line      # 像 MySQL \G

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: /tmp/history.db

sqlite> .dbinfo main
database page size:  4096
write format:        2
read format:         2
reserved bytes:      0
file change counter: 3110
database page count: 6669
freelist page count: 0
schema cookie:       7
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        2
application id:      0
software version:    3033000
number of tables:    4
number of indexes:   6
number of triggers:  0
number of views:     0
schema size:         785
data version         2

常用操作

查看表结构

.schema tablename
pragma dbname.table_info(tablename)
pragma dbname.table_xinfo(tablename) # 包含虚拟表中的隐藏列

SELECT * FROM sqlite_master WHERE tbl_name = 'tablename';

工具

1. SQLite Database Browser
2. heidisql
3. 浏览器拓展
4. Navicat for SQLite

附：sqlite help

sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.binary on|off           Turn binary output on or off.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.connection [close] [#]  Open or close an auxiliary database connection
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?OBJECTS?          Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.imposter INDEX TABLE    Create imposter table TABLE on index INDEX
.indexes ?TABLE?         Show names of indexes
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?       Set output mode
.nonce STRING            Disable safe mode for one command if the nonce matches
.nullvalue STRING        Use STRING in place of NULL values
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Exit this program
.read FILE               Read input from FILE
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save FILE               Write in-memory database into FILE
.scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.selftest ?OPTIONS?      Run tests defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.testcase NAME           Begin redirecting output to 'testcase-out.txt'
.testctrl CMD ...        Run various sqlite3_test_control() operations
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output