组件MySQL

UJava约 20533 字大约 68 分钟

一、数据库简介

MySQL是关系型数据库，解决结构型数据的存储及查询问题。

1. 数据库是什么

数据库是数据管理的有效技术，是由一批数据构成的有序集合，这些数据被存放在结构化的数据表里。数据表之间相互关联，反映客观事物间的本质联系。数据库能有效地帮助一个组织或企业科学地管理各类信息资源。

数据是数据库中存储的基本对象，是按一定顺序排列组合的物理符号。数据有多种表现形式，可以是数字、文字、图像，甚至是音频或视频，它们都可以经过数字化后存入计算机。
数据库是数据的集合，具有统一的结构形式并存放于统一的存储介质内，是多种应用数据的集成，并可被各个应用程序所共享。
在日常生活中，人们可以直接用中文、英文等自然语言描述客观事物。在计算机中，则要抽象出对这些事物感兴趣的特征，并组成一个记录来描述。

索引

全局索引
唯一索引
主键索引

2. 数据库在开发中的作用

从数据库系统应用角度来看，数据库系统常见的运行与应用结构有：客户端/服务器结构、浏览器/服务器结构。

在客户端/服务器（Client/Server，C/S）结构中，数据库的使用者（如 DBA、程序设计者）通过命令行客户端、图形化界面管理工具或应用程序等连接到数据库管理系统，可以通过数据库管理系统查询和处理存储在底层数据库中的各种数据。
数据库使用者与命令行客户端、图形化界面管理工具或应用程序等直接交互，而不与数据库管理系统直接联系。
在这种结构中，命令行客户端、图形化界面管理工具或应用程序等称为“客户端”或“前台”，主要完成与数据库使用者的交互任务；而数据库管理系统则称为“服务器”或“后台”，主要负责数据管理。这种结构经常被称为“C/S”结构。
在客户端/服务器模式中，客户端和服务器可以同时工作在同一台计算机上，这种工作方式称为“单机方式”；也可以“网络方式”运行，即服务器被安装和部署在网络中某一台或多台主机上。
对于客户端应用程序的开发，目前常用的语言工具主要有 Visual C++、Delphi、.NET 框架、Visual Basic、Python 等。
数据库能有效存储数据，读取数据、查找数据更是方便，其实那些管理软件就是通过软件的界面向内部的数据库进行数据的增、删、改、查操作。

3.常见数据库比较

3.1 MySQL数据库
定位
开源、多平台、关系型数据库
目前使用最广泛、流行度最高的的开源数据库。

功能
支持事务，符合关系型数据库原理，符合ACID，支持多数SQL规范，以二维表方式组织数据，有插件式存储引擎，支持多种存储引擎格式

部署
用编译安装的方式，或者二进制包的方式，按照“安装软件-创建实例-库表用户初始化”，可以很快完成数据库部署

使用
使用标准的SQL语句进行数据库管理，简单SQL语句的并发和性能较好，对视图、存储过程、函数、触发器等支持的不是太好

监控
在命令行界面有一些常用的命令显示状态和性能，在图形界面方面，有比较多的开源监控工具来监控和记录数据库的状态，比如zabbix，nagios，cacti，lepus等

备份
逻辑备份 mysqldump/mysqldumper ，物理备份用xtrabackup等工具进行备份；

高可用
MySQL高可用有多种方案，官方有基础的master-slave主从复制，新版本的innodb cluster，第三方的有MHA等高可用方案；

扩展
MySQL水平拆分，可以通过水平拆分proxy中间进行逻辑映射和拆分，扩大MySQL数据库的并发能力和吞吐量。

适用场景
默认的innodb存储引擎，支持高并发，简单的绝大部分OLTP场景；
Tokudb存储引擎，使用高并发insert的场景；
Inforbright存储引擎，可以进行列压缩和OLAP统计查询场景；

选择注意
使用MySQL进行OLTP业务时，需要注意数据量级，如果数据量级过大，需要进行水平拆分；
如果有OLAP需求，可以结合其他架构综合考虑。

3.2 SQL Server数据库
定位
商业、Windows平台、关系型数据库
最早接触、与微软体系结合紧密的的商业数据库，属于“微软技术体系”

功能
支持事务，符合关系型数据库原理，符合ACID，支持多数SQL规范，以二维表方式组织数据

部署
在Windows平台，用图形界面进行软件安装；

使用
在Windows平台，使用SQL Server Mangement Studio图形界面进行安装；

监控
一般通过Windows资源管理和SQL server图形工具进行系统和数据库性能显示；

备份
通常用第三方备份恢复软件进行备份恢复；

高可用
通过共享存储和双机热备的方式，可以实现SQL Server数据库的高可用；

扩展
SQL Server数据库集群采用共存存储的方式，通过硬件垂直升级来对数据库集群进行扩展；

适用场景
大多数OLTP场景（与微软体系配合）

选择注意

SQL Server与微软技术体系结合比较紧密，绝大多数工作，都是通过图形界面完成，对于习惯使用命令行的DBA可能会有不习惯；
SQL server对双引号，大小写，元信息的管理和处理方式，与其他数据库很不相同，需要注意；
使用SQL Server满足OLTP业务，会有比较好的效果，但对于大数据量的OLAP业务，最好还是选用专门的OLAP架构，不要在同一个SQL Server实例上混用OLTP和OLAP业务；
SQL server属于商业软件，需要注意版权和licence授权费用；

3.3 Oracle数据库
定位
商业、多平台、关系型数据库
功能最强大、最复杂、市场占比最高的商业数据库

功能
支持事务，符合关系型数据库原理，符合ACID，支持多数SQL规范，以二维表方式组织数据

部署
Oracle单实例数据库部署相对容易，但Oracle RAC集群环境，部署的步骤和依赖条件都比较多；

使用
通常使用命令行工具，进行各种数据库的管理，通常也可以用shell脚本和python脚本提高Oracle数据库管理效率；各种管理功能，都比较强大；

监控
Oracle官方有比较全面的监控工具，常用的第三方监控平台，如zabbix,cacti,lepus等都有对Oracle数据库的各项指标的完善监控；

备份
支持冷备份和热备份，可以用 exp/imp , expdp/impdp等进行逻辑备份和恢复，可以使用强大的RMAN工具进行专业的物理热备份和恢复；

高可用
Oracle数据库的高可用架构，可以用第三方双机热备软件，结合Oracle单实例实现；可以使用Oracle Dataguard，实现master和standby的备份；可以使用 Oracle RAC集群实现实例级别的高可用和负载均衡，使用ASM实现存储级别的高可用；

扩展
由于Oracle集群采用共享存储的方式，一般只能通过垂直硬件升级进行升级；

适用场景
绝大多数OLTP场景，部分OLAP

选择注意
Oracle从架构到运维，可以说是最难的数据库，学习和使用难度较高。

3.4 Hbase数据库
定位

开源、Linux平台、列存储nosql数据库
可用于海量数据存储、与Hadoop生态圈结合、定位于“大”的列存储nosql数据库

功能
命令执行速度非常看，读写性能可达10万/秒；数据结构是key-value类似字典的功能，可以键过期-缓存，发布订阅-消息系统，简单的事物功能；

部署
相对其他数据库，hbase的部署比较复杂，依赖Hadoop，zookeeper等组件，Hbase集群包括一个mater节点，多个regionServer，zookeeper管理所有regionServer，需要依次部署Hadoop、zookeeper之后，再部署HBASE集群；

使用
用redis-cli客户端连接，一般用简单的 set ,get,del 进行数据管理；在单实例redis的基础上，进行可以数据持久化，主从复制，高可用和分布式等功能；

监控
在命令行界面有一些常用的命令显示状态和性能，在图形界面方面，有开源监控工具来监控和记录数据库的状态，比如cachecloud；

备份
Hbase一般用作海量数据的仓库，本身通过多层副本来保证数据安全性，不用进行专门的备份

高可用
HBASE集群基于Hadoop，需要依次部署Hadoop单机模式、集群模式、HA模式，通过Hadoop HA实现高可用；

扩展
HBASE以集群形式，依次是单机模式，伪分布模式，完全分布模式，底层基于HDFS，zookeeper可以很好地进行扩展；

适用场景
两大用途：

用于简单数据写入和海量、结构简单数据查询的业务场景；
用于成为其他数据库备份和下沉的数据库；

选择注意

Hbase不适合的场景：对数据分析需求高，需要能够用sql或者简单的MapReduce实现分析需求的业务场景，不适合用Hbase；
单表数据量，不超过千万时，使用Hbase，体现不出Hbase的优势，而且会比较慢，不适合用Hbase。
通过对上面数据库“七种”武器的描述，也可以看到目前常用数据库的使用脉络和选择顺序，对应一个业务，可以优先选择最流行的开源数据库——MySQL；如果出于稳定和商业版考虑，可以选择Oracle数据库，或者SQL Server数据库（与Windows体系结合）；如果想用开源，有想要有足够的功能来应对各种场景，可以使用 postgresql数据库。这四种数据库，都是关系型数据库，可以很好地满足大多数业务场景，解决通用性问题。
对于一些特殊性问题，尤其是想要在扩展性方面有比较高的要求，可以考虑nosql数据库。Mongodb数据库，介于关系型数据库和非关系型数据库之间，兼具两者的特点，是非常流行的文档型nosql数据库；redis定位于内存型键值nosql数据库；hbase是海量文件存储的列式nosql数据库。根据合适的业务场景，选择适合的nosql数据库，可以对某一类，或某几类业务问题有很好的解决，可以作为关系型数据库的一种补充。
换个角度，MySQL，Oracle，SQL Server，Postgresql，mongodb这五种数据库，也是DB-Engines排行榜上最流行的排名前五的五种数据库，从使用量和受欢迎程度，也可以看出这些数据库使用的广泛性。

MySQL 使用的 SQL 语言是用于访问数据库的最常用的标准化语言。
由于 MySQL 数据库体积小、速度快、总体拥有成本低、开放源代码，其有着广泛的应用，一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越，因此搭配 PHP 和 Apache 服务器可组成良好的开发环境。
MySQL 数据库管理系统具有以下系统特性：
（1）使用 C 和 C++ 编写，并使用多种编译器进行测试，保证源代码的可移植性。
（2）支持 AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows 等多种操作系统。
（3）为多种编程语言提供了 API。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
（4）支持多线程，充分利用 CPU 资源。
（5）优化的 SQL 查询算法，有效地提高查询速度。
（6）既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入其他的软件中。
（7）提供多语言支持，常见的编码如中文的 GB 2312、BIG 5，日文的 Shift_JIS 等都可以用作数据表名和数据列名。
（8）提供 TCP/IP、ODBC 和 JDBC 等多种数据库连接途径。
（9）提供用于管理、检查、优化数据库操作的管理工具。
（10）支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
（11）支持多种存储引擎。

2. MySQL的版本以及版本号

针对不同的用户，MySQL分为两个版本：
（1）MySQL Community Server（社区版）：该版本完全免费，但是官方不提供技术支持。
（2）MySQL Enterprise Server（企业版）：该版本能够以很高的性价比为企业提供数据仓库应用，支持 ACID 事物处理，提供完整的提交、回滚、崩溃恢复和行级锁定功能，但是该版本需要付费使用，官方提供电话技术支持。
温馨提示：MySQL Cluster 主要用于架设群服务器，需要在社区服务或企业版的基础上使用。

MySQL 的命名机制由 3 个数字和 1 个后缀组成，例如 mysql-5.7.20：

第 1 个数字“5”是主版本号，用于描述文件的格式，所有版本 5 的发行版都有相同的文件夹格式。
第 2 个数字“7”是发行级别，主版本号和发行级别组合在一起便构成了发行序列号。
第 3 个数字“20”是在此发行系列的版本号，随每次新发行的版本递增。通常选择已经发行的最新版本。

在 MySQL 开发过程中，同时存在多个发布系列，每个发布系列的成熟度处在不同阶段。

MySQL 5.7 是最新开发的稳定（GA）发布系列，是将执行新功能的系列，目前已经可以正常使用。
MySQL 5.6 是比较稳定的（GA）发布系列，只针对漏洞修复重新发布，不增加会影响稳定性的新功能。
MySQL 5.1 是一个稳定的（产品质量）发布系列，只针对严重漏洞修复和安全修复重新发布，不增加影响该系列稳定性的重要功能。
对于 MySQL 4.1 等低于 5.0 的老版本，官方将不再提供支持

3. MySQL 5.7的新特性

与 MySQL5.6 相比，MySQL 5.7 具有以下几个方面的新功能。
（1）随机 root 密码
MySQL 5.7 数据库初始化完成后，会自动生成一个 root@localhost 用户，root 用户的密码不为空，而是随机产生一个密码。
（2）自定义 test 数据库
MySQL 5.7 默认安装完成后没有 test 数据库。用户可以自行创建 test 数据库并对其进行权限控制。
（3）默认 SSL 加密
MySQL 5.7 采用了更加简单的 SSL 安全访问机制，默认连接使用 SSL 的加密方式。
（4）密码过期策略
MySQL 5.7 支持用户设置密码过期策略，要求用户在一定时间过后必须修改密码。
（5）用户锁
MySQL 5.7 为管理员提供了暂时禁用某个用户的功能，使被锁定的用户无法访问和使用数据库。
（6）全面支持JSON
MySQL 5.7在服务器端提供了一组便于操作 JSON 的函数。存储的方法是将 JSON 编码成 BLOB 后再由存储引擎进行处理。这样，MySQL 就同时拥有了关系型数据库和非关系型数据库的优点，并且可以提供完整的事务支持。
（7）支持两类生成列（generated column）
生成列是通过数据库中的其他列计算得到的一列。当为生成列创建索引时，可以便捷地加快查询速度。MySQL 5.7 支持虚拟生成列和存储生成列。虚拟生成列仅将数据保存在表的元数据中，作为缺省的生成列类型；存储生成列则是将数据永久保存在磁盘上，需要更多的磁盘空间。
（8）引入系统库（sys schema）
系统库中包含一系列视图、函数和存储过程，通过多线程、多进程、组合事务提交和基于行的优化方式将复制功能提高 5 倍以上，用户向外扩充其跨商品系统的工作负载时，得以大幅提升复制的效能和效率。
与 MySQL5.6 相比，MySQL 5.7 具有以下几个方面的新功能。

三、Mysql安装与服务启动（Windows版本）

1. 下载

用户可以根据自身的操作系统类型，从 MySQL官方下载页面免费下载相应的服务器安装包。本书以 MySQL 5.7.20 为例介绍其在 Windows 10 操作系统下的安装和配置过程。

用户下载 Windows 图形化安装包的步骤如下。

步骤 1)：打开 MySQL 官方网站（http://www.mysql.com），单击 DOWNLOAD，进入 MySQL 产品的下载界面，如图所示。

步骤 2)：在 MySQL 产品分类中选择 Community 菜单，在下载列表中选择 MySQL Community Server，如图所示。

步骤3)：在下载页面中，操作系统选择 Microsoft Windows，下载的安装文件为 mysql-installer-community-5.7.20.0.msi，如图所示。

2. 安装教程

Windows 平台下提供两种安装 MySQL 的方式：

MySQL 二进制分发版（.msi 安装文件）。
免安装版（.zip 压缩文件）。

用户使用图形化安装包安装 MySQL 的步骤如下：

步骤 1)：双击下载的 MySQL 安装文件，进入 MySQL 安装界面，首先进入“License Agreement（用户许可证协议）”窗口，选中“I accept the license terms（我接受系统协议）”复选框，单击“Next（下一步）”按钮，如图所示。

进入MySQL安装界面并接受系统协议

步骤 2)：进入“Choosing a Setup Type（安装类型选择）”窗口，根据右侧的安装类型描述文件选择适合自己的安装类型，这里选择默认的安装类型，如图所示。

选择默认的安装类型

注意：Developer Default：默认安装类型；Server only：仅作为服务；Client only：仅作为客户端；Full：完全安装；Custom：自定义安装类型。

步骤 3)：根据所选择的安装类型安装 Windows 系统框架（framework），单击 Execute 按钮，安装程序会自动完成框架的安装，如图所示。

检查并生成安装所需要的框架列表

当弹出安装程序窗口时，勾选“我同意许可条款和条件”复选框，然后单击“安装”按钮，如图所示。

同意安装框架的许可条件

弹出“设置成功”的界面，表示该框架已经安装完成，单击“关闭”按钮即可。所有的框架安装均可参考本操作，如图所示。

安装框架成功

步骤 4)：所需框架均安装成功后，单击 “Next（下一步）”按钮，如图所示。

所有框架安装完成

步骤 5)：进入安装确认窗口，单击 “Execute（执行）”按钮，开始 MySQL 各个组件的安装，如图所示。

准备安装MySQL各个组件

步骤 6)：开始安装 MySQL 文件，安装完成后在 “Status（状态）”列表下显示 “Complete（安装成功）”，如图所示。

MySQL各个组件安装成功

3. 判断是否安装成功

3.1 启动与关闭服务
net start mysql为启动服务，net stop mysql为关闭命令

3.2 登录数据库
cmd进入数据库的bin文件夹中

输入mysql -u root -p命令，再输入登录密码，出现以下结果代表登录成功

3.3 查看数据库名称
登录完成后，输入show databases

四、Mysql图形化工具

（1）Navicat（重点推荐）

Navicat是MySQL和MariaDB数据库管理与开发理想的解决方案。它可同时在一个应用程序上连接MySQL和MariaDB数据库。这种兼容前端为数据库提供了一个直观而强大的图形界面管理、开发和维护功能，为初级MySQL和MariaDB开发人员和专业开发人员都提供了一组全面的开发工具。

（2）Induction

Induction是一款用于理解数据关系的开源管理工具，它可用来探索行/列，运行查询和数据可视化等方面。该工具支持多种数据库，包括PostgreSQL，MySQL，SQLite，Redis以及MongoDB。此外，Induction还可以通过编写添加其他新的适配器。

（3）SqlWave

SQLWave是一种简单、快速且易用的MySQL客户端。用户可通过该工具轻松地连接到远程主机。SqlWave支持所有MySQL的最新版本，包括它用来管理数据库结构的所有最新功能，如工作表、视图、存储过程、函数、事件、外键和触发器等。

五、Mysql存储引擎精讲

1. 存储引擎分类

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。
提示：InnoDB 事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。MySQL 5.5.5 之后，InnoDB 作为默认存储引擎。MyISAM 是基于 ISAM 的存储引擎，并对其进行扩展，是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM 拥有较高的插入、查询速度，但不支持事务。MEMORY 存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。

2. MySQL 5.7 支持的存储引擎

MySQL 支持多种类型的数据库引擎，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。在 MySQL 中，可以利用 SHOW ENGINES 语句来显示可用的数据库引擎和默认引擎。
MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE 等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型，结果如图所示。

3. MySQL 默认存储引擎

InnoDB 是系统的默认引擎，支持可靠的事务处理。
使用下面的语句可以修改数据库临时的默认存储引擎
SET default_storage_engine=< 存储引擎名 >
例如，将 MySQL 数据库的临时默认存储引擎修改为 MyISAM，输入的 SQL 语句和运行结果如图所示。

此时，可以发现 MySQL 的默认存储引擎已经变成了 MyISAM。但是当再次重启客户端时，默认存储引擎仍然是 InnoDB。

六、Mysql数据类型介绍

1. 基本介绍

在 MySQL 中常见的数据类型如下：

整数类型
包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型 FLOAT 和 DOUBLE，定点数类型 DECIMAL。
日期/时间类型
包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
字符串类型
包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。
二进制类型
包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

2. 整数类型

MySQL提供了多种数值型数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大。
MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。

从上表中可以看到，不同类型的整数存储所需的字节数不相同，占用字节数最小的是 TINYINT 类型，占用字节最大的是 BIGINT 类型，占用的字节越多的类型所能表示的数值范围越大。

根据占用字节数可以求出每一种数据类型的取值范围。例如，TINYINT 需要 1 个字节（8bit）来存储，那么 TINYINT 无符号数的最大值为 28-1，即 255；TINYINT 有符号数的最大值为 27-1，即 127。其他类型的整数的取值范围计算方法相同，如下表所示。

提示：显示宽度和数据类型的取值范围是无关的。显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充。如果插入了大于显示宽度的值，只要该值不超过该类型整数的取值范围，数值依然可以插入，而且能够显示出来。例如，year 字段插入 19999，当使用 SELECT 查询该列值的时候，MySQL 显示的将是完整的带有 5 位数字的 19999，而不是 4 位数字的值。

3. 小数类型

MySQL中使用浮点数和定点数来表示小数。

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。
浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。
浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。
下表中列出了 MySQL 中的小数类型和存储需求。

FLOAT 类型的取值范围如下：

有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。

DOUBLE 类型的取值范围如下：

有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。
提示：不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。
FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围；缺点是会引起精度问题。

最后再强调一下：在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

4. 日期和时间类型

MySQL中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候，可以只使用 YEAR 类型。

每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。
下表中列出了 MySQL 中的日期与时间类型。

YEAR 类型

YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 YEAR，如下所示：
以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 '1901'～'2155'。输入格式为 'YYYY' 或者 YYYY，例如，输入 '2010' 或 2010，插入数据库的值均为 2010。
以 2 位字符串格式表示的 YEAR，范围为 '00' 到 '99'。'00'～'69' 和 '70'～'99' 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。'0' 与 '00' 的作用相同。插入超过取值范围的值将被转换为 2000。
以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。
提示：两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 '0' 或 '00'，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

TIME 类型
TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。

可以使用各种格式指定 TIME 值，如下所示。

'D HH：MM：SS' 格式的字符串。还可以使用这些“非严格”的语法：'HH：MM：SS'、'HH：MM'、'D HH' 或 'SS'。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。
'HHMMSS' 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，'101112' 被理解为'10：11：12'，但是 '106112' 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。
提示：为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。例如，读者可能认为 '1112' 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 '12' 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，'11：12' 表示 11：12：00，而不是 00：11：12。

DATE 类型
DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：
以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符中格式表示的日期，取值范围为 '1000-01-01'～'9999-12-3'。例如，输入 '2015-12-31' 或者 '20151231'，插入数据库的日期为2015-12-31。
以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：'00～69' 范围的年值转换为 '20002069'，'7099' 范围的年值转换为 '1970～1999'。例如，输入 '15-12-31'，插入数据库的日期为 2015-12-31；输入 '991231'，插入数据库的日期为 1999-12-31。
以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。
使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。
提示：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，'98-11-31'、'98.11.31'、'98/11/31'和'98@11@31' 是等价的，这些值也可以正确地插入数据库。

DATETIME 类型
DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH：MM：SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。
以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，'00～79' 范围的年值转换为 '2000～2079'，'80～99' 范围的年值转换为 '1980～1999'。例如，输入 '14-12-31 05：05：05'，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
提示：MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，'98-12-31 11：30：45'、'98.12.31 11+30+35'、'98/12/31 11_30_45' 和 '98@12@31 113045' 是等价的，这些值都可以正确地插入数据库。

TIMESTAMP 类型
TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。
提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：
DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。
提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

5. 字符串类型

字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。

MySQL中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
下表中列出了 MySQL 中的字符串数据类型，括号中的M表示可以为其指定长度。

VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

CHAR 和 VARCHAR 类型

CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M 表示列的长度，范围是 0～255 个字符。

例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当检索到 CHAR 值时，尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串，M 表示最大列的长度，M 的范围是 0～65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。

例如，VARCHAR(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。

【实例】下面将不同的字符串保存到 CHAR(4) 和 VARCHAR(4) 列，说明 CHAR 和 VARCHAR 之间的差别，如下表所示。

插入值 CHAR(4) 存储需求 VARCHAR(4) 存储需求

' ' ' ' 4字节 '' 1字节
'ab' 'ab ' 4字节 'ab' 3字节
'abc' 'abc ' 4字节 'abc' 4字节
'abcd' 'abcd' 4字节 'abcd' 5字节
'abcdef' 'abcd' 4字节 'abcd' 5字节
对比结果可以看到，CHAR(4) 定义了固定长度为 4 的列，无论存入的数据长度为多少，所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加 1。

TEXT 类型

TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。
TINYTEXT 表示长度为 255（28-1）字符的 TEXT 列。
TEXT 表示长度为 65535（216-1）字符的 TEXT 列。
MEDIUMTEXT 表示长度为 16777215（224-1）字符的 TEXT 列。
LONGTEXT 表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。

ENUM 类型
ENUM 是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：

<字段名> ENUM( '值1', '值1', …, '值n' )
字段名指将要定义的字段，值 n 指枚举列表中第 n 个值。

ENUM 类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。

ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

例如，定义 ENUM 类型的列（'first'，'second'，'third'），该列可以取的值和每个值的索引如下表所示。

值 索引  
NULL NULL  
'' 0  
’first 1  
second 2  
third 3

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。
提示：ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

SET 类型
SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号,隔开，语法格式如下：
SET( '值1', '值2', …, '值n' )
与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。
提示：如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

七、Mysql主要专业名称介绍

1. 主键

1.1 什么是主键
“主键（PRIMARY KEY）”的完整称呼是“主键约束”。MySQL主键约束是一个列或者列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。

1.2 选取设置主键约束的字段
主键约束即在表中定义一个主键来唯一确定表中每一行数据的标识符。主键可以是表中的某一列或者多列的组合，其中由多列组合的主键称为复合主键。主键应该遵守下面的规则：

每个表只能定义一个主键。
主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在两行数据有相同的主键值。这是唯一性原则。
一个列名只能在复合主键列表中出现一次。
复合主键不能包含不必要的多余列。当把复合主键的某一列删除后，如果剩下的列构成的主键仍然满足唯一性原则，那么这个复合主键是不正确的。这是最小化原则。

1.3 创建主键
语法规则：<字段名> <数据类型> PRIMARY KEY [默认值]

2. 外键约束

2.1 什么是外键约束
MySQL外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。

外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。
外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。
外键的主要作用是保持数据的一致性、完整性。例如，部门表 tb_dept 的主键是 id，在员工表 tb_emp5 中有一个键 deptId 与这个 id 关联。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。

2.2 选取设置 MySQL 外键约束的字段
定义一个外键时，需要遵守下列规则：
（1）父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
（2）必须为父表定义主键。
（3）主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
（4）在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
（5）外键中列的数目必须和父表的主键中列的数目相同。
（6）外键中列的数据类型必须和父表主键中对应列的数据类型相同。

2.3 在创建表时设置外键约束
在数据表中创建外键使用 FOREIGN KEY 关键字，具体的语法规则如下：
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

其中：外键名为定义的外键约束的名称，一个表中不能有相同名称的外键；字段名表示子表需要添加外健约束的字段列；主表名即被子表外键所依赖的表的名称；主键列表示主表中定义的主键列或者列组合。

3. 唯一约束

MySQL唯一约束（Unique Key）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。

4. 默认值

4.1 什么是默认值
“默认值（Default）”的完整称呼是“默认值约束（Default Constraint）”。MySQL默认值约束用来指定某列的默认值。
例如女性同学较多，性别就可以默认为“女”。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为“女”。

4.2 在创建表时设置默认值约束
创建表时可以使用 DEFAULT 关键字设置默认值约束，具体的语法规则如下：
<字段名> <数据类型> DEFAULT <默认值>;

5. 非空约束

5.1 什么是非空约束
MySQL非空约束（NOT NULL）可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。在表中某个列的定义后加上关键字 NOT NULL 作为限定词，来约束该列的取值不能为空。
非空约束（Not Null Constraint）指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

5.2 在创建表时设置非空约束
创建表时可以使用 NOT NULL 关键字设置非空约束，具体的语法规则如下：
<字段名> <数据类型> NOT NULL;

6. 触发器

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括INSERT语句、UPDATE语句和DELETE语句。当数据库系统执行这些事件时，会激活促发其执行相应的操作。

7. DML

DML（data manipulation language）数据操纵语言：
　　　　就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。主要用来对数据库的数据进行一些操作。

SELECT 列名称 FROM 表名称
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
DELETE FROM 表名称 WHERE 列名称 = 值

8. DDL

DDL（data definition language）数据库定义语言：其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上

CREATE TABLE 表名称
(
列名称1 数据类型,
列名称2 数据类型,
列名称3 数据类型,
....
)

ALTER TABLE table_name
ALTER COLUMN column_name datatype

DROP TABLE 表名称
DROP DATABASE 数据库名称

9. DCL

DCL（Data Control Language）数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。这个比较少用到。在公司呢一般情况下我们用到的是DDL、DML这两种。

八、Mysql常见sql语句

1. select语句

请在资料下载中进行学习

2. 函数

请在资料下载中进行学习

3. 多表查询

请在资料下载中进行学习

4. 表的内连与外连‘

请在资料下载中进行学习’

九、Mysql设计与语句优化

1. 数据库创建优化

请在资料下载中进行学习

2. sql语句优化

请在资料下载中进行学习

十、事务介绍

1. 事务概述

事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务中的操作，要么都执行修改，要么都不执行，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。

严格上来说，事务必须同时满足4个特性，即通常所说事务的ACID特性。虽然理论上定义了严格的事务要求，但是数据库厂商出于各种目的并没有严格满足事务的ACID标准。例如，对于MYSQL的NDB Cluster引擎，虽然支持事务，但是不满足D的要求，即持久性的要求。对于Oracle数据库来说，其默认的事务隔离级别为READ COMMITTED，不满足I的要求，即隔离性的要求。对于InnoDB存储引擎而言，默认的事务隔离级别是READ REPRATABLE，完全遵循和满足事务的ACID特性。

A（atomicity），原子性。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到事务前的状态。

C（consistency），一致性。一致性是指事务将数据库从一种状态转变为另一种状态。在事务的开始之前和事务结束以后，数据库的完整性约束没有被破坏。

I（isolation），隔离性。隔离性还有其他的称呼，如并发控制、可串行化、锁。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能互相分离，即该事务提交前对其他事务都不可见，这通常使用锁来实现。数据库系统中提供了一种粒度锁的策略，允许事务仅锁住一个实体对象的子集，以此来提高事务之间的并发度。（如果是全表锁，事务之间基本就无法实现并发，但是如果只锁住表中处理的行，可以提高事务的并发度）

D（durability），持久性。事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。需要注意的是，持久性只能从事务本身的角度来保证结果的永久性，如事务提交后，所有的变化都是永久的，即使当数据库由于崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失。

事务的（ACID）特性是由关系数据库管理系统（RDBMS，数据库系统）来实现的。数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。数据库管理系统采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

2. 事务分类

（1）扁平事务，最简单，使用最频繁的事务。在扁平事务中，所有的操作都处于一个层次，其有BEGIN WORK开始，有COMMIT WORK或ROLLBACK WORK结束。处于之间的操作是原子的，要么全部执行，要么全部回滚。
（2）带有保存点的扁平事务，除了扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能有些事务在执行过程中出现的错误并不会对有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。
（3）链事务可视为保存点模式的一个变种。
（4）嵌套事务是一个层次结构框架。
（5）分布式事务

3. 事务控制语句

在MYSQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显示的开启一个事务必须使用命令BEGIN和START TRANSACTION，或者执行命令SET AUTOCOMMIT = 0，以禁用当前会话的自动提交。事务控制语句如下：

START TRANSACTION | BEGIN：显示的开启一个事务。在存储过程中，MYSQL数据库的分析器会自动将BEGIN识别为BEGIN...END，因此在存储过程中只能使用START TRANSACTION语句来开启一个事务。
COMMIT：要想使用这个语句的最简形式，只需发出COMMIT。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的。COMMIT和COMMIT WORK语句基本上是一致的，都是用来提交事务。不同的是COMMIT WORK用来控制事务结束后的行为是CHAIN还是RELEASE的。如果是CHAIN方式，那么事务就变成了链事务。用户可以通过参数completion_type来进行控制，默认该参数是0，表示没有任何操作。在这种设置下，COMMIT和COMMIT WORK是完全等价的。当参数值为1时，COMMIT WORK等价于COMMIT AND CHAIN，表示马上自动开启一个相同隔离级别的事务。当参数值为1时，COMMIT WORK等价于COMMIT AND RELEASE。当提交事务后会自动断开与服务器连接。
ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
SAVEPOINT identifiter：SAVEPOINT允许用户在事务中创建一个保存点，一个事务可以有很多个保存点。
RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有一个保存点执行这语句时，会抛出一个异常。
ROLLBACK to [SAVEPOINT] identifier：这个语句与SAVEPOINT命令一起使用。可以把事务回滚到标记点，而不回滚到此标记点之前的任何工作。注意：虽然有ROLLBACK，但是它并没有真正的结束一个事务，因此即使执行了ROLLBACK TO SAVEPOINT，之后也需要显示的运行COMMIT或ROLLBACK命令。
SET TRANSACTION：这个语句用来设置事务的隔离级别。InnoDB存储引擎提供的事务隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

4. 事务的隔离级别

ANSI SQL标准定义的四个隔离级别为：

READ UNCOMMITTED（未提交读），事务中的修改，即使没有提交，在其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读。
READ COMMITTED（提交读），一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读，因为两次执行相同的查询，可能会得到不一样的结果。因为在这2次读之间可能有其他事务更改这个数据，每次读到的数据都是已经提交的。
REPEATABLE READ（可重复读），解决了脏读，也保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重读读隔离级别还是无法解决另外一个幻读的问题，指的是当某个事务在读取某个范围内的记录时，另外一个事务也在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行。
SERIALIZABLE（可串行化），它通过强制事务串行执行，避免了前面说的幻读的问题。
1、脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据。

2、不可重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了（update），也可能被删除了（delete）。

3、幻像读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集。

InnoDB采用MVCC来支持高并发，并实现了四个标准的隔离级别。其默认级别是REPEATABLE READ（可重复读），并且通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影的插入。

隔离级别越低，事务请求的锁越少或保持锁的时间就越短。所以很多数据库系统默认的事务隔离级别是READ COMMITTED。质疑SERIALIZABLE隔离级别的性能，但是InnoDB存储引擎认为两者的开销是一样的，所以默认隔离级别使用REPEATABLE READ。

用命令设置当前会话或全局会话的事务隔离级别。

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE
}
如果想启动时就设置事务的默认隔离级别，修改MYSQL的配置文件，在[mysqld]中添加如下行：

[mysqld]
transaction-isolation = READ-COMMITTED

十一、Mysql数据库备份与恢复

1. 数据库备份

数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。

可以使用 SELECT INTO OUTFILE 语句把表数据导出到一个文本文件中进行备份。

注意：这种方法只能导出或导入数据的内容，而不包括表的结构。若表的结构文件损坏，则必须先设法恢复原来表的结构。

【实例】将数据库 test_db 的表 tb_students_info 的全部数据备份到 C 盘的数据备份目录下文件名为 file.txt 的文件中，要求每个字段用逗号分开，并且字符用双引号标注，每行以问号结束。

输入的SQL语句和执行结果如下所示。

mysql> SELECT * FROM test_db.tb_students_info
    -> INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/file.txt'
    -> FIELDS TERMINATED BY '"'
    -> LINES TERMINATED BY '?';
Query OK, 10 rows affected (0.06 sec)</pre>

用记事本查看 MySQL 备份文件夹下的 file.txt 文件，内容如下图所示。

2. MySQL数据库恢复

数据库恢复是指以备份为基础，与备份相对应的系统维护和管理操作。

系统进行恢复操作时，先执行一些系统安全性的检查，包括检查所要恢复的数据库是否存在、数据库是否变化及数据库文件是否兼容等，然后根据所采用的数据库备份类型采取相应的恢复措施。

数据库恢复机制设计的两个关键问题是：第一，如何建立冗余数据；第二，如何利用这些冗余数据实施数据库恢复。

建立冗余数据最常用的技术是数据转储和登录日志文件。通常在一个数据库系统中，这两种方法是一起使用的。

数据转储是 DBA 定期地将整个数据库复制到磁带或另一个磁盘上保存起来的过程。这些备用的版本成为后备副本或后援副本。

可使用 LOAD DATA…INFILE 语句来恢复先前备份的数据。

【实例】将之前导出的数据备份文件 file.txt 导入数据库 test_db 的表 tb_students_copy 中，其中 tb_students_copy 的表结构和 tb_students_info 相同。

首先创建表 tb_students_copy，输入的 SQL 语句和执行结果如下所示：

mysql> CREATE TABLE tb_students_copy
    -> LIKE tb_students_info;
Query OK, 0 rows affected (0.52 sec)
mysql> SELECT * FROM tb_students_copy;
Empty set (0.00 sec)

导入数据与查询表 tb_students_copy 的过程如下所示：

mysql> LOAD DATA INFILE 'C:/ProgramData/[MySQL](http://c.biancheng.net/mysql/)/MySQL Server 5.7/
Uploads/file.txt'
    -> INTO TABLE test_db.tb_students_copy
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '?';
Query OK, 10 rows affected (0.14 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
mysql> SELECT * FROM test_db.tb_students_copy;
+----+--------+---------+------+------+--------+------------+
| id | name   | dept_id | age  | sex  | height | login_date |
+----+--------+---------+------+------+--------+------------+
|  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 |
|  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 |
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
|  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
| 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.00 sec)</pre>

十二、Mysql分库分表

1. 分库分表原则

关系型数据库本身比较容易成为系统性能瓶颈，单机存储容量、连接数、处理能力等都很有限，数据库本身的“有状态性”导致了它并不像Web和应用服务器那么容易扩展。在互联网行业海量数据和高并发访问的考验下，聪明的技术人员提出了分库分表技术（有些地方也称为Sharding、分片）。同时，流行的分布式系统中间件（例如MongoDB、ElasticSearch等）均自身友好支持Sharding，其原理和思想都是大同小异的。

目前针对海量数据的优化，其分库分表是MySQL永远的话题，一般情况下认为MySQL是个简单的数据库，在数据量大到一定程度之后处理查询的效率降低，如果需要继续保持高性能运转的话，必须分库或者分表了。关于数据量达到多少大是个极限这个事儿，本文先不讨论，研究源码的同学已经证实MySQL或者Innodb内部的锁粒度太大的问题大大限制了MySQL提供QPS的能力或者处理大规模数据的能力。在这点上，一般的使用者只好坐等官方不断推出的优化版本了。
在一般运维的角度来看，我们什么情况下需要考虑分库分表？
首先说明，这里所说的分库分表是指把数据库数据的物理拆分到多个实例或者多台机器上去，而不是类似分区表的原地切分。

1.1 能不分就不分
MySQL 是关系数据库，数据库表之间的关系从一定的角度上映射了业务逻辑。任何分库分表的行为都会在某种程度上提升业务逻辑的复杂度，数据库除了承载数据的存储和访问外，协助业务更好的实现需求和逻辑也是其重要工作之一。分库分表会带来数据的合并，查询或者更新条件的分离，事务的分离等等多种后果，业务实现的复杂程度往往会翻倍或者指数级上升。所以，在分库分表之前，不要为分而分，去做其他力所能及的事情吧，例如升级硬件，升级，升级网络，升级数据库版本，读写分离，负载均衡等等。所有分库分表的前提是，这些你已经尽力了。

1.2 数据量太大，正常的运维影响正常业务访问
这里说的运维，例如：
（1）对数据库的备份。如果单表或者单个实例太大，在做备份的时候需要大量的磁盘IO或者网络IO资源。例如1T的数据，网络传输占用50MB的时候，需要20000秒才能传输完毕，在此整个过程中的维护风险都是高于平时的。我们在Qunar的做法是给所有的数据库机器添加第二块网卡，用来做备份，或者SST，Group Communication等等各种内部的数据传输。1T的数据的备份，也会占用大量的磁盘IO，如果是SSD还好，当然这里忽略某些厂商的产品在集中IO的时候会出一些BUG的问题。如果是普通的物理磁盘，则在不限流的情况下去执行xtrabackup，该实例基本不可用。
（2）对数据表的修改。如果某个表过大，对此表做DDL的时候，MySQL会锁住全表，这个时间可能很长，在这段时间业务不能访问此表，影响甚大。解决的办法有类似腾讯游戏DBA自己改造的可以在线秒改表，不过他们目前也只是能添加字段而已，对别的DDL还是无效；或者使用pt-online-schema-change，当然在使用过程中，它需要建立触发器和影子表，同时也需要很长很长的时间，在此操作过程中的所有时间，都可以看做是风险时间。把数据表切分，总量减小，有助于改善这种风险。
（3）整个表热点，数据访问和更新频繁，经常有锁等待，你又没有能力去修改源码，降低锁的粒度，那么只会把其中的数据物理拆开，用空间换时间，变相降低访问压力。

1.3 某些数据表出现了无穷增长
例子很好举，各种的评论，消息，日志记录。这个增长不是跟人口成比例的，而是不可控的，例如微博的feed的广播，我发一条消息，会扩散给很多很多人。虽然主体可能只存一份，但不排除一些索引或者路由有这种存储需求。这个时候，增加存储，提升机器配置已经苍白无力了，水平切分是最佳实践。拆分的标准很多，按用户的，按时间的，按用途的，不在一一举例。

1.4 安全性和可用性的考虑
这个很容易理解，鸡蛋不要放在一个篮子里，我不希望我的数据库出问题，但我希望在出问题的时候不要影响到100%的用户，这个影响的比例越少越好，那么，水平切分可以解决这个问题，把用户，库存，订单等等本来同统一的资源切分掉，每个小的数据库实例承担一小部分业务，这样整体的可用性就会提升。这对Qunar这样的业务还是比较合适的，人与人之间，某些库存与库存之间，关联不太大，可以做一些这样的切分。

1.5 业务耦合性考虑
这个跟上面有点类似，主要是站在业务的层面上，我们的火车票业务和烤羊腿业务是完全无关的业务，虽然每个业务的数据量可能不太大，放在一个MySQL实例中完全没问题，但是很可能烤羊腿业务的DBA 或者开发人员水平很差，动不动给你出一些幺蛾子，直接把数据库搞挂。这个时候，火车票业务的人员虽然技术很优秀，工作也很努力，照样被老板打屁股。解决的办法很简单:惹不起，躲得起。

2. 分库分表方案

2.1 垂直拆分（垂直分表）
垂直分表在日常开发和设计中比较常见，通俗的说法叫做“大表拆小表”，拆分是基于关系型数据库中的“列”（字段）进行的。通常情况，某个表中的字段比较多，可以新建立一张“扩展表”，将不经常使用或者长度较大的字段拆分出去放到“扩展表”中，如下图所示：

2.2 垂直拆分（垂直分库）
垂直分库在“微服务”盛行的今天已经非常普及了。基本的思路就是按照业务模块来划分出不同的数据库，而不是像早期一样将所有的数据表都放到同一个数据库中。如下图：

小结：
系统层面的“服务化”拆分操作，能够解决业务系统层面的耦合和性能瓶颈，有利于系统的扩展维护。而数据库层面的拆分，道理也是相通的。与服务的“治理”和“降级”机制类似，我们也能对不同业务类型的数据进行“分级”管理、维护、监控、扩展等。
众所周知，数据库往往最容易成为应用系统的瓶颈，而数据库本身属于“有状态”的，相对于Web和应用服务器来讲，是比较难实现“横向扩展”的。数据库的连接资源比较宝贵且单机处理能力也有限，在高并发场景下，垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈，是大型分布式系统中优化数据库架构的重要手段。
然后，很多人并没有从根本上搞清楚为什么要拆分，也没有掌握拆分的原则和技巧，只是一味的模仿大厂的做法。导致拆分后遇到很多问题（例如：跨库join，分布式事务等）。
优势：降低高并发情况下，对于表的锁定。
不足：对于单表来说，随着数据库的记录增多，读写压力将进一步增大。

2.3 水平拆分（水平分表）
水平分表也称为横向分表，比较容易理解，就是将表中不同的数据行按照一定规律分布到不同的数据库表中（这些表保存在同一个数据库中），这样来降低单表数据量，优化查询性能。最常见的方式就是通过主键或者时间等字段进行Hash和取模后拆分。如下图所示：

如果单表的IO压力大，可以考虑用水平分割，其原理就是通过hash算法，将一张表分为N多页，并通过一个新的表（总表），记录着每个页的的位置。假如一个门户网站，它的数据库表已经达到了1000万条记录，那么此时如果通过select去查询，必定会效率低下（不做索引的前提下）。为了降低单表的读写IO压力，通过水平分割，将这个表分成10个页，同时生成一个总表，记录各个页的信息，那么假如我查询一条id=100的记录，它不再需要全表扫描，而是通过总表找到该记录在哪个对应的页上，然后再去相应的页做检索，这样就降低了IO压力。

当下分表有静态分表和动态分表两种：

静态分表：事先估算出表能达到的量，然后根据每一个表需要存多少数据直接算出需要创建表的数量。如：1亿数据每一个表100W条数据那就要建100张表，然后通过一定的hash算法计算每一条数据存放在那张表。其实就有点像是使用partition table一样。静态分表有一个毙命就是当分的那么多表还不满足时，需要再扩展难度和成本就会很高。
动态分表：同样也是对大数据量的表进行拆分，他可以避免静态分表带来的后遗症。当然也需要在设计上多一些东西（这往往是我们能接受的）。
某种意义上来讲，有些系统中使用的“冷热数据分离”（将一些使用较少的历史数据迁移到其他的数据库中。而在业务功能上，通常默认只提供热点数据的查询），也是类似的实践。在高并发和海量数据的场景下，分库分表能够有效缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源的瓶颈。当然，投入的硬件成本也会更高。同时，这也会带来一些复杂的技术问题和挑战（例如：跨分片的复杂查询，跨分片事务等）

3. 分库分表难点

3.1 跨库join的问题
在拆分之前，系统中很多列表和详情页所需的数据是可以通过sql join来完成的。而拆分后，数据库可能是分布式在不同实例和不同的主机上，join将变得非常麻烦。而且基于架构规范，性能，安全性等方面考虑，一般是禁止跨库join的。那该怎么办呢？首先要考虑下垂直分库的设计问题，如果可以调整，那就优先调整。如果无法调整的情况，下面笔者将结合以往的实际经验，总结几种常见的解决思路，并分析其适用场景。
跨库Join的几种解决思路：

全局表
- 所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库join查询，我们可以将这类表在其他每个数据库中均保存一份。同时，这类数据通常也很少发生修改（甚至几乎不会），所以也不用太担心“一致性”问题。
字段冗余
- 这是一种典型的反范式设计，在互联网行业中比较常见，通常是为了性能来避免join查询。
- 举个电商业务中很简单的场景：
- “订单表”中保存“卖家Id”的同时，将卖家的“Name”字段也冗余，这样查询订单详情的时候就不需要再去查询“卖家用户表”。
- 字段冗余能带来便利，是一种“空间换时间”的体现。但其适用场景也比较有限，比较适合依赖字段较少的情况。最复杂的还是数据一致性问题，这点很难保证，可以借助数据库中的触发器或者在业务代码层面去保证。当然，也需要结合实际业务场景来看一致性的要求。就像上面例子，如果卖家修改了Name之后，是否需要在订单信息中同步更新呢？
数据同步
- 定时A库中的tab_a表和B库中tbl_b有关联，可以定时将指定的表做同步。当然，同步本来会对数据库带来一定的影响，需要性能影响和数据时效性中取得一个平衡。这样来避免复杂的跨库查询。笔者曾经在项目中是通过ETL工具来实施的。
系统层组装
- 在系统层面，通过调用不同模块的组件或者服务，获取到数据并进行字段拼装。说起来很容易，但实践起来可真没有这么简单，尤其是数据库设计上存在问题但又无法轻易调整的时候。具体情况通常会比较复杂。

3.2 跨库事务（分布式事务）的问题
按业务拆分数据库之后，不可避免的就是“分布式事务”的问题。想要了解分布式事务，就需要了解“XA接口”和“两阶段提交”。值得提到的是，MySQL5.5x和5.6x中的xa支持是存在问题的，会导致主从数据不一致。直到5.7x版本中才得到修复。Java应用程序可以采用Atomikos框架来实现XA事务（J2EE中JTA）。感兴趣的读者可以自行参考《分布式事务一致性解决方案》

根据系统架构和公司实际情况来，如果你们的系统还是个简单的单体应用，并且没有什么访问量和数据量，那就别着急折腾“垂直分库”了，否则没有任何收益，也很难有好结果。
切记，“过度设计”和“过早优化”是很多架构师和技术人员常犯的毛病。

十三、Mysql权限管理

1. MySQL权限简介

关于mysql的权限简单的理解就是mysql允许你做你全力以内的事情，不可以越界。比如只允许你执行select操作，那么你就不能执行update操作。只允许你从某台机器上连接mysql，那么你就不能从除那台机器以外的其他机器连接mysql。
那么Mysql的权限是如何实现的呢？这就要说到mysql的两阶段验证，下面详细介绍：第一阶段：服务器首先会检查你是否允许连接。因为创建用户的时候会加上主机限制，可以限制成本地、某个IP、某个IP段、以及任何地方等，只允许你从配置的指定地方登陆。第二阶段：如果你能连接，Mysql会检查你发出的每个请求，看你是否有足够的权限实施它。比如你要更新某个表、或者查询某个表，Mysql会查看你对哪个表或者某个列是否有权限。再比如，你要运行某个存储过程，Mysql会检查你对存储过程是否有执行权限等。

2. Mysql权限种类

3. MySQL权限经验原则

权限控制主要是出于安全因素，因此需要遵循一下几个经验原则：
（1）只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
（2）创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
（3）初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户，这些用户默认没有密码。
（4）为每个用户设置满足密码复杂度的密码。
（5）定期清理不需要的用户。回收权限或者删除用户。