本文主要会提及到以下内容

一、MySQL配置选项

二、服务器系统变量

三、动态系统变量

四、客户机和工具

五、数据类型

六、获取元数据

七、事务与锁定

 

一、MySQL配置选项

mysql_config_editor set --login-path=wy --user=root --password --host=localhost

二、服务器系统变量

mysqld --verbose --help可以查看服务器根据其内部编译的默认值使用的值，以及服务器读取的任何选项文件

mysqld --no-defaults --verbose --help可以查看服务器根据其内部编译的默认值使用的值，忽略任何选项文件中的设置

在MySQL数据库内可使用查看全局变量的值show global variables;

三、动态系统变量

1、MySQL维护了两种包含系统变量的作用域。global变量影响服务器的整体操作，session变量影响其对单个客户机连接的操作，变量存在于任一作用域中，也可同时存在于两个作用域中。

2、更改变量时，适用于以下几点：

（1） 设置会话变量不需要任何特殊特权，但客户机只能更改自己的会话变量，不能更改其他任何客户机的会话变量。

（2） LOCAL 和 @@local 是 SESSION 和 @@session 的同义词。

（3）如果不指定 GLOBAL 或 SESSION，则当会话变量存在时，SET 将更改会话变量；会话变量不存在时，将产生错误。

建立一个访问会产生一个会话，session只在当前会话生效，global在当前登陆的全部会话生效，新登陆或登出重新登陆失效。

3、显示动态系统变量

必须按如下方式设置特定的变量类型：

（1）对具有字符串类型（如 CHAR 或 VARCHAR）的变量使用字符串值。

（2）对具有数值类型（如 INT 或 DECIMAL）的变量使用数值。

（3）将具有布尔（BOOL 或 BOOLEAN）类型的变量设置为 0、1、ON 或 OFF。（如果在命令行上或在选项文件中设置布尔变量，则使用数值。

（4）将枚举类型的变量设置为可用的变量值之一，但也可将其设置为与所需枚举值对应的数字。对于枚举的服务器变量，第一个枚举值对应于 0。这不同于 ENUM 列，后者的第一个枚举值对应于 1。

四、日志文件

1、

2、二进制日志记录

二进制日志包含描述数据库更改（如创建数据库或更改表数据）的“事件”。二进制日志还包含可能做出更改的语句的事件（例如，没有匹配行的 DELETE）。该日志还包含有关每条更新语句所用时间的信息。二进制日志有两个重要用途：复制和数据恢复。MySQL 使用日志传送复制解决方案。使用日志传送系统时，可以将主系统上发生的所有

数据更改存储在二进制日志中，然后通过从系统检索这些数据更改，并根据接收到的这些日志文件执行更改。可以实时下载日志文件并执行内容；即，只要生成日志文件事件，就将其发送到连接的从系统供执行。由于网络传播存在延迟，从系统可能需要几秒到几分钟（最坏的情况）时间来接收更新。在理想的情况下，延迟会在一秒以内。

发生以下事件之一时，二进制日志会轮转：

（1）重新启动 MySQL 服务器。

（2）达到允许的最大大小 (max_binlog_size)。

（3）发出了 FLUSH LOGS SQL 命令。

二进制日志独立于存储引擎。不管使用的存储引擎是哪个（即 InnoDB 或 MyISAM），MySQL 复制都会工作

四、客户机和工具

必须运行 mysqld 服务器程序，客户机才能访问数据库。

mysql 是通用的命令行客户机，用于向服务器发送 SQL 语句，其中包括用于管理的SQL 语句。mysqladmin 是可帮助管理服务器的管理命令行客户机。mysqlimport 为 LOAD DATA INFILE 语句提供了命令行界面。使用该客户机可以将数据文件装入表中，而无需手动发出 LOAD DATA INFILE 语句。mysqldump 是用于转储数据库和表中内容的命令行客户机。使用它可备份数据库或将其复制到其他计算机。

1、调用命令行客户机

2、连接参数选项

下面列出了一些最常见的连接参数选项：

（1）-h：后跟给定主机的主机名或 IP 地址，用于连接到服务器（默认为 localhost）

（2）-C：压缩客户机和服务器之间发送的所有信息（如果两者都支持压缩）

（3）--protocol：后跟用于连接到服务器的连接协议：{TCP|SOCKET|PIPE|MEMORY}

（4）-P：后跟端口号，用于代替默认值 (3306)

（5）-S：用于设置 UNIX 套接字文件或在 Windows 上使用的命名管道的名称

（6）--shared-memory-base-name：（仅 Windows）通过共享内存连接到本地服务器时所使用的共享内存的名称。此选项仅在服务器支持共享内存连接时适用。

3、MySQL客户机：SQL语句

4、SQL语句终结符

5、特殊语句终结符

（1）edit (\e)：使用 $EDITOR 编辑命令。

（2）pager (\P)：将 PAGER 设置为 [to_pager]。通过 PAGER 输出查询结果。

（3）rehash (\#)：重构完成散列。

6、mysqladmin客户机

使用 mysqladmin 命令行客户机来查看服务器的配置和当前状态、创建和删除数据库以及执行其他操作。

五、数据类型

1、

Appropriate（适当）：需要以最适合数据所代表的项的类型来表示数据。

Brief（简洁）：选择所用存储空间最少的数据类型。这可节省资源并提高性能。

Complete（完整）：选择的数据类型应分配有可存储特定项的最大可能值的充足空间。

2、创建带有数据类型的表

3、数值数据类型

整数类型按精度升序列出。即，后面的每个整数数据类型可以容纳的整数值范围要大于列表中前一个数据类型的整数值范围。浮点数据类型包括 FLOAT 和 DOUBLE。这两种类型均可用于表示包含整数部分、小数部分或同时包括二者的近似值数值。FLOAT 和 DOUBLE 数据类型使用服务器主机的 CPU 所用的本机二进制浮点格式 (IEEE 754) 来表示值。该数据类型用于存储和计算会很高效，但值会存在舍入误差。对于 FLOAT 和 DOUBLE：如果列可为空，则默认值为 NULL；如果列不可为空，则默认值为 0（数值零）。

4、字符串数据类型

字符串具有以下特征：

（1）序列由特定字符集中的字符组成。

（2）多字节字符集中的每个字符所需的字节数可能是固定的，也可能是可变的。

（3）比较基于对字符串所关联的字符集的整理。

（4）多字节字符比较以字符而不是字节为单位执行。

（5）整理将验证字符的大写版本和小写版本是否等效。

（6）整理将决定同一个字符的不同重音标记是否等效。

（7）整理可以为二进制，其中基于数值字符值进行比较。

MySQL 具有一个包含大量可供选择的字符集和整理的列表。选择正确与否会对性能有很大的影响。要查看可用的字符集，请使用以下语句 show character set;

5、二进制字符串数据类型

6、时间数据类型

（1）YYYY、MM、DD、hh、mm、ss 和 uuuuuu 分别表示“年”、“月”、“日”、“小时”、“分钟”、“秒”和可选的“秒的小数位”。

（2）通过为该类型提供一个参数，可声明秒的小数位（可选）。例如，TIME(3) 是 TIME 类型，其中秒的小数位部分最多可达三位。

（3）DATE 值范围为从 1000-01-01 至 9999-12-31。

（4）DATETIME 值范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

（5）TIMESTAMP 值采用 UTC 进行存储（将根据需要转换为当地时间或从当地时间进行转换），其范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。

可以定义 DATETIME 和 TIMESTAMP 以自动记录当前日期和时间，而不管对行执行INSERT 或 UPDATE 操作的时间为何时。

7、空间数据类型

8、NULL值

（1）在 SQL 中，对表达式求值的结果可以为 null。null 值是一个特殊的值，表示值无法进行计算或未知。

何时使用 NULL

（2）在数据库设计的开始阶段，当列出要包含在内的数据时，发现某些数据可能不适用于所有列。请检查这些情况并决定是否应允许 null 值。此外，如果由于列中出现 null 值而检测到问题，则可以更改现有表的此设置。

（3）何时不应使用 NULL

在某些情况下，不应允许列中使用 null 值。最常见的情况即该列为主键时。另一个例子是：当列必须包含值时，数据库设计才有意义。

9、创建带有属性的列

六、获取元数据

1、

数据库是数据的结构化集合。元数据是“有关数据的数据”。MySQL 通过以下方法向数据库、表和数据库服务器所管理的其他对象提供对元数据的访问：

（1）INFORMATION_SCHEMA：MySQL 服务器包含一个被实现为名为

INFORMATION_SCHEMA 的数据库（模式）的数据字典，其中包含许多显示为表的对象。

（2）SHOW 语句：用于获取服务器统计信息、模式和模式对象的相关数据的专用语法。

- SHOW DATABASES 和 SHOW TABLES：返回包含数据库和表名的列表

- SHOW COLUMNS：生成表中列的定义

- 需要有 SELECT 特权才能使用 SHOW 语句。

（3）DESCRIBE：可用于检查表结构和列属性的 SQL 语句快捷方式

（4）mysqlshow：用作指向一些 SHOW 语句的命令行前端的客户机程序。您设置的参数将决定要显示的信息，然后程序会发出相应的 SHOW 语句并显示语句的结果。

2、

INFORMATION_SCHEMA 数据库充当数据库元数据的中央系统信息库。它未存储在硬盘上，从这个方面来看，它是“虚拟数据库”；但是，它与其他任何数据库一样包含表，与其他任何表一样可以使用 SELECT 来访问其中表的内容。此外，可以使用 SELECT 语句获取 INFORMATION_SCHEMA 表。

列出information_schema中所有表

表信息

（1）COLUMNS：表和视图中的列

（2）ENGINES：存储引擎

（3）SCHEMATA：数据库

（3）TABLES：数据库中的表

（4）VIEWS：数据库中的视图

分区

（1）PARTITIONS：表分区

（2）FILES：存储 MySQL NDB 磁盘数据表的文件

特权

（1）COLUMN_PRIVILEGES：MySQL 用户帐户所拥有的列特权

（2）SCHEMA_PRIVILEGES：MySQL 用户帐户所拥有的数据库特权

（3）TABLE_PRIVILEGES：MySQL 用户帐户所拥有的表特权

（4）USER_PRIVILEGES：MySQL 用户帐户所拥有的全局特权

字符集支持

（1）CHARACTER_SETS：可用的字符集

（2）COLLATIONS：每个字符集的整理

（3）COLLATION_CHARACTER_SET_APPLICABILITY：适用于特定字符集的整理

约束和索引

（1）KEY_COLUMN_USAGE：关键列的约束

（2）REFERENTIAL_CONSTRAINTS：外键

（3）STATISTICS：表索引

（4）TABLE_CONSTRAINTS：表的约束

服务器设置和状态

（1）KEY_COLUMN_USAGE：约束

（2）GLOBAL_STATUS：所有 MySQL 连接的状态值

（3）GLOBAL_VARIABLES：用于新的 MySQL 连接的值

（4）PLUGINS：服务器插件

（5）PROCESSLIST：指示哪些线程正在运行

（6）SESSION_STATUS：当前 MySQL 连接的状态值

（7）SESSION_VARIABLES：当前 MySQL 连接的生效值

例程及相关信息

（1）EVENTS：预定事件

（2）ROUTINES：存储过程和功能

（3）TRIGGERS：数据库中的触发器

（4）PARAMETERS：存储过程和功能参数以及存储函数

InnoDB

（1）INNODB_CMP 和 INNODB_CMP_RESET：对压缩的 InnoDB 表的相关操作的状态

（2）INNODB_CMPMEM 和 INNODB_CMPMEM_RESET：InnoDB 缓冲池中压缩页面的状态

（2）INNODB_LOCKS：InnoDB 事务所请求和持有的每个锁

（4）INNODB_LOCK_WAITS：每个阻塞的 InnoDB 事务的一个或多个行锁

（5）INNODB_TRX：当前正在 InnoDB 内部执行的所有事务

（6）TABLESPACES：活动的表空间

3、使用information_schema表创建shell命令

select concat("mysqldump -uroot -p ",table_schema," ", table_name,">>",table_schema,".bak.sql") from tables where table_name like 'employees';

4、

SHOW DATABASES：列出可用数据库的名称

（1）SHOW TABLES：列出默认数据库中的表

（2）SHOW TABLES FROM <database_name>：列出指定数据库中的表

（3）SHOW COLUMNS FROM <table_name>：显示表的列结构

（4）SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息

（5）SHOW CHARACTER SET：显示可用的字符集及其默认整理

（6）SHOW COLLATION：显示每个字符集的整理

七、事务与锁定

1、通过事务，您可以将一个或多个 SQL 语句作为一个工作单元来执行，这样，所有语句或者都成功，或者都失败。在与其他任何事务执行的工作隔离的情况下，可能会出现这种情况。如果所有语句都成功，则可以提交该事务，以便在数据库中永久记录其效果。如果在事务期间出现错误，则可以回滚以取消它。此前已在该事务中执行的任何语句将被撤消，从而使数据库保持开始该事务之前的状态。

在 MySQL 中，只有那些使用事务存储引擎（如 InnoDB）的表才支持事务。这些语句不会对非事务存储引擎所管理的表产生任何明显影响。

事务处理能够更有力地保证数据库操作的结果，但同时也需要更多的 CPU 周期、内存和磁盘空间开销。事务属性对于某些应用程序来说是必不可少的，但并非所有应用程序都如此，您可以选择对您的应用程序最有意义的事务属性。

财务操作通常需要使用事务，保证数据完整性会比增加开销所带来的成本更重要。另一方面，如果某个应用程序用于记录通过网页访问数据库表的操作，则因服务器主机出现故障而丢失几条记录可能是允许的。

2、事务SQL控制语句

MySQL不论是DDL还是DML都是自动提交的，所以我们要先将自动提交关闭

begin；

insert into t3 values(1)；

insert into t3 values(2)；

savepoint a；

insert into t3 values(3)；

rollback to a；

commit；

3、事务存储引擎

Support 列中的值为 YES 或 NO，用于指示该引擎是否可以使用。如果该值为DISABLED，则表示该引擎存在，但已关闭。值 DEFAULT 用于指示服务器在默认情况下使用的存储引擎。指定为 DEFAULT 的引擎应视为可用。Transactions、XA 和Savepoints 列用于指示该存储引擎是否支持这些功能。

4、事务隔离问题

（1）“脏”读：假定事务 T1 修改了某行。如果事务 T2 读取该行，并发现修改内容，但T1 尚未提交，则会出现“脏”读问题。之所以会成为一个问题，是因为如果 T1 回滚，所做的更改会被撤消，但 T2 并不会意识到这一点。

（2）不可重复读：如果稍后在同一个事务中重复相同的读取操作，则会产生不同的结果。

（3）虚读：假定事务 T1 和 T2 开始，并且 T1 读取了某些行。如果 T2 插入一个新行，而T1 在重复相同的读取操作时发现该行，则会发生虚读问题（新行会成为虚行）。

5、隔离级别

使用存储引擎可实现隔离级别。隔离级别选项在不同的数据库服务器之间是不一样的，因此，InnoDB 所实现的级别可能与其他数据库系统所实现的级别并不完全对应。InnoDB 可实现四种隔离级别，用于控制事务所做的更改在多大程度上可由其他同时进行的事务注意到：

（1）READ UNCOMMITTED：允许发生“脏”读、不可重复读和虚读。

（2）READ COMMITTED：允许发生不可重复读和虚读。未提交的更改仍不可见。

（3）REPEATABLE READ：无论其他事务所做的更改是否已提交，两次都会获得相同的结果。换句话说，也就是不同的事务会对相同的数据产生一致的结果。

（4）SERIALIZABLE：与 REPEATABLE READ 类似，但其限制性更强，即一个事务的行不能由其他事务更改，直到第一个事务完成为止。

6、设置隔离级别

要了解当前隔离级别，可使用 tx_isolation 服务器变量，如果此变量未使用前缀，则会返回会话事务隔离级别。使用 global 和 session 前缀，可以相应地显式获取全局或会话隔离级别。也可以使用此服务器变量来设置事务隔离级别。此隔离级别与在 SET TRANSACTION

ISOLATION LEVEL 语法中设置的隔离级别同样有效，其差别在于，必须以字符串形式（而不是普通关键字）来表示，并且必须用连字符（短划线或减号）来分隔用于定义隔离级别的词，而不是使用空格来分隔。

隔离演示：

（1）

（2）

此时我将会话2的事务提交

会话1还是无法查看到，需要登出重新登陆才可以看到