码厩技术博客

#24 MySQL 版本历史

历史

• 在更早的 20 年间，MySQL 的前身 UNIREG 被 Monty 开发
• 1994 年瑞典公司 TcX 成立，Monty 是三位创始人之一。次年，这家公司改名为 MySQL AB。
  TcX 将 UNIREG 改造为一款支持 SQL 的关系型数据库。
• 1995 年，MySQL 3.11 发布。
• 1999 年，MySQL 3.23 集成 BDB，开始了可插拔存储引擎架构。
• 2000 年，ISAM 改良成 MyISAM，同时 MySQL 基于 GPL 协议开源。
• 2001 年，MySQL AB 和芬兰公司 Infobase Oy 合作，为 MySQL 3.22 带来了 InnoDB 引擎。
  重点是事务和行级锁。
  注意：InnoDB 是 GPL / 专有协议双授权。
• 2005 年，Innobase Oy 被甲骨文收购。同年, MySQL 5.0 发布。
  注意：我们现在数据库系统课的很多概念，触发器，存储过程，视图，游标等都是 5.0 开始支持的。
• 2006 年，Oracle 曾经试图收购 MySQL 但是遭到拒绝。
• 2008-02-26 MySQL AB 被 SUN 10 亿美元收购。
• 2008-11-27 MySQL 5.1，开始支持分区，和主从同步时的行复制（RBR）。
• 2009-04-20 SUN 被甲骨文 74 亿美元收购，收购直到 2010-01-27 才完成。
  最重要的存储引擎 InnoDB 终于和 MySQL 版权归于一处了。
• 2010-12-03 MySQL 5.5，InnoDB 成为默认引擎（5.5.5 开始）。
• innodb 改进
• utf8mb4
• metadata lock
• 2013-02-05 MySQL 5.6
• 2015-10-21 MySQL 5.7
• 2018-04-19 MySQL 8.0
• 最新版本是 2020-10-19 发布的 8.0.22
  2021-10-19 发布的 8.0.27 (Update @ 2021-11-16)

说明：

1. 5.6，5.7 应该是现在最常用的两个版本。
   8.0 还比较新，我还没有在线上业务中用过。但是有很多我感兴趣的特性。
2. 列举的特性可能是在不同子版本引入，开发中可能需要留意不同子版本的差异。
   就现在我理解的主流开发思想，对组件应该简单应用，就数据库而言，应该只用来做基础存储，不要涉及复杂功能，不要涉及所谓特性，最要用好几个大版本就在的功能，其他数据库系统都有的功能。
   我觉得有点过了。这个另说吧。

MySQL 5.6 新特性

• GTID复制。
• 无损复制。
• 延迟复制。
• 基于库级别的并行复制。
• mysqlbinlog 可远程备份 binlog。
• 对 TIME, DATETIME 和 TIMESTAMP 进行了重构，可支持微秒（6 位小数）。
• DATETIME 的空间需求也从之前的 8 个字节减少到 5 个字节。
• 可以有多个时间类型 DEFAULT current_timestamp() ON UPDATE current_timestamp()
  之前只能支持 1 个 TIMESTAMP 类型
• YEAR(2) 被抛弃，新建时会自动转换成 YEAR(4)
• Online DDL，部分 ALTER 操作不再阻塞 DML。
• 可传输表空间（transportable tablespaces）。
• 统计信息的持久化。避免主从之间或数据库重启后，同一个SQL的执行计划有差异。
• 全文索引。
• EXPLAIN 可用来查看 DELETE，INSERT，REPLACE，UPDATE 等 DML 操作的执行计划，在此之前，只支持SELECT操作。

• 分区表的增强

• 最大可用分区数增加至 8192

• 支持分区和非分区表之间的数据交换

• 操作时显式指定分区，比如：

  SELECT * FROM employees PARTITION (p0, p2)
  

• Undo Log 可保存在独立表空间中，因其是随机 IO，更适合放到 SSD 中。但仍然不支持空间的自动回收。

• 可 dump 和 load Buffer pool 的状态，避免数据库重启后需要较长的预热时间。
• InnoDB 优化，并默认使用 InnoDB 做存储引擎。
• NoSQL API：InnoDB Memcached plugin。
• InnoDB 内部的性能提升，包括拆分 kernel mutex，引入独立的刷新线程，可设置多个 purge 线程，减少大内存系统中的buffer_pool竞争。
• Redo Log 总大小的限制从之前的 4G 扩展至 512G（innodb_log_file_size）。
• 死锁算法改进，并记录到错误日志中。
• 为了避免大 buffer_pool 的实例重启服务时过长的warmup时间，你可以在重启后立即加载缓存页面。MySQL可以在关闭时导出完全数据文件，检阅此文件找出重启时需要加载的pages。你可以在任何手工导入导出buffer_pool，比如在性能测试时或者在执行复杂的OLAP查询后。
• 优化器性能提升，引入了ICP，MRR，BKA 等特性，针对子查询进行了优化。
• 若干安全性提升（包括密码自动过期 (mysql.user 的 password_expired)）

复制和日志(replication)

1)支持延时复制，MySQL现在支持延迟复制，默认是0秒。使用CHANGE MASTER TO参数 MASTER_DELAY 来设置延迟。可以让slave跟master之间控制一个时间间隔，方便特殊情况下的数据恢复。(以前是使用第三方工具可以做到) 2)行级复制功能加强，可以降低磁盘、内存、网络等资源开销(只记录能确定行记录的字段即可)。3)现在支持多线程复制。如果开启，sql线程作为协调者协调多个工作线程，数量取决于slave_parallel_workers。现在多线程复制以单库为基础，特定库的更新的相对顺序和主库一样。不过，没有必要协调不同库之间的事务。事务可以被分布到每个库，意味着一个复制从库的工作线程可以顺序执行事务而不必等待其它库的更新完成。4)支持以全局统一事务ID(GTID)为基础的复制。当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务。GTID复制是全部以事务为基础，使得检查主从一致性变得非常简单。如果所有主库上提交的事务也同样提交到从库上，一致性就得到了保证。5)支持启用GTID，对运维人员来说应该是一件令人高兴的事情，在配置主从复制，传统的方式里，你需要找到binlog和POS点，然后change master to指向，而不是很有经验的运维，往往会将其找错，造成主从同步复制报错，在mysql5.6里，如果使用了GTIDs，启动一个新的复制从库或切换到一个新的主库，就不必依赖log文件或者pos位。需要知道master的IP、端口，账号密码即可，因为同步复制是自动的，mysql通过内部机制GTID自动找点同步。
6)binlog的读写现在是崩溃安全的，因为只有完整的事件(或者事务)才会被记录和读取。默认会记录事件的大小以及事件本身，使用大小来验证事件被正确记录。你也可以使用参数binlog_checksum设置使用crc32记录事件的校验值。使用参数master_verify_checksum可以让服务读取校验值。slave-sql-verify-checksum参数使从库读relay日志的时候读取校验值。

MySQL支持在表中保存主库连接信息了。使用参数–master-info-repository和 –relay-log-info-repository来设置。设置 –master-info-repository为表，会记录连接信息到slave_master_info表。设置–relay-log-info-repository为表，会记录relay log信息到slave_relay_log_info表。这几个表都是自动建立在mysql系统库。

增强Performance Schema数据库(Mysql performance schema)

1)记录表的输入与输出，操作包括行级访问表和临时表，如insert,upate,delete. 2)表的事件过滤，以库或者表名为基础。3)线程的事件过滤，更多关于线程的信息被搜集4)表和索引I/O以及表锁的统计表。5)记录命令以及命令的阶段。

不允许在存储过程中或者函数参数中或者存储程序本地变量中使用default来指定(如：SET var_name = DEFAULT命令)，但可以在指定系统变量时使用default。

MySQL 5.7 新特性

1. 引入对 JSON 的支持，JSON 类型和相关函数。

组复制
InnoDB Cluster
多源复制
增强半同步（AFTER_SYNC）
基于WRITESET的并行复制。
在线开启GTID复制。
在线设置复制过滤规则。
在线修改Buffer pool的大小。
在同一长度编码字节内，修改VARCHAR的大小只需修改表的元数据，无需创建临时表。
可设置NUMA架构的内存分配策略（innodb_numa_interleave）。
透明页压缩（Transparent Page Compression）。
UNDO表空间的自动回收。
查询优化器的重构和增强。
可查看当前正在执行的SQL的执行计划（EXPLAIN FOR CONNECTION）。
引入了查询改写插件（Query Rewrite Plugin），可在服务端对查询进行改写。
EXPLAIN FORMAT=JSON会显示成本信息，这样可直观的比较两种执行计划的优劣。
引入了虚拟列，类似于Oracle中的函数索引。
新实例不再默认创建test数据库及匿名用户。
引入ALTER USER命令，可用来修改用户密码，密码的过期策略，及锁定用户等。
mysql.user表中存储密码的字段从password修改为authentication_string。
表空间加密。
优化了Performance Schema，其内存使用减少。
Performance Schema引入了众多instrumentation。常用的有Memory usage instrumentation，可用来查看MySQL的内存使用情况，Metadata Locking Instrumentation，可用来查看MDL的持有情况，Stage Progress instrumentation，可用来查看Online DDL的进度。
同一触发事件（INSERT，DELETE，UPDATE），同一触发时间（BEFORE，AFTER），允许创建多个触发器。在此之前，只允许创建一个触发器。
InnoDB原生支持分区表，在此之前，是通过ha_partition接口来实现的。
分区表支持可传输表空间特性。
集成了SYS数据库，简化了MySQL的管理及异常问题的定位。
原生支持JSON类型，并引入了众多JSON函数。
引入了新的逻辑备份工具-mysqlpump，支持表级别的多线程备份。
引入了新的客户端工具-mysqlsh，其支持三种语言：JavaScript, Python and SQL。两种API：X DevAPI，AdminAPI，其中，前者可将MySQL作为文档型数据库进行操作，后者用于管理InnoDB Cluster。
mysql_install_db被mysqld --initialize代替，用来进行实例的初始化。
原生支持systemd。
引入了super_read_only选项。
可设置SELECT操作的超时时长（max_execution_time）。
可通过SHUTDOWN命令关闭MySQL实例。
引入了innodb_deadlock_detect选项，在高并发场景下，可使用该选项来关闭死锁检测。
引入了Optimizer Hints，可在语句级别控制优化器的行为，如是否开启ICP，MRR等，在此之前，只有Index Hints。
GIS的增强，包括使用Boost.Geometry替代之前的GIS算法，InnoDB开始支持空间索引。

MySQL 8.0 新特性

最重要的是窗口函数和 CTE

1. 窗口函数，这个功能补齐了，非常香。
2. CTE (Common Table Expressions)
3. 递归 SQL
4. UTF8 的变更，默认的 UTF8 字符集是 UTF8MB4
5. 优化和完善了对 JSON 的支持，最重要的是 JSON Partial Updates，支持对 JSON 的局部更新。

其他MySQL 8.0计划更新的特性包括：

在锁定行方面增加了更多选项，如SKIP LOCKED和NOWAIT两个选项。其中，
SKIP LOCKED允许在操作中不锁定那些需要忽略的行；NOWAIT则在遇到行的锁定的时候马上抛出错误。
MySQL能根据可用内存的总量去伸缩扩展，以更好利用虚拟机的部署。
新增“隐藏索引”的特性，这样索引可以在查询优化器中变为不可见。索引在标记为不可用后，和表的数据更改同步，但是优化器不会使用它们。对于使用隐藏索引的建议，是当不决定某个索引是否需要保留的时候，可以使用。

引入了原生的，基于InnoDB的数据字典。数据字典表位于mysql库中，对用户不可见，同mysql库的其它系统表一样，保存在数据目录下的mysql.ibd文件中。不再置于mysql目录下。
Atomic DDL。
重构了INFORMATION_SCHEMA，其中，部分表已重构为基于数据字典的视图，在此之前，其为临时表。
PERFORMANCE_SCHEMA查询性能提升，其已内置多个索引。
不可见索引（Invisible index）。
降序索引。
直方图。
角色（Role）。
资源组（Resource Groups），可用来控制线程的优先级及其能使用的资源，目前，能被管理的资源只有CPU。
引入了innodb_dedicated_server选项，可基于服务器的内存来动态设置innodb_buffer_pool_size，innodb_log_file_size和innodb_flush_method。
快速加列（ALGORITHM=INSTANT）。
自增主键的持久化。
可持久化全局变量（SET PERSIST）。
默认字符集由latin1修改为utf8mb4。
默认开启UNDO表空间，且支持在线调整数量（innodb_undo_tablespaces）。在MySQL 5.7中，默认不开启，若要开启，只能初始化时设置。
备份锁。
Redo Log的优化，包括允许多个用户线程并发写入log buffer，可动态修改innodb_log_buffer_size的大小。
默认的认证插件由mysql_native_password更改为caching_sha2_password。
默认的内存临时表由MEMORY引擎更改为TempTable引擎，相比于前者，后者支持以变长方式存储VARCHAR，VARBINARY等变长字段。从MySQL 8.0.13开始，TempTable引擎支持BLOB字段。
Grant不再隐式创建用户。
SELECT ... FOR SHARE和SELECT ... FOR UPDATE语句中引入NOWAIT和SKIP LOCKED选项，解决电商场景热点行问题。
正则表达式的增强，新增了4个相关函数，REGEXP_INSTR()，REGEXP_LIKE()，REGEXP_REPLACE()，REGEXP_SUBSTR()。
查询优化器在制定执行计划时，会考虑数据是否在Buffer Pool中。而在此之前，是假设数据都在磁盘中。
ha_partition接口从代码层移除，如果要使用分区表，只能使用InnoDB存储引擎。
引入了更多细粒度的权限来替代SUPER权限，现在授予SUPER权限会提示warning。
GROUP BY语句不再隐式排序。
MySQL 5.7引入的表空间加密特性可对Redo Log和Undo Log进行加密。
information_schema中的innodb_locks和innodb_lock_waits表被移除，取而代之的是performance_schema中的data_locks和data_lock_waits表。
引入performance_schema.variables_info表，记录了参数的来源及修改情况。
增加了对于客户端报错信息的统计（performance_schema.events_errors_summary_xxx）。
可统计查询的响应时间分布（call sys.ps_statement_avg_latency_histogram()）。
支持直接修改列名（ALTER TABLE ... RENAME COLUMN old_name TO new_name）。
用户密码可设置重试策略（Reuse Policy）。
移除PASSWORD()函数。这就意味着无法通过“SET PASSWORD ... = PASSWORD('auth_string') ”命令修改用户密码。
代码层移除Query Cache模块，故Query Cache相关的变量和操作均不再支持。
BLOB, TEXT, GEOMETRY和JSON字段允许设置默认值。
可通过RESTART命令重启MySQL实例。

参考资料与拓展阅读

• Old Reliable: A History of MySQL
• O'Reilly, The History of MySQL
• CNET, Oracle tried to buy open-source MySQL
• MySQL 官方文档, MySQL 5.6 Release Notes
• 1.3 What Is New in MySQL 5.6
• MySQL 官方文档, MySQL 5.7 Release Notes
• 1.3 What Is New in MySQL 5.7
• MySQL 官方文档, MySQL 8.0 Release Notes
• 1.3 What Is New in MySQL 8.0

#23 MySQL 常见引擎

SHOW VARIABLES LIKE '%engine%';
SHOW ENGINES;

以 MySQL 8.0 为例：

除了 PERFORMANCE_SCHEMA，

Percona (8.0) 和 MySQL 保持一致，不过 InnoDB 实际被替换成他们自己开发的兼容引擎 XtraDB。

MariaDB (10.1.45) 也使用 XtraDB 替换了 InnoDB，而且没有 BLACKHOKE, ARCHIVE，但是多了：

MyISAM

在 2010 年发布的 MySQL 5.5.5 之前，一直是 MySQL 的默认引擎。

InnoDB

InnoDB 是甲骨文的。

TokuDB

• https://en.wikipedia.org/wiki/TokuDB
• https://github.com/Tokutek/tokudb-engine

TokuTek 公司开发，后来被 Percona 公司收购，现在已被 Percona 废弃（仓库归档）。

其他

• Blackhole
• Mrg_MyISAM
• Memory
• CSV
• Archive

第三方

• federated

参考资料与拓展阅读

#22 MySQL 管理语句

MySQL 语句：

1. DDL
2. DML
3. 事件和锁
4. Replication
5. Prepared
6. Compound
7. 数据库管理
8. 工具

用户管理

角色 (用户模板)

create role stuff@localhost;
... 分配权限，修改属性
select host, user as role from mysql.user where authentication_string = '';

• CREATE ROLE [IF NOT EXISTS] role [, role ] ...
  锁定，密码为空
• DROP ROLE
• SET ROLE
• SET DEFAULT ROLE

用户

select host, user, plugin, left(authentication_string, 5) as pass, account_locked from mysql.user;
select distinct user from mysql.user;

select current_user(), current_role();

• CREATE USER
• DROP USER
• ALTER USER
• RENAME USER

密码

• SET PASSWORD

权限

• GRANT
• REVOKE

资源组管理

• CREATE RESOURCE GROUP
• DROP RESOURCE GROUP
• ALTER RESOURCE GROUP
• SET RESOURCE GROUP

表维护

• ANALYZE TABLE
• CHECK TABLE
• CHECKSUM TABLE
• OPTIMIZE TABLE
• REPAIR TABLE

组件，插件，Loadable Function

• CREATE FUNCTION

• DROP FUNCTION

• INSTALL COMPONENT

• UNINSTALL COMPONENT

• INSTALL PLUGIN

• UNINSTALL PLUGIN

CLONE

SET

• SET 设置变量
• SET CHARACTER SET
• SET NAMES

SHOW

• SHOW BINARY LOGS binlog
• SHOW BINLOG EVENTS binlog
• SHOW CHARACTER SET 字符集
• SHOW COLLATION 排序规则
• SHOW COLUMNS 查看表结构

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

• SHOW CREATE DATABASE
• SHOW CREATE EVENT
• SHOW CREATE FUNCTION
• SHOW CREATE PROCEDURE
• SHOW CREATE TABLE
• SHOW CREATE TRIGGER
• SHOW CREATE USER

• SHOW CREATE VIEW

• SHOW DATABASES 数据库

• SHOW ENGINE 查看指定引擎的状态

部分引擎会提供一些状态信息，比如 INNODB, PERFORMANCE_SCHEMA

• SHOW ENGINES 存储引擎
• SHOW ERRORS
• SHOW EVENTS
• SHOW FUNCTION CODE
• SHOW FUNCTION STATUS
• SHOW GRANTS 用户权限
• SHOW INDEX 查看指定表的索引
• SHOW MASTER STATUS
• SHOW OPEN TABLES
• SHOW PLUGINS 插件
• SHOW PRIVILEGES
• SHOW PROCEDURE CODE
• SHOW PROCEDURE STATUS
• SHOW PROCESSLIST
• SHOW PROFILE
• SHOW PROFILES
• SHOW RELAYLOG EVENTS
• SHOW REPLICAS
• SHOW SLAVE HOSTS | SHOW REPLICAS
• SHOW REPLICA STATUS
• SHOW SLAVE | REPLICA STATUS
• SHOW STATUS
• SHOW TABLE STATUS
• SHOW TABLES 表
• SHOW TRIGGERS
• SHOW VARIABLES
• SHOW WARNINGS

其他

• BINLOG
• CACHE INDEX
• FLUSH
• KILL
• LOAD INDEX INTO CACHE
• RESET
• RESET PERSIST
• RESTART
• SHUTDOWN

#21 MySQL help

输入 help 之后，按照提示就会发现 MySQL 有一个主题树。
从头 help contents 开始：

• Account Management
• ALTER RESOURCE GROUP
• ALTER USER
• CREATE RESOURCE GROUP
• CREATE ROLE
• CREATE USER
• DROP RESOURCE GROUP
• DROP ROLE
• DROP USER
• GRANT
• RENAME USER
• REVOKE
• SET DEFAULT ROLE
• SET PASSWORD
• SET RESOURCE GROUP
• SET ROLE
• Administration
• BINLOG
• CACHE INDEX
• FLUSH
• HELP COMMAND
• KILL
• LOAD INDEX
• RESET
• RESET PERSIST
• RESTART
• SET
• SET CHARACTER SET
• SET CHARSET
• SET NAMES
• SHOW
• SHOW BINARY LOGS
• SHOW BINLOG EVENTS
• SHOW CHARACTER SET
• SHOW COLLATION
• SHOW COLUMNS
• SHOW CREATE DATABASE
• SHOW CREATE EVENT
• SHOW CREATE FUNCTION
• SHOW CREATE PROCEDURE
• SHOW CREATE SCHEMA
• SHOW CREATE TABLE
• SHOW CREATE TRIGGER
• SHOW CREATE USER
• SHOW CREATE VIEW
• SHOW DATABASES
• SHOW ENGINE
• SHOW ENGINES
• SHOW ERRORS
• SHOW EVENTS
• SHOW FIELDS
• SHOW FUNCTION CODE
• SHOW FUNCTION STATUS
• SHOW GRANTS
• SHOW INDEX
• SHOW MASTER LOGS
• SHOW MASTER STATUS
• SHOW OPEN TABLES
• SHOW PLUGINS
• SHOW PRIVILEGES
• SHOW PROCEDURE CODE
• SHOW PROCEDURE STATUS
• SHOW PROCESSLIST
• SHOW PROFILE
• SHOW PROFILES
• SHOW RELAYLOG EVENTS
• SHOW REPLICA STATUS
• SHOW REPLICAS
• SHOW SCHEMAS
• SHOW SLAVE HOSTS
• SHOW SLAVE STATUS
• SHOW STATUS
• SHOW TABLE STATUS
• SHOW TABLES
• SHOW TRIGGERS
• SHOW VARIABLES
• SHOW WARNINGS
• SHUTDOWN
• Components
• CLONE
• INSTALL COMPONENT
• UNINSTALL COMPONENT
• UNINSTALL PLUGIN
• Compound Statements
• BEGIN END
• CASE STATEMENT
• CLOSE
• DECLARE CONDITION
• DECLARE CURSOR
• DECLARE HANDLER
• DECLARE VARIABLE
• FETCH
• GET DIAGNOSTICS
• IF STATEMENT
• ITERATE
• LABELS
• LEAVE
• LOOP
• OPEN
• REPEAT LOOP
• RESIGNAL
• RETURN
• SIGNAL
• WHILE
• Contents
• Data Definition
• ALTER DATABASE
• ALTER EVENT
• ALTER FUNCTION
• ALTER INSTANCE
• ALTER LOGFILE GROUP
• ALTER PROCEDURE
• ALTER SCHEMA
• ALTER SERVER
• ALTER TABLE
• ALTER TABLESPACE
• ALTER VIEW
• CREATE DATABASE
• CREATE EVENT
• CREATE FUNCTION
• CREATE INDEX
• CREATE LOGFILE GROUP
• CREATE PROCEDURE
• CREATE SCHEMA
• CREATE SERVER
• CREATE SPATIAL REFERENCE SYSTEM
• CREATE TABLE
• CREATE TABLESPACE
• CREATE TRIGGER
• CREATE VIEW
• DROP DATABASE
• DROP EVENT
• DROP FUNCTION
• DROP INDEX
• DROP PROCEDURE
• DROP SCHEMA
• DROP SERVER
• DROP SPATIAL REFERENCE SYSTEM
• DROP TABLE
• DROP TABLESPACE
• DROP TRIGGER
• DROP VIEW
• FOREIGN KEY
• RENAME TABLE
• TRUNCATE TABLE
• Data Manipulation
• CALL
• DELETE
• DO
• DUAL
• HANDLER
• IMPORT TABLE
• INSERT
• INSERT DELAYED
• INSERT SELECT
• JOIN
• LOAD DATA
• LOAD XML
• PARENTHESIZED QUERY EXPRESSIONS
• REPLACE
• SELECT
• TABLE
• UNION
• UPDATE
• VALUES STATEMENT
• Data Types
• AUTO_INCREMENT
• BIGINT
• BINARY
• BIT
• BLOB
• BLOB DATA TYPE
• BOOLEAN
• CHAR
• CHAR BYTE
• DATE
• DATETIME
• DEC
• DECIMAL
• DOUBLE
• DOUBLE PRECISION
• ENUM
• FLOAT
• INT
• INTEGER
• LONGBLOB
• LONGTEXT
• MEDIUMBLOB
• MEDIUMINT
• MEDIUMTEXT
• SET DATA TYPE
• SMALLINT
• TEXT
• TIME
• TIMESTAMP
• TINYBLOB
• TINYINT
• TINYTEXT
• VARBINARY
• VARCHAR
• YEAR DATA TYPE
• Functions
• Aggregate Functions and Modifiers
  • AVG
  • BIT_AND
  • BIT_OR
  • BIT_XOR
  • COUNT
  • COUNT DISTINCT
  • GROUP_CONCAT
  • JSON_ARRAYAGG
  • JSON_OBJECTAGG
  • MAX
  • MIN
  • STD
  • STDDEV
  • STDDEV_POP
  • STDDEV_SAMP
  • SUM
  • VARIANCE
  • VAR_POP
  • VAR_SAMP
• Bit Functions
  • &
  • <<
  • >>
  • BIT_COUNT
  • ^
  • |
  • ~
• Cast Functions and Operators
  • BINARY OPERATOR
  • CAST
  • CONVERT
• Comparison Operators
  • !=
  • <
  • <=
  • <=>
  • =
  • >
  • >=
  • BETWEEN AND
  • COALESCE
  • GREATEST
  • IN
  • INTERVAL
  • IS
  • IS NOT
  • IS NOT NULL
  • IS NULL
  • ISNULL
  • LEAST
  • NOT BETWEEN
  • NOT IN
• Date and Time Functions
  • ADDDATE
  • ADDTIME
  • CONVERT_TZ
  • CURDATE
  • CURRENT_DATE
  • CURRENT_TIME
  • CURRENT_TIMESTAMP
  • CURTIME
  • DATE FUNCTION
  • DATEDIFF
  • DATE_ADD
  • DATE_FORMAT
  • DATE_SUB
  • DAY
  • DAYNAME
  • DAYOFMONTH
  • DAYOFWEEK
  • DAYOFYEAR
  • EXTRACT
  • FROM_DAYS
  • FROM_UNIXTIME
  • GET_FORMAT
  • HOUR
  • LAST_DAY
  • LOCALTIME
  • LOCALTIMESTAMP
  • MAKEDATE
  • MAKETIME
  • MICROSECOND
  • MINUTE
  • MONTH
  • MONTHNAME
  • NOW
  • PERIOD_ADD
  • PERIOD_DIFF
  • QUARTER
  • SECOND
  • SEC_TO_TIME
  • STR_TO_DATE
  • SUBDATE
  • SUBTIME
  • SYSDATE
  • TIME FUNCTION
  • TIMEDIFF
  • TIMESTAMP FUNCTION
  • TIMESTAMPADD
  • TIMESTAMPDIFF
  • TIME_FORMAT
  • TIME_TO_SEC
  • TO_DAYS
  • TO_SECONDS
  • UNIX_TIMESTAMP
  • UTC_DATE
  • UTC_TIME
  • UTC_TIMESTAMP
  • WEEK
  • WEEKDAY
  • WEEKOFYEAR
  • YEAR
  • YEARWEEK
• Encryption Functions
  • AES_DECRYPT
  • AES_ENCRYPT
  • COMPRESS
  • MD5
  • RANDOM_BYTES
  • SHA1
  • SHA2
  • STATEMENT_DIGEST
  • STATEMENT_DIGEST_TEXT
  • UNCOMPRESS
  • UNCOMPRESSED_LENGTH
  • VALIDATE_PASSWORD_STRENGTH
• Enterprise Encryption Functions
  • ASYMMETRIC_DECRYPT
  • ASYMMETRIC_DERIVE
  • ASYMMETRIC_ENCRYPT
  • ASYMMETRIC_SIGN
  • ASYMMETRIC_VERIFY
  • CREATE_ASYMMETRIC_PRIV_KEY
  • CREATE_ASYMMETRIC_PUB_KEY
  • CREATE_DH_PARAMETERS
  • CREATE_DIGEST
• Flow Control Functions
  • CASE OPERATOR
  • IF FUNCTION
  • IFNULL
  • NULLIF
• GROUP BY Functions and Modifiers
• GTID
  • GTID_SUBSET
  • GTID_SUBTRACT
  • WAIT_FOR_EXECUTED_GTID_SET
  • WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS
• Information Functions
  • BENCHMARK
  • CHARSET
  • COERCIBILITY
  • COLLATION
  • CONNECTION_ID
  • CURRENT_ROLE
  • CURRENT_USER
  • DATABASE
  • FOUND_ROWS
  • ICU_VERSION
  • LAST_INSERT_ID
  • ROLES_GRAPHML
  • ROW_COUNT
  • SCHEMA
  • SESSION_USER
  • SYSTEM_USER
  • USER
  • VERSION
• Internal Functions
  • CAN_ACCESS_COLUMN
  • CAN_ACCESS_DATABASE
  • CAN_ACCESS_TABLE
  • CAN_ACCESS_USER
  • CAN_ACCESS_VIEW
  • GET_DD_COLUMN_PRIVILEGES
  • GET_DD_CREATE_OPTIONS
  • GET_DD_INDEX_SUB_PART_LENGTH
  • INTERNAL_AUTO_INCREMENT
  • INTERNAL_AVG_ROW_LENGTH
  • INTERNAL_CHECKSUM
  • INTERNAL_CHECK_TIME
  • INTERNAL_DATA_FREE
  • INTERNAL_DATA_LENGTH
  • INTERNAL_DD_CHAR_LENGTH
  • INTERNAL_GET_COMMENT_OR_ERROR
  • INTERNAL_GET_ENABLED_ROLE_JSON
  • INTERNAL_GET_HOSTNAME
  • INTERNAL_GET_USERNAME
  • INTERNAL_GET_VIEW_WARNING_OR_ERROR
  • INTERNAL_INDEX_COLUMN_CARDINALITY
  • INTERNAL_INDEX_LENGTH
  • INTERNAL_IS_ENABLED_ROLE
  • INTERNAL_IS_MANDATORY_ROLE
  • INTERNAL_KEYS_DISABLED
  • INTERNAL_MAX_DATA_LENGTH
  • INTERNAL_TABLE_ROWS
  • INTERNAL_UPDATE_TIME
  • IS_VISIBLE_DD_OBJECT
• Locking Functions
  • GET_LOCK
  • IS_FREE_LOCK
  • IS_USED_LOCK
  • RELEASE_ALL_LOCKS
  • RELEASE_LOCK
• Logical Operators
  • !
  • AND
  • ASSIGN-EQUAL
  • ASSIGN-VALUE
  • OR
  • XOR
• Miscellaneous Functions
  • ANY_VALUE
  • BIN_TO_UUID
  • DEFAULT
  • GROUPING
  • INET6_ATON
  • INET6_NTOA
  • INET_ATON
  • INET_NTOA
  • IS_IPV4
  • IS_IPV4_COMPAT
  • IS_IPV4_MAPPED
  • IS_IPV6
  • IS_UUID
  • MASTER_POS_WAIT
  • NAME_CONST
  • SLEEP
  • SOURCE_POS_WAIT
  • UUID
  • UUID_SHORT
  • UUID_TO_BIN
  • VALUES
• Numeric Functions
  • %
  • *
  • +
  • - BINARY
  • - UNARY
  • /
  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATAN2
  • CEIL
  • CEILING
  • CONV
  • COS
  • COT
  • CRC32
  • DEGREES
  • DIV
  • EXP
  • FLOOR
  • LN
  • LOG
  • LOG10
  • LOG2
  • MOD
  • PI
  • POW
  • POWER
  • RADIANS
  • RAND
  • ROUND
  • SIGN
  • SIN
  • SQRT
  • TAN
  • TRUNCATE
• Performance Schema Functions
  • FORMAT_BYTES
  • FORMAT_PICO_TIME
  • PS_CURRENT_THREAD_ID
  • PS_THREAD_ID
• Spatial Functions
  • Geometry Constructors
  • GEOMCOLLECTION
  • GEOMETRYCOLLECTION
  • LINESTRING
  • MULTILINESTRING
  • MULTIPOINT
  • MULTIPOLYGON
  • POINT
  • POLYGON
  • Geometry Property Functions
  • ST_DIMENSION
  • ST_ENVELOPE
  • ST_GEOMETRYTYPE
  • ST_ISEMPTY
  • ST_ISSIMPLE
  • ST_SRID
  • Geometry Relation Functions
  • ST_CONTAINS
  • ST_CROSSES
  • ST_DISJOINT
  • ST_DISTANCE
  • ST_EQUALS
  • ST_FRECHETDISTANCE
  • ST_HAUSDORFFDISTANCE
  • ST_INTERSECTS
  • ST_OVERLAPS
  • ST_TOUCHES
  • ST_WITHIN
  • GeometryCollection Property Functions
  • ST_BUFFER
  • ST_BUFFER_STRATEGY
  • ST_CONVEXHULL
  • ST_DIFFERENCE
  • ST_GEOMETRYN
  • ST_INTERSECTION
  • ST_LINEINTERPOLATEPOINT
  • ST_LINEINTERPOLATEPOINTS
  • ST_NUMGEOMETRIES
  • ST_POINTATDISTANCE
  • ST_SYMDIFFERENCE
  • ST_TRANSFORM
  • ST_UNION
  • LineString Property Functions
  • ST_ENDPOINT
  • ST_ISCLOSED
  • ST_LENGTH
  • ST_NUMPOINTS
  • ST_POINTN
  • ST_STARTPOINT
  • MBR Functions
  • ->
  • ->>
  • JSON_ARRAY
  • JSON_ARRAY_APPEND
  • JSON_ARRAY_INSERT
  • JSON_CONTAINS
  • JSON_CONTAINS_PATH
  • JSON_DEPTH
  • JSON_EXTRACT
  • JSON_INSERT
  • JSON_KEYS
  • JSON_LENGTH
  • JSON_MERGE
  • JSON_MERGE_PATCH()
  • JSON_MERGE_PRESERVE()
  • JSON_OBJECT
  • JSON_OVERLAPS
  • JSON_PRETTY
  • JSON_QUOTE
  • JSON_REMOVE
  • JSON_REPLACE
  • JSON_SCHEMA_VALID
  • JSON_SCHEMA_VALIDATION_REPORT
  • JSON_SEARCH
  • JSON_SET
  • JSON_STORAGE_FREE
  • JSON_STORAGE_SIZE
  • JSON_TABLE
  • JSON_TYPE
  • JSON_UNQUOTE
  • JSON_VALID
  • JSON_VALUE
  • MBRCONTAINS
  • MBRCOVEREDBY
  • MBRCOVERS
  • MBRDISJOINT
  • MBREQUALS
  • MBRINTERSECTS
  • MBROVERLAPS
  • MBRTOUCHES
  • MBRWITHIN
  • MEMBER OF
  • ST_ASGEOJSON
  • ST_COLLECT
  • ST_DISTANCE_SPHERE
  • ST_GEOHASH
  • ST_GEOMFROMGEOJSON
  • ST_ISVALID
  • ST_LATFROMGEOHASH
  • ST_LONGFROMGEOHASH
  • ST_MAKEENVELOPE
  • ST_POINTFROMGEOHASH
  • ST_SIMPLIFY
  • ST_VALIDATE
  • Point Property Functions
  • ST_LATITUDE
  • ST_LONGITUDE
  • ST_X
  • ST_Y
  • Polygon Property Functions
  • ST_AREA
  • ST_CENTROID
  • ST_EXTERIORRING
  • ST_INTERIORRINGN
  • ST_NUMINTERIORRINGS
  • WKB Functions
  • ST_ASBINARY
  • ST_ASTEXT
  • ST_GEOMCOLLFROMWKB
  • ST_GEOMFROMWKB
  • ST_LINEFROMWKB
  • ST_MLINEFROMWKB
  • ST_MPOINTFROMWKB
  • ST_MPOLYFROMWKB
  • ST_POINTFROMWKB
  • ST_POLYFROMWKB
  • ST_SWAPXY
  • WKT Functions
  • ST_GEOMCOLLFROMTEXT
  • ST_GEOMFROMTEXT
  • ST_LINEFROMTEXT
  • ST_MLINEFROMTEXT
  • ST_MPOINTFROMTEXT
  • ST_MPOLYFROMTEXT
  • ST_POINTFROMTEXT
  • ST_POLYFROMTEXT
• String Functions
  • ASCII
  • BIN
  • BIT_LENGTH
  • CHAR FUNCTION
  • CHARACTER_LENGTH
  • CHAR_LENGTH
  • CONCAT
  • CONCAT_WS
  • ELT
  • EXPORT_SET
  • FIELD
  • FIND_IN_SET
  • FORMAT
  • FROM_BASE64
  • HEX
  • INSERT FUNCTION
  • INSTR
  • LCASE
  • LEFT
  • LENGTH
  • LIKE
  • LOAD_FILE
  • LOCATE
  • LOWER
  • LPAD
  • LTRIM
  • MAKE_SET
  • MATCH AGAINST
  • MID
  • NOT LIKE
  • NOT REGEXP
  • OCT
  • OCTET_LENGTH
  • ORD
  • POSITION
  • QUOTE
  • REGEXP
  • REGEXP_INSTR
  • REGEXP_LIKE
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • REPEAT FUNCTION
  • REPLACE FUNCTION
  • REVERSE
  • RIGHT
  • RPAD
  • RTRIM
  • SOUNDEX
  • SOUNDS LIKE
  • SPACE
  • STRCMP
  • SUBSTR
  • SUBSTRING
  • SUBSTRING_INDEX
  • TO_BASE64
  • TRIM
  • UCASE
  • UNHEX
  • UPPER
  • WEIGHT_STRING
• Window Functions
  • CUME_DIST
  • DENSE_RANK
  • FIRST_VALUE
  • LAG
  • LAST_VALUE
  • LEAD
  • NTH_VALUE
  • NTILE
  • PERCENT_RANK
  • RANK
  • ROW_NUMBER
• XML
  • EXTRACTVALUE
  • UPDATEXML
• Geographic Features
• GEOMETRY HIERARCHY
• SPATIAL COLUMNS
• SPATIAL INDEXES
• MBR
• WKT
• Help Metadata
• HELP_DATE
• HELP_VERSION
• Language Structure
• FALSE
• TRUE
• Loadable Functions
• CREATE FUNCTION LOADABLE FUNCTION
• DROP FUNCTION LOADABLE FUNCTION
• Plugins
• Prepared Statements
• DEALLOCATE PREPARE
• DROP PREPARE
• EXECUTE STATEMENT
• PREPARE
• Replication Statements
• CHANGE MASTER TO
• CHANGE REPLICATION FILTER
• CHANGE REPLICATION SOURCE TO
• PURGE BINARY LOGS
• PURGE MASTER LOGS
• RESET MASTER
• RESET REPLICA
• RESET SLAVE
• SET SQL_LOG_BIN
• START REPLICA
• START SLAVE
• STOP REPLICA
• STOP SLAVE
• Storage Engines
• Table Maintenance
• ANALYZE TABLE
• CHECK TABLE
• CHECKSUM TABLE
• OPTIMIZE TABLE
• REPAIR TABLE
• Transactions
• BEGIN
• COMMIT
• LOCK INSTANCE FOR BACKUP
• LOCK TABLES
• RELEASE SAVEPOINT
• ROLLBACK
• ROLLBACK TO SAVEPOINT
• SAVEPOINT
• SET TRANSACTION
• START TRANSACTION
• XA
• Utility
• DESC
• DESCRIBE
• EXPLAIN
• HELP STATEMENT
• USE

#20 MySQL Error: Packet sequence number wrong

多线程执行 MySQL 查询的时候会遇到 Packet sequence number wrong 错误。

Traceback (most recent call last):
  File "/tmp/db.py", line 167, in _execute
    rowcount = cur.execute(sql, args)
  File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 163, in execute
    result = self._query(query)
  File "/usr/local/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query
    conn.query(q)
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 505, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 724, in _read_query_result
    result.read()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1076, in read
    self._read_result_packet(first_packet)
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1146, in _read_result_packet
    self._read_rowdata_packet()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 1180, in _read_rowdata_packet
    packet = self.connection._read_packet()
  File "/usr/local/lib/python2.7/site-packages/pymysql/connections.py", line 660, in _read_packet
    % (packet_number, self._next_seq_id))
InternalError: Packet sequence number wrong - got 51 expected 178

多个查询共用连接，并发查询的时候，现在的 MySQL 网络传输机制无法判断这个返回属于哪个查询。
所以每个线程应该使用不同的连接，或者在 execute 之前加一个锁。

#19 MySQL 字段类型 varchar 和 text 的差异

一次数据库表结构调整，引起了我对 MySQL 字段类型 TEXT 和 VARCHAR 的思考。

#18 MySQL 数据类型总结

数值

• bit [1 - 64]

• tinyint 1

• smallint 2
• mediumint 3
• int 4

• bigint 8

• float 4 浮点（单精度）

• double 8 浮点（双精度）
• decimal(m, d) max(m, d) + 2 定点

别名 Synonyms：

• integer -> int
• bool / boolean -> tinyint(1)
• dec / numeric / fixed -> decimal
• double precision / real -> double

PS: 如果开启 REAL_AS_FLOAT 模式，REAL 就变成了 FLOAT 的别名。

字符串/文本

• char

• varchar

• tinytext

• text
• mediumtext

• longtext

• JSON MySQL 5.7 加入

二进制

• binary

• varbinary

• tinyblob

• blob
• mediumblob
• longblob

时间

1. 用字符串（varchar）表示时间，除了失去数据库这一层的数据类型保证之外，还会在时间比较运算场景下，失去索引命中可能。
2. 用整型数 bigint（8 字节）表示时间，可以扩大 timestamp 时间范围，但要自己维护数据（其实还是比较好维护的）。
3. 带时区的时间：timestamp
4. 带精度的时间：time、datetime、timestamp，精度最大为 6，表示毫秒。比如：timestamp(6)

其他

• enum
• set
• Geometry 系列

货币

1. 以分为单位 Integer
2. Decimal，不要用 float，double

参考资料与拓展阅读

• https://dev.mysql.com/doc/refman/5.7/en/data-types.html

#17 Gitlab 从 12.1 版本开始将不再支持 MySQL

看到新闻，Gitlab 从 12.1 版本开始将不再支持 MySQL，理由是：

• 无法支持嵌套分组查询（详情）
• 必须使用黑科技来提升 MySQL 对列的限制，这将导致 MySQL 拒绝存储数据
• MySQL 无法添加 TEXT 类型字段的长度限制
• MySQL 不支持分区索引
• 还有类似 Geo

Gitlab 支持的另一个数据库是 PostgreSQL，意思是 PostgreSQL 不存在上面的问题。
这也可以看作是二者的部分区别吧！

值得研究研究。

#16 MySQL 时间默认值 "0000-00-00 00:00:00" 不合法的问题

Invalid default value for 'update_time'

#15 一道有趣的 MySQL 小题目

在开源中国每日动弹中看到这么一道题目，蛮有意思，还学到了一个新的 MySQL 语法：CTE。