MySQL Administration

注意：

此文档对应的 MySQL 版本为 8.0.32 社区版。

Overview

MySQL 是最流行的开源数据库管理系统。

Installing

Supported Platforms

		8.0	5.7
Operating System	Architecture
Oracle Linux / Red Hat / CentOS
Oracle Linux 9 / Red Hat Enterprise Linux 9	x86_64, ARM 64	•
Oracle Linux 8 / Red Hat Enterprise Linux 8 / CentOS 8	x86_64, ARM 64	•
Oracle Linux 7 / Red Hat Enterprise Linux 7 / CentOS 7	ARM 64	•
Oracle Linux 7 / Red Hat Enterprise Linux 7 / CentOS 7	x86_64	•	•
Oracle Linux 6 / Red Hat Enterprise Linux 6 / CentOS 6	x86_32, x86_64	•	•
Oracle Solaris
Solaris 11 (Update 4+)	SPARC_64	•	•
Canonical
Ubuntu 22.04 LTS	x86_64	•
Ubuntu 20.04 LTS	x86_64	•
Ubuntu 18.04 LTS	x86_32, x86_64	•	•
SUSE
SUSE Enterprise Linux 15 / OpenSUSE 15 (15.3)	x86_64	•
SUSE Enterprise Linux 12 (12.5+)	x86_64	•	•
Debian
Debian GNU/Linux 11	x86_64	•
Debian GNU/Linux 10	x86_64	•	•
Microsoft Windows Server
Microsoft Windows 2022 Server	x86_64	•
Microsoft Windows 2019 Server	x86_64	•
Microsoft Windows 2016 Server	x86_64	•	•
Microsoft Windows 2012 Server R2	x86_64	•	•
Microsoft Windows
Microsoft Windows 11	x86_64	•
Microsoft Windows 10	x86_64	•	•
Apple
macOS 13	x86_64, ARM_64	•
macOS 12	x86_64, ARM_64	•
macOS 11	x86_64, ARM_64	•
Various Linux
Generic Linux (tar format)	x86_32, x86_64, glibc 2.12, libstdc++ 4.4	•	•
Yum Repo	•	•
APT Repo	•	•
SUSE Repo	•	•

How to Get MySQL

在 MySQL Community Downloads 页面选择 MySQL Community Server 进入 MySQL 下载页面。选择操作系统和版本，下载对应的安装包。

MySQL 版本：MySQL Community Server 8.0.32

操作系统：Red Hat Enterprise Linux / Oracle Linux

操作系统版本：Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit)

Download Packages: RPM Bundle

MD5: 7743c9f29acb3344729e0fbc6ba72013

MySQL Community Edition RPM Bundle 包含以下安装包：

Package Name	Summary
mysql-community-client	MySQL client applications and tools
mysql-community-common	Common files for server and client libraries
mysql-community-devel	Development header files and libraries for MySQL database client applications
mysql-community-embedded-compat	MySQL server as an embedded library with compatibility for applications using version 18 of the library
mysql-community-libs	Shared libraries for MySQL database client applications
mysql-community-libs-compat	Shared compatibility libraries for previous MySQL installations
mysql-community-server	Database server and related tools
mysql-community-server-debug	Debug server and plugin binaries
mysql-community-test	Test suite for the MySQL server
mysql-community	The source code RPM looks similar to mysql-community-8.0.32-1.el7.src.rpm, depending on selected OS

Verifying Package Integrity Using MD5 Checksums

下载完成后，使用 MD5 校验安装包。

[root@mysql ~]# md5sum mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar 
7743c9f29acb3344729e0fbc6ba72013  mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar

Installing MySQL on Linux

这里展示了如何在 Linux 环境下快速安装 MySQL 数据库。

Planning

（1）系统信息

序号	项目	信息
1	OS Version	CentOS 7.8-x86_64
2	Memory	2G
3	Swap	2G
4	Disk	40G
5	Hostname	mysql
6	IP	192.168.8.141

（2）软件信息

序号	名称	版本	安装包
1	MySQL	8.0.32	mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar

Preparing

（1）操作系统版本

[root@mysql ~]# cat /etc/redhat-release
CentOS Linux release 7.8.2003 (Core)

（2）关闭防火墙

[root@mysql ~]# systemctl stop firewalld
[root@mysql ~]# systemctl disable firewalld

（3）关闭 SELinux

修改文件 /etc/selinux/config，将 SELINUX=enforcing 修改为 SELINUX=disabled

[root@mysql ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

修改完成后重启主机

[root@mysql ~]# init 6

（4）配置地址映射

[root@mysql ~]# echo "192.168.8.141   mysql" >> /etc/hosts
[root@mysql ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.8.141   mysql
[root@mysql ~]# ping mysql

（5）卸载 mariadb

[root@mysql ~]# yum remove mariadb mariadb-server mariadb-libs

Installing

（1）解压安装包

[root@mysql ~]# tar -xvf mysql-8.0.32-1.el7.x86_64.rpm-bundle.tar -C /tmp/
mysql-community-client-8.0.32-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm
mysql-community-common-8.0.32-1.el7.x86_64.rpm
mysql-community-debuginfo-8.0.32-1.el7.x86_64.rpm
mysql-community-devel-8.0.32-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.32-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm
mysql-community-libs-8.0.32-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.32-1.el7.x86_64.rpm
mysql-community-server-8.0.32-1.el7.x86_64.rpm
mysql-community-server-debug-8.0.32-1.el7.x86_64.rpm
mysql-community-test-8.0.32-1.el7.x86_64.rpm

（2）安装

[root@mysql ~]# cd /tmp/
[root@mysql tmp]# yum localinstall --nogpgcheck mysql-community-libs-8.0.32-1.el7.x86_64.rpm mysql-community-common-8.0.32-1.el7.x86_64.rpm mysql-community-client-8.0.32-1.el7.x86_64.rpm mysql-community-client-plugins-8.0.32-1.el7.x86_64.rpm mysql-community-icu-data-files-8.0.32-1.el7.x86_64.rpm mysql-community-server-8.0.32-1.el7.x86_64.rpm mysql-community-libs-compat-8.0.32-1.el7.x86_64.rpm mysql-community-devel-8.0.32-1.el7.x86_64.rpm

安装后文件所在路径：

Files or Resources	Location
Client programs and scripts	/usr/bin
mysqld server	/usr/sbin
Configuration file	/etc/my.cnf
Data directory	/var/lib/mysql
Error log file	For RHEL, Oracle Linux, CentOS or Fedora platforms: /var/log/mysqld.log For SLES: /var/log/mysql/mysqld.log
Value of secure_file_priv	/var/lib/mysql-files
System V init script	For RHEL, Oracle Linux, CentOS or Fedora platforms: /etc/init.d/mysqld For SLES: /etc/init.d/mysql
Systemd service	For RHEL, Oracle Linux, CentOS or Fedora platforms: mysqld For SLES: mysql
Pid file	/var/run/mysql/mysqld.pid
Socket	/var/lib/mysql/mysql.sock
Keyring directory	/var/lib/mysql-keyring
Unix manual pages	/usr/share/man
Include (header) files	/usr/include/mysql
Libraries	/usr/lib/mysql
Miscellaneous support files (for example, error messages, and character set files)	/usr/share/mysql

（3）创建目录

[root@mysql ~]# mkdir -p /data/mysql
[root@mysql ~]# chown -R mysql:mysql /data/mysql/
[root@mysql ~]# touch /var/log/mysql.slow
[root@mysql ~]# chown mysql:mysql /var/log/mysql.slow

（4）修改参数文件

[root@mysql ~]# vi /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log_error=/var/log/mysqld.log
pid_file=/var/run/mysqld/mysqld.pid
default_time_zone = '+8:00'

server_id=8141
#log_bin=/data/mysql/mysql-bin
binlog_format=row
binlog_cache_size=8M
#expire_logs_days=5
binlog_expire_logs_seconds=432000
enforce_gtid_consistency=on
gtid_mode=on
#sync_master_info=1
#master_info_repository=table
#relay_log_info_repository=table
relay_log_recovery=1
#replica_parallel_type=LOGICAL_CLOCK
#slave_parallel_workers=16

skip_name_resolve=1
character_set_server=utf8mb4
innodb_file_per_table=1
#innodb_log_file_size=2G
#innodb_log_files_in_group = 3
innodb_redo_log_capacity=8G
innodb_max_undo_log_size = 4G
innodb_buffer_pool_size=1G
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_open_files = 65535
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:10G
max_connections=5000
max_connect_errors=100000
thread_cache_size=512
slow_query_log=1
slow_query_log_file=/var/log/mysql.slow
long_query_time=10
log_short_format
explicit_defaults_for_timestamp=1
log_timestamps=SYSTEM
#interactive_timeout = 600
#wait_timeout = 600
lower_case_table_names=1
local_infile=ON

sort_buffer_size = 4M
join_buffer_size = 4M
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M

[client]
socket=/data/mysql/mysql.sock

[mysql]
#prompt=(\\u@\\h) [\\d]> \\
prompt=[\\d]> \\
no-auto-rehash

（5）修改文件打开限制

[root@mysql ~]# vi /etc/security/limits.conf
* hard nofile 65535
* soft nofile 65535
[root@mysql ~]# vi /usr/lib/systemd/system/mysqld.service
LimitNOFILE = 65535

（6）启动 MySQL

[root@mysql ~]# systemctl daemon-reload
[root@mysql ~]# systemctl start mysqld
[root@mysql ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2023-02-07 13:39:12 CST; 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3909 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 4016 (mysqld)
   Status: "Server is operational"
    Tasks: 42
   Memory: 545.9M
   CGroup: /system.slice/mysqld.service
           └─4016 /usr/sbin/mysqld

Feb 07 13:39:04 mysql systemd[1]: Starting MySQL Server...
Feb 07 13:39:12 mysql systemd[1]: Started MySQL Server.

可以看到 MySQL 已经启动，且配置为开机自动启动。

（7）从日志中获取临时密码

[root@mysql ~]# grep 'temporary password' /var/log/mysqld.log

（8）修改密码

[root@mysql ~]# mysql -uroot -p
[(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';

（9）测试

[root@mysql ~]# mysqladmin -u root -p version
Enter password: 
mysqladmin  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          8.0.32
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /data/mysql/mysql.sock
Uptime:                 32 min 25 sec

Threads: 2  Questions: 7  Slow queries: 0  Opens: 130  Flush tables: 3  Open tables: 46  Queries per second avg: 0.003

[root@mysql ~]# mysqlshow -u root -p
Enter password: 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

[root@mysql ~]# mysqlshow -u root -p mysql
Enter password: 
Database: mysql
+------------------------------------------------------+
|                        Tables                        |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| ndb_binlog_index                                     |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+

[root@mysql ~]# mysql -u root -p -e "SELECT User, Host, plugin FROM mysql.user" mysql
Enter password: 
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

Tutorial

使用客户端程序 mysql 管理 MySQL，类似于 Oracle 的 SQL*Plus。

查看 mysql 命令选项：

[root@mysql ~]# mysql --help

Connecting to and Disconnecting from the Server

连接到 MySQL Server：

[root@mysql ~]# mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[(none)]>

其中：

• -h：指定主机名，连接本地的 MySQL Server，则可省略
• -u：指定用户名
• -p：指定密码

断开连接：

[(none)]> quit
Bye

Entering Queries

使用 mysql 连接到 MySQL Server 后，就可以使用 SQL 进行查询。

例子：查询版本及当前时间

[(none)]> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 8.0.32    | 2023-02-07   |
+-----------+--------------+
1 row in set (0.00 sec)

注意：

• SQL 语句以分号结束。
• SQL 语句不区分大小写。

例子：使用 SQL 进行简单计算

[(none)]> SELECT SIN(PI()/4), (4+1)*5;
+--------------------+---------+
| SIN(PI()/4)        | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865475 |      25 |
+--------------------+---------+
1 row in set (0.01 sec)

例子：在一行中执行多个语句

[(none)]> SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

+---------------------+
| NOW()               |
+---------------------+
| 2023-02-07 15:29:48 |
+---------------------+
1 row in set (0.00 sec)

例子：使用 \c 放弃执行 SQL 语句

[(none)]> SELECT
    -> USER()
    -> \c
[(none)]> 

各种提示符：

Prompt	Meaning
mysql>	Ready for new query
->	Waiting for next line of multiple-line query
'>	Waiting for next line, waiting for completion of a string that began with a single quote (')
">	Waiting for next line, waiting for completion of a string that began with a double quote (")
`>	Waiting for next line, waiting for completion of an identifier that began with a backtick (`)
/*>	Waiting for next line, waiting for completion of a comment that began with /*

例子：字符串忘记结束的引号，换行后使用 '\c 取消执行

[(none)]> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
    '> '\c
[(none)]> 

Creating and Using a Database

例子：列出数据库

[(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

例子：选择数据库

[(none)]> USE mysql;
Database changed
[mysql]> 

Creating and Selecting a Database

例子：创建数据库

[(none)]> CREATE DATABASE menagerie;
Query OK, 1 row affected (0.01 sec)

注意：

在 Linux 环境中，数据库名和表名是区分大小写的，建议统一使用小写。

例子：选择数据库作为当前数据库

[(none)]> USE menagerie;
Database changed
[menagerie]> 

例子：连接 MySQL Server 时指定数据库

[root@mysql ~]# mysql -h localhost -u root -p menagerie
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


[menagerie]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+
1 row in set (0.00 sec)

Creating a Table

例子：查看当前数据库下的表

[menagerie]> SHOW TABLES;
Empty set (0.01 sec)

例子 ：创建表并查看表结构

[menagerie]> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
    ->       species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.01 sec)

[menagerie]> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet                 |
+---------------------+
1 row in set (0.01 sec)


[menagerie]> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Loading Data into a Table

创建数据，数据以 TAB 分割，空值以 \N 表示。

[root@mysql ~]# vi pet.txt
Fluffy	Harold	cat	f	1993-02-04	\N
Claws	Gwen	cat	m	1994-03-17	\N
Buffy	Harold	dog	f	1989-05-13	\N
Fang	Benny	dog	m	1990-08-27	\N
Bowser	Diane	dog	m	1979-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11	\N
Whistler	Gwen	bird	\N	1997-12-09	\N
Slim	Benny	snake	m	1996-04-29	\N

例子：使用 LOAD DATA 加载数据

[root@mysql ~]# mysql -h localhost -u root -p menagerie --local-infile
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[menagerie]> LOAD DATA LOCAL INFILE '/root/pet.txt' INTO TABLE pet;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

例子：使用 INSERT 插入数据

[menagerie]> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.01 sec)

字符串和日期需要加上单引号，空值使用 NULL。

Retrieving Information from a Table

使用 SELECT 语句查询表数据。

语法：

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;

Selecting All Data

使用 * 查询表所有字段数据。

例子：查询所有字段数据

[menagerie]> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

Selecting Particular Rows

使用 WHERE 子句指定查询条件。

例子：查询名称为 Bowser 的记录

[menagerie]> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

例子：查询出生日期大于等于 1998-1-1 的记录

[menagerie]> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)

例子：查询类别为 dog 且性别为 f 的记录

[menagerie]> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
1 row in set (0.01 sec)

例子：查询类别为 snake 或者 bird 的记录

[menagerie]> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)

例子：查询类别为 cat 且性别为 m 或者类别为 dog 且性别为 f 的记录

[menagerie]> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
    ->       OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

Selecting Particular Columns

例子：查询指定字段的所有记录

[menagerie]> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1979-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)

例子：查询指定字段的所有记录，并去掉重复值

[menagerie]> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Benny  |
| Diane  |
+--------+
4 rows in set (0.01 sec)

例子：查询指定字段，并指定查询条件

[menagerie]> SELECT name, species, birth FROM pet
    ->       WHERE species = 'dog' OR species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1979-08-31 |
+--------+---------+------------+
5 rows in set (0.00 sec)

Sorting Rows

使用 ORDER BY 子句进行排序。

例子：查询结果按照 birth 升序排序

[menagerie]> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Bowser   | 1979-08-31 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+
9 rows in set (0.00 sec)

例子：使用 DESC 将查询结果按照 birth 降序排序

[menagerie]> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Buffy    | 1989-05-13 |
| Bowser   | 1979-08-31 |
+----------+------------+
9 rows in set (0.00 sec)

例子：查询结果按照 species 升序， birth 降序排序

[menagerie]> SELECT name, species, birth FROM pet
    ->       ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Buffy    | dog     | 1989-05-13 |
| Bowser   | dog     | 1979-08-31 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+
9 rows in set (0.00 sec)

升序排序时，NULL 排在最前面；降序排序时，NULL 排在最后面。

Date Calculations

例子：通过计算获取年龄

[menagerie]> SELECT name, birth, CURDATE(),
    ->       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    ->       FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2023-02-08 |   30 |
| Claws    | 1994-03-17 | 2023-02-08 |   28 |
| Buffy    | 1989-05-13 | 2023-02-08 |   33 |
| Fang     | 1990-08-27 | 2023-02-08 |   32 |
| Bowser   | 1979-08-31 | 2023-02-08 |   43 |
| Chirpy   | 1998-09-11 | 2023-02-08 |   24 |
| Whistler | 1997-12-09 | 2023-02-08 |   25 |
| Slim     | 1996-04-29 | 2023-02-08 |   26 |
| Puffball | 1999-03-30 | 2023-02-08 |   23 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

例子：通过计算获取年龄并按照名称排序

[menagerie]> SELECT name, birth, CURDATE(),
    ->       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    ->       FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1979-08-31 | 2023-02-08 |   43 |
| Buffy    | 1989-05-13 | 2023-02-08 |   33 |
| Chirpy   | 1998-09-11 | 2023-02-08 |   24 |
| Claws    | 1994-03-17 | 2023-02-08 |   28 |
| Fang     | 1990-08-27 | 2023-02-08 |   32 |
| Fluffy   | 1993-02-04 | 2023-02-08 |   30 |
| Puffball | 1999-03-30 | 2023-02-08 |   23 |
| Slim     | 1996-04-29 | 2023-02-08 |   26 |
| Whistler | 1997-12-09 | 2023-02-08 |   25 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

例子：通过计算获取年龄并按照年龄排序

[menagerie]> SELECT name, birth, CURDATE(),
    ->       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    ->       FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Puffball | 1999-03-30 | 2023-02-08 |   23 |
| Chirpy   | 1998-09-11 | 2023-02-08 |   24 |
| Whistler | 1997-12-09 | 2023-02-08 |   25 |
| Slim     | 1996-04-29 | 2023-02-08 |   26 |
| Claws    | 1994-03-17 | 2023-02-08 |   28 |
| Fluffy   | 1993-02-04 | 2023-02-08 |   30 |
| Fang     | 1990-08-27 | 2023-02-08 |   32 |
| Buffy    | 1989-05-13 | 2023-02-08 |   33 |
| Bowser   | 1979-08-31 | 2023-02-08 |   43 |
+----------+------------+------------+------+
9 rows in set (0.01 sec)

例子：通过计算获取死亡年龄

[menagerie]> SELECT name, birth, death,
    ->       TIMESTAMPDIFF(YEAR,birth,death) AS age
    ->       FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1979-08-31 | 1995-07-29 |   15 |
+--------+------------+------------+------+
1 row in set (0.00 sec)

注意

空值 NULL 的判断需要使用 IS 或者 IS NOT。

例子：获取出生月份信息

[menagerie]> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1979-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+
9 rows in set (0.00 sec)

例子：查询指定月份记录

[menagerie]> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)

例子：查询下个月生日的记录

[menagerie]> SELECT name, birth FROM pet
    ->       WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
+----------+------------+
| name     | birth      |
+----------+------------+
| Claws    | 1994-03-17 |
| Puffball | 1999-03-30 |
+----------+------------+
2 rows in set (0.00 sec)

[menagerie]> SELECT name, birth FROM pet
    ->       WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
+----------+------------+
| name     | birth      |
+----------+------------+
| Claws    | 1994-03-17 |
| Puffball | 1999-03-30 |
+----------+------------+
2 rows in set (0.00 sec)

例子：查看告警信息

[menagerie]> SELECT '2018-10-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2018-11-01                    |
+-------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT '2018-10-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2018-10-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL                          |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

[menagerie]> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2018-10-32' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

Working with NULL Values

例子：空值 NULL 的判断需要使用 IS NULL 或者 IS NOT NULL

[menagerie]> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
1 row in set (0.00 sec)

• 和 NULL 进行运算的结果也是 NULL。
• GROUP BY 语句中，NULL 为一个分组。
• 升序排序，NULL 在最前面；降序排序，NULL 在最后面。
• NULL 既不是 0，也不是空字符串 ''。

[menagerie]> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+
1 row in set (0.00 sec)

Pattern Matching

• 使用 LIKE 或者 NOT LIKE 进行模式匹配。
• 使用 _ 匹配单个字符。
• 使用 % 匹配任意字符。
• 使用 REGEXP_LIKE() 进行正则匹配。

例子：查找姓名以 b 开头的记录

[menagerie]> SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.02 sec)

[menagerie]> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.03 sec)

强制匹配大小写

[menagerie]> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');
Empty set (0.00 sec)

例子：查找姓名以 fy 结尾的记录

[menagerie]> SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

[menagerie]> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.03 sec)

例子：查找姓名包含 w 的记录

[menagerie]> SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

[menagerie]> SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)

例子：查找姓名为 5 个字符的记录

[menagerie]> SELECT * FROM pet WHERE name LIKE '_____';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

[menagerie]> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

[menagerie]> SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

Counting Rows

使用 COUNT() 计算记录数量，使用 GROUP BY 进行分组。

例子：查询宠物总数

[menagerie]> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
1 row in set (0.03 sec)

例子：查询每人的宠物数量

[menagerie]> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        2 |
| Gwen   |        3 |
| Benny  |        2 |
| Diane  |        2 |
+--------+----------+
4 rows in set (0.03 sec)

例子：查询每种宠物的数量

[menagerie]> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| cat     |        2 |
| dog     |        3 |
| bird    |        2 |
| snake   |        1 |
| hamster |        1 |
+---------+----------+
5 rows in set (0.01 sec)

例子：查询每种性别的数量

[menagerie]> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| f    |        4 |
| m    |        4 |
| NULL |        1 |
+------+----------+
3 rows in set (0.01 sec)

例子：查询每种宠物每种性别的数量

[menagerie]> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| bird    | f    |        1 |
| bird    | NULL |        1 |
| snake   | m    |        1 |
| hamster | f    |        1 |
+---------+------+----------+
8 rows in set (0.01 sec)

例子：根据查询条件过滤后进行分组

[menagerie]> SELECT species, sex, COUNT(*) FROM pet
    ->       WHERE species = 'dog' OR species = 'cat'
    ->       GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+
4 rows in set (0.00 sec)

[menagerie]> SELECT species, sex, COUNT(*) FROM pet
    ->       WHERE sex IS NOT NULL
    ->       GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| bird    | f    |        1 |
| snake   | m    |        1 |
| hamster | f    |        1 |
+---------+------+----------+
7 rows in set (0.00 sec)

Using More Than one Table

创建另一张表：

[menagerie]> CREATE TABLE event (name VARCHAR(20), date DATE,
    ->       type VARCHAR(15), remark VARCHAR(255));
Query OK, 0 rows affected (0.19 sec)

创建数据：

[root@mysql ~]# vi event.txt
Fluffy  1995-05-15      litter  4 kittens, 3 female, 1 male
Buffy   1993-06-23      litter  5 puppies, 2 female, 3 male
Buffy   1994-06-19      litter  3 puppies, 3 female
Chirpy  1999-03-21      vet     needed beak straightened
Slim    1997-08-03      vet     broken rib
Bowser  1991-10-12      kennel  NULL
Fang    1991-10-12      kennel  NULL
Fang    1998-08-28      birthday        Gave him a new chew toy
Claws   1998-03-17      birthday        Gave him a new flea collar
Whistler        1998-12-09      birthday        First birthday

加载数据到 MySQL：

[menagerie]> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
Query OK, 10 rows affected (0.03 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0


[menagerie]> SELECT * FROM event;
+----------+------------+----------+-----------------------------+
| name     | date       | type     | remark                      |
+----------+------------+----------+-----------------------------+
| Fluffy   | 1995-05-15 | litter   | 4 kittens, 3 female, 1 male |
| Buffy    | 1993-06-23 | litter   | 5 puppies, 2 female, 3 male |
| Buffy    | 1994-06-19 | litter   | 3 puppies, 3 female         |
| Chirpy   | 1999-03-21 | vet      | needed beak straightened    |
| Slim     | 1997-08-03 | vet      | broken rib                  |
| Bowser   | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1991-10-12 | kennel   | NULL                        |
| Fang     | 1998-08-28 | birthday | Gave him a new chew toy     |
| Claws    | 1998-03-17 | birthday | Gave him a new flea collar  |
| Whistler | 1998-12-09 | birthday | First birthday              |
+----------+------------+----------+-----------------------------+
10 rows in set (0.01 sec)

例子：查询宠物产仔的年龄

[menagerie]> SELECT pet.name,
    ->       TIMESTAMPDIFF(YEAR,birth,date) AS age,
    ->       remark
    ->       FROM pet INNER JOIN event
    ->         ON pet.name = event.name
    ->       WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    5 | 3 puppies, 3 female         |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
+--------+------+-----------------------------+
3 rows in set (0.01 sec)

例子：自关联

[menagerie]> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    ->       FROM pet AS p1 INNER JOIN pet AS p2
    ->         ON p1.species = p2.species
    ->         AND p1.sex = 'f' AND p1.death IS NULL
    ->         AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+
2 rows in set (0.00 sec)

Getting Information About Databases and Tables

例子：查看当前数据库

[menagerie]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+
1 row in set (0.00 sec)

例子：查看当前数据库的表

[menagerie]> SHOW TABLES;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+
2 rows in set (0.06 sec)

例子：查看表结构

[menagerie]> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

Using mysql in Batch Mode

语法：

$> mysql -h host -u user -p < batch-file
Enter password: ********

• 如果要忽略脚本中的错误，使用 --force 选项。
• 如果要获取交互模式的输出格式，使用 -t 选项。
• 如果要输出执行的语句，使用 -v 选项。

将执行输出重定向到文件：

$> mysql < batch-file > mysql.out

也可以在交互模式使用 source 或者 \. 执行脚本：

mysql> source filename;
mysql> \. filename

Examples of Common Queries

创建表并插入数据：

[menagerie]> CREATE TABLE shop (
    ->           article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    ->           dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    ->           price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    ->           PRIMARY KEY(article, dealer));
Query OK, 0 rows affected (0.05 sec)

[menagerie]> INSERT INTO shop VALUES
    ->           (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    ->           (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

[menagerie]> SELECT * FROM shop ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.01 sec)

The Maximum Value for a Column

例子：获取指定列最大值

[menagerie]> SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
|       4 |
+---------+
1 row in set (0.01 sec)

The Row Holding the Maximum of a Certain Column

例子：获取价格最高的记录

[menagerie]> SELECT article, dealer, price
    ->       FROM   shop
    ->       WHERE  price=(SELECT MAX(price) FROM shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.01 sec)

[menagerie]> SELECT s1.article, s1.dealer, s1.price
    ->       FROM shop s1
    ->       LEFT JOIN shop s2 ON s1.price < s2.price
    ->       WHERE s2.article IS NULL;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

[menagerie]> SELECT article, dealer, price
    ->       FROM shop
    ->       ORDER BY price DESC
    ->       LIMIT 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

Maximum of Column per Group

例子：查询每种商品的最高价格

[menagerie]> SELECT article, MAX(price) AS price
    ->       FROM   shop
    ->       GROUP BY article
    ->       ORDER BY article;
+---------+-------+
| article | price |
+---------+-------+
|       1 |  3.99 |
|       2 | 10.99 |
|       3 |  1.69 |
|       4 | 19.95 |
+---------+-------+
4 rows in set (0.01 sec)

The Rows Holding the Group-wise Maximum of a Certain Column

例子：查询每种商品最贵价格的经销商

[menagerie]> SELECT article, dealer, price
    ->       FROM   shop s1
    ->       WHERE  price=(SELECT MAX(s2.price)
    ->                     FROM shop s2
    ->                     WHERE s1.article = s2.article)
    ->       ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

以上使用的是效率不高的关联子查询，可以使用非关联子查询：

[menagerie]> SELECT s1.article, dealer, s1.price
    ->       FROM shop s1
    ->       JOIN (
    ->         SELECT article, MAX(price) AS price
    ->         FROM shop
    ->         GROUP BY article) AS s2
    ->         ON s1.article = s2.article AND s1.price = s2.price
    ->       ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

可以使用左连接：

[menagerie]> SELECT s1.article, s1.dealer, s1.price
    ->       FROM shop s1
    ->       LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
    ->       WHERE s2.article IS NULL
    ->       ORDER BY s1.article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

可以使用窗口函数：

[menagerie]> WITH s1 AS (
    ->          SELECT article, dealer, price,
    ->                 RANK() OVER (PARTITION BY article
    ->                                  ORDER BY price DESC
    ->                             ) AS `Rank`
    ->            FROM shop
    ->       )
    ->       SELECT article, dealer, price
    ->         FROM s1
    ->         WHERE `Rank` = 1
    ->       ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

Using User-Defined Variables

使用用户变量记录查询结果。

例子：查询价格最低和最高的记录

[menagerie]> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
|                   1.25 |                  19.95 |
+------------------------+------------------------+
1 row in set, 2 warnings (0.02 sec)

[menagerie]> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+
2 rows in set (0.00 sec)

Using Foreign Keys

MySQL 支持外键和外键约束。

创建父表和子表：

[menagerie]> CREATE TABLE parent (
    ->           id INT NOT NULL,
    ->           PRIMARY KEY (id)
    ->       ) ENGINE=INNODB;
Query OK, 0 rows affected (0.04 sec)

[menagerie]> CREATE TABLE child (
    ->           id INT,
    ->           parent_id INT,
    ->           INDEX par_ind (parent_id),
    ->           FOREIGN KEY (parent_id)
    ->               REFERENCES parent(id)
    ->       ) ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)

父表插入数据：

[menagerie]> INSERT INTO parent (id) VALUES (1);
Query OK, 1 row affected (0.00 sec)

[menagerie]> SELECT * FROM parent;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

子表插入数据：

[menagerie]> INSERT INTO child (id,parent_id) VALUES (1,1);
Query OK, 1 row affected (0.01 sec)

子表插入数据的 parent_id 在父表不存在则报错：

[menagerie]> INSERT INTO child (id,parent_id) VALUES(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`menagerie`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

删除被引用的父表数据时报错：

[menagerie]> DELETE FROM parent WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`menagerie`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

为避免以上报错，可以创建带 ON UPDATE CASCADE 和 ON DELETE CASCADE 子句的外键约束：

[menagerie]> DROP TABLE child;
Query OK, 0 rows affected (0.03 sec)

[menagerie]> CREATE TABLE child (
    ->           id INT,
    ->           parent_id INT,
    ->           INDEX par_ind (parent_id),
    ->           FOREIGN KEY (parent_id)
    ->               REFERENCES parent(id)
    ->               ON UPDATE CASCADE
    ->               ON DELETE CASCADE
    ->       ) ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)


[menagerie]> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

[menagerie]> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
|    3 |         1 |
+------+-----------+
3 rows in set (0.00 sec)

修改父表记录：

[menagerie]> UPDATE parent SET id = 2 WHERE id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[menagerie]> SELECT * FROM parent;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

查看子表记录，已经进行了级联更新：

[menagerie]> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+
3 rows in set (0.00 sec)

删除父表记录：

[menagerie]> DELETE FROM parent WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

查看子表记录，已经进行了级联删除：

[menagerie]> SELECT * FROM child;
Empty set (0.01 sec)

Calculating Visits Per Day

例子：计算用户每月访问网页的天数

[menagerie]> CREATE TABLE t1 (year YEAR, month INT UNSIGNED,day INT UNSIGNED);
Query OK, 0 rows affected (0.04 sec)

[menagerie]> INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

[menagerie]> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |
+------+-------+------+
2 rows in set (0.01 sec)

该查询计算每年每月不同天数，并自动删除重复条目。

Using AUTO_INCREMENT

例子：使用 AUTO_INCREMENT 生成唯一标识，作为主键

[menagerie]> CREATE TABLE animals (
    ->            id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->            name CHAR(30) NOT NULL,
    ->            PRIMARY KEY (id)
    ->       );
Query OK, 0 rows affected (0.03 sec)

[menagerie]> INSERT INTO animals (name) VALUES
    ->           ('dog'),('cat'),('penguin'),
    ->           ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

[menagerie]> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

[menagerie]> INSERT INTO animals (id,name) VALUES(100,'rabbit');
Query OK, 1 row affected (0.01 sec)

[menagerie]> INSERT INTO animals (id,name) VALUES(NULL,'mouse');
Query OK, 1 row affected (0.00 sec)

[menagerie]> SELECT * FROM animals;
+-----+---------+
| id  | name    |
+-----+---------+
|   1 | dog     |
|   2 | cat     |
|   3 | penguin |
|   4 | lax     |
|   5 | whale   |
|   6 | ostrich |
| 100 | rabbit  |
| 101 | mouse   |
+-----+---------+
8 rows in set (0.00 sec)

指定 AUTO_INCREMENT 起始值语法：

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

MySQL Programs

Overview of MySQL Programs

安装 MySQL 后，经常使用的程序有：

• 服务器端：
  • mysqld：MySQL 主程序，也就是 MySQL Server。
• 客户端：
  • mysql：执行 SQL 的命令行程序。
  • mysqladmin：执行管理操作的客户端程序。
  • mysqlcheck：维护表的客户端程序。
  • mysqldump：导出数据的客户端程序。
  • mysqlimport：导入数据的客户端程序。
  • mysqlshow：显示数据库，表，字段和索引信息的客户端程序。
• 管理程序：
  • innochecksum：InnoDB 离线文件校验程序。
  • mysqlbinlog：从二进制日志中读取 SQL 语句的程序。
  • mysqldumpslow：读取和汇总慢查询日志内容的程序。

Using MySQL Programs

Invoking MySQL Programs

命令行调用 MySQL 程序示例：

$> mysql --user=root test
$> mysqladmin extended-status variables
$> mysqlshow --help
$> mysqldump -u root personnel

• 以单短划线（-）或双短划线（--）指定程序参数。
• 如果出现 “找不到程序” 错误，则应调整 PATH 环境变量或者使用程序全路径。

Specifying Program Options

为 MySQL 程序指定参数的方法有：

• 在命令行上指定参数。

• 在参数文件中指定参数。

• 在环境变量中指定参数。

MySQL 程序首先使用环境变量，然后使用参数文件，最后使用命令行参数。

Using Options on the Command Line

命令行参数遵循以下规则：

• 参数在命令名称之后。

• 参数以单短划线（-）或双短划线（--）开头。

• 参数区分大小写。

• 某些参数需要指定参数值。

• 双短划线（--）开头的参数，用 = 或空格分隔参数名称和参数值，如果参数有默认值，则必须使用 =。

• 单短划线（-）开头的参数，参数值可以紧跟在参数之后，也可以在两者之间加一个空格：-hlocalhost 和 -h localhost 是等效的。但是 -p 参数和参数值之间不能有空格。

• 参数值如果包含空格，则需要加上引号。例如：

$> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"

Using Option Files

参数文件查找顺序（首先列出的文件先读取，稍后读取的文件优先）：

File Name	Purpose
/etc/my.cnf	Global options
/etc/mysql/my.cnf	Global options
SYSCONFDIR/my.cnf	Global options
$MYSQL_HOME/my.cnf	Server-specific options (server only)
defaults-extra-file	The file specified with --defaults-extra-file, if any
~/.my.cnf	User-specific options
~/.mylogin.cnf	User-specific login path options (clients only)
DATADIR/mysqld-auto.cnf	System variables persisted with SET PERSIST or SET PERSIST_ONLY (server only)

在参数文件中，省略参数名称前双短划线（--），并且每行仅指定一个参数。

• # 后面表示注释
• opt_name=value 格式指定参数及其值，= 前后可以有空格
• \b，\t，\n，\r，\\，\s 分别表示退格符、制表符、换行符、回车符、反斜杠和空格字符

[group] 指定要为其设置参数的程序名称或组，[client] 表示适用于所有客户端。应该先列出更通用的参数组，然后再列出具体的程序。例如应该先列出 [client] ，再列出 [mysqldump] 。

全局参数文件示例：

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=128M

[mysqldump]
quick

用户参数文件示例：

[client]
# The following password is sent to all standard MySQL clients
password="my password"

[mysql]
no-auto-rehash
connect_timeout=2

特定 MySQL 版本的参数组：

[mysqld-8.0]
sql_mode=TRADITIONAL

例子：查看程序使用的参数

[root@mysql ~]# mysql --print-defaults
mysql would have been started with the following arguments:
--socket=/data/mysql/mysql.sock --prompt=[\d]> \ --no-auto-rehash 

Command Options for Connecting to the Server

建立连接的命令参数：

Option Name	Description	Introduced
--default-auth	Authentication plugin to use
--host	Host on which MySQL server is located
--password	Password to use when connecting to server
--password1	First multifactor authentication password to use when connecting to server	8.0.27
--password2	Second multifactor authentication password to use when connecting to server	8.0.27
--password3	Third multifactor authentication password to use when connecting to server	8.0.27
--pipe	Connect to server using named pipe (Windows only)
--plugin-dir	Directory where plugins are installed
--port	TCP/IP port number for connection
--protocol	Transport protocol to use
--shared-memory-base-name	Shared-memory name for shared-memory connections (Windows only)
--socket	Unix socket file or Windows named pipe to use
--user	MySQL user name to use when connecting to server

Connecting to the MySQL Server Using Command Options

连接到 MySQL Server 的命令示例：

mysql --host=localhost --user=myname --password=password mydb
mysql -h localhost -u myname -ppassword mydb

更安全的方式：

mysql --host=localhost --user=myname --password mydb
mysql -h localhost -u myname -p mydb

如果 host 参数指定为 localhost，则使用套接字文件进行连接，否则使用 TCP/IP 进行连接。

也可以在参数文件的 [client] 组指定连接参数：

[client]
host=host_name
user=user_name
password=password

这里配置了用户名和密码，后续连接到 MySQL Server，就不需要输入密码了。

Server and Server-Startup Programs

mysqld — The MySQL Server

MySQL Server 也就是 mysqld，是一个单进程，多线程程序。

MySQL Server 监听来自客户端程序的网络连接，并管理对数据库的访问。

查看 mysqld 的参数：

mysqld --verbose --help

Client Programs

mysql — The MySQL Command-Line Client

使用 mysql 连接到 MySQL Server 管理数据库。

mysql Client Options

options 参考 mysql Options

mysql Client Commands

命令：

mysql> help

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given
               outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing
               binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
query_attributes Sets string parameters (name1 value1 name2 value2 ...)
for the next query to pick up.
ssl_session_data_print Serializes the current SSL session data to stdout 
or file.

For server side help, type 'help contents'

例子：以垂直格式显示查询结果

[menagerie]> show create table pet \G
*************************** 1. row ***************************
       Table: pet
Create Table: CREATE TABLE `pet` (
  `name` varchar(20) DEFAULT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

例子：查看状态

[menagerie]> \s
--------------
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          10
Current database:       menagerie
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.32 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /data/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 8 hours 5 min 43 sec

Threads: 2  Questions: 14  Slow queries: 0  Opens: 143  Flush tables: 3  Open tables: 62  Queries per second avg: 0.000
--------------

例子：执行操作系统命令

[menagerie]> \! cat pet.txt
Fluffy  Harold  cat     f       1993-02-04      \N
Claws   Gwen    cat     m       1994-03-17      \N
Buffy   Harold  dog     f       1989-05-13      \N
Fang    Benny   dog     m       1990-08-27      \N
Bowser  Diane   dog     m       1979-08-31      1995-07-29
Chirpy  Gwen    bird    f       1998-09-11      \N
Whistler        Gwen    bird    \N      1997-12-09      \N
Slim    Benny   snake   m       1996-04-29      \N

例子：查看服务器端帮助

[menagerie]> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Components
   Compound Statements
   Contents
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Geographic Features
   Help Metadata
   Language Structure
   Loadable Functions
   Plugins
   Prepared Statements
   Replication Statements
   Storage Engines
   Table Maintenance
   Transactions
   Utility

prompt 命令的选项有：

Option	Description
\C	The current connection identifier
\c	A counter that increments for each statement you issue
\D	The full current date
\d	The default database
\h	The server host
\l	The current delimiter
\m	Minutes of the current time
\n	A newline character
\O	The current month in three-letter format (Jan, Feb, …)
\o	The current month in numeric format
\P	am/pm
\p	The current TCP/IP port or socket file
\R	The current time, in 24-hour military time (0–23)
\r	The current time, standard 12-hour time (1–12)
\S	Semicolon
\s	Seconds of the current time
\T	Print an asterisk (*) if the current session is inside a transaction block (from MySQL 8.0.28)
\t	A tab character
\U	Your full *user_name*@*host_name* account name
\u	Your user name
\v	The server version
\w	The current day of the week in three-letter format (Mon, Tue, …)
\Y	The current year, four digits
\y	The current year, two digits
\_	A space
\	A space (a space follows the backslash)
\'	Single quote
\"	Double quote
\\	A literal \ backslash character
\x	x, for any “x” not listed above

修改命令提示符的方式有：

• 设置 MYSQL_PS1 环境变量

export MYSQL_PS1="(\u@\h) [\d]> "

• 使用 mysql 的命令行参数 --prompt

$> mysql --prompt="(\u@\h) [\d]> "
(user@host) [database]>

• 配置参数文件中的 prompt 参数

[mysql]
prompt=(\\u@\\h) [\\d]>\\_

• 使用 prompt 命令

mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(user@host) [database]>
(user@host) [database]> prompt
Returning to default PROMPT of mysql>
mysql>

mysql Client Logging

mysql 客户端为以交互方式执行的语句生成日志：

• 默认将语句写入到主目录下名为 .mysql_history 的历史文件。可以设置 MYSQL_HISTFILE 环境变量指定其他文件。

• 使用 --syslog 选项，将语句写入系统日志文件 /var/log/messages。

使用 --histignore 参数（优先）或者 MYSQL_HISTIGNORE 环境变量指定需要忽略的语句：

• 忽略 UPDATE 和 DELETE 语句：

mysql --histignore="*UPDATE*:*DELETE*"

• 忽略所有语句：

mysql --histignore="*"

如果基于安全考虑，不想维护 .mysql_history 历史文件，可以先删除该文件，然后配置 export MYSQL_HISTFILE=/dev/null 到环境变量配置文件中，或者创建如下软链接：

ln -s /dev/null $HOME/.mysql_history

如果使用 --syslog 选项，输出示例如下：

Mar  7 12:39:25 myhost MysqlClient[20824]:
  SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
  DB_SERVER:'127.0.0.1', DB:'--', QUERY:'USE test;'
Mar  7 12:39:28 myhost MysqlClient[20824]:
  SYSTEM_USER:'oscar', MYSQL_USER:'my_oscar', CONNECTION_ID:23,
  DB_SERVER:'127.0.0.1', DB:'test', QUERY:'SHOW TABLES;'

mysqladmin — A MySQL Server Administration Program

使用 mysqladmin 管理数据库，语法：

mysqladmin [options] command [command-arg] [command [command-arg]] ...

options 参考 mysqladmin Options

command 有：

Where command is a one or more of: (Commands may be shortened)
  create databasename   Create a new database
  debug                 Instruct server to write debug information to log
  drop databasename     Delete a database and all its tables
  extended-status       Gives an extended status message from the server
  flush-hosts           Flush all cached hosts
  flush-logs            Flush all logs
  flush-status          Clear status variables
  flush-tables          Flush all tables
  flush-threads         Flush the thread cache
  flush-privileges      Reload grant tables (same as reload)
  kill id,id,...        Kill mysql threads
  password [new-password] Change old password to new-password in current format
  ping                  Check if mysqld is alive
  processlist           Show list of active threads in server
  reload                Reload grant tables
  refresh               Flush all tables and close and open logfiles
  shutdown              Take server down
  status                Gives a short status message from the server
  start-replica         Start replication
  start-slave           Deprecated: use start-replica instead
  stop-replica          Stop replication
  stop-slave            Deprecated: use stop-replica instead
  variables             Prints variables available
  version               Get version info from server

例子：查看 MySQL Server 是否可用

[root@mysql ~]# mysqladmin ping
mysqld is alive

例子：查看活动线程，相当于 show processlist

[root@mysql ~]# mysqladmin processlist
+----+-----------------+-----------+----+---------+------+------------------------+------------------+
| Id | User            | Host      | db | Command | Time | State                  | Info             |
+----+-----------------+-----------+----+---------+------+------------------------+------------------+
| 5  | event_scheduler | localhost |    | Daemon  | 1454 | Waiting on empty queue |                  |
| 11 | root            | localhost |    | Query   | 0    | init                   | show processlist |
+----+-----------------+-----------+----+---------+------+------------------------+------------------+

例子：查看所有线程，相当于 show full processlist

[root@mysql ~]# mysqladmin processlist status --verbose
+----+-----------------+-----------+----+---------+------+------------------------+-----------------------+
| Id | User            | Host      | db | Command | Time | State                  | Info                  |
+----+-----------------+-----------+----+---------+------+------------------------+-----------------------+
| 5  | event_scheduler | localhost |    | Daemon  | 2929 | Waiting on empty queue |                       |
| 14 | root            | localhost |    | Query   | 0    | init                   | show full processlist |
+----+-----------------+-----------+----+---------+------+------------------------+-----------------------+
Uptime: 2932  Threads: 2  Questions: 14  Slow queries: 0  Opens: 119  Flush tables: 3  Open tables: 38  Queries per second avg: 0.004

• Uptime: The number of seconds the MySQL server has been running.
• Threads: The number of active threads (clients).
• Questions: The number of questions (queries) from clients since the server was started.
• Slow queries: The number of queries that have taken more than long_query_time seconds.
• Opens: The number of tables the server has opened.
• Flush tables: The number of flush-*, refresh, and reload commands the server has executed.
• Open tables: The number of tables that currently are open.

mysqlcheck — A Table Maintenance Program

使用 mysqlcheck 检查（CHECK TABLE），修复（REPAIR TABLE），分析（ANALYZE TABLE）和优化（OPTIMIZE TABLE）表。

语法：

mysqlcheck [options] db_name [tbl_name ...]
mysqlcheck [options] --databases db_name ...
mysqlcheck [options] --all-databases

options 参考 mysqlcheck Options

例子：检查表

[root@mysql ~]# mysqlcheck menagerie pet
menagerie.pet                                      OK

mysqldump — A Database Backup Program

使用 mysqldump 进行逻辑备份。

语法：

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

options 参考 mysqldump Options

例子：备份表

[root@mysql ~]# mysqldump --single-transaction --set-gtid-purged=OFF menagerie pet > pet.sql
[root@mysql ~]# cat pet.sql 
-- MySQL dump 10.13  Distrib 8.0.32, for Linux (x86_64)
--
-- Host: localhost    Database: menagerie
-- ------------------------------------------------------
-- Server version       8.0.32

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `pet`
--

DROP TABLE IF EXISTS `pet`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pet` (
  `name` varchar(20) DEFAULT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `pet`
--

LOCK TABLES `pet` WRITE;
/*!40000 ALTER TABLE `pet` DISABLE KEYS */;
INSERT INTO `pet` VALUES ('Fluffy','Harold','cat','f','1993-02-04',NULL),('Claws','Gwen','cat','m','1994-03-17',NULL),('Buffy','Harold','dog','f','1989-05-13',NULL),('Fang','Benny','dog','m','1990-08-27',NULL),('Bowser','Diane','dog','m','1979-08-31','1995-07-29'),('Chirpy','Gwen','bird','f','1998-09-11',NULL),('Whistler','Gwen','bird',NULL,'1997-12-09',NULL),('Slim','Benny','snake','m','1996-04-29',NULL),('Puffball','Diane','hamster','f','1999-03-30',NULL);
/*!40000 ALTER TABLE `pet` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2023-02-10 13:45:36

例子：备份数据库

[root@mysql ~]# mysqldump --single-transaction --set-gtid-purged=OFF menagerie > menagerie.sql

例子：备份整个数据库

[root@mysql ~]# mysqldump --all-databases --triggers --routines --events --single-transaction > all.sql

mysqlimport — A Data Import Program

使用 mysqlimport 加载数据，是 LOAD DATA 语句的命令行接口。

语法：

mysqlimport [options] db_name textfile1 [textfile2 ...]

options 参考 mysqlimport Options

例子：加载数据

[menagerie]> truncate table pet;
Query OK, 0 rows affected (0.04 sec)

[root@mysql ~]# mysqlimport --local menagerie pet.txt 
menagerie.pet: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysqlshow — Display Database, Table, and Column Information

使用 mysqlshow 查看信息，是 SHOW 语句的命令行接口。

语法：

mysqlshow [options] [db_name [tbl_name [col_name]]]

options 参考 mysqlshow Options

例子：查看数据库

[root@mysql ~]# mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

例子：查看数据库下的表

[root@mysql ~]# mysqlshow menagerie
Database: menagerie
+---------+
| Tables  |
+---------+
| animals |
| child   |
| event   |
| parent  |
| pet     |
| shop    |
| t1      |
+---------+

Administrative and Utility Programs

innochecksum — Offline InnoDB File Checksum Utility

使用 innochecksum 校验 InnoDB 文件，必须关闭 MySQL Server 后才能使用。

语法：

innochecksum [options] file_name

如果使用了 innodb-data-file-path 参数定义了多个系统表空间文件，如下：

./bin/mysqld --no-defaults --innodb-data-file-path="ibdata1:10M;ibdata2:10M;ibdata3:10M:autoextend"

则需要使用如下方式进行校验：

cat ibdata* | innochecksum -

例子：校验 pet.ibd 文件

[root@mysql ~]# systemctl stop mysqld
[root@mysql ~]# innochecksum -l /tmp/log.txt /data/mysql/menagerie/pet.ibd 
[root@mysql ~]# cat /tmp/log.txt 
InnoDB File Checksum Utility.
Filename = /data/mysql/menagerie/pet.ibd
Innochecksum: checking pages in range 0 to 6
page::0; log sequence number:first = 20152120; second = 20152120
page::0; old style: calculated = 1313526402; recorded = 3279055376
page::0; new style: calculated = 1987799433; crc32 = 3279055376; recorded = 3279055376
page::1; log sequence number:first = 20148686; second = 20148686
page::1; old style: calculated = 1845381723; recorded = 1304358113
page::1; new style: calculated = 1464511861; crc32 = 1304358113; recorded = 1304358113
page::2; log sequence number:first = 20152120; second = 20152120
page::2; old style: calculated = 1027245425; recorded = 2498843816
page::2; new style: calculated = 60277401; crc32 = 2498843816; recorded = 2498843816
page::3; log sequence number:first = 20159166; second = 20159166
page::3; old style: calculated = 3448043226; recorded = 595098929
page::3; new style: calculated = 2785241711; crc32 = 595098929; recorded = 595098929
page::4; log sequence number:first = 20161777; second = 20161777
page::4; old style: calculated = 608877859; recorded = 164612778
page::4; new style: calculated = 4200735241; crc32 = 164612778; recorded = 164612778
page::5; log sequence number:first = 0; second = 0
Page::0 is empty and uncorrupted
page::6; log sequence number:first = 0; second = 0
Page::0 is empty and uncorrupted

[root@mysql ~]# innochecksum --page-type-summary /data/mysql/menagerie/pet.ibd 

File::/data/mysql/menagerie/pet.ibd
================PAGE TYPE SUMMARY==============
#PAGE_COUNT     PAGE_TYPE
===============================================
       1        Index page
       1        SDI Index page
       0        Undo log page
       1        Inode page
       0        Insert buffer free list page
       2        Freshly allocated page
       1        Insert buffer bitmap
       0        System page
       0        Transaction system page
       1        File Space Header
       0        Extent descriptor page
       0        BLOB page
       0        Compressed BLOB page
       0        Subsequent Compressed BLOB page
       0        SDI BLOB page
       0        Compressed SDI BLOB page
       0        Other type of page
===============================================
Additional information:
Undo page type: 0 insert, 0 update, 0 other
Undo page state: 0 active, 0 cached, 0 to_free, 0 to_purge, 0 prepared, 0 other

mysqlbinlog — Utility for Processing Binary Log Files

使用 mysqlbinlog 查看二进制日志，二进制日志记录了对数据库内容修改的事件。

语法：

mysqlbinlog [options] log_file ...

options 参考 mysqlbinlog Options

使用 mysqlbinlog 解析二进制日志并传递给 mysql 执行：

mysqlbinlog binlog.000001 | mysql -u root -p
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
mysqlbinlog binlog.[0-9]* | mysql -u root -p

使用 mysqlbinlog 解析二进制日志到文本文件，修改后传递给 mysql 执行：

mysqlbinlog binlog.000001 > tmpfile
mysqlbinlog binlog.000002 >> tmpfile
... edit tmpfile ...
mysql -u root -p < tmpfile

流式输入，不能使用 --stop-position 参数：

gzip -cd binlog-files_1.gz | ./mysqlbinlog - | ./mysql -uroot  -p
gzip -cd binlog-files_1.gz binlog-files_2.gz | ./mysqlbinlog - | ./mysql -uroot  -p

mysqlbinlog Row Event Display

使用基于行日志 binlog_format=row 配置，执行以下语句：

CREATE TABLE t
(
  id   INT NOT NULL,
  name VARCHAR(20) NOT NULL,
  date DATE NULL
) ENGINE = InnoDB;

START TRANSACTION;
INSERT INTO t VALUES(1, 'apple', NULL);
UPDATE t SET name = 'pear', date = '2009-01-01' WHERE id = 1;
DELETE FROM t WHERE id = 1;
COMMIT;

使用 mysqlbinlog 直接解析：

$> mysqlbinlog log_file
...
# at 218
#080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
...
# at 302
#080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
...
# at 400
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;

加上 -v 参数查看 SQL：

$> mysqlbinlog -v log_file
...
# at 218
#080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
###   @1=1
###   @2='apple'
###   @3=NULL
...
# at 302
#080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
###   @1=1
###   @2='apple'
###   @3=NULL
### SET
###   @1=1
###   @2='pear'
###   @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
###   @1=1
###   @2='pear'
###   @3='2009:01:01'

使用 -vv 查看数据类型和字段元数据：

$> mysqlbinlog -vv log_file
...
# at 218
#080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;
### INSERT INTO test.t
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
###   @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
...
# at 302
#080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAC4BAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBgBAAAANgAAAGQBAAAQABEAAAAAAAEAA////AEAAAAFYXBwbGX4AQAAAARwZWFyIbIP
'/*!*/;
### UPDATE test.t
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='apple' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
###   @3=NULL /* VARSTRING(20) meta=0 nullable=1 is_null=1 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
###   @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */
...
# at 400
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F

BINLOG '
fAS3SBMBAAAALAAAAJABAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBkBAAAAKgAAALoBAAAQABEAAAAAAAEAA//4AQAAAARwZWFyIbIP
'/*!*/;
### DELETE FROM test.t
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='pear' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
###   @3='2009:01:01' /* DATE meta=0 nullable=1 is_null=0 */

使用 --base64-output=DECODE-ROWS 查看 SQL：

$> mysqlbinlog -v --base64-output=DECODE-ROWS log_file
...
# at 218
#080828 15:03:08 server id 1  end_log_pos 258   Write_rows: table id 17 flags: STMT_END_F
### INSERT INTO test.t
### SET
###   @1=1
###   @2='apple'
###   @3=NULL
...
# at 302
#080828 15:03:08 server id 1  end_log_pos 356   Update_rows: table id 17 flags: STMT_END_F
### UPDATE test.t
### WHERE
###   @1=1
###   @2='apple'
###   @3=NULL
### SET
###   @1=1
###   @2='pear'
###   @3='2009:01:01'
...
# at 400
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F
### DELETE FROM test.t
### WHERE
###   @1=1
###   @2='pear'
###   @3='2009:01:01'

注意

如果需要执行 mysqlbinlog 输出，则需要保留 BINLOG 语句。

Using mysqlbinlog to Back Up Binary Log Files

查看二进制日志信息：

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000130 |     27459 | No        |
| binlog.000131 |     13719 | No        |
| binlog.000132 |     43268 | No        |
+---------------+-----------+-----------+

静态备份，备份指定日志：

mysqlbinlog --read-from-remote-server --host=host_name --raw
  binlog.000130 binlog.000131 binlog.000132

静态备份，从指定日志开始，备份到最后一个日志：

mysqlbinlog --read-from-remote-server --host=host_name --raw
  --to-last-log binlog.000130

在线备份，从指定日志开始，保持连续备份：

mysqlbinlog --read-from-remote-server --host=host_name --raw
  --stop-never binlog.000130

可以使用 --result-file 指定备份的路径和文件前缀：

--result-file Option	Output File Names
--result-file=x	xbinlog.000999 and up
--result-file=/tmp/	/tmp/binlog.000999 and up
--result-file=/tmp/x	/tmp/xbinlog.000999 and up

例子：使用 mysqldump 和 mysqlbinlog 进行备份和恢复

使用 mysqlbinlog 创建二进制日志的连续备份：

mysqlbinlog --read-from-remote-server --host=host_name --raw
  --stop-never binlog.000999

使用 mysqldump 创建数据 dump 备份：

mysqldump --host=host_name --all-databases --events --routines --master-data=2> dump_file

如果数据丢失，使用最近的数据 dump 备份进行恢复：

mysql --host=host_name -u root -p < dump_file

从最近的数据 dump 备份中获取产生该备份时的二进制日志位置：

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.001002', MASTER_LOG_POS=27284;

然后使用二进制日志恢复之后的数据：

mysqlbinlog --start-position=27284 binlog.001002 binlog.001003 binlog.001004
  | mysql --host=host_name -u root -p

mysqldumpslow — Summarize Slow Query Log Files

使用 mysqldumpslow 解析慢查询日志。

语法：

mysqldumpslow [options] [log_file ...]

options 有：

Option Name	Description
-a	Do not abstract all numbers to N and strings to 'S'
-n	Abstract numbers with at least the specified digits
--debug	Write debugging information
-g	Only consider statements that match the pattern
--help	Display help message and exit
-h	Host name of the server in the log file name
-i	Name of the server instance
-l	Do not subtract lock time from total time
-r	Reverse the sort order
-s	How to sort output
-t	Display only first num queries
--verbose	Verbose mode

输出示例：

Reading mysql slow query log from /usr/local/mysql/data/mysqld80-slow.log
Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1

Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1 limit N

Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t1 select * from t1

Environment Variables

MySQL 可以使用的环境变量参考 Environment Variables

优先级如下：

• 命令行优先级最高
• 参数文件次之
• 环境变量优先级最低

MySQL Server Administration

MySQL Server

Configuring the Server

一般在参数文件 /etc/my.cnf 的 [mysqld] 组中配置 mysqld 的参数。

配置完成后可以使用以下命令进行验证：

mysqld --validate-config

查看 mysqld 的参数和系统变量：

mysqld --verbose --help

查看当前会话使用的系统变量（参数）：

[(none)]> SHOW VARIABLES LIKE '%BUFFER%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 67108864       |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 1073741824     |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_ddl_buffer_size              | 1048576        |
| innodb_log_buffer_size              | 16777216       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 4194304        |
| key_buffer_size                     | 33554432       |
| myisam_sort_buffer_size             | 8388608        |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 8388608        |
| read_rnd_buffer_size                | 4194304        |
| select_into_buffer_size             | 131072         |
| sort_buffer_size                    | 4194304        |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
27 rows in set (0.00 sec)

查看全局参数：

[(none)]> SHOW GLOBAL VARIABLES LIKE '%BUFFER%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 67108864       |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 1073741824     |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_ddl_buffer_size              | 1048576        |
| innodb_log_buffer_size              | 16777216       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 4194304        |
| key_buffer_size                     | 33554432       |
| myisam_sort_buffer_size             | 8388608        |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 8388608        |
| read_rnd_buffer_size                | 4194304        |
| select_into_buffer_size             | 131072         |
| sort_buffer_size                    | 4194304        |
| sql_buffer_result                   | OFF            |
+-------------------------------------+----------------+
27 rows in set (0.01 sec)

查看当前会话统计和状态信息：

[(none)]> SHOW STATUS LIKE '%BUFFER%';
+-------------------------------------------+--------------------------------------------------+
| Variable_name                             | Value                                            |
+-------------------------------------------+--------------------------------------------------+
| Error_log_buffered_bytes                  | 31800                                            |
| Error_log_buffered_events                 | 197                                              |
| Innodb_buffer_pool_dump_status            | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 230213 10:32:21 |
| Innodb_buffer_pool_resize_status          |                                                  |
| Innodb_buffer_pool_resize_status_code     | 0                                                |
| Innodb_buffer_pool_resize_status_progress | 0                                                |
| Innodb_buffer_pool_pages_data             | 1239                                             |
| Innodb_buffer_pool_bytes_data             | 20299776                                         |
| Innodb_buffer_pool_pages_dirty            | 0                                                |
| Innodb_buffer_pool_bytes_dirty            | 0                                                |
| Innodb_buffer_pool_pages_flushed          | 201                                              |
| Innodb_buffer_pool_pages_free             | 64287                                            |
| Innodb_buffer_pool_pages_misc             | 10                                               |
| Innodb_buffer_pool_pages_total            | 65536                                            |
| Innodb_buffer_pool_read_ahead_rnd         | 0                                                |
| Innodb_buffer_pool_read_ahead             | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted     | 0                                                |
| Innodb_buffer_pool_read_requests          | 16030                                            |
| Innodb_buffer_pool_reads                  | 1098                                             |
| Innodb_buffer_pool_wait_free              | 0                                                |
| Innodb_buffer_pool_write_requests         | 1974                                             |
+-------------------------------------------+--------------------------------------------------+
22 rows in set (0.01 sec)

查看全局统计和状态信息：

[(none)]> SHOW GLOBAL STATUS LIKE '%BUFFER%';
+-------------------------------------------+--------------------------------------------------+
| Variable_name                             | Value                                            |
+-------------------------------------------+--------------------------------------------------+
| Error_log_buffered_bytes                  | 40008                                            |
| Error_log_buffered_events                 | 248                                              |
| Innodb_buffer_pool_dump_status            | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status            | Buffer pool(s) load completed at 230216  8:54:12 |
| Innodb_buffer_pool_resize_status          |                                                  |
| Innodb_buffer_pool_resize_status_code     | 0                                                |
| Innodb_buffer_pool_resize_status_progress | 0                                                |
| Innodb_buffer_pool_pages_data             | 1233                                             |
| Innodb_buffer_pool_bytes_data             | 20201472                                         |
| Innodb_buffer_pool_pages_dirty            | 0                                                |
| Innodb_buffer_pool_bytes_dirty            | 0                                                |
| Innodb_buffer_pool_pages_flushed          | 214                                              |
| Innodb_buffer_pool_pages_free             | 64292                                            |
| Innodb_buffer_pool_pages_misc             | 11                                               |
| Innodb_buffer_pool_pages_total            | 65536                                            |
| Innodb_buffer_pool_read_ahead_rnd         | 0                                                |
| Innodb_buffer_pool_read_ahead             | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted     | 0                                                |
| Innodb_buffer_pool_read_requests          | 16076                                            |
| Innodb_buffer_pool_reads                  | 1085                                             |
| Innodb_buffer_pool_wait_free              | 0                                                |
| Innodb_buffer_pool_write_requests         | 1992                                             |
+-------------------------------------------+--------------------------------------------------+
22 rows in set (0.01 sec)

也可以使用 mysqladmin 命令查看：

$> mysqladmin variables
$> mysqladmin extended-status

参数和状态参考：

• Server Option, System Variable, and Status Variable
• Server System Variable
• Server Status Variable
• Dynamic System Variables

​

除了在参数文件中配置启动参数外，还可以在运行时使用 SET 语句修改动态参数。此时参数必须使用下划线（_），不能使用短划线（-）。

例子：修改内存中的全局参数，全局生效

[(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.01 sec)

--方式 1
[(none)]> SET GLOBAL max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1000  |
+-----------------+-------+
1 row in set (0.01 sec)

--方式 2
[(none)]> SET @@GLOBAL.max_connections = 2000;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)

例子：修改内存中的会话参数，当前会话生效

[(none)]> SHOW VARIABLES LIKE 'net_read_timeout';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| net_read_timeout | 30    |
+------------------+-------+
1 row in set (0.01 sec)

--方式 1
[(none)]> SET SESSION net_read_timeout=20;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'net_read_timeout';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| net_read_timeout | 20    |
+------------------+-------+
1 row in set (0.00 sec)

[(none)]> SHOW SESSION VARIABLES LIKE 'net_read_timeout';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| net_read_timeout | 20    |
+------------------+-------+
1 row in set (0.00 sec)

[(none)]> SHOW GLOBAL VARIABLES LIKE 'net_read_timeout';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| net_read_timeout | 30    |
+------------------+-------+
1 row in set (0.00 sec)

[(none)]> select @@global.net_read_timeout,@@session.net_read_timeout ;
+---------------------------+----------------------------+
| @@global.net_read_timeout | @@session.net_read_timeout |
+---------------------------+----------------------------+
|                        30 |                         20 |
+---------------------------+----------------------------+
1 row in set (0.00 sec)

--方式 2
[(none)]> SET @@SESSION.net_read_timeout=25;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'net_read_timeout';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| net_read_timeout | 25    |
+------------------+-------+
1 row in set (0.00 sec)

--方式 3
[(none)]> SET @@net_read_timeout=30;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'net_read_timeout';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| net_read_timeout | 30    |
+------------------+-------+
1 row in set (0.00 sec)

例子：设置全局参数到 datadir/mysqld-auto.cnf 文件，下次启动生效

--方式 1
[(none)]> SET PERSIST_ONLY max_connections = 1000;
Query OK, 0 rows affected (0.00 sec)

[(none)]> select * from performance_schema.persisted_variables ;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 1000           |
+-----------------+----------------+
1 row in set (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.00 sec)

--方式 2
[(none)]> SET @@PERSIST_ONLY.max_connections = 2000;
Query OK, 0 rows affected (0.01 sec)

[(none)]> select * from performance_schema.persisted_variables ;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 2000           |
+-----------------+----------------+
1 row in set (0.00 sec)

查看文件：

[root@mysql ~]# cat /data/mysql/mysqld-auto.cnf | jq
{
  "Version": 2,
  "mysql_dynamic_parse_early_variables": {
    "max_connections": {
      "Value": "2000",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1676362605071443
      }
    }
  }
}

例子：设置全局参数到 datadir/mysqld-auto.cnf 文件，立即生效

--方式 1
[(none)]> SET PERSIST max_connections = 3000;
Query OK, 0 rows affected (0.01 sec)

[(none)]> select * from performance_schema.persisted_variables ;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 3000           |
+-----------------+----------------+
1 row in set (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 3000  |
+-----------------+-------+
1 row in set (0.00 sec)

--方式 2
[(none)]> SET @@PERSIST.max_connections = 4000;
Query OK, 0 rows affected (0.00 sec)

[(none)]> select * from performance_schema.persisted_variables ;
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 4000           |
+-----------------+----------------+
1 row in set (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 4000  |
+-----------------+-------+
1 row in set (0.01 sec)

查看文件：

[root@mysql ~]# cat /data/mysql/mysqld-auto.cnf | jq
{
  "Version": 2,
  "mysql_dynamic_parse_early_variables": {
    "max_connections": {
      "Value": "4000",
      "Metadata": {
        "Host": "localhost",
        "User": "root",
        "Timestamp": 1676363492825205
      }
    }
  }
}

例子：移除 datadir/mysqld-auto.cnf 文件中的全局参数

--移除指定参数
[(none)]> RESET PERSIST IF EXISTS max_connections;
Query OK, 0 rows affected (0.01 sec)

--移除所有参数
[(none)]> RESET PERSIST;
Query OK, 0 rows affected (0.00 sec)

[(none)]> select * from performance_schema.persisted_variables ;
Empty set (0.01 sec)

查看文件：

[root@mysql ~]# cat /data/mysql/mysqld-auto.cnf | jq
{
  "Version": 2
}

注意

可以使用参数 persisted_globals_load 指定是否使用 mysqld-auto.cnf 参数文件。

​

启动时，在命令行或者参数文件中指定的参数不能使用表达式，可以使用 K，M，G，T，P 后缀。

$> mysql --max_allowed_packet=16M

运行时，使用 SET 指定的参数可以使用表达式，不能使用 K，M，G，T，P 后缀。

mysql> SET GLOBAL max_allowed_packet=16*1024*1024;

Server SQL Modes

通过调整 sql_mode 参数，可以支持某些非标准 SQL 语法以及无效数据。一般情况下，不要调整该参数，而应该修改 SQL 或者数据。

默认的 sql_mode 值为：

[(none)]> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

• ONLY_FULL_GROUP_BY：SELECT 子句中的列要么在聚合函数中，要么同时出现在 GROUP BY 子句中。
• STRICT_TRANS_TABLES：严格模式，拒绝无效数据。
• NO_ZERO_IN_DATE：严格模式下，拒绝月和日为 0 的数据。
• NO_ZERO_DATE：严格模式下，拒绝 0000-00-00 数据。
• ERROR_FOR_DIVISION_BY_ZERO：严格模式下，拒绝除数为 0。
• NO_ENGINE_SUBSTITUTION：如果 CREATE TABLE 语句指定的存储引擎不存在，则报错。

Connection Management

MySQL 社区版没有企业版的线程池插件，通过线程缓存来管理客户端连接。

参数 thread_cache_size 设置线程缓存。

[(none)]> SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 512   |
+-------------------+-------+
1 row in set (0.19 sec)

查看当前线程状态，根据状态调整 thread_cache_size 参数。

[(none)]> SHOW STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

参数 thread_stack 设置每个线程的内存，默认 1 M。

[(none)]> SHOW VARIABLES LIKE 'thread_stack';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| thread_stack  | 1048576 |
+---------------+---------+
1 row in set (0.00 sec)

参数 thread_handling 设置线程处理方式。默认为 one-thread-per-connection，即一个线程处理一个客户端连接。连接断开后，该线程可以进入线程缓存。

[(none)]> SHOW VARIABLES LIKE 'thread_handling';
+-----------------+---------------------------+
| Variable_name   | Value                     |
+-----------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------+---------------------------+
1 row in set (0.01 sec)

​

参数 max_connections 设置允许最大并发客户端连接数。MySQL 还会提供一个额外的连接用于管理。

[(none)]> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 4000  |
+-----------------+-------+
1 row in set (0.00 sec)

参数 max_connections 与参数 open_files_limit 相关，max_connections 小于 open_files_limit - 810。参数 open_files_limit 又与操作系统的文件描述符相关。

[(none)]> SHOW VARIABLES LIKE 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.00 sec)

查看过去最大并发连接数。

[(none)]> SHOW STATUS LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1     |
+----------------------+-------+
1 row in set (0.00 sec)

查看达到最大并发数后，被拒绝的连接数。

[(none)]> SHOW STATUS LIKE 'Connection_errors_max_connections';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_max_connections | 0     |
+-----------------------------------+-------+
1 row in set (0.00 sec)

​

从 MySQL 8.0.14 开始，可以配置额外端口用于 MySQL 管理。

[mysqld]
admin_address=127.0.0.1
admin_port=33064

DNS Lookups and the Host Cache

MySQL 会维护一个主机缓存（Host Cache），包含客户端的 IP，主机名和错误信息，用于非本地 TCP 连接。

• 通过缓存 IP 和主机名，不需要为客户端的每次连接进行 DNS 解析，提高性能。
• 参数 max_connect_errors 使用连接错误信息限制客户端登录。

通过查询 PERFORMANCE_SCHEMA.HOST_CACHE 获取主机缓存。

[(none)]> SELECT IP,HOST,SUM_CONNECT_ERRORS FROM PERFORMANCE_SCHEMA.HOST_CACHE;
Empty set (0.00 sec)

但是 DNS 不是完全可靠的，故可以使用参数 skip_name_resolve 禁止 DNS 解析。

[(none)]> SHOW VARIABLES LIKE 'skip_name_resolve';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | ON    |
+-------------------+-------+
1 row in set (0.01 sec)

参数 max_connect_errors 设置阻止连接前客户端连续错误连接数量。

[(none)]> SHOW VARIABLES LIKE 'max_connect_errors';
+--------------------+--------+
| Variable_name      | Value  |
+--------------------+--------+
| max_connect_errors | 100000 |
+--------------------+--------+
1 row in set (0.00 sec)

某个客户端的连续连接错误数量达到该参数设定值会报如下错误：

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

根据提示，可以使用命令 mysqladmin flush-hosts 或者语句 FLUSH HOSTS 刷新主机缓存，解除对客户端的连接限制；或者等待该条目成为最近最少使用的缓存条目而被丢弃。

Time Zone

在配置文件中使用参数 default_time_zone 在 MySQL 启动时指定时区。

[mysqld]
default_time_zone='timezone'

还可以使用参数 time_zone 在运行时指定时区。

--修改全局时区
SET GLOBAL time_zone = timezone;

--修改当前会话时区
SET time_zone = timezone;

timezone 可以为：

• SYSTEM，表示使用服务器时区，默认值。
• UTC，例如 '+8:00'。

基于性能考虑，建议直接在配置文件中指定正确的时区。

[mysqld]
default_time_zone = '+8:00'

查看服务器时区。

[root@mysql ~]# date -R
Fri, 17 Feb 2023 09:22:01 +0800

查看 MySQL 全局和会话时区。

[(none)]> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +08:00             | +08:00              |
+--------------------+---------------------+
1 row in set (0.01 sec)

函数 NOW() 和 CURTIME() 的值会随着时区变化。

[(none)]> SELECT NOW(),CURTIME();
+---------------------+-----------+
| NOW()               | CURTIME() |
+---------------------+-----------+
| 2023-02-17 10:06:57 | 10:06:57  |
+---------------------+-----------+
1 row in set (0.00 sec)

--修改时区
[(none)]> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

[(none)]> SELECT NOW(),CURTIME();
+---------------------+-----------+
| NOW()               | CURTIME() |
+---------------------+-----------+
| 2023-02-17 02:07:35 | 02:07:35  |
+---------------------+-----------+
1 row in set (0.00 sec)

类型为 TIMESTAMP 的字段会随着时区变化，DATE，TIME，DATETIME 类型字段不受影响。

[(none)]> use menagerie;
Database changed
[menagerie]> set time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)

[menagerie]> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +08:00             | +08:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

[menagerie]> create table test(ts timestamp, dt datetime);
Query OK, 0 rows affected (0.03 sec)

[menagerie]> insert into test values('2023-02-17 10:20:30','2023-02-17 10:20:30');
Query OK, 1 row affected (0.02 sec)

[menagerie]> select * from test;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2023-02-17 10:20:30 | 2023-02-17 10:20:30 |
+---------------------+---------------------+
1 row in set (0.00 sec)

[menagerie]> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

[menagerie]> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +08:00             | +00:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

[menagerie]> select * from test;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2023-02-17 02:20:30 | 2023-02-17 10:20:30 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Data Directory

在配置文件中使用参数 datadir 指定 MySQL 数据目录。

[mysqld]
datadir=/data/mysql

查看数据目录参数。

[(none)]> SHOW VARIABLES LIKE 'datadir';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| datadir       | /data/mysql/ |
+---------------+--------------+
1 row in set (0.02 sec)

查看数据目录。

[root@mysql ~]# ll /data/mysql/
total 91368
-rw-r----- 1 mysql mysql       56 Feb  7 13:39 auto.cnf
-rw------- 1 mysql mysql     1680 Feb  7 13:39 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Feb  7 13:39 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Feb  7 13:39 client-cert.pem
-rw------- 1 mysql mysql     1676 Feb  7 13:39 client-key.pem
-rw-r----- 1 mysql mysql   589824 Feb 17 10:21 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql  8978432 Feb  7 13:39 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql     4402 Feb 17 09:27 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Feb 17 10:20 ibdata1
-rw-r----- 1 mysql mysql 12582912 Feb 17 09:27 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Feb 17 09:27 #innodb_redo
drwxr-x--- 2 mysql mysql      187 Feb 17 09:27 #innodb_temp
drwxr-x--- 2 mysql mysql      138 Feb 17 10:19 menagerie
drwxr-x--- 2 mysql mysql      143 Feb  7 13:39 mysql
-rw-r----- 1 mysql mysql      220 Feb 13 10:16 mysql-bin.000011
-rw-r----- 1 mysql mysql      220 Feb 13 17:27 mysql-bin.000012
-rw-r----- 1 mysql mysql      220 Feb 14 17:22 mysql-bin.000013
-rw-r----- 1 mysql mysql      220 Feb 15 21:51 mysql-bin.000014
-rw-r----- 1 mysql mysql      220 Feb 16 17:30 mysql-bin.000015
-rw-r----- 1 mysql mysql      533 Feb 16 21:24 mysql-bin.000016
-rw-r----- 1 mysql mysql      220 Feb 17 09:27 mysql-bin.000017
-rw-r----- 1 mysql mysql      716 Feb 17 10:20 mysql-bin.000018
-rw-r----- 1 mysql mysql      232 Feb 17 09:27 mysql-bin.index
-rw-r----- 1 mysql mysql       14 Feb 16 09:55 mysqld-auto.cnf
-rw-r----- 1 mysql mysql 25165824 Feb 17 10:20 mysql.ibd
srwxrwxrwx 1 mysql mysql        0 Feb 17 09:27 mysql.sock
-rw------- 1 mysql mysql        5 Feb 17 09:27 mysql.sock.lock
drwxr-x--- 2 mysql mysql     8192 Feb  7 13:39 performance_schema
-rw------- 1 mysql mysql     1676 Feb  7 13:39 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Feb  7 13:39 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Feb  7 13:39 server-cert.pem
-rw------- 1 mysql mysql     1676 Feb  7 13:39 server-key.pem
drwxr-x--- 2 mysql mysql       28 Feb  7 13:39 sys
-rw-r----- 1 mysql mysql 16777216 Feb 17 10:20 undo_001
-rw-r----- 1 mysql mysql 16777216 Feb 17 10:21 undo_002

MySQL 会为每个数据库创建一个对应的目录。

mysql System Schema

查看 MySQL 中的数据库：

[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

其中 mysql 数据库为自带的系统数据库，包含 MySQL Server 运行时需要的表数据。

mysql 的系统表和数据字典表位于 MySQL 数据目录中名为 mysql.ibd 的单个 InnoDB 表空间文件中。

Data Dictionary Tables

以下数据字典表包含数据库对象元数据。

• catalogs: Catalog information.
• character_sets: Information about available character sets.
• check_constraints: Information about CHECK constraints defined on tables.
• collations: Information about collations for each character set.
• column_statistics: Histogram statistics for column values.
• column_type_elements: Information about types used by columns.
• columns: Information about columns in tables.
• dd_properties: A table that identifies data dictionary properties, such as its version. The server uses this to determine whether the data dictionary must be upgraded to a newer version.
• events: Information about Event Scheduler events.If the server is started with the --skip-grant-tables option, the event scheduler is disabled and events registered in the table do not run.
• foreign_keys, foreign_key_column_usage: Information about foreign keys.
• index_column_usage: Information about columns used by indexes.
• index_partitions: Information about partitions used by indexes.
• index_stats: Used to store dynamic index statistics generated when ANALYZE TABLE is executed.
• indexes: Information about table indexes.
• innodb_ddl_log: Stores DDL logs for crash-safe DDL operations.
• parameter_type_elements: Information about stored procedure and function parameters, and about return values for stored functions.
• parameters: Information about stored procedures and functions.
• resource_groups: Information about resource groups.
• routines: Information about stored procedures and functions.
• schemata: Information about schemata. In MySQL, a schema is a database, so this table provides information about databases.
• st_spatial_reference_systems: Information about available spatial reference systems for spatial data.
• table_partition_values: Information about values used by table partitions.
• table_partitions: Information about partitions used by tables.
• table_stats: Information about dynamic table statistics generated when ANALYZE TABLE is executed.
• tables: Information about tables in databases.
• tablespace_files: Information about files used by tablespaces.
• tablespaces: Information about active tablespaces.
• triggers: Information about triggers.
• view_routine_usage: Information about dependencies between views and stored functions used by them.
• view_table_usage: Used to track dependencies between views and their underlying tables.

数据字典表不可见，不能用 SELECT 读取，不会出现在 SHOW TABLES 的输出中，也不会在 INFORMATION_SCHEMA.TABLES 中列出。但是可以查询相应的 INFORMATION_SCHEMA 表获取元数据信息。从概念上讲，INFORMATION_SCHEMA 提供了一个视图，MySQL 通过该视图公开数据字典元数据。例如，不能直接从 mysql.schemata 表中进行查询：

[mysql]> SELECT * FROM MYSQL.SCHEMATA;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.schemata' is rejected.

而应该从对应的 INFORMATION_SCHEMA 表中查询：

[mysql]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA\G
*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: mysql
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_ai_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO
*************************** 2. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: information_schema
DEFAULT_CHARACTER_SET_NAME: utf8mb3
    DEFAULT_COLLATION_NAME: utf8mb3_general_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO
*************************** 3. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: performance_schema
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_ai_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO
*************************** 4. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: sys
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_ai_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO
*************************** 5. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: menagerie
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_ai_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO
5 rows in set (0.00 sec)

例子：查看指定数据库下的所有索引

[mysql]> SELECT DISTINCT TABLE_NAME, INDEX_NAME
    ->   FROM INFORMATION_SCHEMA.STATISTICS
    ->   WHERE TABLE_SCHEMA = 'menagerie';
+------------+------------+
| TABLE_NAME | INDEX_NAME |
+------------+------------+
| shop       | PRIMARY    |
| parent     | PRIMARY    |
| child      | par_ind    |
| animals    | PRIMARY    |
+------------+------------+
4 rows in set (0.01 sec)

例子：查询主外键关系表

[mysql]> SELECT
    ->       C.TABLE_SCHEMA,
    ->       C.REFERENCED_TABLE_NAME,
    ->       C.REFERENCED_COLUMN_NAME,
    ->       C.TABLE_NAME,
    ->       C.COLUMN_NAME,
    ->       C.CONSTRAINT_NAME,
    ->       T.TABLE_COMMENT,
    ->       R.UPDATE_RULE,
    ->       R.DELETE_RULE
    ->   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
    ->   JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME = C.TABLE_NAME
    ->   JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON R.TABLE_NAME = C.TABLE_NAME
    ->   AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
    ->   AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME
    ->   WHERE C.REFERENCED_TABLE_NAME IS NOT NULL;
+--------------+-----------------------+------------------------+------------+-------------+-----------------+---------------+-------------+-------------+
| TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | TABLE_COMMENT | UPDATE_RULE | DELETE_RULE |
+--------------+-----------------------+------------------------+------------+-------------+-----------------+---------------+-------------+-------------+
| menagerie    | parent                | id                     | child      | parent_id   | child_ibfk_1    |               | CASCADE     | CASCADE     |
+--------------+-----------------------+------------------------+------------+-------------+-----------------+---------------+-------------+-------------+
1 row in set (0.02 sec)

例子：查看所有约束

SELECT
    O.CONSTRAINT_SCHEMA,
    O.CONSTRAINT_NAME,
    O.TABLE_SCHEMA,
    O.TABLE_NAME,
    O.COLUMN_NAME,
    O.REFERENCED_TABLE_SCHEMA,
    O.REFERENCED_TABLE_NAME,
    O.REFERENCED_COLUMN_NAME,
    O.UPDATE_RULE,
    O.DELETE_RULE,
    O.UNIQUE_CONSTRAINT_NAME,
    T.CONSTRAINT_TYPE
FROM
    (
        SELECT
            K.CONSTRAINT_SCHEMA,
            K.CONSTRAINT_NAME,
            K.TABLE_SCHEMA,
            K.TABLE_NAME,
            K.COLUMN_NAME,
            K.REFERENCED_TABLE_SCHEMA,
            K.REFERENCED_TABLE_NAME,
            K.REFERENCED_COLUMN_NAME,
            R.UPDATE_RULE,
            R.DELETE_RULE,
            R.UNIQUE_CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
        LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON K.CONSTRAINT_NAME = R.CONSTRAINT_NAME
    ) AS O
INNER JOIN Information_schema.TABLE_CONSTRAINTS T ON O.Table_Name = T.TABLE_NAME
AND T.CONSTRAINT_NAME = O.CONSTRAINT_NAME
WHERE O.CONSTRAINT_SCHEMA != 'mysql'
AND O.CONSTRAINT_SCHEMA != 'sys'
AND O.CONSTRAINT_SCHEMA != 'performance_schema';

Grant System Tables

以下系统表包含有关用户帐户及其所持有权限的授权信息。

• user: User accounts, global privileges, and other nonprivilege columns.

• global_grants: Assignments of dynamic global privileges to users.

• db: Database-level privileges.

• tables_priv: Table-level privileges.

• columns_priv: Column-level privileges.

• procs_priv: Stored procedure and function privileges.

• proxies_priv: Proxy-user privileges.

• default_roles: This table lists default roles to be activated after a user connects and authenticates, or executes SET ROLE DEFAULT.

• role_edges: This table lists edges for role subgraphs.

  A given user table row might refer to a user account or a role. The server can distinguish whether a row represents a user account, a role, or both by consulting the role_edges table for information about relations between authentication IDs.

• password_history: Information about password changes.

例子：查看用户信息

[mysql]> SELECT USER,HOST FROM MYSQL.USER;
+------------------+-----------+
| USER             | HOST      |
+------------------+-----------+
| stone            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

Object Information System Tables

以下系统表包含有关组件、可加载函数和服务器端插件的信息：

• component: The registry for server components installed using INSTALL COMPONENT. Any components listed in this table are installed by a loader service during the server startup sequence.

• func: The registry for loadable functions installed using CREATE FUNCTION. During the normal startup sequence, the server loads functions registered in this table. If the server is started with the --skip-grant-tables option, functions registered in the table are not loaded and are unavailable.

• plugin: The registry for server-side plugins installed using INSTALL PLUGIN. During the normal startup sequence, the server loads plugins registered in this table. If the server is started with the --skip-grant-tables option, plugins registered in the table are not loaded and are unavailable.

Log System Tables

使用以下系统表进行日志记录：

• general_log: The general query log table.
• slow_log: The slow query log table.

日志表使用 CSV 存储引擎。

Server-Side Help System Tables

以下系统表包含服务器端帮助信息：

• help_category: Information about help categories.
• help_keyword: Keywords associated with help topics.
• help_relation: Mappings between help keywords and topics.
• help_topic: Help topic contents.

Time Zone System Tables

以下系统表包含时区信息：

• time_zone: Time zone IDs and whether they use leap seconds.
• time_zone_leap_second: When leap seconds occur.
• time_zone_name: Mappings between time zone IDs and names.
• time_zone_transition, time_zone_transition_type: Time zone descriptions.

Replication System Tables

使用以下系统表来支持复制：

• gtid_executed: Table for storing GTID values.
• ndb_binlog_index: Binary log information for NDB Cluster replication. This table is created only if the server is built with NDBCLUSTER support.
• slave_master_info, slave_relay_log_info, slave_worker_info: Used to store replication information on replica servers.

以上列出的表都使用 InnoDB 存储引擎。

Optimizer System Tables

以下表用于优化器：

• innodb_index_stats, innodb_table_stats: Used for InnoDB persistent optimizer statistics.
• server_cost, engine_cost: The optimizer cost model uses tables that contain cost estimate information about operations that occur during query execution. server_cost contains optimizer cost estimates for general server operations. engine_cost contains estimates for operations specific to particular storage engines.

Miscellaneous System Tables

其余系统表：

• audit_log_filter, audit_log_user: If MySQL Enterprise Audit is installed, these tables provide persistent storage of audit log filter definitions and user accounts.
• firewall_group_allowlist, firewall_groups, firewall_memebership, firewall_users, firewall_whitelist: If MySQL Enterprise Firewall is installed, these tables provide persistent storage for information used by the firewall.
• servers: Used by the FEDERATED storage engine.
• innodb_dynamic_metadata: Used by the InnoDB storage engine to store fast-changing table metadata such as auto-increment counter values and index tree corruption flags. Replaces the data dictionary buffer table that resided in the InnoDB system tablespace.

MySQL Server Logs

MySQL Server 的日志类型有：

Log Type	Information Written to Log
Error log	Problems encountered starting, running, or stopping mysqld
General query log	Established client connections and statements received from clients
Binary log	Statements that change data (also used for replication)
Relay log	Data changes received from a replication source server
Slow query log	Queries that took more than long_query_time seconds to execute
DDL log (metadata log)	Metadata operations performed by DDL statements

• 默认情况下，除了 Windows 上的错误日志外，不启用任何日志。（DDL 日志始终在需要时创建，并且没有用户可配置的选项）
• 默认情况下，MySQL 将启用的日志写入数据目录下。
• 在运行时可以启用或者禁用通用查询日志和慢查询日志，可以修改日志文件名，可以将日志写入文件，或者表，或者两者。
• 中继日志仅用于复制。
• 建议生产环境不要启用通用查询日志。

配置文件 /etc/logrotate.d/mysql 进行日志轮转。

[root@mysql ~]# vi /etc/logrotate.d/mysql
/var/log/mysqld.log
/var/log/mysql.slow
{
rotate 12
copytruncate
monthly
dateext
nocompress
missingok
notifempty
}

Selecting General Query Log and Slow Query Log Output Destinations

参数 log_output 指定日志输出目标，默认为 FILE，表示日志输出到文件。还可以设置为 TABLE 和 NONE。

如果设置为 TABLE，分别将通用查询日志和慢查询日志写入到 general_log 和 slow_log 表。建议生产环境不要设置为 TABLE。

[mysql]> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

参数 general_log 指定是否启用通用查询日志，默认为 OFF。参数 general_log_file 指定其日志文件名。

[mysql]> SHOW VARIABLES LIKE 'general_log%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| general_log      | OFF                   |
| general_log_file | /data/mysql/mysql.log |
+------------------+-----------------------+
2 rows in set (0.00 sec)

参数 slow_query_log 指定是否启用慢查询日志，默认为 OFF。参数 slow_query_log_file 指定其日志文件名。

[mysql]> SHOW VARIABLES LIKE 'slow_query_log%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_query_log      | ON                  |
| slow_query_log_file | /var/log/mysql.slow |
+---------------------+---------------------+
2 rows in set (0.00 sec)

在通用查询日志启用的情况下，可以使用参数 sql_log_off 启用或者禁用当前会话的通用查询日志。

[mysql]> SHOW VARIABLES LIKE 'sql_log_off';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_off   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

Error Log

MySQL 将错误，警告及启动和关闭时的信息写入到错误日志。

在配置文件中使用参数 log-error 指定错误日志的位置。

[mysqld]
log_error=/var/log/mysqld.log

查看参数 log-error：

[mysql]> SHOW VARIABLES LIKE 'log_error';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| log_error     | /var/log/mysqld.log |
+---------------+---------------------+
1 row in set (0.00 sec)

可以使用 FLUSH ERROR LOGS 或者 FLUSH LOGS 语句，或者 mysqladmin flush-logs 命令刷新错误日志。

重命名错误日志示例：

mv host_name.err host_name.err-old
mysqladmin flush-logs error
mv host_name.err-old backup-directory

General Query Log

通用查询日志记录 mysqld 的操作，包括客户端连接和断开以及客户端执行的 SQL 语句。

参考 Selecting General Query Log and Slow Query Log Output Destinations 进行配置。

可以在运行时启用或者禁用通用查询日志：

SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 'OFF';

重命名通用查询日志示例：

$> mv host_name.log host_name-old.log
$> mysqladmin flush-logs general
$> mv host_name-old.log backup-directory

Binary Log

二进制日志记录了对数据库内容修改的事件以及每个语句的时间信息，在事务提交时写入，主要用于：

• 复制
• 恢复

MySQL 默认开启二进制日志（参数 log_bin 为 ON），文件名为 datadir/binlog.xxxxxx。xxxxxx 为 6 位数字，从 000001 开始递增。在以下情况创建新的二进制日志文件：

• 启动或者重启 MySQL
• 刷新日志
• 日志文件达到参数 max_binlog_size 大小，默认 1 G。

查看参数：

[(none)]> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'log_bin_basename';
+------------------+--------------------+
| Variable_name    | Value              |
+------------------+--------------------+
| log_bin_basename | /data/mysql/binlog |
+------------------+--------------------+
1 row in set (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

查看文件：

[root@mysql ~]# ll /data/mysql/binlog.*
-rw-r----- 1 mysql mysql 157 Feb 17 20:47 /data/mysql/binlog.000001
-rw-r----- 1 mysql mysql  16 Feb 17 20:47 /data/mysql/binlog.index

binlog.index 为二进制日志索引文件，记录二进制日志文件名。

[root@mysql ~]# cat /data/mysql/binlog.index
./binlog.000001

可以在配置文件中使用参数 log_bin 指定二进制日志文件的 BASENAME，使用参数 server_id 指定唯一 ID 用于复制。

[mysqld]
server_id=8141
log_bin=/data/mysql/mysql-bin

参数 sql_log_bin 指定是否为会话启用二进制日志。如果不希望某些修改复制到从库，可以临时将该参数设置为 OFF。

[(none)]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

使用 RESET MASTER 语句删除所有二进制日志文件，使用 PURGE BINARY LOGS 语句删除部分二进制日志文件，在配置文件中使用参数 binlog_expire_logs_seconds 指定二进制日志保留时间（参数 expire_logs_days 被废弃了），默认为 2592000 秒（30 天），超过此时间将被自动删除。

[mysqld]
binlog_expire_logs_seconds=432000

使用 mysqlbinlog 查看二进制日志文件内容。

在配置文件中使用参数 binlog_cache_size 指定缓存大小。

[mysqld]
binlog_cache_size=8M

查看参数：

[(none)]> SHOW VARIABLES LIKE 'binlog_cache_size';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| binlog_cache_size | 8388608 |
+-------------------+---------+
1 row in set (0.01 sec)

查看使用缓存的事务数量，以及因缓存不足而使用临时文件的事务数量：

[(none)]> SHOW STATUS LIKE 'Binlog_cache%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0     |
| Binlog_cache_use      | 0     |
+-----------------------+-------+
2 rows in set (0.01 sec)

根据这两个状态值调整参数 binlog_cache_size。

为了在使用 InnoDB 的复制设置中获得最大的持久性和一致性，需要设置（均保持默认值）：

sync_binlog=1
innodb_flush_log_at_trx_commit=1

当 sync_binlog=1 时，表示每次提交事务时都会将 Binlog Cache 里的 Binlog 直接持久化到磁盘。

当 innodb_flush_log_at_trx_commit=1 时，表示每次提交事务时都会将 Redo Log Buffer 里的 Redo Log 直接持久化到磁盘。

Binary Logging Formats

二进制日志格式有：

• Statement-Based：记录的是 SQL 语句。
• Row-Based：默认格式，记录的是受影响的行数据，如果使用 InnoDB 表且事务隔离级别为 READ COMMITTED 则只能使用此格式。
• mixed：默认采用 Statement-Based，在某些情况会使用 Row-Based。

在配置文件中使用参数 binlog_format 指定二进制日志格式，默认为 row。

[mysqld]
binlog_format=row

建议不要在运行时调整参数 binlog_format。

针对 mysql 数据库表操作的日志格式规则如下：

• 直接修改 mysql 数据库表的 DML 语句，使用参数 binlog_format 指定的格式，包括 INSERT, UPDATE, DELETE, REPLACE, DO, LOAD DATA, SELECT 和 TRUNCATE TABLE 语句。
• 间接修改 mysql 数据库表的 DDL 语句，使用 Statement-Based 格式，包括 GRANT, REVOKE, SET PASSWORD, RENAME USER, CREATE (除了 CREATE TABLE ... SELECT), ALTER 和 DROP 语句。
• CREATE TABLE ... SELECT 语句的 CREATE TABLE 部分使用 Statement-Based 格式，SELECT 部分使用参数 binlog_format 指定的格式。

Slow Query Log

慢查询日志记录执行时间超过参数 long_query_time 指定时间的 SQL 语句，且该 SQL 语句至少需要查询参数 min_examined_row_limit 指定的行数。可以使用 mysqldumpslow 命令解析慢查询日志，定位性能不佳的 SQL。执行时间不包括等待锁的时间，在获取到所有锁后开始执行才计时。

MySQL 默认禁用慢查询日志，在配置文件中使用参数 slow_query_log 启用慢查询日志；使用参数 slow_query_log_file 指定日志文件名，默认为 datadir/host_name-slow.log；使用参数 long_query_time 指定超时时间，默认为 10 秒。使用参数 log_short_format 减少日志信息。使用参数 log_timestamps 设置写入到错误日志，通用查询日志及慢查询日志文件的消息时区信息，默认为 UTC。

[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/mysql.slow
long_query_time=10
log_short_format
log_timestamps=SYSTEM

MySQL 默认不记录管理语句（包括 ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE 和 REPAIR TABLE）到慢查询日志，可以使用参数 log_slow_admin_statements 进行调整。

[(none)]> SHOW VARIABLES LIKE 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF   |
+---------------------------+-------+
1 row in set (0.00 sec)

MySQL 默认不记录未使用索引的查询语句到慢查询日志，可以使用参数 log_queries_not_using_indexes 进行调整。

[(none)]> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

如果将参数 log_queries_not_using_indexes 设置为 ON，建议使用参数 log_throttle_queries_not_using_indexes 限制每分钟可写入慢速查询日志的此类查询数。

[(none)]> SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_throttle_queries_not_using_indexes | 0     |
+----------------------------------------+-------+
1 row in set (0.00 sec)

还可以设置参数 log_slow_extra 将更多信息写入到慢查询日志文件，以便辅助性能调优，默认为 OFF。

[(none)]> SHOW VARIABLES LIKE 'log_slow_extra';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| log_slow_extra | OFF   |
+----------------+-------+
1 row in set (0.01 sec)

写入慢查询日志文件的每个语句前面都有一个包含时间戳的 SET 语句。从 MySQL 8.0.14 开始，时间戳为语句开始执行的时间。在 8.0.14 之前，时间戳为语句写入的时间（在语句执行完成后写入）。

从 MySQL 8.0.29 开始，无法解析的语句（例如，由于语法错误）不会写入慢查询日志。

Account Management

连接到 MySQL Server 需要用户名和密码，操作数据库对象，则需要权限。

MySQL 根据客户端提供的用户名，密码以及客户端主机名（IP）判断是否允许该用户登录，之后对该连接发出的每条 SQL 进行鉴权。

Account User Names and Passwords

MySQL 存储用户账户到 mysql 数据库的 user 表。用户账户包含用户名及允许连接的客户端主机。

• 用户名最长 32 字符。
• 密码加密存储在 user 表。

Privileges Provided by MySQL

MySQL 权限分为：

• 管理权限：允许用户执行管理操作，全局授予。
• 数据库权限：允许操作数据库及其对象，可全局授予所有数据库，也可以只授予指定数据库。
• 对象权限：允许操作数据库指定对象。

还可以分为：

• 静态权限，MySQL 自带，始终可用。
• 动态权限，启用实现动态权限的组件时才可用，包括更细分的权限替换被废弃的 SUPER 静态权限。

帐户权限信息存储在 mysql 数据库的授权表中。

• 在 GRANT 和REVOKE 语句中使用的静态权限名称及其对应的授权表字段名参考：Permissible Static Privileges for GRANT and REVOKE
• 在 GRANT 和REVOKE 语句中使用的动态权限名称参考：Permissible Dynamic Privileges for GRANT and REVOKE

动态权限仅适用于全局级别，存储在 mysql.global_grants 表中：

• 在启动时自动注册 global_grants 表中的权限。

• GRANT 和 REVOKE 语句修改 global_grants 表的内容。

• global_grants 表中列出的动态权限是永久性的，服务器关闭时不会删除它们。

[(none)]> SELECT * FROM MYSQL.GLOBAL_GRANTS;
+------------------+-----------+------------------------------+-------------------+
| USER             | HOST      | PRIV                         | WITH_GRANT_OPTION |
+------------------+-----------+------------------------------+-------------------+
| mysql.infoschema | localhost | AUDIT_ABORT_EXEMPT           | N                 |
| mysql.infoschema | localhost | FIREWALL_EXEMPT              | N                 |
| mysql.infoschema | localhost | SYSTEM_USER                  | N                 |
| mysql.session    | localhost | AUDIT_ABORT_EXEMPT           | N                 |
| mysql.session    | localhost | AUTHENTICATION_POLICY_ADMIN  | N                 |
| mysql.session    | localhost | BACKUP_ADMIN                 | N                 |
| mysql.session    | localhost | CLONE_ADMIN                  | N                 |
| mysql.session    | localhost | CONNECTION_ADMIN             | N                 |
| mysql.session    | localhost | FIREWALL_EXEMPT              | N                 |
| mysql.session    | localhost | PERSIST_RO_VARIABLES_ADMIN   | N                 |
| mysql.session    | localhost | SESSION_VARIABLES_ADMIN      | N                 |
| mysql.session    | localhost | SYSTEM_USER                  | N                 |
| mysql.session    | localhost | SYSTEM_VARIABLES_ADMIN       | N                 |
| mysql.sys        | localhost | AUDIT_ABORT_EXEMPT           | N                 |
| mysql.sys        | localhost | FIREWALL_EXEMPT              | N                 |
| mysql.sys        | localhost | SYSTEM_USER                  | N                 |
| root             | localhost | APPLICATION_PASSWORD_ADMIN   | Y                 |
| root             | localhost | AUDIT_ABORT_EXEMPT           | Y                 |
| root             | localhost | AUDIT_ADMIN                  | Y                 |
| root             | localhost | AUTHENTICATION_POLICY_ADMIN  | Y                 |
| root             | localhost | BACKUP_ADMIN                 | Y                 |
| root             | localhost | BINLOG_ADMIN                 | Y                 |
| root             | localhost | BINLOG_ENCRYPTION_ADMIN      | Y                 |
| root             | localhost | CLONE_ADMIN                  | Y                 |
| root             | localhost | CONNECTION_ADMIN             | Y                 |
| root             | localhost | ENCRYPTION_KEY_ADMIN         | Y                 |
| root             | localhost | FIREWALL_EXEMPT              | Y                 |
| root             | localhost | FLUSH_OPTIMIZER_COSTS        | Y                 |
| root             | localhost | FLUSH_STATUS                 | Y                 |
| root             | localhost | FLUSH_TABLES                 | Y                 |
| root             | localhost | FLUSH_USER_RESOURCES         | Y                 |
| root             | localhost | GROUP_REPLICATION_ADMIN      | Y                 |
| root             | localhost | GROUP_REPLICATION_STREAM     | Y                 |
| root             | localhost | INNODB_REDO_LOG_ARCHIVE      | Y                 |
| root             | localhost | INNODB_REDO_LOG_ENABLE       | Y                 |
| root             | localhost | PASSWORDLESS_USER_ADMIN      | Y                 |
| root             | localhost | PERSIST_RO_VARIABLES_ADMIN   | Y                 |
| root             | localhost | REPLICATION_APPLIER          | Y                 |
| root             | localhost | REPLICATION_SLAVE_ADMIN      | Y                 |
| root             | localhost | RESOURCE_GROUP_ADMIN         | Y                 |
| root             | localhost | RESOURCE_GROUP_USER          | Y                 |
| root             | localhost | ROLE_ADMIN                   | Y                 |
| root             | localhost | SENSITIVE_VARIABLES_OBSERVER | Y                 |
| root             | localhost | SERVICE_CONNECTION_ADMIN     | Y                 |
| root             | localhost | SESSION_VARIABLES_ADMIN      | Y                 |
| root             | localhost | SET_USER_ID                  | Y                 |
| root             | localhost | SHOW_ROUTINE                 | Y                 |
| root             | localhost | SYSTEM_USER                  | Y                 |
| root             | localhost | SYSTEM_VARIABLES_ADMIN       | Y                 |
| root             | localhost | TABLE_ENCRYPTION_ADMIN       | Y                 |
| root             | localhost | XA_RECOVER_ADMIN             | Y                 |
+------------------+-----------+------------------------------+-------------------+
51 rows in set (0.00 sec)

使用 GRANT 和 REVOKE 处理 ALL 权限：

• GRANT ALL 会授予所有静态权限和所有注册的动态权限。
• REVOKE ALL 会回收所有静态权限和动态权限。

在 MySQL 8.0 中，使用动态权限来替换 SUPER 权限，以遵守最小权限原则。

查看被授予 SUPER 权限的用户：

SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE PRIVILEGE_TYPE = 'SUPER';

对以上查询出来的用户，先授予对应的动态权限，再回收 SUPER 权限：

GRANT BINLOG_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost';
REVOKE SUPER ON *.* FROM 'u1'@'localhost';

Grant Tables

前面提到帐户权限信息存储在 mysql 数据库的授权表中，不要使用 DML 语句直接修改这些表。

包含授权信息的表有：

• user: User accounts, static global privileges, and other nonprivilege columns.
• global_grants: Dynamic global privileges.
• db: Database-level privileges.
• tables_priv: Table-level privileges.
• columns_priv: Column-level privileges.
• procs_priv: Stored procedure and function privileges.
• proxies_priv: Proxy-user privileges.
• default_roles: Default user roles.
• role_edges: Edges for role subgraphs.
• password_history: Password change history.

每个授权表都包含作用域（Scope columns）字段和权限（Privilege columns）字段：

• 多个作用域字段指定权限授予的范围，例如 user 表的 Host 字段和 User 字段，db 表的 Host 字段， User 字段和 Db 字段。
• 多个权限字段指定具体授予哪些权限。

授权表具体用途如下：

• user 表列出可以连接的用户及其静态全局权限，在此表中授予的权限适用于所有数据库。
• global_grants 表列出当前分配给用户的动态权限。
• db 表确定哪些用户可以从哪些主机访问哪些数据库，在数据库级别授予的权限适用于数据库和数据库中的所有对象。
• tables_priv 和 columns_priv 分别在表级和列级授予权限。
• procs_priv 列出对于存储过程和函数的权限。

MySQL 在启动的时候读取授权表到内存中，如果使用 DML 语句修改了授权表，则需要使用 FLUSH PRIVILEGES 语句或者执行 mysqladmin flush-privileges 或 mysqladmin reload 命令重载授权表。

修改权限后，使用 SHOW GRANTS 语句检查是否生效。

[(none)]> SHOW GRANTS FOR 'stone'@'%';
+-----------------------------------+
| Grants for stone@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `stone`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)

使用 SHOW CREATE USER 查看用户的非权限属性。

[(none)]> SHOW CREATE USER 'stone'@'%'\G
*************************** 1. row ***************************
CREATE USER for stone@%: CREATE USER `stone`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$j}ZoK7ptlPdoJuy,QZGzbMXc8mBFlXMBJfHmAPWa/ViT3MRvvOPCg.nBJ3y1' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)

The user and db Grant Tables

MySQL 在访问控制的第一阶段和第二阶段使用 mysql 数据库中的 user 表和 db 表，它们的列如下：

Table Name	user	db
Scope columns	Host	Host
	User	Db
		User
Privilege columns	Select_priv	Select_priv
	Insert_priv	Insert_priv
	Update_priv	Update_priv
	Delete_priv	Delete_priv
	Index_priv	Index_priv
	Alter_priv	Alter_priv
	Create_priv	Create_priv
	Drop_priv	Drop_priv
	Grant_priv	Grant_priv
	Create_view_priv	Create_view_priv
	Show_view_priv	Show_view_priv
	Create_routine_priv	Create_routine_priv
	Alter_routine_priv	Alter_routine_priv
	Execute_priv	Execute_priv
	Trigger_priv	Trigger_priv
	Event_priv	Event_priv
	Create_tmp_table_priv	Create_tmp_table_priv
	Lock_tables_priv	Lock_tables_priv
	References_priv	References_priv
	Reload_priv
	Shutdown_priv
	Process_priv
	File_priv
	Show_db_priv
	Super_priv
	Repl_slave_priv
	Repl_client_priv
	Create_user_priv
	Create_tablespace_priv
	Create_role_priv
	Drop_role_priv
Security columns	ssl_type
	ssl_cipher
	x509_issuer
	x509_subject
	plugin
	authentication_string
	password_expired
	password_last_changed
	password_lifetime
	account_locked
	Password_reuse_history
	Password_reuse_time
	Password_require_current
	User_attributes
Resource control columns	max_questions
	max_updates
	max_connections
	max_user_connections

• password_lifetime 指定密码有效期（以天为单位），0 表示密码不过期，NULL （默认值）表示使用参数 default_password_lifetime （默认值为 0）指定的全局有效期。
• account_locked 表示账户是否被锁定。

The tables_priv and columns_priv Grant Tables

在访问控制的第二阶段，MySQL 执行请求验证，以确保每个客户端对其发出的每个请求都有足够的权限。除了 user 和 db 授权表之外，还可以查询 tables_priv 表和 columns_priv 表，它们的列如下：

Table Name	tables_priv	columns_priv
Scope columns	Host	Host
	Db	Db
	User	User
	Table_name	Table_name
		Column_name
Privilege columns	Table_priv	Column_priv
	Column_priv
Other columns	Timestamp	Timestamp
	Grantor

The procs_priv Grant Table

为了验证涉及程序（包括存储过程和函数）的请求，使用 procs_priv 表，该表具有的列如下：

Table Name	procs_priv
Scope columns	Host
	Db
	User
	Routine_name
	Routine_type
Privilege columns	Proc_priv
Other columns	Timestamp
	Grantor

Grant Table Privilege Column Properties

user 和 db 表一个权限字段对应一个权限，权限字段类型为 ENUM('N','Y') ，默认为 N。

tables_priv，columns_priv 和 procs_priv 表的权限字段类型为 SET，值如下：

Table Name	Column Name	Possible Set Elements
tables_priv	Table_priv	'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter', 'Create View', 'Show view', 'Trigger'
tables_priv	Column_priv	'Select', 'Insert', 'Update', 'References'
columns_priv	Column_priv	'Select', 'Insert', 'Update', 'References'
procs_priv	Proc_priv	'Execute', 'Alter Routine', 'Grant'

只有 user 和 global_grants 表指定管理权限，例如 RELOAD， SHUTDOWN 和 SYSTEM_VARIABLES_ADMIN 权限。

Specifying Account Names

MySQL 帐户由用户名和主机名组成，规则如下：

• 语法为 'user_name'@'host_name'。
• @'host_name' 为可选部分，如果不指定，则默认为 @'%'，表示支持所有主机连接。
• host_name 可以使用主机名或者 IP 地址，localhost 表示本机，127.0.0.1 表示 IPV4 环回地址，::1 表示 IPV6 环回地址。
• 如果用户名有特殊字符，或者主机名有特殊字符或通配符，则需要使用引号分别括起来。
• 引号可以使用反引号（'）、单引号（'）或双引号（“）。

MySQL 将帐户名称存储在 mysql 数据库的授权表中：

• user 表一行为一个账户，User 字段为用户名，Host 字段为主机名，此表还指示帐户具有哪些全局权限。
• 其他授权表指示帐户对数据库和数据库中的对象具有的权限。
• 用户名区分大小写，主机名不区分大小写。

Access Control

访问控制的第一阶段是对客户端连接进行验证，包括：

• 验证身份以及密码
• 账户是否被锁定

身份包括用户名和主机名，user 表 User 字段和 Host 字段的各种组合示例：

User Value	Host Value	Permissible Connections
'fred'	'h1.example.net'	fred, connecting from h1.example.net
''	'h1.example.net'	Any user, connecting from h1.example.net
'fred'	'%'	fred, connecting from any host
''	'%'	Any user, connecting from any host
'fred'	'%.example.net'	fred, connecting from any host in the example.net domain
'fred'	'x.example.%'	fred, connecting from x.example.net, x.example.com, x.example.edu, and so on; this is probably not useful
'fred'	'198.51.100.177'	fred, connecting from the host with IP address 198.51.100.177
'fred'	'198.51.100.%'	fred, connecting from any host in the 198.51.100 class C subnet
'fred'	'198.51.100.0/255.255.255.0'	Same as previous example

如果传入连接的客户端主机名和用户名可以匹配 user 表中的多行时，则 Host 字段值越具体越先匹配。

非 TCP 连接作为本地连接，匹配 localhost 主机。

例子：查看当前连接匹配到的用户名和主机名

[(none)]> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

​

访问控制的第二阶段是对客户端发起的请求进行权限校验。需要使用 user， global_grants， db， tables_priv， columns_priv 或 procs_priv 授权表。

• user 和 global_grants 表授予全局权限，表里的权限适用于全局，无论默认数据库是什么。如果 user 表授予用户 DELETE 权限，则可以从任何数据库的任何表中删除行。建议只将这些权限授予管理员。
• db 表授予适用于数据库的权限。
• tables_priv，columns_priv 和 procs_priv 表分别授予适用于表，字段和程序的权限。

Adding Accounts, Assigning Privileges, and Dropping Accounts

MySQL 使用以下 SQL 语句管理账户：

• CREATE USER 和 DROP USER 创建和删除账户。
• GRANT 和 REVOKE 授予和回收权限。
• SHOW GRANTS 查看权限。

例子：创建管理用户，授予所有全局权限

[(none)]> CREATE USER 'stone'@'localhost' IDENTIFIED BY 'Abcd1234!';
Query OK, 0 rows affected (0.01 sec)

[(none)]> GRANT ALL ON *.* TO 'stone'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)

[(none)]> SHOW GRANTS FOR 'stone'@'localhost'\G
*************************** 1. row ***************************
Grants for stone@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `stone`@`localhost` WITH GRANT OPTION
*************************** 2. row ***************************
Grants for stone@localhost: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `stone`@`localhost` WITH GRANT OPTION
2 rows in set (0.00 sec)

例子：创建应用用户，授予指定数据库的权限

[(none)]> CREATE USER 'app'@'192.168.44.%' IDENTIFIED BY 'Abcd1234!';
Query OK, 0 rows affected (0.01 sec)

[(none)]> GRANT ALL ON menagerie.* TO 'app'@'192.168.44.%';
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW GRANTS FOR 'app'@'192.168.44.%'\G
*************************** 1. row ***************************
Grants for app@192.168.44.%: GRANT USAGE ON *.* TO `app`@`192.168.44.%`
*************************** 2. row ***************************
Grants for app@192.168.44.%: GRANT ALL PRIVILEGES ON `menagerie`.* TO `app`@`192.168.44.%`
2 rows in set (0.00 sec)

例子：回收权限

[(none)]> REVOKE ALL ON menagerie.* FROM 'app'@'192.168.44.%';
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW GRANTS FOR 'app'@'192.168.44.%'\G
*************************** 1. row ***************************
Grants for app@192.168.44.%: GRANT USAGE ON *.* TO `app`@`192.168.44.%`
1 row in set (0.00 sec)

例子：删除账户

[(none)]> DROP USER 'app'@'192.168.44.%';
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT USER,HOST FROM MYSQL.USER;
+------------------+-----------+
| USER             | HOST      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| stone            | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

Reserved Accounts

安装 MySQL 会自动创建以下用户：

'root'@'localhost ：用于管理数据库，拥有所有权限，可以执行任何操作。

'mysql.sys'@'localhost' ： sys 数据库对象的定义者，默认锁定。

'mysql.session'@'localhost' : 插件使用，默认锁定。

'mysql.infoschema'@'localhost' ：information_schema 数据库视图的定义者，默认锁定。

Account Categories

MySQL 从 8.0.16 开始，使用 SYSTEM_USER 权限区分系统用户和普通用户。

• 有 SYSTEM_USER 权限的账户为系统用户，系统用户可以修改系统用户和普通用户。
• 没有 SYSTEM_USER 权限的账户为普通用户，普通用户只能修改普通用户。

此权限是为了解决之前普通用户权限过大的问题。例如之前具有 CREATE USER 语句权限的用户可以修改和删除

root 用户，而现在 root 用户有了 SYSTEM_USER 权限后，则只有具有该权限的用户才能修改 root 用户了。

[(none)]> SELECT * FROM MYSQL.GLOBAL_GRANTS WHERE PRIV='SYSTEM_USER';
+------------------+-----------+-------------+-------------------+
| USER             | HOST      | PRIV        | WITH_GRANT_OPTION |
+------------------+-----------+-------------+-------------------+
| mysql.infoschema | localhost | SYSTEM_USER | N                 |
| mysql.session    | localhost | SYSTEM_USER | N                 |
| mysql.sys        | localhost | SYSTEM_USER | N                 |
| root             | localhost | SYSTEM_USER | Y                 |
+------------------+-----------+-------------+-------------------+
4 rows in set (0.00 sec)

[(none)]> SELECT CURRENT_USER();
+-----------------+
| CURRENT_USER()  |
+-----------------+
| stone@localhost |
+-----------------+
1 row in set (0.00 sec)

[(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Abcd1234!';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

那么在创建应用对应的数据库账户时，就不应该授予其 SYSTEM_USER 权限，默认其为普通用户，且不要授予其对 mysql 数据库的修改权限，以防止普通用户通过修改 mysql 数据库中的授权表来获取额外的权限。

MySQL 建议通过 CREATE USER 和 GRANT 语句进行账户管理。

例子：创建一个普通用户，授予除 mysql 数据库外的所有数据库权限。

CREATE USER u1 IDENTIFIED BY 'password';

GRANT ALL ON *.* TO u1 WITH GRANT OPTION;
-- GRANT ALL includes SYSTEM_USER, so at this point
-- u1 can manipulate system or regular accounts

REVOKE SYSTEM_USER ON *.* FROM u1;
-- Revoking SYSTEM_USER makes u1 a regular user;
-- now u1 can use account-management statements
-- to manipulate only regular accounts

REVOKE ALL ON mysql.* FROM u1;
-- This partial revoke prevents u1 from directly
-- modifying grant tables to manipulate accounts

例子：创建用户，可以查看不能增删改 mysql 数据库，可以增删改查其他所有数据库。

CREATE USER u2 IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u2;
REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u2;

例子：创建用户，只能查看 mysql 数据库的指定表或者指定字段。

CREATE USER u3 IDENTIFIED BY 'password';
GRANT ALL ON *.* TO u3;
REVOKE ALL ON mysql.* FROM u3;
GRANT SELECT ON mysql.db TO u3;
GRANT SELECT(Host,User) ON mysql.user TO u3;

Partial Revokes

MySQL 从 8.0.16 开始，支持权限的部分回收，即类似于上面的例子，先授予用户所有权限，再回收指定的权限。该功能由参数 partial_revokes 控制，默认关闭，仅适用于模式级别。

[(none)]> SHOW VARIABLES LIKE 'partial_revokes';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

[(none)]> SET GLOBAL partial_revokes=ON;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'partial_revokes';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

例子：创建用户，授予模式级别权限，然后回收。

mysql> CREATE USER u1 IDENTIFIED BY 'Abcd1234!';
mysql> GRANT UPDATE ON mysql.* TO u1;
mysql> GRANT DELETE ON world.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------+
| Grants for u1@%                       |
+---------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`        |
| GRANT UPDATE ON `mysql`.* TO `u1`@`%` |
| GRANT DELETE ON `world`.* TO `u1`@`%` |
+---------------------------------------+

mysql> REVOKE UPDATE ON mysql.* FROM u1;
mysql> REVOKE DELETE ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+--------------------------------+
| Grants for u1@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%` |
+--------------------------------+

例子：创建用户，可以查看不能增删改 mysql 数据库，可以增删改查其他所有数据库。

mysql> DROP USER u1;
mysql> CREATE USER u1 IDENTIFIED BY 'Abcd1234!';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;
mysql> REVOKE INSERT, UPDATE, DELETE ON mysql.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+----------------------------------------------------------+
| Grants for u1@%                                          |
+----------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%`  |
| REVOKE INSERT, UPDATE, DELETE ON `mysql`.* FROM `u1`@`%` |
+----------------------------------------------------------+

再次授予对所有数据库的增加权限，移除了 REVOKE 语句中的 INSERT 限制。

mysql> GRANT INSERT ON *.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE UPDATE, DELETE ON `mysql`.* FROM `u1`@`%`        |
+---------------------------------------------------------+

再次授予对 mysql 数据库的更新权限，移除了 REVOKE 语句中的 UPDATE 限制。

mysql> GRANT UPDATE ON mysql.* TO u1;
mysql> SHOW GRANTS FOR u1;
+---------------------------------------------------------+
| Grants for u1@%                                         |
+---------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `u1`@`%` |
| REVOKE DELETE ON `mysql`.* FROM `u1`@`%`                |
+---------------------------------------------------------+

再次授予对所有数据库的删除权限，移除了 REVOKE 语句中的 INSERT 限制。

mysql> REVOKE DELETE ON *.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+-------------------------------------------------+
| Grants for u1@%                                 |
+-------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON *.* TO `u1`@`%` |
+-------------------------------------------------+

例子：用户同时具有全局和模式级别的权限，则需要使用 REVOKE 语句两次才能实现部分回收。

mysql> DROP USER u1;
mysql> CREATE USER u1 IDENTIFIED BY 'Abcd1234!';
mysql> GRANT SELECT, INSERT ON *.* TO u1;
mysql> GRANT INSERT ON world.* TO u1;
mysql> SHOW GRANTS FOR u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
| GRANT INSERT ON `world`.* TO `u1`@`%`   |
+-----------------------------------------+

mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
+-----------------------------------------+

mysql> REVOKE INSERT ON world.* FROM u1;
mysql> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+

一旦将参数 partial_revokes 设置为 ON 启用权限部分回收，则如果存在权限部分回收，则不能禁用该参数。

[(none)]> SET GLOBAL partial_revokes=OFF;
ERROR 3896 (HY000): At least one partial revoke exists on a database. The system variable '@@partial_revokes' must be set to ON.

[(none)]> SHOW VARIABLES LIKE 'partial_revokes';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | ON    |
+-----------------+-------+
1 row in set (0.01 sec)

如果需要禁用参数 partial_revokes，则应该先找到使用权限部分回收的用户。

[(none)]> SELECT User, Host, User_attributes->>'$.Restrictions'
    ->    FROM mysql.user WHERE User_attributes->>'$.Restrictions' <> '';
+------+------+---------------------------------------------------+
| User | Host | User_attributes->>'$.Restrictions'                |
+------+------+---------------------------------------------------+
| u1   | %    | [{"Database": "world", "Privileges": ["INSERT"]}] |
+------+------+---------------------------------------------------+
1 row in set (0.00 sec)

[(none)]> SHOW GRANTS FOR u1;
+------------------------------------------+
| Grants for u1@%                          |
+------------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%`  |
| REVOKE INSERT ON `world`.* FROM `u1`@`%` |
+------------------------------------------+
2 rows in set (0.01 sec)

然后移除权限限制。

[(none)]> GRANT INSERT ON world.* TO u1;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW GRANTS FOR u1;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT SELECT, INSERT ON *.* TO `u1`@`%` |
+-----------------------------------------+
1 row in set (0.00 sec)

然后禁用参数 partial_revokes。

[(none)]> SET GLOBAL partial_revokes=OFF;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SHOW VARIABLES LIKE 'partial_revokes';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)

在复制环境中，需要将所有主机的参数 partial_revokes 设置为一致。

在生产环境慎用该功能，而应该基于最小权限原则对用户进行正向授权。

When Privilege Changes Take Effect

• 当不使用参数 --skip-grant-tables 启动 mysqld 时，则会读取所有授权表到内存中。
• 如果使用账户管理语句修改授权表，则会立即再次加载授权表到内存。
• 如果使用 DML 语句直接修改授权表（不推荐），则需要手动执行 FLUSH PRIVILEGES 语句或者执行 mysqladmin flush-privileges 或 mysqladmin reload 命令重载授权表。
• 当使用参数 --skip-grant-tables 启动 mysqld 时，不会读取授权表，忘记 root 用户密码时可以使用此参数进入数据库重置密码。

Password Management

例子：创建用户并设置密码

[(none)]> CREATE USER 'stone'@'localhost' IDENTIFIED BY 'Abcd1234!';
Query OK, 0 rows affected (0.01 sec)

例子：修改密码

[(none)]> ALTER USER 'stone'@'localhost' IDENTIFIED BY 'Asdf4321!';
Query OK, 0 rows affected (0.02 sec)

Password Expiration Policy

MySQL 能够手动设置帐户密码过期，还可以配置自动密码过期的策略。过期策略可以全局建立，也可以对某个账户单独配置。

例子：手动设置密码过期

[(none)]> SELECT Host,User,password_expired,password_last_changed,password_lifetime FROM MYSQL.USER WHERE User='stone';
+-----------+-------+------------------+-----------------------+-------------------+
| Host      | User  | password_expired | password_last_changed | password_lifetime |
+-----------+-------+------------------+-----------------------+-------------------+
| localhost | stone | N                | 2023-02-27 16:31:03   |              NULL |
+-----------+-------+------------------+-----------------------+-------------------+
1 row in set (0.01 sec)

[(none)]> ALTER USER 'stone'@'localhost' PASSWORD EXPIRE;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT Host,User,password_expired,password_last_changed,password_lifetime FROM MYSQL.USER WHERE User='stone';
+-----------+-------+------------------+-----------------------+-------------------+
| Host      | User  | password_expired | password_last_changed | password_lifetime |
+-----------+-------+------------------+-----------------------+-------------------+
| localhost | stone | Y                | 2023-02-27 16:31:03   |              NULL |
+-----------+-------+------------------+-----------------------+-------------------+
1 row in set (0.01 sec)

--密码过期用户执行操作会提示重置密码
[(none)]> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

[(none)]> ALTER USER 'stone'@'localhost' IDENTIFIED BY 'Abcd1234!';
Query OK, 0 rows affected (0.01 sec)

[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

使用参数 default_password_lifetime 配置全局自动密码过期时间间隔，默认值为 0，表示禁用自动密码过期。

例子：在参数文件中配置所有用户密码 180 天过期

[mysqld]
default_password_lifetime=180

例子：在运行时配置密码过期时间并写入到 datadir/mysqld-auto.cnf 文件，立即生效

SET PERSIST default_password_lifetime = 180;

例子：修改某个用户的密码过期时间

[(none)]> ALTER USER 'stone'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT Host,User,password_expired,password_last_changed,password_lifetime FROM MYSQL.USER WHERE User='stone';
+-----------+-------+------------------+-----------------------+-------------------+
| Host      | User  | password_expired | password_last_changed | password_lifetime |
+-----------+-------+------------------+-----------------------+-------------------+
| localhost | stone | Y                | 2023-02-27 16:31:03   |                90 |
+-----------+-------+------------------+-----------------------+-------------------+
1 row in set (0.00 sec)

例子：修改某个用户的密码永不过期

[(none)]> ALTER USER 'stone'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SELECT Host,User,password_expired,password_last_changed,password_lifetime FROM MYSQL.USER WHERE User='stone';
+-----------+-------+------------------+-----------------------+-------------------+
| Host      | User  | password_expired | password_last_changed | password_lifetime |
+-----------+-------+------------------+-----------------------+-------------------+
| localhost | stone | Y                | 2023-02-27 16:31:03   |                 0 |
+-----------+-------+------------------+-----------------------+-------------------+
1 row in set (0.00 sec)

Password Reuse Policy

MySQL 可以限制密码重用，可以根据密码更改次数和/或已用时间建立重用策略。可以全局建立，也可以对某个账户单独配置。

• 次数限制：新密码不能与最近几次密码中的任何一个相同。
• 时间限制：新密码不得属于过去多少天内以前选择的密码。

使用参数 password_history 配置次数限制，使用参数 password_reuse_interval 配置时间限制。

例子：在参数文件中配置密码重用次数限制为 6 次，时间限制为 365 天

[mysqld]
password_history=6
password_reuse_interval=365

例子：在运行时配置密码重用次数限制为 6 次，时间限制为 365 天，并写入到 datadir/mysqld-auto.cnf 文件，立即生效

SET PERSIST password_history = 6;
SET PERSIST password_reuse_interval = 365;

例子：修改某个用户的密码重用策略

[(none)]> SELECT Host,User,Password_reuse_history,Password_reuse_time FROM MYSQL.USER WHERE User='stone';
+-----------+-------+------------------------+---------------------+
| Host      | User  | Password_reuse_history | Password_reuse_time |
+-----------+-------+------------------------+---------------------+
| localhost | stone |                   NULL |                NULL |
+-----------+-------+------------------------+---------------------+
1 row in set (0.00 sec)

[(none)]> ALTER USER 'stone'@'localhost' PASSWORD HISTORY 5;
Query OK, 0 rows affected (0.00 sec)

[(none)]> ALTER USER 'stone'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT Host,User,Password_reuse_history,Password_reuse_time FROM MYSQL.USER WHERE User='stone';
+-----------+-------+------------------------+---------------------+
| Host      | User  | Password_reuse_history | Password_reuse_time |
+-----------+-------+------------------------+---------------------+
| localhost | stone |                      5 |                 365 |
+-----------+-------+------------------------+---------------------+
1 row in set (0.00 sec)

例子：修改某个用户的密码重用策略与全局一致

[(none)]> ALTER USER 'stone'@'localhost' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT Host,User,Password_reuse_history,Password_reuse_time FROM MYSQL.USER WHERE User='stone';
+-----------+-------+------------------------+---------------------+
| Host      | User  | Password_reuse_history | Password_reuse_time |
+-----------+-------+------------------------+---------------------+
| localhost | stone |                   NULL |                NULL |
+-----------+-------+------------------------+---------------------+
1 row in set (0.00 sec)

Failed-Login Tracking and Temporary Account Locking

可以在 CREATE USER 和 ALTER USER 语句中使用 FAILED_LOGIN_ATTEMPTS 和 PASSWORD_LOCK_TIME 选择指定登录失败次数和密码锁定天数。

• FAILED_LOGIN_ATTEMPTS N： N 表示次数，默认为 0 表示禁用。
• PASSWORD_LOCK_TIME {N | UNBOUNDED}：N 表示天数，默认为 0 表示禁用，UNBOUNDED 表示天数无限。

例子：配置用户失败登录 10 次即锁定账户

[(none)]> ALTER USER 'stone'@'localhost' FAILED_LOGIN_ATTEMPTS 10 PASSWORD_LOCK_TIME UNBOUNDED;
Query OK, 0 rows affected (0.01 sec)

失败登录超过 10 次报错

ERROR 3955 (HY000): Access denied for user 'stone'@'localhost'. Account is blocked for unlimited day(s) (unlimited day(s) remaining) due to 10 consecutive failed logins.

Account Locking

使用 CREATE USER 和 ALTER USER 语句的 ACCOUNT LOCK 和 ACCOUNT UNLOCK 子句锁定和解锁用户账户。

例子：解锁账户

[(none)]> ALTER USER 'stone'@'localhost' ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT Host,User,account_locked FROM MYSQL.USER WHERE User='stone';
+-----------+-------+----------------+
| Host      | User  | account_locked |
+-----------+-------+----------------+
| localhost | stone | N              |
+-----------+-------+----------------+
1 row in set (0.00 sec)

[(none)]> ALTER USER 'stone'@'localhost' ACCOUNT LOCK;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT Host,User,account_locked FROM MYSQL.USER WHERE User='stone';
+-----------+-------+----------------+
| Host      | User  | account_locked |
+-----------+-------+----------------+
| localhost | stone | Y              |
+-----------+-------+----------------+
1 row in set (0.01 sec)

Setting Account Resource Limits

使用参数 max_user_connections 指定任一账户的并发连接数，默认值为 0 ，表示无限制。

[(none)]> SHOW VARIABLES LIKE 'max_user_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_user_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

除此以外，MySQL 可以对单个用户做的限制有：

• 帐户每小时可以发出的查询数，默认为 0，表示无限制。

• 帐户每小时可以发布的更新数，默认为 0，表示无限制。

• 帐户每小时可以连接到服务器的次数，默认为 0，表示无限制。

• 帐户同时连接到服务器的数量，如果不设置或保持默认值为 0，则使用参数 max_user_connections 的设置。

客户端可以发出的任何语句都计入查询限制。只有修改数据库或表的语句才计入更新限制。

使用 CREATE USER 语句在创建用户的时候指定这些限制。使用 ALTER USER 语句修改限制。

例子：为账户指定资源限制

[(none)]> SELECT Host,User,max_questions,max_updates,max_connections,max_user_connections FROM MYSQL.USER WHERE User='stone';
+-----------+-------+---------------+-------------+-----------------+----------------------+
| Host      | User  | max_questions | max_updates | max_connections | max_user_connections |
+-----------+-------+---------------+-------------+-----------------+----------------------+
| localhost | stone |             0 |           0 |               0 |                    0 |
+-----------+-------+---------------+-------------+-----------------+----------------------+
1 row in set (0.01 sec)

[(none)]> ALTER USER 'stone'@'localhost' WITH 
    ->    MAX_QUERIES_PER_HOUR 20
    ->    MAX_UPDATES_PER_HOUR 10
    ->    MAX_CONNECTIONS_PER_HOUR 10
    ->    MAX_USER_CONNECTIONS 10;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT Host,User,max_questions,max_updates,max_connections,max_user_connections FROM MYSQL.USER WHERE User='stone';
+-----------+-------+---------------+-------------+-----------------+----------------------+
| Host      | User  | max_questions | max_updates | max_connections | max_user_connections |
+-----------+-------+---------------+-------------+-----------------+----------------------+
| localhost | stone |            20 |          10 |              10 |                   10 |
+-----------+-------+---------------+-------------+-----------------+----------------------+
1 row in set (0.01 sec)

当任何账户对任何资源的使用施加了非零限制时，将进行资源使用计数。

当 MySQL 运行时，会计算每个帐户使用资源的次数。如果某个帐户在过去一小时内达到其连接数限制，MySQL 将拒绝该帐户的进一步连接，直到时间结束。同样，如果帐户达到其查询或更新数限制，MySQL 将拒绝进一步的查询或更新，直到时间结束。在所有这些情况下，MySQL 都会发出相应的错误消息。

可以针对所有账户全局重置当前每小时资源使用计数，也可以为给定账户单独重置：

• 使用 FLUSH USER_RESOURCES 语句重置所有账户的资源使用计数（也可以使用 FLUSH PRIVILEGES 语句或者 mysqladmin reload 命令）。
• 可以通过再次设置其限制将单个帐户的计数重置为零。

MySQL 启动时，所有计数都从零开始。

Troubleshooting Problems Connecting to MySQL

连接到 MySQL 时遇到的问题及可以采取的措施：

• 确保数据库是正常运行状态，否则报如下错误：

$> mysql
ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
$> mysql
ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (111)

• 确保配置了正确的网络参数。是否配置了 skip_networking 或者 bind_address 参数。
• 确认是否存在防火墙，阻止了连接。
• 确认授权表是否被正确安装。
• 使用 root 账户登录，没有输入密码报如下错误：

$> mysql -u root 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

• 确认是否使用了正确的连接参数，可以使用 --no-defaults 命令行选项来禁止使用配置文件：

$> mysqladmin --no-defaults -u root version

• 确认使用了正确的密码，否则报错如下：

$> mysqladmin -u root -pxxxx ver
Access denied for user 'root'@'localhost' (using password: YES)

• 如果不显式指定主机名，则默认使用 localhost，也可以指定 --host=127.0.0.1 来使用 TCP/IP 连接到本地的 MySQL Server。
• 确认客户端可以解析连接的主机名，否则报错如下：

$> mysqladmin -u root -pxxxx -h some_hostname ver
Access denied for user 'root'@'' (using password: YES)

• 确认用户是否具有相应的权限。

SQL-Based Account Activity Auditing

• USER() 函数，返回的是客户端连接时指定的用户名和主机名，不会包含通配符。
• CURRENT_USER() 函数，返回的是 mysql 数据库中 user 表的对应的 Host 和 User。

[root@mysql ~]# mysql -u u1 -p

[(none)]> SELECT USER(), CURRENT_USER();
+--------------+----------------+
| USER()       | CURRENT_USER() |
+--------------+----------------+
| u1@localhost | u1@%           |
+--------------+----------------+
1 row in set (0.00 sec)

[(none)]> SELECT Host,User FROM MYSQL.USER WHERE User='u1';
+------+------+
| Host | User |
+------+------+
| %    | u1   |
+------+------+
1 row in set (0.00 sec)

Language Structure

Literal Values

String Literals

字符串需要使用单引号 ' 或者 双引号 " 括起来。

'a string'
"another string"

可以为字符串指定字符集和排序规则，语法为：

[_charset_name]'string' [COLLATE collation_name]

SELECT _latin1'string';
SELECT _binary'string';
SELECT _utf8mb4'string' COLLATE utf8mb4_danish_ci;

使用 N'literal' （或 n'literal）为字符串指定国家字符集，以下语句等效：

SELECT N'some text';
SELECT n'some text';
SELECT _utf8'some text';

使用 \ 对特殊字符进行转义，在字符串中包含引号的方式有：

• 在单引号中使用两个单引号（''）表示一个单引号（'）
• 在双引号中使用两个双引号（""）表示一个双引号（"）
• 在引号前面使用转义符（\）
• 在单引号中使用双引号，或者在双引号中使用单引号

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+

mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+

Date and Time Literals

日期和时间值可以使用字符串或者数字表示，例如 '2015-07-21', '20150721', 20150721。

MySQL 识别以下格式为 DATE：

• 字符串为 'YYYY-MM-DD' 或者 'YY-MM-DD' 格式。
• 字符串为 'YYYYMMDD' 或者 'YYMMDD' 格式。
• 数字为 YYYYMMDD 或者 YYMMD 格式。

MySQL 识别以下格式为 DATETIME 和 TIMESTAMP：

• 字符串为 'YYYY-MM-DD hh:mm:ss' 或者 'YY-MM-DD hh:mm:ss' 格式。
• 字符串为 'YYYYMMDDhhmmss' 或者 'YYMMDDhhmmss' 格式。
• 数字为 YYYYMMDDhhmmss 或者 YYMMDDhhmmss 格式。

MySQL 解析两位数字年的规则为：

• 数字范围在 70-99，为 1970-1999。
• 数字范围在 00-69，为 2000-2069。

MySQL 识别以下格式为 TIME：

• 字符串为 'D hh:mm:ss' 格式，D 表示天数，值可以从 0 到 34。
• 字符串为 'hh:mm:ss' 格式。
• 数字为 hhmmss 格式。

可以使用 'D hh:mm:ss.fraction'， 'hh:mm:ss.fraction'，'hhmmss.fraction' 和 hhmmss.fraction 格式表示毫秒和微秒。

Schema Object Names

Identifier Case Sensitivity

MySQL 的数据库，表和触发器名称是区分大小写的，使用参数 lower_case_table_names 指定数据库和表名的大小写规则。在 Unix，默认值为 0，在 Windows，默认值为 1，在 macOS，默认值为 2。

Value	Meaning
0	Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case-sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or macOS). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.
1	Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
2	Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case-sensitive. This works only on file systems that are not case-sensitive! InnoDB table names and view names are stored in lowercase, as for lower_case_table_names=1.

参数 lower_case_table_names 只能在初始化服务器时进行配置，禁止在初始化服务器后更改其设置。

建议所有环境统一将该参数设置为 1。

[mysqld]
lower_case_table_names=1

User-Defined Variables

可以将值赋予用户自定义变量，以便后续使用。用户自定义变量为会话级。

语法：

SET @var_name = expr [, @var_name = expr] ...

用户自定义变量可以使用的类型有：integer, decimal, floating-point, binary string, nonbinary string。

例子：使用用户自定义变量

[(none)]> SET @t1=1, @t2=2, @t3:=3;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SET @t4=@t1+@t2+@t3;
Query OK, 0 rows affected (0.00 sec)

[(none)]> SELECT @t1, @t2, @t3, @t4;
+------+------+------+------+
| @t1  | @t2  | @t3  | @t4  |
+------+------+------+------+
|    1 |    2 |    3 |    6 |
+------+------+------+------+
1 row in set (0.00 sec)

Comments

MySQL Server 支持三种注释：

• 以 # 开头的单行注释。
• 以 -- 开头的单行注释，-- 与注释之间至少需要有 1 个空格。
• 以 /* 开头，以 */ 结尾的多行注释。

mysql> SELECT 1+1;     # This comment continues to the end of line
mysql> SELECT 1+1;     -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;

Character Sets, Collations, Unicode

MySQL 支持多种字符集和排序规则，默认字符集为 utf8mb4，默认排序规则为 utf8mb4_0900_ai_ci，可以在 MySQL Server 服务器，数据库，表，列和字符串级别指定字符集。

例子：查看字符集及其默认排序规则

[(none)]> SHOW CHARACTER SET LIKE 'utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
| utf8mb3 | UTF-8 Unicode    | utf8mb3_general_ci |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_0900_ai_ci |      4 |
+---------+------------------+--------------------+--------+
5 rows in set (0.01 sec)

[(none)]> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS WHERE CHARACTER_SET_NAME='utf8mb4';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8mb4            | utf8mb4_0900_ai_ci   | UTF-8 Unicode |      4 |
+--------------------+----------------------+---------------+--------+
1 row in set (0.00 sec)

一个字符集至少对应一种排序规则。

例子：查看字符集对应的排序规则

[(none)]> SHOW COLLATION WHERE Charset = 'utf8mb4';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
......
| utf8mb4_zh_0900_as_cs      | utf8mb4 | 308 |         | Yes      |       0 | NO PAD        |
+----------------------------+---------+-----+---------+----------+---------+---------------+
89 rows in set (0.00 sec)

[(none)]> SELECT * FROM INFORMATION_SCHEMA.COLLATIONS WHERE CHARACTER_SET_NAME='utf8mb4' ORDER BY COLLATION_NAME;
+----------------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME             | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+----------------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4            | 255 | Yes        | Yes         |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4            | 305 |            | Yes         |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4            | 278 |            | Yes         |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4            | 309 |            | Yes         |       1 | NO PAD        |
......
| utf8mb4_zh_0900_as_cs      | utf8mb4            | 308 |            | Yes         |       0 | NO PAD        |
+----------------------------+--------------------+-----+------------+-------------+---------+---------------+
89 rows in set (0.00 sec)

• 不同的字符集不能有相同的排序规则。
• 每个字符集都有一个默认排序规则。

Specifying Character Sets and Collations

Collation Naming Conventions

MySQL 排序规则名称遵循以下约定：

• 排序规则名称以其对应的字符集名称开头。
• 特定于语言的排序规则包括区域设置代码或语言名称。
• 排序规则后缀指示排序规则是区分大小写、区分重音、区分假名还是二进制。

排序规则后缀：

Suffix	Meaning
_ai	Accent-insensitive
_as	Accent-sensitive
_ci	Case-insensitive
_cs	Case-sensitive
_ks	Kana-sensitive
_bin	Binary

对于不指定区分重音的非二进制排序规则名称，由区分大小写确定。如果排序规则名称不包含 _ai 或 _as，则名称中的 _ci 表示 _ai，名称中的 _cs 表示 _as。例如，latin1_general_ci 显式不区分大小写和隐式步区分重音，latin1_general_cs 显式区分大小写和隐式区分重音，utf8mb4_0900_ai_ci 显式不区分大小写和不区分重音。

Server Character Set and Collation

MySQL Server 在服务器级别的字符集和排序规则默认为 utf8mb4 和 utf8mb4_0900_ai_ci。

使用参数 character_set_server 和 collation_server 设置 MySQL Server 在服务器级别的字符集和排序规则。

[mysqld]
character_set_server=utf8mb4

[(none)]> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_server | utf8mb4 |
+----------------------+---------+
1 row in set (0.01 sec)

在 MySQL 中，如果已经设置了 character_set_server 为 utf8mb4，通常就不需要再设置 default-character-set 为 utf8mb4。原因如下：

• character_set_server 的作用：character_set_server 是服务器级别的默认字符集。当新建数据库或表时，如果没有指定字符集，MySQL 会使用这个默认值。因此，设置 character_set_server 为 utf8mb4，就确保了在未指定字符集的情况下，所有新创建的数据库和表都会使用 utf8mb4 作为字符集。
• default-character-set 的作用：default-character-set 是 MySQL 客户端的参数，用于指定客户端与服务器之间通信时使用的默认字符集。它主要影响客户端与服务器之间的数据交换过程。例如，从客户端插入数据到服务器时，数据的编码会自动转换为服务器的字符集。
• 客户端和服务器通信的关系：如果你已经将 character_set_server 设置为 utf8mb4，那么在与服务器建立连接时，客户端会默认使用服务器端的字符集进行通信。如果客户端需要使用不同的字符集进行通信，则可以在连接时显式指定。
• 是否必要：如果所有客户端默认都与服务器使用相同的 utf8mb4 字符集进行通信，并且不会改变这个设置，那么设置 default-character-set 为 utf8mb4 就显得多余了。但如果希望确保客户端和服务器之间一致使用 utf8mb4，可以在客户端配置文件或连接字符串中设置 default-character-set 为 utf8mb4，以避免任何可能的字符集不一致问题。

总之，如果确定所有客户端会一致使用 utf8mb4 与服务器通信，并且服务器的默认字符集已经设置为 utf8mb4，那么再设置 default-character-set 为 utf8mb4 并不是必要的。但是，为了确保配置的明确性和一致性，可以考虑在客户端也进行设置。

Database Character Set and Collation

每个数据库都有其字符集和排序规则。CREATE DATABASE 和 ALTER DATABASE 语句具有用于指定数据库字符集和排序规则的可选子句：

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

MySQL 通过以下方式选择数据库字符集及其排序规则：

• 如果同时指定了字符集和排序规则，则使用该字符集和排序规则。
• 如果只指定了字符集，则使用该字符集及该字符集默认的排序规则。
• 如果只指定了排序规则，则使用该排序规则及该排序规则对应的字符集。
• 如果都没有指定，则使用服务器级别的字符集和排序规则。

使用参数 character_set_database 和 collation_database 指定数据库的默认字符集和排序规则。

[(none)]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_0900_ai_ci   |
+--------------------------+----------------------+
1 row in set (0.01 sec)

[(none)]> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
    ->    FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'menagerie';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_0900_ai_ci     |
+----------------------------+------------------------+
1 row in set (0.00 sec)

Table Character Set and Collation

每个表都有其字符集和排序规则。CREATE TABLE 和 ALTER TABLE 语句具有用于指定表字符集和排序规则的可选子句：

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

MySQL 通过以下方式选择表字符集及其排序规则：

• 如果同时指定了字符集和排序规则，则使用该字符集和排序规则。
• 如果只指定了字符集，则使用该字符集及该字符集默认的排序规则。
• 如果只指定了排序规则，则使用该排序规则及该排序规则对应的字符集。
• 如果都没有指定，则使用数据库级别的字符集和排序规则。

Column Character Set and Collation

每个字符类型字段都有其字符集和排序规则。CREATE TABLE 和 ALTER TABLE 语句具有用于指定字段字符集和排序规则的可选子句：

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]
    
col_name {ENUM | SET} (val_list)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

MySQL 通过以下方式选择字段字符集及其排序规则：

• 如果同时指定了字符集和排序规则，则使用该字符集和排序规则。
• 如果只指定了字符集，则使用该字符集及该字符集默认的排序规则。
• 如果只指定了排序规则，则使用该排序规则及该排序规则对应的字符集。
• 如果都没有指定，则使用表别的字符集和排序规则。

Character String Literal Character Set and Collation

每个字符串都有其字符集和排序规则。

对于语句 SELECT 'string'，其中的 'string' 具有由参数 character_set_connection 和 collation_connection 指定的连接默认字符集和排序规则。

为字符串指定字符集和排序规则的语法：

[_charset_name]'string' [COLLATE collation_name]

SELECT 'abc';
SELECT _latin1'abc';
SELECT _binary'abc';
SELECT _utf8mb4'abc' COLLATE utf8mb4_danish_ci;

MySQL 通过以下方式选择字符串字符集及其排序规则：

• 如果同时指定了字符集和排序规则，则使用该字符集和排序规则。
• 如果只指定了字符集，则使用该字符集及该字符集默认的排序规则。
• 如果只指定了排序规则，则使用参数 character_set_connection 指定的字符集及其排序规则。
• 如果都没有指定，则使用由参数 character_set_connection 和 collation_connection 指定的连接默认字符集和排序规则。

Connection Character Sets and Collations

服务器使用参数 character_set_client 设置客户端发送语句的字符集。

服务器将客户端发送的语句从 character_set_client 转换为 character_set_connection，单独设置了字符集的字符串不会转换。

参数 character_set_results 设置服务器向客户端返回查询结果的字符集，查询结果包括结果数据（如列值）、元数据（如列名）和错误消息。

若要告知服务器不执行结果集或错误消息的转换，将 character_set_results 设置为 NULL 或二进制：

SET character_set_results = NULL;
SET character_set_results = binary;

查看当前会话的字符集和排序规则参数：

[(none)]> SELECT * FROM performance_schema.session_variables
    ->    WHERE VARIABLE_NAME IN (
    ->      'character_set_client', 'character_set_connection',
    ->      'character_set_results', 'collation_connection'
    ->    ) ORDER BY VARIABLE_NAME;
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_results    | utf8mb4            |
| collation_connection     | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
4 rows in set (0.00 sec)

查看所有的字符集和排序规则参数：

[(none)]> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_client     | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database   | utf8mb4 |
| character_set_filesystem | binary  |
| character_set_results    | utf8mb4 |
| character_set_server     | utf8mb4 |
| character_set_system     | utf8mb3 |
+--------------------------+---------+
7 rows in set (0.00 sec)

[(none)]> SHOW SESSION VARIABLES LIKE 'collation\_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)

当客户端连接到服务器时，会指示其要用于与服务器通信的字符集。服务器使用此信息设置参数 character_set_client、character_set_results 和 character_set_connection，并将 collation_connection 设置为字符集默认排序规则。

在当前会话修改连接字符集的语法：

SET NAMES 'charset_name' [COLLATE 'collation_name']

表示客户端使用该字符集发送 SQL 语句到服务器，服务器使用该字符集发送结果数据到客户端，等价于：

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

也可以使用以下语法修改：

SET CHARACTER SET 'charset_name'

不同之处为设置参数 character_set_connection 和 collation_connection 为参数 character_set_database 和 collation_database 的值，等价于：

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET collation_connection = @@collation_database;

Data Types

MySQL 支持的 SQL 数据类型有：

• 数字类型
• 日期和时间类型
• 字符串（字符和字节）类型
• 空间类型
• JSON 数据类型

Numeric Data Types

MySQL 支持的数字类型有：

• 精确数字类型
  • INTEGER，其同义词为 INT
  • SMALLINT
  • DECIMAL，其同义词为 DEC ，FIXED 和 NUMERIC
• 近似数字类型
  • FLOAT
  • REAL
  • DOUBLE PRECISION，其同义词为 DOUBLE 和 REAL
• BIT 数据类型

INTEGER 的扩展类型：

Type	Storage (Bytes)	Minimum Value Signed	Minimum Value Unsigned	Maximum Value Signed	Maximum Value Unsigned
TINYINT	1	-128	0	127	255
SMALLINT	2	-32768	0	32767	65535
MEDIUMINT	3	-8388608	0	8388607	16777215
INT	4	-2147483648	0	2147483647	4294967295
BIGINT	8	-2^63	0	2^63-1	2^64-1

关键字 SERIAL 等同于 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE。

BIT[(M)] 类型用于存储单个状态值，M 表示包含几位。默认为1，最大可取 64。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] 为定点型数字，其中 M 表示总的位数（不包含正负号及小数点），D 表示小数位数。D 为 0 则表示没有小数部分。M 最大取值 65，默认 10；D 最大取值 30，默认 0。所有的算术运算（+，-，*，/）都基于 65 位的 DECIMAL。

在实际工作中，整数根据字段实际情况使用 TINYINT，SMALLINT 或者 INT 类型，有小数的数字一般使用 DECIMAL 类型。

Date and Time Data Types

MySQL 支持的日期时间类型有：

• DATE：日期类型，输出格式为 YYYY-MM-DD，取值范围为 1000-01-01 到 9999-12-31，可以使用字符串或者数字。
• TIME：时间类型，输出格式为 hh:mm:ss[.fraction]，取值范围为 -838:59:59.000000 到 838:59:59.000000，可以使用字符串或者数字。fraction 取值范围为 0 到 6，可省略，默认为 0。
• DATETIME：日期时间类型，输出格式为 YYYY-MM-DD hh:mm:ss[.fraction]，取值范围为 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999，可以使用字符串或者数字。fraction 取值范围为 0 到 6，可省略，默认为 0。建议使用 DATETIME 时间类型。
• TIMESTAMP：时间戳类型，取值范围为 1970-01-01 00:00:01.000000 UTC 到 2038-01-19 03:14:07.999999 UTC。不建议使用 TIMESTAMP 时间类型。
• YEAR：年类型，输出格式为 YYYY，取值范围 1901 到 2155，0000 也是合法的值，可以使用字符串或者数字。

具体的字符串或数字格式参考 Date and Time Literals。

TIMESTAMP 和 DATETIME 还支持自动初始化和自动更新。

• 自动初始化是指插入记录的时候，不指定该字段的值，自动设置为当前时间或指定的默认值。
• 自动更新是指更新记录的时候，不指定该字段的值，自动更新为当前时间。

在定义列时使用 DEFAULT CURRENT_TIMESTAMP 指定自动初始化，使用 ON UPDATE CURRENT_TIMESTAMP 指定自动更新。可同时指定，也可指定其中一项，也可都不指定。

CURRENT_TIMESTAMP 指代当前时间，也可以使用 CURRENT_TIMESTAMP(), NOW(), LOCALTIME, LOCALTIME(), LOCALTIMESTAMP 和 LOCALTIMESTAMP()。

DEFAULT 也可指定为常量，比如 DEFAULT 0 或 DEFAULT '2000-01-01 00:00:00'。

指定 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP ：

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

指定 DEFAULT CURRENT_TIMESTAMP ：

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0,
  dt DATETIME DEFAULT 0
);

指定 DEFAULT 0 和 ON UPDATE CURRENT_TIMESTAMP ：

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP
);

指定 ON UPDATE CURRENT_TIMESTAMP ：

CREATE TABLE t1 (
  ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,     -- default 0
  ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL
);

CREATE TABLE t1 (
  dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP,         -- default NULL
  dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0
);

例子：创建表，指定创建时间字段和更新时间字段，在插入数据时自动设置创建时间和更新时间，在更新数据时自动设置更新时间

[menagerie]> create table test(
    ->       id int not null auto_increment primary key,
    ->       name varchar(255) not null,
    ->       create_time datetime not null default current_timestamp,
    ->       update_time datetime not null default current_timestamp on update current_timestamp);
Query OK, 0 rows affected (0.02 sec)

[menagerie]> insert into test(name) values('stone');
Query OK, 1 row affected (0.02 sec)

[menagerie]> select * from test;
+----+-------+---------------------+---------------------+
| id | name  | create_time         | update_time         |
+----+-------+---------------------+---------------------+
|  1 | stone | 2023-03-06 10:07:55 | 2023-03-06 10:07:55 |
+----+-------+---------------------+---------------------+
1 row in set (0.00 sec)

[menagerie]> update test set name='stonebox' where name='stone';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[menagerie]> select * from test;
+----+----------+---------------------+---------------------+
| id | name     | create_time         | update_time         |
+----+----------+---------------------+---------------------+
|  1 | stonebox | 2023-03-06 10:07:55 | 2023-03-06 10:08:53 |
+----+----------+---------------------+---------------------+
1 row in set (0.00 sec)

String Data Types

MySQL 支持的字符串数据类型有：

• CHAR
• VARCHAR
• BINARY
• VARBINARY
• BLOB
• TEXT
• ENUM
• SET

​

对于类型为 CHAR，VARCHAR 和 TEXT 字段 ，以字符为单位指定长度。

对于类型为 BINARY，VARBINARY 和 BLOB 字段，以字节为单位指定长度。

对于类型为 CHAR，VARCHAR，TEXT，ENUM 和 SET 字段，可以指定字符集和排序规则。

具体语法为：

• [NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

固定长度字符串，长度不足时右向空格填充，M 表示字符长度，范围为 0 到 255，如果省略，则为 1。CHAR 为 CHARACTER 的简写，NATIONAL CHAR（简写为 NCHAR）表示使用预定义的 utf8mb3 字符集。获取 CHAR 类型字段值时，将删除尾随空格。

• [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

变长字符串。M 表示最大字符长度，范围为 0 到 65,535。VARCHAR 的有效最大长度取决于最大行大小（65,535 字节，在所有列之间共享）和使用的字符集。例如对于每个字符占 3 字节的 utf8mb3 字符集，使用该字符集的 VARCHAR 类型字段最大长度为 21,844 字符。

以 1 字节或者 2 字节将字段长度存储在数据前面，表示数据字节数。

VARCHAR 为 CHARACTER VARYING 的简写，NATIONAL VARCHAR（简写为 NVARCHAR）表示使用预定义的 utf8mb3 字符集。

Value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
''	' '	4 bytes	''	1 byte
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes

• BINARY[(M)]

类似于 CHAR 类型，但存储的是二进制字节串，M 表示列长度（以字节为单位），如果省略，默认为 1。

• VARBINARY(M)

类似于 VARCHAR 类型，但存储的是二进制字节串，M 表示最大字节长度。

• TINYBLOB

最大长度为 255 字节的 BLOB 类型，使用 1 字节长度前缀表示值的字节数。

• BLOB[(M)]

最大长度为 65,535 字节的 BLOB 类型，使用 2 字节长度前缀表示值的字节数。

• MEDIUMBLOB

最大长度为 16,777,215 字节的 BLOB 类型，使用 3 字节长度前缀表示值的字节数。

• LONGBLOB

最大长度为 4,294,967,295 字节的 BLOB 类型，使用 4 字节长度前缀表示值的字节数。

• TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

最大长度为 255 字符的 TEXT 类型，使用 1 字节长度前缀表示值的字节数。

• TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

最大长度为 65,535 字符的 TEXT 类型，使用 2 字节长度前缀表示值的字节数。

• MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

最大长度为 16,777,215 字符的 TEXT 类型，使用 3 字节长度前缀表示值的字节数。

• LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

最大长度为 4,294,967,295 字符的 TEXT 类型，使用 4 字节长度前缀表示值的字节数。

• ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

枚举类型。最多可以有 65,535 不同元素，每个元素最大字符数为 255，最大字节数为 1020。元素被分配索引号，从 1 开始，NULL 的索引为 NULL，空字符串错误值的索引值为 0，例如 ENUM('Mercury', 'Venus', 'Earth') 的索引为：

Value	Index
NULL	NULL
''	0
'Mercury'	1
'Venus'	2
'Earth'	3

按字母顺序指定枚举列表，强烈建议不要使用数字作为枚举值。

• SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

集合类型。最多可以有 64 不同元素，每个元素最大字符数为 255，最大字节数为 1020。元素之间使用逗号分隔，则元素本身不应包含逗号。插入时会按照定义的顺序插入，并去掉重复值。

[menagerie]> CREATE TABLE myset(col SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.12 sec)

[menagerie]> SHOW COLUMNS FROM myset LIKE 'col';
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| col   | set('a','b','c','d') | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.06 sec)

[menagerie]> INSERT INTO myset(col) VALUES 
    ->       ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

[menagerie]> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.01 sec)

​

对于 BLOB 和 TEXT 类型字段：

• 如果在字段上创建索引，必须指定索引前缀长度。
• 字段不能设置默认值。
• 使用参数 max_sort_length 指定排序时使用的前缀字节数，默认为 1024。
• 由于查询 BLOB 和 TEXT 类型字段会使用磁盘临时表，基于性能考虑，应尽量避免查询这些字段。
• 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中

Data Type Storage Requirements

Numeric Type Storage Requirements

Data Type	Storage Required
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INT, INTEGER	4 bytes
BIGINT	8 bytes
FLOAT(p)	4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53
FLOAT	4 bytes
DOUBLE [PRECISION], REAL	8 bytes
DECIMAL(M,D), NUMERIC(M,D)	Varies; see following discussion
BIT(M)	approximately (M+7)/8 bytes

Date and Time Type Storage Requirements

Data Type	Storage Required Before MySQL 5.6.4	Storage Required as of MySQL 5.6.4
YEAR	1 byte	1 byte
DATE	3 bytes	3 bytes
TIME	3 bytes	3 bytes + fractional seconds storage
DATETIME	8 bytes	5 bytes + fractional seconds storage
TIMESTAMP	4 bytes	4 bytes + fractional seconds storage

Fractional Seconds Precision	Storage Required
0	0 bytes
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

String Type Storage Requirements

Data Type	Storage Required
CHAR(M)	The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Storage Characteristics. Otherwise, M × w bytes, <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set.
BINARY(M)	M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M)	L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXT	L + 1 bytes, where L < 28
BLOB, TEXT	L + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT	L + 3 bytes, where L < 224
LONGBLOB, LONGTEXT	L + 4 bytes, where L < 232
ENUM('value1','value2',...)	1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...)	1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

Functions and Operators

MySQL 内置函数和运算符参考 Built-In Functions and Operators

Operators

运算符参考 Operators

运算符优先级如下表，从最高优先级到最低优先级，在一行上的运算符具有相同的优先级。

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

比较运算符参考 Comparison Functions and Operators，比较结果为 1 (TRUE)，0 (FALSE) 或者 NULL。

逻辑运算符如下，运算结果为 1 (TRUE)，0 (FALSE) 或者 NULL。

Name	Description
AND, &&	Logical AND
NOT, !	Negates value
OR, ||	Logical OR
XOR	Logical XOR

赋值运算符如下：

Name	Description
:=	Assign a value, never interpreted as a comparison operator
=	Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)

例子：使用赋值运算符 :=

[menagerie]> SELECT @var1, @var2;
+--------------+--------------+
| @var1        | @var2        |
+--------------+--------------+
| NULL         | NULL         |
+--------------+--------------+
1 row in set (0.00 sec)

[menagerie]> SELECT @var1 := 1, @var2;
+------------+--------------+
| @var1 := 1 | @var2        |
+------------+--------------+
|          1 | NULL         |
+------------+--------------+
1 row in set, 1 warning (0.00 sec)

[menagerie]> SELECT @var1, @var2;
+-------+--------------+
| @var1 | @var2        |
+-------+--------------+
|     1 | NULL         |
+-------+--------------+
1 row in set (0.00 sec)

[menagerie]> SELECT @var1, @var2 := @var1;
+-------+----------------+
| @var1 | @var2 := @var1 |
+-------+----------------+
|     1 |              1 |
+-------+----------------+
1 row in set, 1 warning (0.00 sec)

[menagerie]> SELECT @var1, @var2;
+-------+-------+
| @var1 | @var2 |
+-------+-------+
|     1 |     1 |
+-------+-------+
1 row in set (0.00 sec)

[menagerie]> SELECT @var1:=COUNT(*) FROM t1;
+-----------------+
| @var1:=COUNT(*) |
+-----------------+
|               4 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

[menagerie]> SELECT @var1;
+-------+
| @var1 |
+-------+
|     4 |
+-------+
1 row in set (0.00 sec)

[menagerie]> SELECT * FROM t1;
+------+
| c1   |
+------+
|    1 |
|    3 |
|    5 |
|    7 |
+------+
4 rows in set (0.00 sec)

[menagerie]> UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

[menagerie]> SELECT @var1;
+-------+
| @var1 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

[menagerie]> SELECT * FROM t1;
+------+
| c1   |
+------+
|    2 |
|    3 |
|    5 |
|    7 |
+------+
4 rows in set (0.00 sec)

Flow Control Functions

流程控制函数有：

Name	Description
CASE	Case operator
IF()	If/else construct
IFNULL()	Null if/else construct
NULLIF()	Return NULL if expr1 = expr2

​

两种 Case 语法：

CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...] [ELSE result] END
CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END

[menagerie]> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one                                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true                                       |
+--------------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
+-----------------------------------------------------+
| CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
+-----------------------------------------------------+
|                                                NULL |
+-----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

​

IF() 函数语法：

IF(expr1,expr2,expr3)

如果 expr1 为 TRUE (expr1 <> 0 且 expr1 IS NOT NULL)，则返回 expr2，否则 expr3。

[menagerie]> SELECT IF(1>2,2,3);
+-------------+
| IF(1>2,2,3) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

[menagerie]> SELECT IF(1<2,'yes','no');
+--------------------+
| IF(1<2,'yes','no') |
+--------------------+
| yes                |
+--------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT IF(STRCMP('test','test1'),'no','yes');
+---------------------------------------+
| IF(STRCMP('test','test1'),'no','yes') |
+---------------------------------------+
| no                                    |
+---------------------------------------+
1 row in set (0.01 sec)

​

IFNULL() 函数语法：

IFNULL(expr1,expr2)

如果 expr1 不为 NULL，返回 expr1，否则返回 expr2。

[menagerie]> SELECT IFNULL(1,0);
+-------------+
| IFNULL(1,0) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

[menagerie]> SELECT IFNULL(NULL,10);
+-----------------+
| IFNULL(NULL,10) |
+-----------------+
|              10 |
+-----------------+
1 row in set (0.00 sec)

​

NULLIF() 语法：

NULLIF(expr1,expr2)

如果 expr1 = expr2 为 TRUE，返回 NULL，否则 expr1。等价于 CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END。

[menagerie]> SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

[menagerie]> SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

Numeric Functions and Operators

数值函数和运算符参考 Numeric Functions and Operators

例子：ROUND() 函数和 TRUNCATE() 函数

[menagerie]> SELECT ROUND(-1.23);
+--------------+
| ROUND(-1.23) |
+--------------+
|           -1 |
+--------------+
1 row in set (0.00 sec)

[menagerie]> SELECT ROUND(-1.58);
+--------------+
| ROUND(-1.58) |
+--------------+
|           -2 |
+--------------+
1 row in set (0.00 sec)

[menagerie]> SELECT ROUND(1.58);
+-------------+
| ROUND(1.58) |
+-------------+
|           2 |
+-------------+
1 row in set (0.01 sec)

[menagerie]> SELECT ROUND(1.298, 1);
+-----------------+
| ROUND(1.298, 1) |
+-----------------+
|             1.3 |
+-----------------+
1 row in set (0.00 sec)

[menagerie]> SELECT ROUND(1.298, 0);
+-----------------+
| ROUND(1.298, 0) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

[menagerie]> SELECT ROUND(23.298, -1);
+-------------------+
| ROUND(23.298, -1) |
+-------------------+
|                20 |
+-------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
|           150.00 |          150 |
+------------------+--------------+
1 row in set (0.00 sec)

[menagerie]> SELECT TRUNCATE(1.223,1);
+-------------------+
| TRUNCATE(1.223,1) |
+-------------------+
|               1.2 |
+-------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT TRUNCATE(1.999,1);
+-------------------+
| TRUNCATE(1.999,1) |
+-------------------+
|               1.9 |
+-------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT TRUNCATE(1.999,0);
+-------------------+
| TRUNCATE(1.999,0) |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT TRUNCATE(-1.999,1);
+--------------------+
| TRUNCATE(-1.999,1) |
+--------------------+
|               -1.9 |
+--------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT TRUNCATE(122,-2);
+------------------+
| TRUNCATE(122,-2) |
+------------------+
|              100 |
+------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT TRUNCATE(10.28*100,0);
+-----------------------+
| TRUNCATE(10.28*100,0) |
+-----------------------+
|                  1028 |
+-----------------------+
1 row in set (0.00 sec)

Date and Time Functions

日期和时间函数参考 Date and Time Functions

例子：日期运算

[menagerie]> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
+-----------------------------------------+
| DATE_ADD('2008-01-02', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2008-02-02                              |
+-----------------------------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
+----------------------------------------+
| ADDDATE('2008-01-02', INTERVAL 31 DAY) |
+----------------------------------------+
| 2008-02-02                             |
+----------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT ADDDATE('2008-01-02', 31);
+---------------------------+
| ADDDATE('2008-01-02', 31) |
+---------------------------+
| 2008-02-02                |
+---------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
+---------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
+---------------------------------------------------------+
| 2008-01-02 01:01:01.000001                              |
+---------------------------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997                               |
+-----------------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
+----------------------------------------------+
| DATEDIFF('2010-11-30 23:59:59','2010-12-31') |
+----------------------------------------------+
|                                          -31 |
+----------------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT PERIOD_ADD(200801,2);
+----------------------+
| PERIOD_ADD(200801,2) |
+----------------------+
|               200803 |
+----------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT PERIOD_DIFF(200802,200703);
+----------------------------+
| PERIOD_DIFF(200802,200703) |
+----------------------------+
|                         11 |
+----------------------------+
1 row in set (0.00 sec)

例子：获取年、月、日、周

[menagerie]> SELECT YEAR('1987-01-01');
+--------------------+
| YEAR('1987-01-01') |
+--------------------+
|               1987 |
+--------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT MONTH('2008-02-03');
+---------------------+
| MONTH('2008-02-03') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT DATE('2003-12-31 01:02:03');
+-----------------------------+
| DATE('2003-12-31 01:02:03') |
+-----------------------------+
| 2003-12-31                  |
+-----------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT MINUTE('2008-02-03 10:05:03');
+-------------------------------+
| MINUTE('2008-02-03 10:05:03') |
+-------------------------------+
|                             5 |
+-------------------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT QUARTER('2008-04-01');
+-----------------------+
| QUARTER('2008-04-01') |
+-----------------------+
|                     2 |
+-----------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT EXTRACT(YEAR FROM '2019-07-02');
+---------------------------------+
| EXTRACT(YEAR FROM '2019-07-02') |
+---------------------------------+
|                            2019 |
+---------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03') |
+------------------------------------------------+
|                                         201907 |
+------------------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03');
+------------------------------------------------+
| EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03') |
+------------------------------------------------+
|                                          20102 |
+------------------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT DAYOFYEAR('2007-02-03');
+-------------------------+
| DAYOFYEAR('2007-02-03') |
+-------------------------+
|                      34 |
+-------------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT DAYOFMONTH('2007-02-03');
+--------------------------+
| DAYOFMONTH('2007-02-03') |
+--------------------------+
|                        3 |
+--------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT DAYOFWEEK('2007-02-03');
+-------------------------+
| DAYOFWEEK('2007-02-03') |
+-------------------------+
|                       7 |
+-------------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
+--------------------------+---------------------+
| DAYOFMONTH('2001-11-00') | MONTH('2005-00-00') |
+--------------------------+---------------------+
|                        0 |                   0 |
+--------------------------+---------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT LAST_DAY('2003-02-05');
+------------------------+
| LAST_DAY('2003-02-05') |
+------------------------+
| 2003-02-28             |
+------------------------+
1 row in set (0.01 sec)

例子：获取当前日期和时间

[menagerie]> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2023-03-06 |
+------------+
1 row in set (0.00 sec)

[menagerie]> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 16:50:26  |
+-----------+
1 row in set (0.00 sec)

[menagerie]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2023-03-06 16:55:27 |
+---------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2023-03-06 16:58:48 |        0 | 2023-03-06 16:58:48 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

[menagerie]> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2023-03-06 16:58:57 |        0 | 2023-03-06 16:58:59 |
+---------------------+----------+---------------------+
1 row in set (2.01 sec)

String Functions and Operators

字符串函数参考 String Functions and Operators

例子：字符拼接

[menagerie]> SELECT CONCAT('My', 'S', 'QL');
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.01 sec)

例子：子字符串位置

[menagerie]> SELECT INSTR('foobarbar', 'bar');
+---------------------------+
| INSTR('foobarbar', 'bar') |
+---------------------------+
|                         4 |
+---------------------------+
1 row in set (0.00 sec)

例子：字符串长度

[menagerie]> SELECT LENGTH('text');
+----------------+
| LENGTH('text') |
+----------------+
|              4 |
+----------------+
1 row in set (0.00 sec)

例子：大小写转换

[menagerie]> SELECT LOWER('QUADRATICALLY');
+------------------------+
| LOWER('QUADRATICALLY') |
+------------------------+
| quadratically          |
+------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT UPPER('Hej');
+--------------+
| UPPER('Hej') |
+--------------+
| HEJ          |
+--------------+
1 row in set (0.00 sec)

例子：左右填充

[menagerie]> SELECT LPAD('hi',4,'??');
+-------------------+
| LPAD('hi',4,'??') |
+-------------------+
| ??hi              |
+-------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT RPAD('hi',5,'?');
+------------------+
| RPAD('hi',5,'?') |
+------------------+
| hi???            |
+------------------+
1 row in set (0.00 sec)

例子：去除前后空格

[menagerie]> SELECT LTRIM('  barbar');
+-------------------+
| LTRIM('  barbar') |
+-------------------+
| barbar            |
+-------------------+
1 row in set (0.01 sec)

[menagerie]> SELECT RTRIM('barbar   ');
+--------------------+
| RTRIM('barbar   ') |
+--------------------+
| barbar             |
+--------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT TRIM('  bar   ');
+------------------+
| TRIM('  bar   ') |
+------------------+
| bar              |
+------------------+
1 row in set (0.01 sec)

例子：替换

[menagerie]> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
+-------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Ww') |
+-------------------------------------+
| WwWwWw.mysql.com                    |
+-------------------------------------+
1 row in set (0.00 sec)

例子：获取子字符串

[menagerie]> SELECT SUBSTRING('Quadratically',5,6);
+--------------------------------+
| SUBSTRING('Quadratically',5,6) |
+--------------------------------+
| ratica                         |
+--------------------------------+
1 row in set (0.00 sec)

例子：字符串比较，两个字符串相等结果为 0，第一个字符串比第二个字符串小为 -1，任意参数为 NULL，结果为 NULL，其余结果为 1。

[menagerie]> SELECT STRCMP('text', 'text2');
+-------------------------+
| STRCMP('text', 'text2') |
+-------------------------+
|                      -1 |
+-------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT STRCMP('text2', 'text');
+-------------------------+
| STRCMP('text2', 'text') |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT STRCMP('text', 'text');
+------------------------+
| STRCMP('text', 'text') |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

例子：使用正则表达式

[menagerie]> SELECT REGEXP_LIKE('a', '^[a-d]');
+----------------------------+
| REGEXP_LIKE('a', '^[a-d]') |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT REGEXP_REPLACE('a b c', 'b', 'X');
+-----------------------------------+
| REGEXP_REPLACE('a b c', 'b', 'X') |
+-----------------------------------+
| a X c                             |
+-----------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');
+----------------------------------------+
| REGEXP_SUBSTR('abc def ghi', '[a-z]+') |
+----------------------------------------+
| abc                                    |
+----------------------------------------+
1 row in set (0.00 sec)

[menagerie]> SELECT REGEXP_INSTR('dog cat dog', 'dog');
+------------------------------------+
| REGEXP_INSTR('dog cat dog', 'dog') |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)

Information Functions

获取系统信息的函数参考 Information Functions

例子：获取系统信息

[menagerie]> SELECT CURRENT_USER(),DATABASE(),VERSION();
+----------------+------------+-----------+
| CURRENT_USER() | DATABASE() | VERSION() |
+----------------+------------+-----------+
| root@localhost | menagerie  | 8.0.32    |
+----------------+------------+-----------+
1 row in set (0.00 sec)

Aggregate Functions

聚合函数参考 Aggregate Functions

例子：计算总和，平均值，最大值，最小值，总记录

[menagerie]> select sum(price),avg(price),max(price),min(price),count(price) from shop;
+------------+------------+------------+------------+--------------+
| sum(price) | avg(price) | max(price) | min(price) | count(price) |
+------------+------------+------------+------------+--------------+
|      42.77 |   6.110000 |      19.95 |       1.25 |            7 |
+------------+------------+------------+------------+--------------+
1 row in set (0.00 sec)

Window Functions

窗口函数参考 Window Functions

与 Oracle 的窗口函数类似，具体用法可参考 Analytic Functions

Miscellaneous Functions

其他函数参考 Miscellaneous Functions

例子：使用 INT UNSIGNED 类型存储函数 INET_ATON 转换 IP 地址的数字

-- 167773449 = 10×256^3 + 0×256^2 + 5×256 + 9
[menagerie]> SELECT INET_ATON('10.0.5.9');
+-----------------------+
| INET_ATON('10.0.5.9') |
+-----------------------+
|             167773449 |
+-----------------------+
1 row in set (0.03 sec)

[menagerie]> SELECT INET_NTOA(167773449);
+----------------------+
| INET_NTOA(167773449) |
+----------------------+
| 10.0.5.9             |
+----------------------+
1 row in set (0.01 sec)