MySQL 笔记

MySQL 原本是一个开放源代码的关系数据库管理系统,原开发者为瑞典的 MySQL AB 公司,该公司于 2008 年被昇阳微系统(Sun Microsystems)收购。2009 年,甲骨文公司(Oracle)收购昇阳微系统公司,MySQL 成为 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

简介

MySQL(官方发音为/maɪ ˌɛskjuːˈɛl/“My S-Q-L”[1],但也经常读作/maɪ ˈsiːkwəl/“My Sequel”)原本是一个开放源代码的关系数据库管理系统,原开发者为瑞典的MySQL AB公司,该公司于2008年被昇阳微系统(Sun Microsystems)收购。2009年,甲骨文公司(Oracle)收购昇阳微系统公司,MySQL成为Oracle旗下产品。

MySQL在过去由于性能高、成本低、可靠性好,已经成为最流行的开源数据库,因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟,它也逐渐用于更多大规模网站和应用,比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

但被甲骨文公司收购后,Oracle大幅调涨MySQL商业版的售价,且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展,因此导致自由软件社区们对于Oracle是否还会持续支持MySQL社区版(MySQL之中唯一的免费版本)有所隐忧,MySQL的创始人麦克尔·维德纽斯以MySQL为基础,成立分支计划MariaDB。而原先一些使用MySQL的开源软件逐渐转向MariaDB或其它的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库[2]。

大部分 Linux 发行版已经使用 MariaDB 作为 MySQL 的官方实现,包括 ArchLinux。MariaDB 基本与 MySQL 兼容,用户基本可以无障碍的迁移到 MariaDB,本篇笔记安装的也是 MariaDB。

MySQL 是 C/S 结构的,服务端是 mysqld 守护进程,客户端是 mysql 命令行交互环境。使用 mysql 连接数据库之前,应该先在目标主机上运行 mysqld 服务。mysqld 默认监听 3306/tcp 端口,同时 mysqld 还会创建 /run/mysqld/mysqld.sock Unix 套接字文件,本机连接建议使用 UNIX 套接字(localhost 而非 127.0.0.1),因为没有了多余的 TCP/IP 协议开销,性能更好。

思考:为什么需要数据库?
使用文件不也可以存储数据吗,为什么还要弄出数据库这种东西呢?因为文件系统提供的 API 太低级,open/read/write/close,除此之外,文件系统不会帮你做任何事情,如果你需要查找文件中个某个信息,你必须自己编程,或者使用 grep 等 unix 工具,如果要修改,需通过 sed 等工具,总之,仅仅通过文件系统的 API 来进行数据的增删改查太麻烦,而且效率很低,同时会让软件开发人员的负担变重,因为不仅要处理数据的逻辑,还要关心数据的具体存储问题,想想头都大。

而数据库则提供了相对比较高级的 API 让我们专门关心于数据的逻辑处理,具体底层怎么操作、怎么存储的,我们无需关心。比如查询数据 SELECT、插入数据 INSERT,为了与数据库进行交互,大部分数据库都采用了 SQL(结构化查询语言)API,虽然不同的数据库系统的 SQL 语句不太相同,但是基本语法还是通用的,如果程序要与数据库交互,那就必须使用 SQL 语言。

SQL 结构化查询语言
SQL(聆听i/ˈɛs kjuː ˈɛl/,[4] or 聆听i/ˈsiːkwəl/;[5]结构化查询语言[6][7][8][9])是一种特定目的程序语言,用于管理关系数据库管理系统(RDBMS),或在关系流数据管理系统(RDSMS)中进行流处理。

SQL基于关系代数和元组关系演算,包括一个数据定义语言和数据操纵语言。SQL的范围包括数据插入、查询、更新和删除,数据库模式创建和修改,以及数据访问控制。尽管SQL经常被描述为,而且很大程度上是一种声明式编程(4GL),但是其也含有过程式编程的元素。

SQL是对埃德加·科德的关系模型的第一个商业化语言实现,这一模型在其1970年的一篇具有影响力的论文《一个对于大型共享型数据库的关系模型》[10]中被描述。尽管SQL并非完全按照科德的关系模型设计,但其依然成为最为广泛运用的数据库语言。 [11][12]

SQL在1986年成为美国国家标准学会(ANSI)的一项标准,在1987年成为国际标准化组织(ISO)标准。[13]在此之后,这一标准经过了一系列的增订,加入了大量新特性。虽然有这一标准的存在,但大部分的SQL代码在不同的数据库系统中并不具有完全的跨平台性。

SQL是高级的非过程化编程语言,它允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解其具体的数据存放方式。而它的界面,能使具有底层结构完全不同的数据库系统和不同数据库之间,使用相同的SQL作为数据的输入与管理。它以记录项目〔records〕的合集(set)〔项集,record set〕作为操纵对象,所有SQL语句接受项集作为输入,回提交的项集作为输出,这种项集特性允许一条SQL语句的输出作为另一条SQL语句的输入,所以SQL语句可以嵌套,这使它拥有极大的灵活性和强大的功能。在多数情况下,在其他编程语言中需要用一大段程序才可实践的一个单独事件,而其在SQL上只需要一个语句就可以被表达出来。这也意味着用SQL可以写出非常复杂的语句,在不特别考虑性能下。

SQL 同时也是数据库文件格式的扩展名。习惯上,SQL 语句的关键字为大写形式,当然,大部分数据库管理系统都对关键字的大小写不敏感,我个人的话比较喜欢使用小写形式,因为绝大多数编程语言都是使用小写字母作为关键字,当然,正式场合,比如面试时,建议使用大写,不然可能留下你对 SQL 不熟悉的不好印象哦(千万别高估了 HR 的智商,它们有时候挺智障的)。

对于 Java 来说,用的最多的关系型数据库管理系统是 Oracle、MySQL。Oracle 是大型数据库系统,安装就够你折腾一天了,新手的话,建议先学习 MySQL,然后学习 Oracle,由易到难,其实 SQL 语句都差不多,只不过有细微差异而已。我也是打算先学习 MySQL。

SQL 结构化查询语言和 C 家族编程语言一样,使用分号表示一个语句的结束。

关系型数据库管理系统 RDBMS
代表:Oracle、DB2、MSSQL、MySQL

非关系型数据库 NoSQL
代表:redis、mongodb、memcached

目前主流还是 RDBMS,NoSQL 主要是用来做临时数据库用的,比如高速缓存。

安装

添加用户
默认只有 root 用户(mysql 用户与 linux 用户没有关系),root 用户即管理员用户,它拥有至高无上的权利,因此实际操作中非常不建议使用 root 用户,应该使用普通用户,这是添加方法:

配置文件
mysql 配置文件分为系统范围、用户范围,使用 mysqld --help --verbose | more 可查看:

远程连接
通常不建议这么做,如果需要远程连接,请使用 SSH、VNC、VPN,修改 /etc/mysql/my.cnf

如果需要禁止远程连接,请注释 bind-address,然后取消注释 skip-networking。然后重启 mysqld.service,禁用 networking 后,你仍然可以从 localhost 连接(使用 unix 套接字)。

自动补全
默认情况下,mysql 交互式客户端不会进行自动补全(Tab),如果需要像 shell 那样利用 Tab 键自动补全,修改 /etc/mysql/my.cnf,将 no-auto-rehash 替换为 auto-rehash,重启客户端。

字符编码
强烈建议使用 utf8mb4,而不是 utf8,因为 mysql 的 utf8 只支持 BMP 基本平面的 Unicode 字符,也就是 65536 个字符,它实际上是阉割版的 utf8(这应该是历史遗留问题,可能 mysql 推出 utf8 编码时只有基本平面字符),修改 /etc/mysql/my.cnf,添加以下配置,然后重启 mysqld:

增加字符限制
不是很清楚,个人感觉应该是增加索引的 767/3072 字节长度限制的,配置:

建表时指定 ROW_FORMAT=DYNAMIC 表选项即可:

bin-log 配置
默认情况下,mysqld 会在 /var/lib/mysql 中创建二进制日志文件。这对复制主服务器或数据恢复很有用。但是这些二进制日志会占用你的磁盘空间。如果您不打算使用复制或数据恢复功能,可以通过在 /etc/mysql/my.cnf 中注释掉这些行来禁用二进制日志记录:

timezone 配置

mysqld 升级
升级数据库后建议运行 mysql_upgrade 命令来让旧的数据库适应新的 mysqld 版本:

mysqlcheck 工具

重置 root 密码

入门

基本概念
DBMS(数据库管理系统)由多个 database(数据库)组成,每个 database 一般都代表一个独立的项目,就像一个域名一样。而每个 database 又由多个 table(数据表)组成,table 是一个二维表(行和列,由表头和数据组成),table 的每一行(row)都是一个记录(record),每一列(column)都是一个字段(field)。行/列是从结构角度出发的,记录/字段是从数据角度出发的。数据表(二维表)一般的形式为(第一行为表头,每列为不同的字段,每行都是一份数据):

ID Name Host
1 zfl9 127.0.0.1
2 root localhost
3 other 192.168.1.1

底层存储
MySQL 中的每个 database 都对应一个同名文件夹,database 中的每个 table 都对应两个同名文件(后缀不同)。所以,库名和表名的大小写是否敏感依赖于底层操作系统(准确的说是文件系统),所以在 unix 中,库名和表名都是严格区分大小写的,但在 windows 中,库名和表名是不区分大小写的。注意,字段名无论是什么操作系统,都是不区分大小写的,变量名在 unix 中是区分大小写的,但在 windows 中没有什么是区分大小写的,全部都是大小写不敏感的(真是一个神奇的操作系统)。

总结,mysql 其实就是字段名 column name 不区分大小写,其它的都区分,但是 windows 这个奇葩的系统除外,它什么都不区分大小写。

命名规范
库名、表名、列名 一律使用小写字符,单词之间使用下划线分隔,以消除大小写问题带来的疑惑。

用户管理
mysql 中默认只有 root 用户,root 用户没有任何权限限制,因为它就是管理员,显然,生产环境中是不会使用 root 用户操作的,唯一需要使用 root 用户的可能就是 DBA(数据库管理员)了。一般,DBA 会创建一个普通用户,然后限制它只能使用某个数据库,只能进行什么操作(权限,在 MySQL 中也称为特权),开发人员只需要使用 DBA 给我们的帐号和密码就行,其它不用操心。

mysql 的用户信息存放在 mysql.user 表中,创建用户可以直接往此表中增加一个记录,也可以使用 mysql 提供的创建用户指令,我更推荐使用后者,因为 mysql.user 表的字段名可能会更改。

连接数据库
mysql -h<host> -P<port> -u<user> -p[pass] [database]

  • -h<host>:服务器地址,如 -h192.168.1.1 默认 localhost
  • -P<port>:服务器端口,如 -P6666 默认 3306
  • -u<user>:mysql 账户,如 -uroot 默认 root
  • -p[pass]:mysql 密码,如 -p123456-p(交互验证)
  • [database]:进入的数据库,如 mysql -p mysql 进入 mysql 数据库

退出交互式命令行请使用 exitquit 命令。

mysql 中的 127.0.0.1 与 localhost 是不一样的,127.0.0.1 表示使用 TCP/IP 协议连接,而 localhost 表示使用 Unix Socket 连接,很明显 localhost 更好,使用 status 命令可查看。

如果想直接在命令行中运行 mysql 语句,可以使用 -e 选项,如 mysql -uroot -p -e'show databases;',与 perl 一样,最后一个 sql 语句中的分号可以省略。如果想要执行一个 sql 文件,那么可以使用 stdin 重定向的方式进行(仅适用于 unix 系统):mysql -uroot -p </path/to/data.sql,或者管道 command | mysql -uroot -p

数据库基本操作
基本操作可以用四个字概括:增删改查,英文 Create, Retrieve, Update and Delete (CRUD)。
根据操作对象的不同,我们可以将 SQL 基本操作粗略的分为三类:库操作、表操作、数据操作。

大小写问题
mysql 中,database、table 名对大小写敏感(windows 除外),field 名对大小写不敏感。
虽然 field 字段名对大小写不敏感,但是习惯上我们会以首字母大写的形式描述 field 字段名。

SQL 引号问题
SQL 标准使用单引号包含字符串,如果字符串中包含单引号,那么需要使用两个连续单引号来表示单引号本身(和 printf 的 %% 表示 % 类似),双引号是 MySQL 语法,Oracle 只能用单引号:

单引号、双引号、反引号

  • 单引号:标准 SQL 中字符串使用单引号,表示单引号本身需使用两个单引号;
  • 双引号:MySQL 对标准 SQL 的扩展,允许使用单引号、双引号来表示字符串;
  • 反引号:主要用在库名、表名、字段名,区分关键字,建议始终使用反引号表示。

mysql 注释语法
mysql 中支持 3 种注释语法:

字符集、字符序
character set:字符集,这个比较好理解,如 unicode 字符集,utf-8 字符编码。
collation:字符序,应该是 mysql 专有概念,主要作用是规定字符之间的比较顺序。

始终建议使用 utf8mb4 字符编码,mb4 即 multi byte 4,也就是标准的 utf-8 字符编码,mysql 中的 utf8 其实是阉割版,那时候 Unicode 还做着 65536 个字符统治全世界的美梦,这 6 万多个字符其实就是现在的 BMP 基本多文种平面,目前 Unicode 有 17 个平面,每个平面有 65536 个字符(码点),只有 utf8mb4 支持其它平面的字符,比如手机上的表情就是补充平面的字符。

而字符序的话,通常选择默认的即可,或者选择 unicode 规则的,分别是:
utf8_bin:二进制(区分大小写)
utf8_general_ci:默认字符序
utf8_unicode_ci:unicode 规则
utf8mb4_bin:二进制(区分大小写)
utf8mb4_general_ci:默认字符序
utf8mb4_unicode_ci:unicode 规则
一般 general 就够了,速度也是最快的,unicode 符合 unicode 语义,但速度略慢。
如果只指定 character set,那么 collation 会使用对应字符集的默认 collation 字符序。
大部分 charset 的默认 collate 都是 general_ci,其实我们不需要特别指定,默认的就好。

字符集深入
mysql 提供了不同级别的字符集配置,按照范围的从大到小顺序分别是:

  • server 级:服务器默认字符集
  • database 级:数据库默认字符集
  • table 级:数据表默认字符集
  • column 级:数据字段字符集

如果没有指定,那么会从最近的上级范围中继承字符集配置(包括字符序,下同)。注意,server 级、database 级、table 级的字符集配置都是拿来缺省用的,实际上 mysql 中的字符集只存在于 column 字段(也就是实际的数据)。server 级的字符集是创建的 database 的默认字符集,database 的字符集是创建的 table 的默认字符集,table 的字符集是其中未指定字符集的字段的默认字符集,都是一级一级传递下来的。最终有效的其实就是 column 字符集(charvarchartext系列)。

查看支持的字符集
show character set;
show character set like 'utf8%';

查看支持的字符序
show collation;
show collation like 'utf8\_%';
show collation like 'utf8mb4%';
show collation where Charset = 'utf8';
show collation where Charset = 'utf8mb4';
字符序名都是以对应字符集名作为前缀的,如 utf8 字符集对应 utf8_general_ci 字符序。

server 级别
查看 server 字符集、字符序:

mysqld 运行时修改:

mysqld 启动时指定:

配置文件中指定(建议):

然后重启 mysqld.service 生效,使用 show variables like 'character%' 验证。

个人理解,其实配置文件就是更方便的命令行参数了,每次启动时从配置文件中读取参数。

database 级别
创建、修改数据库时指定:

查看 database 字符集/字符序:

table 级别

查看 table 字符集/字符序:

column 级别

字符集再深入
查看 mysql 字符集状态信息有两种常用方法:
status;
show variables like 'charactet_set%';
status 查看的字符集信息比较简略,推荐使用后者查看:

其中可以看到这些 character_set 信息:

  • character_set_server:全局默认字符集,也即创建 database 时的默认字符集
  • character_set_database:当前选择的数据库的字符集,如果没有则与 server 值相同
  • character_set_connection:当前 mysql 连接器的字符集
  • character_set_client:当前 mysql 客户端的字符集
  • character_set_results:当前 mysql 结果集的字符集
  • connection、client、results 的字符集强烈建议保持一致,推荐使用 utf8、utf8mb4

一般来说,只要 connection、client、results 三个一致,那么就不会出现乱码问题。设置方法:

注意,mysql 中也可以使用 charset 替代 character set,它们是等价的。
如:create database `test` charset utf8mb4 collate utf8mb4_general_ci;

变量的概念
mysql 中有三种变量类型,它们分别是:

  • 系统变量:以 @@ 开头,分为 global、session 作用域
  • 用户变量:以 @ 开头,作用域为 session 会话级别
  • 局部变量:位于 BEGIN…END 语句块中,离开作用域被回收

global 全局作用域、session 会话作用域:

  • global:变量存放在 mysqld 守护进程中
  • session:变量存放在 mysql 客户端进程中

global 变量在多个 mysql 连接中有效,而 session 只在当前连接中有效。

mysql 在连接数据库时,会将 global 系统变量 dump 一份到同名 session 系统变量中,而 session 系统变量的优先级比 global 系统变量的优先级更高,所以修改系统变量时,如果想让它在当前会话中生效,则只设置 session 作用域的系统变量,如果想让它在全局生效,那么请同时设置 global 作用域和 session 作用域的系统变量,否则你需要重新连接数据库才能生效哦。

系统变量

用户变量

mysqld 启动时,会从 my.cnf 配置文件中初始化 global 变量,存放在内存之中。
因此,我们可以在运行时修改 global 变量,它的作用和配置文件中修改是一样的。
下次启动时 mysqld 仍从 my.cnf 中初始化变量,若想跨启动修改,请放在 my.cnf 中。

SQL 分为 4 个类别

  • 数据定义语言 DDLcreatealterdrop
  • 数据操纵语言 DMLselectinsertupdatedelete
  • 数据控制语言 DCLgrantdenyrevoke
  • 事务控制语言 TCLcommitrollbacksavepoint

DML 需要进行 commit 提交,否则所进行的操作都是没有生效的。与 commit 相对的是 rollback 回退,也就是撤销当前未 commit 的操作。当然你也可以在某个位置打个标记,即使用 savepoint <point_name>,然后你可以使用 rollback to <point_name> 来撤销到指定 point。如果未指定 point,则撤销自上次 commit 后的所有操作。使用 release savepoint <point_name> 删除回退点。在数据库领域,每次 commit 的语句序列被称为一个独立的事务(Transaction),事务是并发控制的基本单位,是用户定义的一个操作序列。这些操作要么都做,要么都不做,是一个不可分割的工作单位。注意,每个事务都以上一个事务的 commit 开始,以当前事务的 commit 结束,因此每个 DML 语句都必然属于某个事务。每个事务都有自己独立的 savepoint 回退点,他们互不干扰,即使重名也不会有任何问题。使用 commit 提交后,该事务中的 savepoint 都会被丢弃,也就是说,commit 后你不能进行 rollback 撤销操作了。rollback 能操作的范围也仅限当前事务,因为他们还未 commit(你可以理解为当前未提交的操作都是在内存中进行的,而进行 commit 后才会写入到磁盘中。但实际上并不是这样的,数据库内部的机制远比想象的复杂,因为我也是 SQL 初学者,所以暂且先这么认为),rollback 给了你反悔的能力,它允许你撤销当前未提交的操作(或者回到某个 point,而不是全部都撤销)。还有一点要注意,如果是 rollback 撤销当前事务的所有未提交操作,那么相当于删除当前事务,也就是说这一步之后不需要 commit;但如果是 rollback to point,那么记得使用 commit 提交,不然该 point 前的操作都作废了。

MySQL 中默认启用 autocommit 自动提交功能,也就是说每个 DML 语句后都隐式的进行了 commit,也就是说默认情况下,每条 DML 语句都是一个独立的事务。如果需要关闭该行为,请使用 set autocommit = 0 来关闭它,使用 set global autocommit = 0 则表示全局关闭(直到下次重启 mysqld 进程)。

如果你不想关闭 autocommit 功能,也可以使用 start transaction 语句显式开始一个事务,该语句还有一个别名 begin,但是不建议使用 begin,因为这不是 SQL 标准语法。不论是不是启用了 autocommit,你都可以使用 start transaction 来显式开始一个事务,并且建议始终这么做。

为什么需要事务?
事务(Transaction)是并发控制的基本单位。所谓事务,它是一个操作序列,这些操作要么都执行,要么都不执行,它是一个不可分割的工作单位。例如,银行转帐工作:从一个帐号扣款并使另一个帐号增款,这两个操作要么都执行,要么都不执行。否则,可能出现一个账号扣了款,而另一个帐号没有收到钱的现象。所以,应该把他们看成一个事务。事务是数据库维护数据一致性的单位,在每个事务结束时,都能保持数据一致性。

SQL 事务有 4 个属性:原子性、一致性、隔离性、持久性。简称为 ACID(首字母缩写)。

  • 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。
  • 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
  • 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
  • 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

注意,DML 中的 select 语句没有所谓的 commit 之分,只有 update、insert、delete 才适用。在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。Innodb 也是 MySQL、MariaDB 中的默认数据库引擎(除非你显式指定了其它数据库引擎)。

事务控制语句

  • BEGINSTART TRANSACTION:二者是等价的。显式地开启一个事务;
  • COMMITCOMMIT WORK:二者是等价的。COMMIT 会提交事务,并使已对数据库进行的所有修改成为永久性的;
  • ROLLBACKROLLBACK WORK:二者是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
  • SAVEPOINT identifier:SAVEPOINT 允许在事务中创建一个保存点,一个事务中可以有多个 SAVEPOINT;每个事务之间的 SAVEPOINT 互不影响,因为同时只会保存一个事务中的 SAVEPOINT。
  • RELEASE SAVEPOINT identifier:删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
  • ROLLBACK TO identifier:把事务回滚到标记点;
  • SET TRANSACTION:用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务的概念
事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。

存储引擎的概念
在 mysql 中的数据用各种不同的技术存储在文件(或内存)中。这些技术中的每一种技术都使用不同的存储机制,索引技巧,并且最终提供广泛的不同的功能和能力。可以通过选择不同的技术,可以获得额外的速度或功能,从而改善应用的整体功能。这些不同的技术以及配套的相关功能在 mysql 中被称为存储引擎(也称为表类型)。通过 show engines 来查看 mysql 支持的存储引擎。在 mysql 中用的最多的存储引擎有:innodb,bdb,myisam,memory 等。其中 innodb 和 bdb 支持事务而 myisam 等不支持事务。mysql 的默认存储引擎为 innodb。

事务的四个属性
1、原子性:事务是由一个或一组相互关联的SQL语句组成,这些语句被认为是一个不可分割的单元,要么都成功要么全回滚。
2、一致性:对于数据库的修改是一致的,即多个用户查的的数据是一样的。一致性主要由mysql的日志机制处理,他记录数据的变化,为事务恢复提供跟踪记录。
3、隔离性:每个事务都有自己的空间,和其他发生在系统中的事务隔离开来,而且事务的结果只在他完全被执行时才能看到
4、持久性:一但提交了这个事务之后对数据的修改更新就是永久的。当一个事务完成,数据库的日志已经被更新时,持久性即可发挥其特有的功效,在mysql中,如果系统崩溃或者数据存储介质被破坏,通过日志,系统能够恢复在重启前进行的最后一次成功更新,可以反应系统崩溃时处于执行过程的事物的变化。

事务的隔离性
现在重点来说明下事务的隔离性,当多个线程都开启事务操作数据库中的数据时,数据库系统要能进行隔离操作,以保证各个线程获取数据的准确性,下面看看四种 隔离级别(级别由低到高):

  1. Read uncommitted 未提交读/脏读:该隔离级别的事务,在数据修改过程中,即使没有提交,其他事务对于这些数据也是可读的。事务可读到未提交的数据也叫脏读(Dirty Read),由于脏读在实际应用中会导致很多问题,一般这类隔离级别应用很少。
  2. Read committed 已提交读/不可重复读:不可重复读是指在对于数据库中的某个数据,一个事务范围内多次查询却返回了不同的数据值,这是由于在该事务的两次查询间隔内被另一个事务修改并提交了。例如事务 T1 在读取某一数据,而事务 T2 立马修改了这个数据并且提交事务给数据库,事务 T1 再次读取该数据就得到了不同的结果,发生了不可重复读。不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。该隔离级别也是一般数据库的默认级别,如 Oracle。只有当前事务执行完,把数据提交之后,其他事务才可对这些数据进行读取。也叫不可重复读,因为其他事务执行 2 次查询可能前后会得到 2 个不同结果(事务执行前读一次,执行后读一次)。
  3. Repeatable read 可重复读/幻读:幻读是事务非独立执行时发生的一种现象。例如事务 T1 对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作,这时事务 T2 又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。而操作事务 T1 的用户如果再查看刚刚修改的数据,会发现还有一行没有修改,其实这行是从事务 T2 中添加的,就好像产生幻觉一样,这就是发生了幻读。幻读和不可重复读都是读取了另一条已经提交的事务(这点就脏读不同),所不同的是不可重复读查询的都是同一个数据项,而幻读针对的是一批数据整体(比如数据的个数)。该隔离级别是 Mysql 的默认隔离级别,它解决了脏读的问题,该级别保证了在同一事务中,多次读取的结果是一致的。但仍旧无法解决幻读问题。幻读:事务 A 在读取一定范围内数据时,事务 B 有对该范围数据进行插入等更新操作,事务 A 再次读取该范围记录时,会产生幻读。
  4. Serializable 可串行化:在读取每一行数据的时候都对该行数据加锁,强制事务串行执行,在事务提交的时候会释放锁,这时其他事务才能获取相关数据的锁进行处理这样避免了幻读问题,隔离性达到了最高但是这种执行方式效率低,锁竞争激烈容易出现超时问题!只有在对数据一致性要求很强的时候和没有并发或并发量很小的时候使用!

最常用的其实就是级别二和级别三,即 Read Committed 和 Repeatable Read。其中已提交读是大部分数据库的默认隔离级别,但是 MySQL 却选择了更高一级的可重复读。注意:级别越高,执行效率就越低,锁竞争就越激烈,还容易出现锁竞争超时问题。

这几个隔离级别究竟有什么区别呢?估计你看了上面的介绍也还是不太明白它们的具体区别。这是另一篇文章的介绍,我觉得介绍得更加明了一些,每个事务隔离级别其实都比上一级多解决了一个问题,总共有 3 个问题,而第一个级别没有解决任何问题,实际上它也非常少用:

  • RAED UNCOMMITED:使用查询语句不会加锁,可能会读到未提交的行(Dirty Read);
  • READ COMMITED:只对记录加记录锁,而不会在记录之间加间隙锁,所以允许新的记录插入到被锁定记录的附近,所以再多次使用查询语句时,可能得到不同的结果(Non-Repeatable Read);
  • REPEATABLE READ:多次读取同一范围的数据会返回第一次查询的快照,不会返回不同的数据行,但是可能发生幻读(Phantom Read);
  • SERIALIZABLE:InnoDB 隐式地将全部的查询语句加上共享锁,解决了幻读的问题;

其中 InnoDB 实现可重复读的基本原理就是快照(或者可以理解为缓存,后面的读取操作其实都没有读取实际的数据,而是读取第一次返回的快照/缓存而已),这个技术的名称为 MVCC(多版本并发控制),其实就是保存的快照(个人简单理解)。

事务隔离级别的查看与修改

隔离级别的总结
1、Serializable (串行化):可避免脏读、不可重复读、幻读的发生。
2、Repeatable read (可重复读):可避免脏读、不可重复读的发生。
3、Read committed (读已提交):可避免脏读的发生。
4、Read uncommitted (读未提交):都无法保证。

笔记相关说明
<option>:表示必选参数,其中 <> 只是用来说明的,不用实际输入;
[option]:表示可选参数,其中 [] 只是用来说明的,不用实际输入;
{opt1|opt2}:表示二者都可以,{|} 只是用来说明的,不用实际输入;
如未特殊说明,所有语句都以 ; 分号结尾,本篇主要以小写关键字为主。

库操作

创建数据库
create {database|schema} [if not exists] <db_name> [db_opts]

  • {database|schema}:在 mysql 中没有区别,习惯使用 database
  • [if not exists]:如果指定 database 不存在则创建,否则不执行
  • <db_name>:数据库名称,一般建议小写,如 mysql、wordpress
  • [db_opts]:数据库字符集、字符序(校对集),它们分别是:
    • [default] character set <charset_name>:default 可有可无
    • [default] collate <collation_name>:default 可有可无,默认为字符集的默认字符序

数据库名不能使用关键字、保留字,如果非要使用,必须使用反引号将数据库名括起来。数据库名不建议使用中文,应始终使用 ascii 字符,避免各种各样的问题,为了提高 sql 语句的执行性,建议始终添加反引号,与 mysql 关键字、保留字进行区分。

查询数据库
show databases;:查看所有数据库
show databases like 'pattern';:模糊匹配,pattern 中有两个通配符
show create database <db_name>;:查看创建数据库的语句(会进行优化)

  • %:表示匹配任意多个字符,同 shell 中的 *,百分号本身使用 \%
  • _:表示匹配任意单个字符,同 shell 中的 ?,下划线本身使用 \_

pattern 的匹配规则与 shell 中的 glob 一样,要求整个字符串匹配,即 ^pattern$

更新数据库
注意,数据库名称不能修改,只能导出再导入进行改名。数据库的修改仅限字符集、字符序。

删除数据库

表操作

创建数据表

任何数据表都属于某个数据库,创建表时必须指定所属的数据库,有两种方式:

查询数据表

关于 mysql 的显示格式,;\g\G,我们先来看个例子:

可以看出,分号和 \g 是一样的,它们完全相同,显示格式可以概括为 二维表形式,而 \G 则是一行一行显示,相当于旋转了 90o,每次显示一条记录(一行),看情况了,一般哪个合适选哪个。

另外,还可以使用 show table status [from db_name] [like 'pattern']\G 来查看表的详细信息。

更新数据表

表的修改分为:修改表本身、修改表字段。

修改表本身

  • 修改表名,基本语法:rename table <old_name> to <new_name>;
  • 修改表选项,基本语法:alter table <table_name> <table_opts>;

修改表字段

  • 添加字段
    • alter table <tab_name> add [column] <col_name> <data_type> [col_opts] [col_pos];
    • col_pos 表示在哪个位置添加字段,first 表示第一个位置,after <col_name> 表示在指定字段后面,如果未指定,则添加到末尾处。
    • 例子:alter table `student` add column `id` int first;
  • 修改字段
    • alter table <tab_name> modify [column] <col_name> <data_type> [col_opts] [col_pos];
    • 例子:alter table `student` modify column `class` varchar(10) after `name`;
  • 重命名字段
    • alter table <tab_name> change [column] <old_col_name> <new_col_name> <data_type> [col_opts] [col_pos];
    • 例子:alter table `student` change column `score` `grade` float(4, 1);
  • 删除字段
    • alter table <tab_name> drop [column] <col_name>;
    • 例子:alter table `student` drop column `age`;

删除数据表
语法:drop table [if exists] <table_name>[, table_name ...];

临时表
临时表和普通表在使用上没有什么区别,主要的区别是生命周期,普通表除非你使用 drop table 显式删除,否则不会凭空消失。而临时表,顾名思义,生命周期只限定于当前 MySQL 连接,连接被释放后,属于该链接的所有临时表都会被自动删除。临时表可以和普通表同名,此时只能看到临时表,因为被名称屏蔽了,只有在该临时表被删除后才能看到普通表。

创建临时表的语法:create temporary table (...),多个 temporary 关键字,其它基本相同。
使用临时表的语法:基本上和普通表一样,select、insert、delete、update、alter 都差不多。
查看临时表的语法:使用 show tables 看不到临时表,只能通过 show create table 表名 查看。
删除临时表的语法:drop table 临时表,语法和删除普通表一样。

表的复制

数据操作

数据操作也称为记录操作,row、record 操作。添加/删除/修改/查询 记录。

添加记录
添加全部字段:insert into <table_name> values(value1, value2, ...)[,(value1, value2, ...),...];
添加部分字段:insert into <table_name>(field1, field2, ...) values(value1, value2, ...)[,(value1, value2, ...),...];
无论哪种方式,都必须保持顺序的一致,字符串建议使用单引号括住,数值数据不需要也不能,可以一次性插入多条数据,使用逗号隔开就行。

查询记录
查询全部字段:select * from <table_name> [where condition];
查询部分字段:select col_name[,col_name,...] from <table_name> [where condition];

where 中的 condition 语法:condition_1 [and|or] condition_2 ...,其中条件一般用于测试字段,如 grade >= 80 表示成绩优秀的人有哪些,支持的比较运算符有:

  • = 是否相等
  • <>!= 是否不相等
  • < 是否小于
  • <= 是否小于等于
  • > 是否大于
  • >= 是否大于等于

除此之外,还有两个模糊查询(针对字符串),如果算上取反,那就有 4 个:
like:sql 通配符,_ 单个字符,% 任意多个字符,隐含 ^pattern$
not like:sql 通配符取反
regexp:sql 正则表达式,没有隐含 ^pattern$
not regexp:sql 正则表达式取反

多个条件之间可以使用逻辑连接符 and、or 连接,表示逻辑与、逻辑或。

sql 支持对查询结果排序,使用关键字 order by,基本语法为:
select * from <table_name> order by col_name1 [asc|desc], col_name2 [asc|desc] ...;
asc 为升序,desc 为降序,默认为升序。可指定多个排序字段,如果前面的相等,则比较后面的。

null值的处理
在 MySQL 中,NULL 值是比较特殊的,你不能使用 = NULL!= NULL<> NULL 来判断 NULL 值,因为 NULL 与任何值进行比较都返回 false,包括 NULL 自己,也就是 NULL = NULL 也返回 false。正确判断 NULL 值的方法是使用 IS NULLIS NOT NULL,或者使用 <=> 操作符替代 =,它可以正确处理 NULL 值,当两边的值都为 NULL 时它会返回 true。

更新记录
update <table_name> set col_name = new_value, col_name = new_value ... [where condition];
如果没有指定 where 条件,那么操作全表的数据,这可能不是你想要的;判断更新操作是否成功不是单看 SQL 语句是否执行成功,要看返回的 affected(受影响的)记录数量是否大于等于 1。

删除记录
删除部分记录:delete from <table_name> [where condition]
删除全部记录:delete from <table_name>truncate table <table_name>(推荐)

数值类型

SQL 将数据类型分为三大类,分别为:数值型字符串型日期时间型
SQL 中的数据类型
对于数值型数据,可以进一步分为 整数型小数型

整数型
在 SQL 中由于要考虑节省磁盘空间的问题,因此系统又将整型细分为 5 类,分别为:

  • tinyint:迷你整型,使用 1 个字节存储数据(常用);
  • smallint:小整型,使用 2 个字节存储数据;
  • mediumint:中整型,使用 3 个字节存储数据;
  • int:标准整型,使用 4 个字节存储数据(常用);
  • bigint:大整型,使用 8 个字节存储数据。

MySQL 中的整数默认都是有符号的(标准 SQL 只能是有符号数,没有所谓的 unsigned 无符号数),如果在整数型后使用 unsigned 进行修饰,那么表示这是一个无符号数。无符号数只能表示正数,无符号数可以表示正数和负数。

例如 tinyint,一个字节长度,默认的有符号数可表示的范围为 [-128, 127],添加了 unsigned 的 tinyint 可表示的范围为 [0, 255]。虽然都是 2^8 = 256 个数字,但是如果你的数据全都是正数(如用户 ID),可以考虑使用无符号数。不过使用无符号数会给可移植性带来一些问题,因为其他数据库基本都不支持无符号数。而且 mysql 中使用无符号数有时候也会遇到非常诡异的问题,因此不建议使用无符号数,因为同样的数据长度的情况下,无符号也仅比有符号的大一倍而已,实际上没多少,还不如使用更长的数据类型呢。

无论是有符号数还是无符号数,都是有大小范围的,如果存入的数值大于最大值则发生上溢,如果存入的数值小于最小值则发生下溢,它们都统称为溢出。发生上溢时,数值为 max 值,发生下溢时,数值为 min 值。同时 mysql 会产生警告信息,使用 show warnings 可查看上一个语句中的 warning 信息。

例子:

使用 desc 查看字段信息:

可以发现,每个字段的数据类型后面都会跟一个圆括号,里面还有一个数字。注意这个数字表示的是数值的显示宽度,与其他数据类型后面的圆括号里面的数字的意义是不一样的,其他数据类型里面的数字表示的是数据长度(字节)。这个显示宽度在一般情况下是没有任何使用和显示上的区别的,也就是说 int(1)int(11) 的区别在一般情况下看不出来,它们的数据长度都是 4 个字节。除非你使用了 zerofill 修饰(zerofill 修饰会将数据类型变为无符号数),zerofill 的意思是不足宽度的位置使用 0 补齐(和 printf 中的格式对齐是一样的道理)。请看例子:

为了可阅读性,建议使用 zerofill 时这样声明字段的数据类型:int(8) unsigned zerofill
但是,实际应用中不建议使用 unsigned 和 zerofill,尽量按标准行事(SQL 的标准,便于移植)。也不建议指定什么显示宽度,比如 int 就写作 int,bigint 就写作 bigint,不要加什么括号。

小数型
小数型,即带有小数点或者范围超出整型的数值类型。
在 SQL 中,小数型可细分为浮点型定点型两种,其中:

  • 浮点型:小数点浮动,精度有限,容易丢失精度;
  • 定点型:小数点固定,精度固定,不会丢失精度。

第 1 种:浮点型
浮点型数据是一种精度型数据,因为超出指定范围之后,其会丢失精度,自动进行四舍五入操作。理论上,浮点型分为两种精度:

  • float:单精度,占用 4 个字节存储数据,精度只保证 7 位有效数字;
  • double:双精度,占用 8 个字节存储数据,精度只保证 15 位有效数字。

float/double 声明:float(M, D)double(M, D),其中 M 代表总长度,D 代表小数部分长度,M-D 则为整数部分长度(M 最大 255,D 最大 30)。向 float/double 赋值时,可以直接插入小数(如 3.14),也可以插入用科学计数法表示的数据(如 2.18E3)。此外,插入浮点型数据时,整数部分是不能超出长度范围的,但是小数部分是可以超出长度范围的,系统会自动进行四舍五入的操作。特别的,如果浮点数是因为系统进位(四舍五入)导致整数部分超出指定的长度,那么是允许的。经测试(mariadb),不可以,会产生警告,实际存储的的值也不会超过 M,D 的限制。例子:

第 2 种:定点型
类型声明:decimal(M, D),其中 M 代表总长度,D 代表小数部分长度,M-D 则为整数部分长度。M 最大值为 65,D 最大值为 30。

定点型数据,绝对的保证整数部分不会被四舍五入,也就是说不会丢失精度,但小数部分有可能丢失精度,虽然理论上小数部分也不会丢失精度(这里的意思是只要小数部分的长度未超过声明时的长度就不会出现精度丢失的问题,MySQL 允许你插入超过声明时指定的小数部分长度,自动进行四舍五入操作)。

tinyint、smallint、mediumint、int、bigint、float、double、decimal 都可以使用 unsigned、zerofill 修饰,但是对于小数类型(float、double、decimal)强烈不建议使用 unsigned、zerofill 修饰,对于整数类型,也不是特别建议使用,除非真的需要。

日期时间型

日期时间型数据,顾名思义,就是用来表示日期和时间的数据类型,共有 5 种类型,分别为:

  • datetime日期时间,8 个字节,格式 yyyy-MM-dd HH:mm:ss,范围 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59,存储方式 yyyyMMddHHmmss,不保存时区信息。
  • timestampUnix 时间戳,4 个字节,范围 1970-2038 年,存储自 1970-01-01 00:00:00 UTC 起的秒数。timestamp 会根据当前时区(系统/mysql)自动显示对应时间(格式同 datetime)。timestamp 还有一个特殊属性:自动更新,可利用此属性存储记录的最后修改时间。
  • date日期,即 datetimedate 部分。
  • time时间,即 datetimetime 部分,可以为负值(为了方便运算)。
  • year年份,有两种格式 year(2)year(4),强烈建议使用 4 位数的形式。不过 mariadb 好像不建议自定义长度了,一律使用 year(4) 格式,但是要使用 year 进行声明,否则产生警告。
日期时间类型 占用空间 日期格式 最小值 最大值 零值表示
DATETIME 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 9999-12-31 23:59:59 0000-00-00 00:00:00
TIMESTAMP 4 bytes YYYY-MM-DD HH:MM:SS 19700101080001 2038 年的某个时刻 00000000000000
DATE 4 bytes YYYY-MM-DD 1000-01-01 9999-12-31 0000-00-00
TIME 3 bytes HH:MM:SS -838:59:59 838:59:59 00:00:00
YEAR 1 bytes YYYY 1901 2155 0000

一般使用 datetime 即可,可以使用各种日期时间函数。如果不关心 date,则使用 time,反之可以使用 date。year 单独使用的比较少(个人认为),timestamp 用的也不是很多,因为它会自动更新,比较适合的地方也就是最后的记录修改时间(lastmodify time),而 datetime 一般适合存储记录的创建时间(create time)。也有人使用 int、bigint 存储 unixtime(时间戳),具体的方式是通过函数 unix_timestamp() 获取 unix 时间(整数,存储到字段中),显示时可以使用 from_unixtime() 函数解析 unixtime,显示的格式与 datetime 相同。

timestamp 显示的时间与当前设置的时区精密相关,看例子:

关于 timestamp 的自动更新,自动更新属性默认是启用的,insert 时如果没有给 timestamp 字段赋值,那么 mysql 会自动将当前的时间赋给该字段,同时 update 记录时如果没有给 timestamp 字段赋值,那么 mysql 也会自动将当前的时间赋给该字段,因此利用 timestamp 可以很容易实现 last modify 时间。例子:

最后提一下,datetime、timestamp 类型可以插入字符串、整数(字符串缩写形式),例子:

字符串类型

  • char,文本、定长字符串:char(N),N <= 255,单位为字符,有字符集/字符序属性,可以有 default 值。如果存入的数据尾部有空格则被自动删除,然后再存入,如果长度不足则自动使用空格填充,取出数据时自动忽略填充的空格符。char 和 varchar 这两个以字符为单位的数据类型的具体长度等于 N * length of charset,比如 utf8 字符集为 N * 3,utf8mb4 字符集为 N * 4
  • varchar,文本、变长字符串:varchar(N),N <= 65535,单位为字符,有字符集/字符序属性,可以有 default 值。因为是变长的,所以需要额外的 1、2 字节存储实际数据的长度,如果字符串长度不超过 255,则使用 1 byte,如果超过 255,则使用 2 byte。虽然 varchar 的长度最大可以为 65535 字符,但是 mysql 会将它转换为 text 存储,而且因为 mysql 表中的所有字段的长度加起来不能超过 65535 byte,所以过大很容易导致建表失败。一个建议是 char 和 varchar 只用来存储少于 255 个字符的数据,超过此值的数据使用 text 来存储。text/blob 字段的实际数据不存储在表内的,类似于 C 语言中的指针,text/blob 存储的只是一个位置,实际数据在外部,所以不会导致 row size too large 错误。
  • binary,二进制、定长字符串:binary(L),L <= 255,单位为字节,没有字符集/字符序属性,可以有 default 值。如果存入的数据长度不足,则自动使用 0x00 字符填充,取数据时会一并取出 0x00,特别注意。binary 和 varbinary 可以存储的字符个数要看使用的什么字符集,假设是 10 字节,则可以存储 10 个 ASCII 字符(utf8、utf8mb4),或者存储 3 个中文(BMP) + 1 个英文(utf8、utf8mb4)。
  • varbinary,二进制、变长字符串:varbinary(L),L <= 65535,单位为字节,没有字符集/字符序属性,可以有 default 值。binary/varbinary 与 char/varchar 基本类似,很多属性、特点可以类推。比如 varbinary 的长度如果太大 mysql 会自动将其转换为 blob(可以知道 char/varchar 对应 text,binary/varbinary 对应 blob,前者主要存储不超过 255 个长度的数据,后者则用于存储大数据)。
  • text,文本、变长字符串(一般):0 ~ 65535 字节,实际存储的数据在外部,该字段的内容其实是实际数据的位置,大概占 9 ~ 12 字节。不能设置 default 默认值,有 charset/collate 属性。
  • tinytext,文本、变长字符串(极小): 0 ~ 255 字节,其余同 text。
  • mediumtext,文本、变长字符串(中等):0 ~ 16777215 字节,其余同 text。
  • longtext,文本、变长字符串(极大):0 ~ 4294967295 字节,其余同 text。
  • blob,二进制、变长数据(一般):0 ~ 65535 字节,实际存储的数据在外部,该字段的内容其实是实际数据的位置,大概占 9 ~ 12 字节。不能设置 default 默认值,没有 charset/collate 属性。
  • tinyblob,二进制、变长数据(极小):0 ~ 255 字节,其余同 blob。
  • mediumblob,二进制、变长数据(中等):0 ~ 16777215 字节,其余同 blob。
  • longblob,二进制、变长数据(极大):0 ~ 4294967295 字节,其余同 blob。

mysql 每个表的字段数不能超过 4096 个,所有字段的长度加起来不能超过 65535 字节

字符串类型总结:char 系对应 text 系,binary 系对应 blob 系,前者支持 charset/collate 设置,后者不支持。char 系和 binary 系都是存储小数据用的,分别有定长和不定长之选,数据的长度一般不超过 255 单位长度(字符/字节),如果很长,请选择对应的 text、blob 数据类型,char 系和 binary 系都可以设置默认值,且效率比 text/blob 系好,如果可以尽量考虑 char 系、binary 系。基本上所有字段的数据都是内联在数据表中的,除了 text 和 blob 类型,因为他们很有可能是非常大的数据(G 级别),所以 mysql 将 text/blob 的实际数据存储在表外,本身只存储它们的具体位置。

eg:姓名、地址等变长信息优先使用 varchar,身份证号、电话号码等建议使用 char 定长字符串。

  • char -> varchar -> text系列:使用字符作为长度单位,字符数据,可指定字符集、字符序。
  • binary -> varbinary -> blob系列:使用字节作为长度单位,二进制数据,没有字符集设置。

枚举类型 enum
类似单选题,只能选择已定义的选项中的一个,内部使用整数序号存储,节省存储空间,可以规范输入数据,但是因为存在额外的查找对比过程所以效率略低一些,这是 enum 的例子:

枚举在进行数据规范(定义)的时候,系统会自动建立一个数字与枚举元素的对应关系(放在日志中);在进行数据插入的时候,系统自动将字符串转换为对应的数值进行存储;在进行数据提取的时候,系统自动将数值转换成对应的字符串进行显示。

集合类型 set
set 和 enum 类似,存储的是数值而不是字符串,类似多选题(当然可以单选)。它们的特点都是一样的,优点是节省空间&规范数据,缺点是效率不是很高。这是使用例子:

这里稍微解释一下内部存储的数值的意义:集合字符串中每一个元素都对应一个二进制位,其中被选中的为 1,未选中的为 0,最后再反过来,这个二进制数对应的十进制数即为其数据库中实际存储的是数值。

再谈字符集
人与人之间通常使用“字符”进行交流,但是计算机却只认识二进制(数字),如果想在计算机中存储“字符”,那么我们必须使用对应的数字表示我们的“字符”。每个数字都只能对应一个字符,每个字符也只能对应一个数字,要呈一对一关系,不然转换时必定出错。这个 字符 <-> 数值 的映射关系我们称为字符集。简单的说,字符集就是一张表,规定了每个字符对应的数值是多少,这样在将字符存入计算机、从计算机中取出字符时才能正确的显示。

历史的发展中出现了很多字符集,最早的是 ASCII 字符集,ASCII 字符集只包含了美国人使用的字符,即:33 个控制字符、 26 + 26 个英文字母(大写、小写),10 个阿拉伯数字,以及常用的标点符号。总共也就 128 个字符而已(7 个比特位)。注意,我们把字符集中的每个字符对应的数值都称为一个码点,一个码点就代表字符集中的一个字符,比如 ASCII 有 128 个字符,也就是说 ASCII 字符集有 128 个码点(code point)。

但是很明显 ASCII 只能够美国人使用,像西欧、CJK (中日韩)字符,很多字符都没有囊括在其中,那怎么办,西欧人想到一个办法,ASCII 字符集不是只使用了 1 个字节中的 7 个比特位吗,还有一个位(128 个空闲的码点),我们可以利用这 128 个空闲的码点来存储我们的字符,不就可以了吗。这就是后来的 EASCII 字符集(扩展的 ASCII 字符集),后来发展为了 ISO 8859-1 字符集,正式编号为 ISO/IEC 8859-1:1998,又称 Latin-1 或“西欧语言”,即使用 1 byte 的字符集。

那么我们中文怎么办,貌似剩下的 128 个码点也不够表示我们的汉字啊(常用的汉字有 3500+ 多个呢),没关系,我们使用两个字节来存储,这样就有 216 = 65536 个码点了,前 8 bit 与 EASCII 相同,后面的几万个码点就拿来存储汉字(简体、繁体,等等)。类似的,其他国家也使用类似的方法创建了属于自己的字符集,可谓是百花齐放。

但是,各种各样的字符集却带来了很大的麻烦 - 乱码。因为同一个数值,使用不同的字符集来解释显然会得到不同的字符,于是就产生了乱码。而且各大字符集都互不兼容,你弄你的标准,我玩我的标准。为了快点结束这种混乱的局面,Unicode 字符集出现了,它号称要表示世界上的所有字符,将它们都容纳在 Unicode 字符集中,因此也成为万国码、统一码。Unicode 至今仍在不断增修,每个新版本都加入更多新的字符。目前最新的版本为 2018 年 6 月 5 日公布的 11.0.0,已经收录超过 13 万个字符(第十万个字符在 2005 年获采纳)。Unicode 涵盖的数据除了视觉上的字形、编码方法、标准的字符编码外,还包含了字符特性,如大小写字母。因为 Unicode 字符数量庞大,为了方便管理,将其划分为了 17 个平面(plane),每个平面都有 65536 个码点。总共有 65536 * 17 = 1,114,112 个码点,111 万个码点,表示所有的字符应该是足够了。不过目前只使用了很少的一部分,平面的编号从 0 开始,一直到 16。0 号平面称为“基本多文种平面” BMP,其中包含了绝大多数常用的字符,包括 CJK 字符。而 1 号平面则称为“多文种补充平面” SMP,复杂的汉字就包含在其中。BMP 和 SMP 总共 12 万个码点/字符,日常使用中 99.99% 的字符都来自其中。

Unicode 字符集的出现确实解决了字符集不兼容的局面,但是一个新的问题又来了,我们该如何存储这 111 多万个码点呢?如果要全部装下,最少也要 3 个字节来存储,对于基本只使用 ASCII 字符的人来说,要使用原来的 3 倍的存储空间来存储同等数量的字符那是很难接受的,毕竟那时候的存储空间本来就小,网络带宽也很小。那怎么办呢?于是大佬们又设计了好几种“存储方案”(我们将这些存储方案称为“字符编码”),常用的有 UTF-8、UTF-16、UTF-32。其中比较节省空间的就是 UTF-8、UTF-16 了,而 UTF-8 字符编码的前 128 个码点与 ASCII 字符编码的值相同,所以得到了互联网的广泛认可,建议大家都使用 UTF-8 编码。因为它完全兼容 ASCII 编码,合法的 ASCII 编码同时也是合法的 UTF-8 编码。

这里说明一下,我们通常将 Unicode 称为字符集,将 UTF-8、UTF-16 称为字符编码,因为 Unicode 字符集实在太大了,所以产生了好几种存储方案,但是 ASCII、Latin-1、GB2312、GBK 这些字符集只有一种编码方案(也就是码点是什么值就存入什么值),所以 ASCII 既是字符集名,也是字符编码名,同理,Latin-1、GB2312、GBK 这些也是。

  • 字符 -> 数值:“编码”,encode,可以理解为将明文(字符)加密为密文(字节);
  • 数值 -> 字符:“解码”,decode,可以理解为将密文(字节)解密为明文(字符)。

很显然,如果想要让同一个字符在经过 encode、decode 后能保持一致,就必须保证 encode 和 decode 时的“算法”(其实就是字符编码)要一致,不然就是对牛弹琴,牛头不对马嘴,必然乱码。

所以乱码的根源就是 encode 时使用的字符编码和 decode 时使用的字符编码不一致导致的。OK,回到 MySQL 中来,字符编码的设置有两个地方,一个是 set names,一个是 column 的字符编码:

  • SET NAMES 'charset_name' COLLATE 'collation_name'
  • `column_name` char/varchar/text charset 'charset_name' collate 'collation_name'

为了好讲解,我将 set names 设置的字符编码称为“环境编码”,将 char/varchar/text 字段的字符编码称为“字段编码”(或者“数据编码”,因为字段是实际存储数据的地方)。先说说我自己的结论:

  • 环境编码和数据编码可以不一样,比如一个为 utf8mb4,另一个为 gbk。
  • 写入数据时、读取数据时,只要环境编码没有改动过,就不会出现乱码问题。
  • 但是请不要给自己找麻烦,务必保证环境编码与字段编码一致,比如 utf8mb4。

记录长度限制

MySQL 中规定:任何一条记录最长不超过 65535 个字节,这意味着 varchar 永远达不到理论最大值。

那么,varchar 实际存储长度能达到多大呢?由编码字符集决定。下面,以 varchar 在 UTF-8 和 GBK 的情况为例,执行如下 SQL 语句,进行演示:

mysql 中的 utf8 码点最大值为 3 个字节,gbk 为 2 个字节,所以能够存储的 utf8 字符有 65535 / 3 = 21845 个,同理能存储的 gbk 字符为 65535 / 2 = 32767.5,即 32767 个。注意这只是理论值,还没有算 varchar 额外的 2 个用于记录数据长度的字节,所以能存储的 utf8 字符最多为 65533 / 3 = 21844 个,能存储的 gbk 字符最多为 65533 / 2 = 32766 个。我们来再试一次:

如果你仔细计算了 utf8、gbk 的 varchar 的长度的话,你会发现它们都还剩余 1 个字节,那么我们再添加一个 tinyint 进去,正好一个字节长度:

字段属性

列属性
列属性:实际上,真正约束字段的是数据类型,但是数据类型的约束比较单一,因此就需要额外的一些约束来保证数据的有效性,这就是列属性。

列属性有很多,例如:nullnot nulldefaultindexprimary keyforeign keyunique keyauto_incrementcomment 等。

空属性
空属性有两个值,分别为:nullnot null

虽然默认数据库的字段基本都为空,但是实际上在真正开发的时候,要尽可能的保证数据不为空,因为空数据没有意义,也没办法参与运算。而且 MySQL 很难对允许 NULL 值的列进行优化,因为可为 NULL 的列使得索引、索引统计和值比较都更复杂,可为 NULL 的列也需要更多的存储空间。如果需要“空值”,可以使用 0、空串等特殊值表示。

列的属性默认为 null,除非你显式的声明 not null。例子:

列描述
列描述:comment,表示描述(注释),没有实际含义,是专门用来描述字段的,其会随着表创建语句自动保存,用来给程序员(数据库管理员)了解数据库使用。

默认值
默认值:default,某一数据会经常性出现某个具体的值,因此可以在开始的时候就指定好,而在需要真实数据的时候,用户可以选择性的使用默认值。如果 insert 时没有给该字段赋值,则自动使用 default 值填充。例子:

主键索引

primary key主键,一张表中只能有一个主键,主键可以由一个或多个字段组成,多个字段组成的主键称为复合主键。主键的作用是唯一标识表中的一条记录,因此同一表中的主键列之间的值不能相同,且规定主键列的值不能为 NULL,建议主键列声明为 NOT NULL(不声明也不会报错但是最好不要这么做)。

建表时设置主键有两种方式,一是直接在字段中添加 primary key 修饰,二是在建表语句的最后使用 primary key (column1, column2, ...) 声明,如果主键只有一个字段,可以使用第一种方式,如果有多个字段,只能使用第二种方式了。

如果表已经存在,也可以使用 alter 来设置主键,也有两种方式,一种是修改列的属性添加 primary key 属性,一种是 add column 到 primary key 列表中:

注意,要想在建表后追加/修改主键设置,必须保证对应的主键列中的数据时全表唯一的,否则会导致修改失败,请看例子:

主键约束
主键约束,即主键列(单个或组合)中的数据不允许重复,如果重复,则数据操作(主要是增和改)会失败。这个前面已经演示过了,不在复述。

修改主键
对于已存在主键的数据表,不能直接更新主键的设置(添加、删除主键列等),必须先 drop primary key 后重新建立主键才可以(其实上面也演示过了)。删除主键的语法:alter table <table_name> drop primary key,删除后使用上面的添加主键的方式设置新主键。

自动增长

自动增长:auto_increment,当对应的字段未给定数值,或者是默认值,或者是 null 时,自动增长机制就会被自动触发,MySQL 会从当前字段中取已有的最大值进行 +1 操作,然后将得到的新字段值作为当前字段的值存储起来。auto_increment 属性仅适用于整数数据类型,即 tinyint、smallint、mediumint、int、bigint 这些。还有,自动增长列必须是一个索引列(主键也是一种特殊的索引)。另外,一张表最多只能有一个自动增长列,这和一张表最多只有一个主键是一样的。另外,自动增长列必须为 not null 列(声明为 null 也没用,还是会变成 not null 属性的)。

例子:

发现没,我们可以显式的给自动增长类赋值,也可以不给它赋值,然后 MySQL 会自动找到已有的最大值,然后将其加 1,得到的数值则作为当前字段的值(注意上面的 10,11 号 ID)。

如果给自动增长列手动赋予 0 值、null 值、default 默认值,那么也会触发自动增长机制,例子:

可以知道,自动增长列的初始值为 1,每次触发自动增长时,MySQL 会自动查找最大的值,然后将其加一得到的值作为新值存入,如何查看下一个 auto_increment 的数值呢,比如这里是 14,使用 show create table <table_name> 就可以了,表的属性中的 AUTO_INCREMENT 值就是下一个自动增长列的数值了:

修改自增长列
因为一个表中同时只能有一个自增长列,所以必须先取消掉原来的自增长列中的 auto_increment 属性,然后 alter 自增长属性到要设置的新字段中,例子:

修改自增长的值
语法 alter table <table_name> auto_increment = <value>,value 必须大于当前自增长列中的最大值,否则无效。例子:

设置自增长列的初始值、步长
auto increment 列的初始值和步长都是通过 mysql 系统变量控制的,使用 show variables like 'auto_increment%' 查看默认值:

auto_increment_offset 是初始值,auto_increment_increment 是增长步长。默认值都是 1,可以通过修改这两个变量来修改 auto_increment 的相关属性,但是要注意,我们没办法只给某个表修改,这两个变量修改后会影响所有的表。实际上不建议修改这两个值,默认的 1 就很好了,不要自找麻烦。

删除自增长属性
上面已经演示过了,因为自增长是列的一个属性,所以可以使用 alter table <table_name> modify ... 语句修改,去掉 auto_increment 属性即可。

唯一索引

唯一键:每张表往往有多个字段需要具有唯一性,数据不能重复,但是在每张表中,只能有一个主键,因此唯一键就是用来解决表中多个字段需要具有唯一性问题的。

唯一键与主键很相似,都不允许数据的重复,但是唯一键允许数据为 NULL(且允许多个数据为 NULL,NULL 字段不参与唯一性的比较),主键不允许,我们可以将主键看作是一种特殊的唯一键。

增加唯一键
创建时设置唯一键,有两种方式,一是直接使用 unique key 修饰(允许多个字段使用此修饰),而是在后面使用 unique key (column_name) 声明(如果又多个,请添加多个声明)。例子:

创建表之后设置唯一键也有两种方法,一是直接修改字段的属性,二是添加唯一键(推荐):

和主键一样,一个唯一键(唯一索引)允许为单个字段,也可以为多个字段(复合唯一键),还有一个细节,MySQL 会为唯一键命名,这个名称的作用就是标识不同的索引,因此而已,如果没有指定名称(前面我们都未指定),那么第一个索引字段的名称将被作为索引的名称。指定名称很简单,例子:

提示:可以使用 show index from <table_name> 来查看某个表中的索引信息。当然使用 show create table <table_name> 也可以查看,而且我个人觉得更加直观,建议使用。

同一张表中可以有多个唯一键,但是只能有一个主键。

普通索引

索引:系统根据某种算法,将已有的数据(未来可能新增的数据),单独建立一个文件,这个文件能够实现快速匹配数据,并且能够快速的找到对应的记录,几乎所有的索引都是建立在字段之上的。

如果把数据库比作一本图书,那么索引就是图书的目录,有了目录我们就可以快速的找到我们感兴趣的内容,而不用一页一页的翻书,因为效率太低了,书的页数越多工作量越大,它们是成正比关系的。这个过程其实和数据库中的全表查询是一样的,如果没有索引,那么每次从数据库中查询数据都是要进行全表扫描的,表越大,数据量越多,所耗费的时间就越长,效率也就越低。所以我们必须要创建一个类似目录一样的东西,也就是索引,这样进行查询时就可以直接查找这个索引文件而快速的定位一条记录,不用进行耗时耗力的全表查找。

MySQL 中的索引都是建立在字段上的,索引虽然可以提高数据查询的效率,但是维护索引也是需要代价的,那就是更新、删除数据时必须同时更新与之相关的所有索引,所以你不能把索引当作数据库查询优化的灵丹妙药,比如你可能会想:在每个字段上都创建索引,这样数据查询的效率应该会很高吧。请及时打消你的想法,因为过多的索引会严重的增加记录更新/删除的 IO 负担,而且 MySQL 进行查询操作时,每次也只能使用一个索引(单列索引或多列索引),所以再多的索引也是无济于事,只有其中一个能够发挥作用,而且索引也是数据,也是需要存储空间的,当数据量很大时,索引数据也需要不少的存储。

索引的意义/作用:提高查询数据的效率,约束数据的有效性(唯一索引、主键索引)。

但是增加索引是有前提条件的,这是因为索引本身会产生索引文件(有的时候可能会比数据本身都大),因此非常耗费磁盘空间。

  • 如果某个字段需要作为查询的条件经常使用,可以使用索引;
  • 如果某个字段需要进行数据的有效性约束,也可以使用索引(主键或唯一键)。

MySQL 中提供了多种索引,包括:

  • 普通索引 key/index
  • 唯一索引 unique key
  • 主键索引 primary key
  • 全文索引 fulltext key

其中,主键和唯一键咱们之前已经了解过啦!至于普通索引,顾名思义,并没有什么特色,唯一的任务就是加快数据的查询速度,普通索引对数据没有什么要求。

在这里,咱们说说全文索引。全文索引,即根据文章内部的关键字进行索引,其最大的难度就是在于如何确定关键字。对于英文来说,全文索引的建立相对容易,因为英文的两个单词之间有空格;但是对于中文来说,全文索引的建立就比较难啦,因为中文两个字之间不仅没有空格,而是还可以随意组合。

在 mysql 5.6 版本以前,fulltext key 只能用在 MyISAM 类型的表上,但是从 5.6 版本开始,也支持用在 InnoDB 类型的表上了,因为是全文索引,顾名思义,只能用在 char、varchar、text 上。

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里,我们就不追根究底其底层实现原理了,现在我们来看看在 MySQL 中如何创建并使用全文索引。

注意:MySQL 自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。因为它是根据空格来进行分词的,对中文来说没有任何作用。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用 Sphinx(斯芬克斯)、Coreseek 技术来处理中文。

目前,使用 MySQL 自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL 全文索引所能找到的词的默认最小长度为 4 个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。

如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高(道听途说,具体还请自行验证)。

幸好,从 MySQL5.7.6 版本开始提供了一种内建的全文索引 ngram parser,可以很好的支持 CJK 字符集(中文、日文、韩文),CJK 有个共同点就是单词不像英语习惯那样根据空格进行分解的,因此传统的内建分词方式无法准确的对类似中文进行分词。

ngram parser 内建在代码中,该解析器默安装,你可以通过指定索引属性(WITH PARSER ngram)来利用该 parser,例如:

但是很不幸,mariadb 至今(2018-07)也还没支持 ngram 分词方式,所以本文不重点介绍全文索引。

简单的介绍一下如何使用全文索引,进行文本内容的搜索(关键字、关键词搜索):
select * from `table` where match(`column`) against('word'),column 可以有多个,逗号隔开。

索引总结

索引概念
比喻:索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书 1000 页,有 500 页也是目录,效率也是很低的,目录是要占纸张的,而索引是要占磁盘空间的。

数据结构
MySQL 索引主要有两种数据结构:B+树Hash

  • Hash:Hsah 索引在 mysql 上比较少用,它把数据的索引以 hash 形式组织起来,因此当查找某一条记录的时候,速度非常快。因为是 hash 结构,每个键只对应一个值,而且是散列的方式分布。所以它并不支持范围查找和排序等功能。Memory 存储引擎的默认索引结构。
  • B+ 树:b+tree 是 mysql 使用最频繁的一个索引数据结构,该数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能。相对 hash 索引,B+ 树在查找单条记录的速度虽然比不上 hash 索引,但是因为更适合排序等操作,所以更受用户的欢迎。毕竟不可能只对数据库进行单条记录的操作。InnoDB、MyISAM 存储引擎的默认索引结构。

索引类型
mysql 中共有 5 种索引类型,其中前 3 种是比较常用的:

  • 普通索引,key/index:最普通的索引,对数据没有限制。
  • 唯一索引,unique key:索引列的值必须唯一,允许 NULL 值(NULL 不参与唯一性比较)。
  • 主键索引,primary key:可看作一种特殊的唯一索引,不允许 NULL 值(声明为 NOT NULL)。
  • 全文索引,fulltext key:关键字搜索技术,但是目前仅适用于英文,因为它使用空格进行分词。
  • 空间索引,spatial key:可以理解为QQ微信查找附近的人的功能,不是很了解,大家也很少使用。

由于空间索引不常用,也不太了解,所以只简单的说明前面的 4 种索引类型。

mysql 索引是建立在字段(列)上的,所以又可分为单列索引、多列索引(复合/组合索引):

  • 单列索引:该索引包含一个字段(列);
  • 多列索引:该索引包含多个字段(列)。

前面的 4 种索引类型都可以为单列索引,也可以为多列索引。多列索引也称为组合索引、复合索引。

索引名称
除了主键索引外,其 3 个索引都是有名称的,名称的作用就是为了标识同一表中的不同索引,如果没有指定索引名,那么 mysql 默认将第一个索引列的列名作为索引名。指定索引名的方法:以普通索引为例,key key_name(col_name1, col_name2, ...),省略 key_name 时 col_name1 就是 key_name。

为什么主键索引没有名称呢?因为同一张表只允许一个主键索引,所以没必要用名称标识。

创建索引

  • 普通索引
    • 建表时:key `keyname`(`colname1`, `colname2`, ...)
    • 建表后:alter table `table` add key `keyname`(`colname1`, `colname2`, ...)
  • 唯一索引
    • 建表时:unique key `keyname`(`colname1`, `colname2`, ...)
    • 建表后:alter table `table` add unique key `keyname`(`colname1`, `colname2`, ...)
  • 主键索引
    • 建表时:primary key (`colname1`, `colname2`, ...)
    • 建表后:alter table `table` add primary key (`colname1`, `colname2`, ...)
  • 全文索引
    • 建表时:fulltext key `keyname`(`colname1`, `colname2`, ...)
    • 建表后:alter table `table` add fulltext key `keyname`(`colname1`, `colname2`, ...)

删除索引

  • 主键索引:alter table `table` drop primary key;
  • 其他索引:alter table `table` drop key `keyname`;

查看索引
show index from `table_name`;:详细
show create table `table_name`;:直观

前缀索引
前缀索引是什么,在解释什么是前缀索引之前,先来搞明白为什么需要前缀索引。MySQL 中的索引可以包含单个字段,也可以包含多个字段,但是单个索引的长度是有限制的,这个值为 3072 byte。此外,还有一个限制,那就是索引中的单个字段的长度最大为 767 byte。也就是说索引的最大长度为 3072 字节,而索引中的单个字段的索引长度最大为 767 字节。如果超过这些限制,都会导致 ERROR 错误。

而常见的数据类型中,只有字符串类型的长度是不固定的,可大可小:

  • charvarchartext:单位:字符,长度依字符集而变,都有可能超过 767 字节的限制
  • binaryvarbinaryblob:单位:字节,binary 最大长度 255 字节,没问题,其它的可能。

比如 utf8mb4 的 char,最大索引长度为 767 / 4 = 191 个字符,那么如果超过了要怎么办呢?这时候就需要使用前缀索引了,语法和数据类型后面的括号一样,在里面填上要索引的长度(前缀)即可。

因为 ascii 单字符最大长度为 1 字节,所以 255 * 1 = 255 不会超过 767 字节的限制,同理 gbk 单字符最大长度为 2 字节,所以 255 * 2 = 500 也不会超过 767 字节的限制,但是 utf8mb4 单字符最大长度为 4 字节,所以 255 * 4 = 1020 超过了 767 字节的限制,所以会报错,所以需要指定前缀长度,换算过来就是 191 个 utf8mb4 字符。

例二,单个索引的最大长度为 3072 字节的限制:

细想一下,为什么 mysql 要限制索引的长度,其实很容易知道,索引是要占用存储空间的,而且索引过大会给 insert、update、delete 操作带来 IO 负担,因为它们不仅要操作数据本身,还要维护索引。索引也仅仅对 select 有帮助,对其它 3 个操作来说都是一个负担。所以 mysql 必须设置一个合理的限制值,不能让索引过大,拖慢数据库的性能。其实我们自己也不能让索引过大,对于 char、varchar、text、binary、varbinary、blob 等数据类型,强烈建议指明要索引的前缀长度,不要等到 mysql 报错再来设置,过大的索引只有坏处没有好处。

所谓的前缀索引就是像上面的那样,指定前缀长度就 OK 了,和声明数据类型的长度很相似。

既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?答案是否定的。因为索引虽然加快了查询速度,但索引也是有代价的:索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担,另外,MySQL 在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般这两种情况下是不建议建索引的:

  • 表记录比较少:例如一两千条甚至只有几百条记录的表,没必要建索引,让查询做全表扫描就好了。至于多少条记录才算多,每个人都有自己的看法,我个人的经验是以 2000 作为分界线,记录数不超过 2000可以考虑不建索引,超过2000 条的可以酌情考虑建立索引。
  • 索引选择性较低:所谓索引的选择性(Selectivity),是指不重复的索引数(也叫基数,Cardinality)与表记录数(#T)的比值,这个比值的范围在 [0, 1] 之间,比值越大则索引的选择性越高,索引的价值也就越大。如果选择性为 1(如唯一索引、主键索引),那么性能是最好的,相反,如果选择性为 0,那么它一点儿价值都没有,因为索引条目都是重复的,找不到你想要的数据,还不如全表扫描。

因此,在决定是否给一个字段(或多个字段的联合索引)创建索引前,不妨先计算一下它的选择性,如果不是很高,那么就不要创建了,还浪费存储空间。例子:

title 的选择性不足 0.0001(精确值为 0.00001579),所以实在没有什么必要为其单独建索引。

有一种与索引选择性有关的索引优化策略叫做 前缀索引,就是用列的前缀代替整个列作为索引 key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时因为索引 key 变短而减少了索引文件的大小和维护开销。下面以 employees.employees 表为例介绍前缀索引的选择和使用。

employees 表只有一个索引,那么如果我们想按名字搜索一个人,就只能全表扫描(ALL)了:

如果频繁按名字搜索员工,显然效率很低,因此可以考虑建索引。先来看看不同字段的选择性:

显然 first_name + last_name 多列索引更有价值,但是它们加起来的总长度为 30,有没有兼顾长度和选择性的方法,那就是前缀索引了,来看一下它们的选择性:

使用 last_name 的前四个字符的选择性就很不错了,但是索引的长度却只有原来的一半:18 个字符。那就把这个索引给建上吧,MySQL 语句:

此时再执行一遍按名字查询,比较分析一下与建索引前的结果:

性能的提升是显著的,查询速度提高了 120 多倍。

当需要为某个数据类型为字符串的列创建索引时,通常都是创建 全文索引,通过全文匹配条件来筛选记录。其实没有必要,因为全文索引费时费力非空间,一种更好的办法是:前缀索引。它可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。

优缺点:前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于 ORDER BY 和 GROUP BY 操作(这两个都要进行排序,要访问全字段,所以用不了前缀 ),也不能用于Covering index(即当索引本身包含查询所需全部数据时,不再访问数据文件本身)。

多列索引
多列索引是什么前面已经说了,这里详细的说一下多列索引的其他细节。多列索引中有一个很重要的原则:最左匹配原则。其实就是说一个查询可以只使用复合索引最左侧的一部分。例如多列索引 key name(a, b, c),可以支持 aa, ba, b, c 3 种组合进行查找,但不支持 bcb, c 等组合的查找。很好理解,你可以将多列索引 (a, b, c) 理解为这 3 个索引的集合:(a)(a, b)(a, b, c)。创建一个 (a, b, c) 多列索引就相当于创建了这 3 个索引。

为了更好的理解为什么可以使用最终匹配原则进行查找,而不允许其他的组合,请看这个比喻:

复合索引的结构与电话簿类似,人名由姓和名构成,电话簿首先按姓氏对进行排序,然后按名字对有相同姓氏的人进行排序。如果您知道姓,电话簿将非常有用;如果您知道姓和名,电话簿则更为有用,但如果您只知道名不知道姓,电话簿将没有用处。

相当于这是一个链子,你可以只使用前面一部分,但是不允许中间断了,或者取哪一部分这种用法。
因此建立多列索引时,必须要合理的安排字段的顺序,安排的好可以最大限度的发挥它应有的作用。

复合索引的建立原则

  • 使用最频繁的字段应该放在多列索引的列表的最前面,整个字段列表也应该按照使用频率进行排列。
  • 较短的字段应该放在前面,任何索引的长度都不要过长,不仅增加 IO 负担,还可能导致内存溢出。
  • 如果您很可能仅对一个列多次执行搜索,则该列应该是复合索引中的第一列。如果您很可能对一个两列索引中的两个列执行单独的搜索,则应该创建另一个仅包含第二列的单列索引。
  • 请注意,创建复合索引应当包含少数几个列,并且这些列经常在 select 查询里使用。在复合索引里包含太多的列不仅不会给带来太多好处。而且由于使用相当多的内存来存储复合索引的列的值,其后果是内存溢出和性能的降低。

索引的好处除了提高 select 语句的执行效率外,还可以降低记录的排序成本。我们知道,每个索引中的数据都是按照索引键键值进行排序后存放的,所以,当 Query 语句中包含排序(order by)/分组(group by)操作时,如果排序字段和索引键字段刚好一致,MySQL Query Optimizer 就会告诉 mysqld 在取得数据后不用排序了,因为根据索引取得的数据已经满足客户的排序要求。

那如果是分组操作呢?分组操作没办法直接利用索引完成。但是分组操作是须要先进行排序然后分组的,所以当 Query 语句中包含分组操作,而且分组字段也刚好和索引键字段一致,那么 mysqld 同样可以利用索引已经排好序的这个特性,省略掉分组中的排序操作。

如何判定是否需要索引

  • 频繁作为查询条件的字段应创建索引
  • 不会出现在 WHERE 子句中的字段不该创建索引
  • 更新非常频繁的字段不适合创建索引(注意是“非常”)
  • 选择性太低的字段不适合单独创建索引,即使频繁作为查询条件

MySQL 每次进行 Query 操作时都只能使用 1 个索引,适当的使用多列索引能有效提高查询效率

如果查询的 where 条件只有一个,我们完全可以用单列索引,这样的查询速度较快,索引也比较瘦身。如果我们的业务场景是需要经常查询多个组合列,不要试图分别基于单个列建立多个单列索引(因为虽然有多个单列索引,但是 MySQL 只能用到其中的那个它认为最有效率的单列索引,但即使这样,它的效率也不如多列索引)。这是因为当 SQL 语句所查询的列,全部都出现在复合索引中时(或者是左边的一部分,即最左匹配原则),此时由于只需要查询索引块即可获得所有数据,当然比使用一个单列索引然后从里面筛选数据要快得多。下面以实际例子说明:

这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的 peopleid。例如,我们可能需要查找姓名为 Mike Sullivan、年龄 17 岁用户的 peopleid,SQL 为:SELECT peopleid FROM people WHERE firstname="Mike" AND lastname="Sullivan" AND age=17。由于我们不想让 MySQL 每次执行查询就去扫描整个表,这里需要考虑运用索引。

首先,我们可以考虑在单个列上创建索引,比如 firstname、lastname 或者 age 列。假设我们创建 firstname 列的索引,MySQL 将通过这个索引迅速把搜索范围限制到那些 firstname="Mike" 的记录,然后再在这个“中间结果集”上进行其他条件的搜索:它首先排除那些 lastname 不等于 “Sullivan” 的记录,然后排除那些 age 不等于 17 的记录。当记录满足所有搜索条件之后,MySQL 就返回最终的搜索结果。

由于建立了 firstname 列的索引,与执行表的完全扫描相比,MySQL 的效率提高了很多,但我们要求 MySQL 扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除 firstname 列上的索引,再创建 lastname 或者 age 列的索引,但总地看来,不论在哪个列上创建索引搜索效率仍旧相似。

为了提高搜索效率,我们需要考虑运用多列索引。如果为 firstname、lastname 和 age 这三个列创建一个多列索引,MySQL 只需检索一下这个多列索引就能够快速的找出结果(多列索引也是一个索引)!

那么,如果分别在 firstname、lastname、age 这三个列上创建单列索引,效果是否和创建一个 firstname、lastname、age 的多列索引一样呢?答案是否定的,两者完全不同。当我们执行查询的时候,MySQL 只能使用一个索引。如果你有三个单列的索引,MySQL 会试图选择一个限制最严格的索引。但是,即使是限制最严格(匹配度最高的)的单列索引,它的匹配度也肯定远远低于 firstname、lastname、age 这三个列上的多列索引。

而且,我们还可以使用这个多列索引的前一部分,因为它相当于创建了 (firstname,lastname,age)(firstname,lastname) 以及 (firstname) 这些列组合上的索引。为什么没有 (lastname, age) 等这样的组合索引呢?这是因为 mysql 组合索引”最左前缀”(Leftmost Prefixing)的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。以下是代码片段:

理论来说,组合索引(包括最左前缀中的组合索引)在使用时必须严格按照定义时的字段顺序来使用,否则是不会使用索引的。比如上面的例子,正确:select * from people where firstname = 'A' and lastname = 'B' and age = 17,错误: select * from people where age = 17 and lastname = 'B' and firstname = 'A'。但是由于存在 mysql 查询优化器,它会自动的调整到合适的顺序再执行查询语句,所以实际上顺序不是问题。但是还是建议能够按照顺序来,不要依赖 mysql 查询优化器,要养成良好的习惯。

引申
对于联合索引 (col1, col2, col3),查询语句 SELECT * FROM test WHERE col2 = 2 是否能够触发索引?大多数人都会说 NO,实际上却是 YES。观察两个 explain 结果(稍后讲解)中的 type 字段:

EXPLAIN SELECT * FROM test WHERE col1 = 1;,查询类型为 type: ref
EXPLAIN SELECT * FROM test WHERE col2 = 2;,查询类型为 type: index

index 这种类型表示 mysql 会对整个索引进行扫描(比 ALL 全表数据扫描好,毕竟索引数据较小)。不过,index 方式依旧不如 ref 高,mysql 会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。

使用 Explain 分析 Select 语句
有时候我们不想直接执行 Select 语句,而是想知道 MySQL 执行此语句的细节,比如是否使用了索引,就可以在这条 Select 语句前面加上 Explain 关键字,然后 MySQL 会返回一张表给你,像这样:

Explain 返回的字段的意义:

  • id:执行编号,标识 select 所属的行。
  • select_type:select 语句的类型(简单、复杂)。
  • table:该行数据属于哪张表,后面的数字表示执行到第几步。
  • type:表示 MySQL 在表中找到所需行的方式,又称“访问类型”。
  • possible_keys:表中可供 MySQL 使用的索引列表,NULL 表示没有。
  • key:MySQL 实际选择/使用的索引名称(possible_key 中的其中一个)。
  • key_len:查询时使用这个选定的索引的索引长度(单位字节),越短越好。
  • ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。
  • rows:MySQL 为了找到所需的行而需要读取的行数,这是一个估算值,不精确。
  • Extra:关于 Select 查询语句的额外信息,比如 using index、filesort 等。

其中最重要的就是 type 字段了,常见的类型有(性能从差到好):ALLindexrangerefeq_refconstsystemNULL。相关的解释:

  • ALL:全表扫描(Full Table Scan),效率最低。
  • index:全索引扫描(Full Index Scan),比 ALL 效率高,因为通常索引文件比数据文件小。
  • range:只检索给定范围的行,使用一个索引来选择行。这种情况一般出现在 where 条件语句中包含 between...and...<>in 等的范围查询。这种范围检索给全表检索性能要好,因为它只需要开始于索引的某一点,结束于另一点,不需要全表扫描。
  • ref:非唯一性索引扫描(普通索引、全文索引、最左前缀),返回匹配某个单独值的所有行。因为不是唯一性的索引,所以它可能返回多个符合条件的行。
  • eq_ref:唯一性索引扫描(唯一索引、主键索引),对于每一个索引键,表中只有一条记录与之匹配。
  • const:常量查询,在整个查询过程中这个表最多只会有一条匹配的行,比如主键 id=1 就肯定只有一行,只需读取一次表数据便能取得所需的结果,且表数据在分解执行计划时读取。
  • system:表中只有一行记录,这是 const 的一个特例,当表只有一行记录时会出现。
  • NULL:优化过程中就已经得到结果,不需要访问表或索引。

表的关系

在数据库中,将实体与实体的关系反应到表的设计上来,可以细分为 3 种,分别为:一对一(1:1),一对多(1:N)(或多对一(N:1))和多对多(N:N)。在此,所有的关系都是指表与表之间的关系。

一对一:即一张表的一条记录只能与另外一张表的一条记录相对应,反之亦然。

例如,咱们设计一张「个人信息表」,其字段包含:姓名、性别、年龄、身高、体重、籍贯和居住地等。

ID 姓名 性别 年龄 身高 体重 籍贯 居住地
1 Charies 18 182 75 中国 北京
2 Swift 18 172 50 美国 纽约

如上表所示,基本满足咱们的要求,其中姓名、性别和年龄属于常用数据,但是身高、体重、籍贯和居住地为不常用数据。如果每次查询都要查询所有数据的话,那么不常用数据就会影响效率,而且又不常用。因此,咱们可以将常用的数据和不常用的数据分离存储,即分为两张表,例如:
表 1:常用数据

ID 姓名 性别 年龄
1 Charies 18
2 Swift 18

表 2:不常用数据

ID 身高 体重 籍贯 居住地
1 182 75 中国 北京
2 172 50 美国 纽约

如上面表 1 和表 2 所示,通过字段 ID,表 1 中的一条记录只能匹配表 2 中的一条记录,反之亦然,这就是一对一的关系。

一对多:即一张表中的一条记录可以对应另外一张表中的多条记录,但是反过来,另外一张表中的一条记录只能对应这张表中的一条记录。

例如,咱们设计「国家城市表」,其包含两个实体,即国家和城市。

表 3:国家表

COUNTRY_ID 国家 位置
1 中国 亚洲
2 美国 北美洲
3 俄罗斯 亚洲和欧洲

表 4:城市表

CITY_ID 城市 国家
1 北京 中国
2 深圳 中国
3 纽约 美国
4 莫斯科 俄罗斯

如上面表 3 和表 4 所示,通过字段国家,表 3 中的一条记录可以匹配表 4 中的多条记录,但反过来,表 4 中的一条记录只能匹配表 3 中的一条记录,这就是典型的一对多的关系。

多对多,即一张表中的记录可以对应另外一张表中的多条记录,反过来,另外一张表中的一条记录也可以对应这张表中的多条记录。

例如,咱们设计「教师学生表」,其包含两个实体,即教师和学生。

表 5:教师表

TEA_ID 姓名 性别
1 刘涛
2 刘亦菲
3 刘德华

表 6:学生表

STU_ID 姓名 性别
1 齐岳
2 杜康

观察上面的表 5 和表 6,咱们会发现:表 5 和表 6 的设计满足了实体的属性,但没有维护实体之间的关系,即一个老师教过多个学生,一个学生也被多个老师教过。但是无论咱们在表 5 中还是在表 6 中增加字段,都会出现一个问题,那就是:该字段要保存多个数据,并且还是与其他表有关系的字段,不符合设计规范。因此,咱们可以再设计一张「中间表」,专门用来维护表 5 和表 6 的关系。

表 7:中间表

ID TEA_ID STU_ID
1 1 1
2 1 2
3 2 1
4 3 2

观察上面的表 5、表 6 和表 7,咱们会发现增加表 7 之后,咱们维护表 5 和表 6 的关系更加方便啦!无论是想从表 5 通过表 7 查到表 6,还是想从表 6 通过表 7 查到表 5,都非常容易啦!这就是典型的多对多的关系。

设计范式

范式
范式:Normal Form,为了解决数据的存储和优化问题。

在数据存储之后,凡是能够通过关系寻找出来的数据,坚决不再重复存储,范式的终极目标是减少数据冗余。

范式是一种分层结构的规范,共 6 层,分别为 1NF、2NF、3NF、4NF、5NF 和 6NF,每一层都比上一层严格,若要满足下一层范式,其前提是先满足上一层范式。其中,1NF 是最底层的范式,6NF 为最高层的范式,也最严格。

MySQL 数据库属于关系型数据库,其存储数据的时候有些浪费空间,但也致力于节省空间,这就与范式想要解决的问题不谋而合,因此在设计数据库的时候,大都会利用范式来指导设计。但是数据库不单是要解决存储空间的问题,还要保证效率的问题,而范式只为解决存储空间的问题,所以数据库的设计又不能完全按照范式的要求来实现,因此在一般情况下,只需要满足前三种范式即可。

此外,需要知道:范式在数据库的设计中是有指导意义的,但不是强制规范

1NF
第一范式:在设计表存储数据的时候,如果表中设计的字段存储的数据,在取出来使用之前还需要额外的处理(拆分),那么表的设计就不满足第一范式,第一范式要求字段的数据具有原子性,不可再分

例如,咱们设计一个「学校假期时间表」,如下所示:

表 1:学校假期时间表

ID(P) 学校名称 起始日期,结束日期
1 哈尔滨工业大学 20170625,20170903
2 浙江大学 20170630,20170901

观察上表,咱们会发现表 1 的设计并没有什么问题,但是如果需求是查询各学校开始放假的日期呢?那显然上表的设计并不满足 1NF,数据不具有原子性。对于此类问题,解决的方案就是将表 1 进行拆分:

表 2:拆分后的表 1

ID(P) 学校名称 起始日期 结束日期
1 哈尔滨工业大学 20170625 20170903
2 浙江大学 20170630 20170901

2NF
第二范式:在数据表的设计过程中,如果有复合主键(多字段主键),且表中有字段并不是由整个主键来确定,而是依赖复合主键中的某个字段(主键的部分),也就是说存在字段依赖主键的部分的问题(称之为部分依赖),第二范式就是要解决表设计中不允许出现部分依赖。

例如,咱们设计一个「教室授课表」,如下所示:

表 3:教室授课表

教师(P) 性别 课程 授课地点(P)
许仙 《如何追到心爱的女孩》 杭州西湖
白娘子 《论女人的恋爱修养》 雷峰塔
白娘子 《如何打赢与和尚之间的持久战》 金山寺

观察上表,咱们会发现:教师不能作为独立的主键,需要与授课地点相结合才能作为主键(复合主键,每个教师的某个课程只能在固定的地点上),其中性别依赖于具体的教师,而课程依赖于授课地点,这就出现了表的字段依赖于部分主键的问题,从而导致不满足第二范式。

  • 解决方案 1:将教师和性别,课程和授课地点,分成两张单独的表;
  • 解决方案 2:取消复合主键,使用逻辑主键。

在此,咱们采用方案 2 的解决方法,即取消复合主键,使用逻辑主键

ID(P) 教师 性别 课程 授课地点
1 许仙 《如何追到心爱的女孩》 杭州西湖
2 白娘子 《论女人的恋爱修养》 雷峰塔
3 白娘子 《如何打赢与和尚之间的持久战》 金山寺

3NF
第三范式:需要满足第一范式和第二范式,理论上讲,每张表中的所有字段都应该直接依赖主键(逻辑主键,代表是业务主键),如果表设计中存在一个字段,并不直接依赖主键,而是通过某个非主键字段依赖,最终实现主键依赖(把这种不是直接依赖主键,而是依赖非主键字段的依赖关系,称之为传递依赖),第三范式就是要解决表设计中出现传递依赖的问题。

以上述的添加逻辑主键后的 表3 为例:

ID(P) 教师 性别 课程 授课地点
1 许仙 《如何追到心爱的女孩》 杭州西湖
2 白娘子 《论女人的恋爱修养》 雷峰塔
3 白娘子 《如何打赢与和尚之间的持久战》 金山寺

在以上表的设计中,性别依赖教师,教师依赖主键;课程依赖授课地点,授课地点依赖主键,因此性别和课程都存在传递依赖的问题。

解决方案:将存在传递依赖的字段,以及依赖的字段本身单独取出来,形成一个单独的表,然后在需要使用对应的信息的时候,把对应的实体表的主键添加进来。

表 4:教师表

TEACHER_ID(P) 教师 性别
1 许仙
2 白娘子
3 白娘子

表 5:授课地点表

ADDRESS_ID(P) 课程 授课地点
1 《如何追到心爱的女孩》 杭州西湖
2 《论女人的恋爱修养》 雷峰塔
3 《如何打赢与和尚之间的持久战》 金山寺

表 6:进行处理后的表

ID(P) TEACHER_ID ADDRESS_ID
1 1 1
2 2 2
3 3 3

在观察上述 表 4 和 表 5,咱们会发现 TEACHER_ID 等价于教师且 ADDRESS_ID 等价于授课地点,因此其逻辑主键并没有什么实际的限制意义,咱们只需要看其具体代表的业务主键即可。咱们之所以使用逻辑主键,是因为:逻辑主键可以实现自动增长,并且数字传递比较方便,而且有利于节省空间。

逆规范化
在某些特定的环境中(例如淘宝数据库),在设计表的时候,如果一张表中有几个字段是需要从另外的表中去获取数据,理论上讲,的确可以获得想要的数据,但是相对来说,其效率低会一点。此时为了提高查询效率,咱们会刻意的在某些表中,不去保存另外一张表的主键(逻辑主键),而是直接保存想要存储的数据信息,这样的话,在查询数据的时候,这张表就可以直接提供咱们想要的数据,而不需要多表查询,但是这样做会导致数据冗余。

实际上,逆规范化是磁盘利用率和效率之间的对抗。

主键冲突

在插入数据时,如果主键对应的值已存在,那么会插入失败,这就是主键冲突。解决方法:

推荐使用方法二,因为更简单,语法上只是将 insert into 改为 replace into,这两个方式都是一样的,在没有发生主键冲突时进行正常 insert,只有在发生冲突时才会替换已有的主键的对应的值。

蠕虫复制

蠕虫复制:从已有的数据表中获取数据,然后将数据进行插入操作,数据成倍(以指数形式)的增加。

复制表结构:create table <new_table> like <old_table>,两个表的字段、结果是相同的。
蠕虫复制:insert into <tab>[(col1, col2, ...)] select {*|(col1, col2, ...)} from <tab>

简单例子:

蠕虫复制的意义

  • 从已有的数据表中拷贝数据到新的数据表;
  • 可以迅速的让表中的数据膨胀到一定的数量级,多用于测试表的压力及效率。

更新删除

更新数据(基本):update <table_name> set col1=val1, col2=val2 ... [where cond]
更新数据(高级):update <table_name> set col1=val1, col2=val2 ... [where cond] [limit N]
删除数据(基本):delete from <table_name> [where cond]
删除数据(高级):delete from <table_name> [where cond] [limit N]

其中 limit N 表示要操作的记录数量(更新、删除),比如 limit 1,表示只操作其中一条匹配的记录,即使它匹配很多条记录。如果设为 limit 0 则表示不进行任何操作,mysql 通常会立即返回。

注意,删除全表数据时,如果使用 delete from <table_name>,那么 auto_increment 的值不会改变,你必须使用 truncate <table_name> 命令来清空数据表才能恢复为初始状态,建议使用 truncate 来清空表,因为这更干净也更彻底,甚至可能更快。

高级查询

查询数据(上)

  • 基本语法: select 字段列表/* from 表名 [where 条件]
  • 完整语法: select [select 选项] 字段列表[字段别名]/* from 数据源 [where 条件] [1] [2] [3]
    • [1] = [group by 子句]
    • [2] = [order by 子句]
    • [3] = [limit 子句]

SELECT 选项
select 选项,即 select 对查出来的结果的处理方式。

  • all:默认,保留所有的查询结果;
  • distinct:去重,去除相同的查询结果。

执行如下 SQL 语句,进行测试:

字段别名
字段别名,即当数据进行查询的时候,有时候字段的名字并不一定满足需求(特别是在多表查询的时候,很可能会有同名字段),这时就需要对字段进行重命名、取别名。

基本语法:列名 [as] 别名

执行如下 SQL 语句,进行测试:

数据源
数据源,即数据的来源,关系型数据库的数据源都是数据表,本质上只要保证数据类似二维表,最终就可以作为数据源。数据源分为 3 种,分别为:单表数据源多表数据源查询语句

第 1 种:单表数据源
基本语法:select * from 表名

第 2 种:多表数据源
基本语法:select * from 表名1, 表名2...

第 3 种:查询语句(子查询)
基本语法:select * from (select * from 表名) [as] 别名

单表数据源没有什么好说的,我们用的一直都是这种。说一下多表数据源,使用多表数据源时,每个表中的一条记录都会对应另外的表的全部记录,比如两个表,都是 10 条记录,查询结果就会有 10 * 10 条,如果有三个这样的表,那么插查询结果就有 10 * 10 * 10 条,所以比较浪费资源,应尽量避免。

第 3 种子查询,允许嵌套,看一个无聊的例子(注意子查询只能来自 select 语句,必须指定别名):

这里详细说一下多表查询,假设存在两个表,test0 和 test1,它们的数据分别为:
test0

test1

select * from test0, test1 的结果

没有使用 where 过滤的情况下,存在很多重复的数据,仔细观察可以发现两个表的同时查询的结果其实就是两个表的结果行的笛卡尔积。所谓笛卡尔积,如果集合 X 是 13 个元素的点数集合 { A, K, Q, J, 10, 9, 8, 7, 6, 5, 4, 3, 2 },而集合 Y 是 4 个元素的花色集合 {♠, ♥, ♦, ♣},则这两个集合的笛卡儿积(X x Y)是有 52 个元素的标准扑克牌的集合 { (A, ♠), (K, ♠), …, (2, ♠), (A, ♥), …, (3, ♣), (2, ♣) }。通俗的讲,就是集合 X 与集合 Y 中的元素的对应关系是 N:1。但实际上,像上面那样的多表查询其实没有什么实际的意义,因为我找不到我要的信息,而且如果两个表的数据量稍微大一点,就会非常耗资源,比如 X 表有 10000 条记录,Y 表有 10000 条记录,那么同时查询 X 表和 Y 表的结果表就有 100000000 行,如果再来一个 10000 条记录的表 Z,那么就是一万亿条。如果需要获取的数据真的需要来自多张表,那么使用 join 比较好一点,可以设置连接条件。

通过 where 对结果表进行过滤

当然,强迫症患者可能会想去掉两个一样的 id 字段:

不过,等你会了 join 连接查询后,有更加优雅的方法实现它:

查询数据(中)
where 子句
where 字句:用来判断数据和筛选数据,返回的结果为 0 或者 1,其中 0 代表 false,1 代表 true,where 是唯一一个直接从磁盘获取数据的时候就开始判断的条件,从磁盘中读取一条数据,就开始进行 where 判断,如果判断的结果为真,则保存,反之,不保存。

判断条件
比较运算符:><>=<==!=<>innot inbetween andlikenot likeregexpnot regexp
逻辑连接符:&&||!andornot(建议使用 and、or、not,不要用前面的)。

例如:查询表中 id 为 1、3、5 的数据:

例如:查询 id 在 1 ~ 10 之间的记录:

注意,MySQL 中的 between V1 and V2 是包含边界 V1 和 V2 的,但是 not between V1 and V2 是不包含边界 V1 和 V2 的,还有,不同数据库对 between and 的边界处理不一样,为了省时省力,建议改写为 <> 等形式。还有,between and 之间的数值必须是 V1 <= V2 的,否则返回空集。

group by子句
group by:根据表中的某个字段进行分组,即将含有相同字段值的记录放在一组,不同的放在不同组。

基本语法:group by 字段名
执行如下 SQL 语句,进行测试:

观察 group by sex 的输出,会发现表 employees 在分组过后,数据“丢失”了,变得只有两条数据!实际上并非如此,产生这样现象原因为:group by 分组的目的是为了(按分组字段)统计数据,并不是为了单纯的进行分组而分组。为了方便统计数据,SQL 提供了一系列的统计函数,例如:

  • max():统计每组中的最大值;
  • min():统计每组中的最小值;
  • avg():统计每组中的平均值;
  • sum():统计每组中的数据总和;
  • count():统计每组中的总记录数。

执行如下 SQL 语句,进行测试:

其中,count() 函数里面可以使用两种参数,分别为:*表示统计组内记录的数量;字段名表示统计组内对应字段的非 null 记录的数量。此外,使用 group by 进行分组之后,展示的记录会根据分组的字段值(上面的 sex)进行排序,默认为升序。当然,也可以人为的设置升序和降序。例子:

函数 group_concat(字段名) 可以对分组的结果中的某个字段值进行字符串连接(逗号隔开)。例如:

查询数据(下)
having子句
having字句:与where子句一样,都是进行条件判断的,但是where是针对磁盘数据进行判断,数据进入内存之后,会进行分组操作,分组结果就需要having来处理。思考可知,having能做where能做的几乎所有事情,但是where却不能做having能做的很多事情。

个人通俗理解:where 是在数据(记录)从磁盘中取出时的条件过滤,而 having 则是在进行 group by 分组后的结果行(记录)的条件过滤,结合例子更容易理解:

having 中可以使用上面介绍的聚合函数(统计函数),例子:

order by子句
order by:根据某个字段进行升序(asc)或者降序(desc)排列,字符串比较依赖校对集。

基本语法:order by [asc|desc],asc 为升序,默认值,des 为降序。

执行如下 SQL 语句,进行测试:

order by 支持多字段排序,其意义是当第一个字段比较后两个记录“相同”,无法判断谁前谁后,这是 MySQL 就会再将这两个记录按照第二个字段进行比较,分出先后,以此类推。看例子:

limit子句
limit子句:用于限制输出结果中的记录数量,或者取其中的某些记录。

基本语法:limit [offset] length,其中:offset 为偏移量(从 0 开始),length 为取的长度。如果省略 offset,则默认从第 0 条记录开始取,也即 offset 默认等于 0。用法简单就不演示了。

连接查询

连接查询:将多张表按照某个指定的条件进行数据的拼接,其最终记录数可能变化,但列数一定会增加。
连接查询的意义:在用户查询数据的时候,需要显示的数据来自多张表
连接查询的关键字join,使用方式为:左表 join 右表
连接查询子句的位置都是位于 select * from 后面,即数据表所在的位置。
连接查询的分类:在 SQL 中将连接查询分为 3 类:内连接外连接交叉连接

由于后面会频繁使用两个表 test0 和 test1,所以这里先给出它们的具体数据:

交叉连接cross join,返回两个表的笛卡尔积。多表查询的结果也是两个表的笛卡尔积,不要奇怪,交叉连接有两种形式,一种是显式的 join 语法,一种则是前面的多表查询:

  • 显式交叉连接:select * from testA cross join testB;
  • 隐式交叉连接:select * from testA, testB;

交叉连接本身不应该用任何谓词来过滤联接表中的行(也就是所谓的 ON 连接条件)。但是可以使用 WHERE 子句过滤交叉连接的结果(废话,当然可以),交叉连接 + WHERE 子句 可以实现与内连接 + ON 子句 的相同效果(但是效率不一样,明显内连接更优)。在 SQL:2011 标准中,交叉连接是可选F401“扩展连接表”包的一部分,正常用途是用于检查服务器的性能,也就是说交叉连接并没有多少实际的用途(PS:其实交叉连接也可以看作是内连接的一种)。

交叉连接的例子:

内连接inner join
内连接图示 - 维基百科
与交叉连接的区别在于,内连接要指定连接条件(ON 条件),其效果和交叉连接 + WHERE 一样。但是效率明显更高,为什么呢?join 操作会产生中间表,这个中间表一般都保存在内存中(如果比较大则放在磁盘中),而 ON 关键字是在产生中间表前进行过滤的WHERE 则是在产生中间表后进行过滤的。所以哪个效率高显而易见,虽然它们都能实现相同的结果。内连接也有隐式和显式之分,但是隐式的内连接语法已经不是最佳实践了,不要使用:

  • 显式内连接:select * from testA inner join testB on testA.id = testB.id;,inner 可选
  • 隐式内连接:select * from testA, testB where testA.id = testB.id;,等效,但不推荐使用

内连接的例子(相当与“交集”):

内连接有三种形式:等值连接不等连接自然连接(等值连接的特殊形式)。

等值连接,顾名思义,就是使用 = 比较符的 ON 条件连接

不等连接:所谓不等连接就是使用除 = 比较符外的 ON 连接条件(如 <>>=

自然连接:自然连接是等值连接的特殊形式,它也是 SQL 标准中可选的,可以理解为语法糖。

当然,我们也可以使用 as 来给字段定义别名,给表定义别名,例子:

外连接outer join。外连接可分为左外连接右外连接全外连接。外连接相当于并集(仅指全外连接,当然左外连接和右外连接也差不多)。外连接也有所谓的隐式语法,但 SQL 标准只支持显式语法!

左外连接left outer join
左外连接 - 维基百科
左外连接以 outer join 左边的表为主表,结果表中只会出现主表中存在的记录行,右表中多余的记录行会被忽略,而缺少的记录行则被自动以 NULL 值填充。例子(注意,OUTER 关键字是可选的):

右外连接right outer join
右外连接 - 维基百科
右外连接以 outer join 右边的表为主表,结果表中只会出现主表中存在的记录行,左表中多余的记录行会被忽略,而缺少的记录行则被自动以 NULL 值填充。例子(注意,OUTER 关键字是可选的):

全外连接full outer join(mysql 不支持,但可以使用 union 模拟)
全外连接 - 维基百科
全外连接同时结合了左外连接、右外连接的特点(真正的“补集”),但是 mysql 不支持 full outer join 语法,不过我们仍然可以使用 union 联合关键字来实现“全外连接”,还是前面的那两张表:

外键详解

外键foreign key,外键是两个字段(或者说是两个表)之间的一个完整性约束,所谓约束也可以理解为建立在两个表(这两个表为父子关系)之间的关系。因为 mysql 中的外键所在的列必须为一个索引,所以这里可以简单的理解为,外键是两张表的两个索引之间的关系,这个关系是有一个“方向”的,如果某个索引指向另一个索引,那么这个索引就是它所属表的一个外键,而这个被指向的索引在它的表中必须为一个主键索引唯一索引。同时,这两个表之间也是有关系的,外键所在的表为“子表”外键指向的表为“父表”。但是绝大多数情况下,这两个索引都是单列索引,因此我们也可以将它们看作是两个字段之间的关系,SQL 规定,外键所在的字段的数据类型必须严格与该外键所指向的主键或唯一键的字段的数据类型一致,否则创建外键时会报错。同一个表中允许创建多个外键,外键具有保持数据完整性和一致性的机制,对业务处理有着很好的校验作用。注意,每个外键其实都是有名字的,但是我们一般都不会手动去指定这个名字,因为同一个数据库中的外键名是共享一个命名空间的,很容易出现命名冲突,如果没有指定外键名,那么系统会自动为其指定一个唯一的名字,方便省事。外键通常都只是用来引用(指向)同一个数据库中的其他表中的主键、唯一键,不过好像 mysql 支持引用其他数据库中的表中的主键、唯一键。外键也可以引用当前外键所在的表中的其他字段(主键、唯一键),称为自引用,不过我修行尚浅,还不能理解它们的实际意义。

从技术上讲,外键所在的字段是不需要创建索引的,但是如果这样的话,在更新/删除父表中主键或唯一键的值/记录时,因为要同步更新/删除子表中的值/记录,而又因为没有索引,所以必须进行全表扫描,看看有没有匹配的数据行,这个操作是非常很耗时的。所以很有必要在外键上创建索引(MySQL 对外键字段的索引类型没有要求,只要是索引就行,比如主键索引),这样就不需要对子表进行全表扫描了,只需搜索索引数据即可。当然如果父表中被外键引用的字段如果不会变动,那么就不需要索引了(这里是指其他数据库,当然 MySQL 是强制要求建立索引的)。不过这种情况很少,创建外键的目的基本就是为了级联操作(cascade),否则意义不大。因此 MySQL 干脆规定了外键所在的列必须创建索引。但在 Oracle 等数据库中,没有此硬性规定。

这是某教程中关于外键的定义:外键:foreign key,外面的键,即不在自己表中的键。如果一张表中有一个非主键的字段指向另外一张表的主键,那么将该字段称之为外键。每张表中,可以有多个外键。实际上它有两个错误,外键所在的字段其实是可以为主键的,实际上外键对其所在的字段没有什么特殊要求除了需要建立索引外(注意主键其实也是一种索引)。另一个错误则是外键不是必须指向主键,外键也可以指向唯一键。我们来通过两个例子来证明一下:

例一,外键所在的列其实可以为主键列

例二,外键也可以指向唯一键

新增外键
外键既可以在创建表的时候增加,也可以在创建表之后增加(但是要考虑数据的问题)。

第 1 种:在创建表的时候,增加外键
基本语法:foreign key (外键字段) references 外部表名 (主键字段)

因为外键所在的列没有索引,所以 MySQL 会自动在它上面创建一个普通索引,再创建外键。

第 2 种:在创建表之后,增加外键
基本语法:alter table 表名 add [constraint 外键名] foreign key (外键字段) references 外部表名 (主键字段)
执行如下 SQL 语句,进行测试:

删除外键
基本语法:alter table 表名 drop foreign key 外键名字
执行如下 SQL 语句,进行测试:

不知道你发现没,外键的自动名称其实是有规律的,表名_ibfk_N,N 从 1 开始。

外键作用
首先,给出父表和子表的定义:

  • 父表,外键指向的表;
  • 子表,拥有外键的表。

外键默认的作用有两个,分别对子表和父表进行约束

再谈外键中的父表和子表,将父表和子表看作是两片叶子,父表也就是父叶子,子表也就是子叶子(子叶子长在父叶子上,我描述的可能不准确,自己意会),而子表上的外键(foreign key)其实就相当于子叶子上面的枝条,它是长在父叶子上面的(reference)。如果需要长出子叶子(对应的数据库操作就是在子表中插入一条不存在于父表中的记录),那么必须先长出对应的父叶子才行(也就是说在往子表插入新数据前必须先往父表中插入对应的新数据才行,否则报错)。在我们需要剪掉这些枝叶时,可以剪掉子叶子(对应删除子表中的一条记录),也可以剪掉父叶子(也就是删除父表中的一条记录,很容易知道,它会同步删除对应子表中的记录)。当你无法理解子表与父表之间的约束细节时,请联想到这个例子。

将叶子的例子延伸到数据库中则表现为:

  • 对于子表:insertupdate 操作,如果操作后的记录在父表中找不到匹配,则操作失败,否则操作成功。很好理解,因为必须先有爸爸才能有儿子呀。在子表上进行 delete 操作没有限制。
  • 对于父表:updatedelete 操作,根据 foreign key 的定义决定其约束行为。分别为:
    • CASCADE,级联操作:也就是所谓的同步操作,对于 delete,表现为同步 delete 子表中的记录,对于 update,表现为同步 update 子表中的记录。
    • SET NULL,置为空值:当父表 update/delete 时,将子表中对应的外键字段设为 NULL。当然前提是子表中的外键字段允许设置为 NULL 值,否则会导致 ERROR。
    • RESTRICT,限制操作:当父表 update/delete 时,MySQL 会返回错误提示,不会进行操作。
    • NO ACTION,没有动作:在 MySQL 中等同于 RESTRICT。NO ACTION 是标准 SQL 中的关键字,某些数据库系统具有延迟检查,NO ACTION 是延迟检查。在 MySQL 中,立即检查外键约束,因此 NO ACTION 与 RESTRICT 相同。
    • SET DEFAULT,设置默认值:MySQL 识别此操作,但是 InnoDB 不支持 SET DEFAULT 动作。

因此 MySQL 中只有 4 种约束,除了两个相同的动作,实际上只有 3 个约束限制。默认为RESTRICT

父表中的 update/delete 操作约束行为可以通过 foreign key 的两个属性设置:

  • on update:设置对父表进行 update 操作时的约束行为。
  • on delete:设置对父表进行 delete 操作时的约束行为。

具体语法:foreign key (id) references test0 (id) [on update ACTION on delete ACTION]

默认动作

级联操作

置为空值

关于外键约束的设置约定:

  • on update,一般都设置为 cascade,进行同步更新操作,方便,通常符合业务要求。
  • on delete,如果需要防止误操作则用 RESTRICT,如果需要同步删除则用 cascade。

我们会发现外键的功能非常强大,能够进行各种的约束,也正是由于外键这种约束的强大性降低了开发语言对数据的可控性(外键需要额外的维护开销),因此在实际的开发中,很少使用外键来处理数据。当然这不是绝对的,并不是说外键一无是处,如果数据量比较小,那么外键还是很有用的,毕竟使用开发语言维护这种约束关系也要开销啊,而且你还不一定做的比数据库好呢。

观点 A
数据库的诸多设计,帐号,权限,约束,触发器,都是为 C/S 结构设计的,是以 C 端不可信做为假设前提的。B/S 模式安全边界前移到 Web 服务层,应用与数据库之间是可信的,应用自行完成这些功能更加灵活。所以能不用就不用。

  1. 互联网行业应用不推荐使用外键:用户量大,并发度高,为此数据库服务器很容易成为性能瓶颈,尤其受 IO 能力限制,且不能轻易地水平扩展;若是把数据一致性的控制放到事务中,也即让应用服务器承担此部分的压力,而应用服务器一般都是可以做到轻松地水平的伸缩。
  2. 如果数据库服务器的性能不是问题,或者数据量不是很大,那么还是可以考虑外键的。因为使用外键可以降低开发成本,借助数据库产品自身的触发器可以实现表与关联表之间的数据一致性和更新;另外,还可以做到开发人员和数据库设计人员的分工,可以为程序员承担更多的工作量。

为何说外键有性能问题

  1. 数据库需要维护外键的内部管理;
  2. 外键等于把数据的一致性事务实现全部交给数据库服务器完成;
  3. 当做一些涉及外键字段的增,删,更新操作之后,需要触发相关操作去检查,不得不消耗资源;
  4. 外键还会因为需要请求对其他表内部加锁而容易出现死锁情况;

观点 B
1、使用外建,简单直观,可以直接在数据模型中体现,无论是设计、维护等回有很大的好处,特别是对于分析现有的数据库的好处时非常明显的。前不久我分析了一个企业现有的数据库,里面的参照完整性约束有的是外键描述,有的是用触发器实现,感觉很明显。当然,文档里可能有,但是也可能不全,但是外键就非常明显和直观。

2、既然我们可以用触发器或程序完成的这个工作(指参照完整性约束),DBMS 已经提供了手段,为什么我们要自己去做?而且我们做的应该说没有 RDBMS 做得好。实际上,早期的 RDBMS 并没有外键,现在都有了,我认为数据库厂商增加这个功能是有道理的。从这个角度来说,外键更方便。

其他杂碎知识

使用的时候谁做为谁的外键,主要从以下两点考虑:

  1. 删除是如何相互影响的,删除记录受约束的那个是父表,不受约束的那个是子表;
  2. 记录必须先存在的是父表;

外键的两种用途:

  1. 减少重复数据。表 A 中拥有外键,表 B 的数据基本是不允许删除的,这时选择对 INSERT 和 UPDATE 强制关系即可。
  2. 增加一个从属表。如果表 A 删除一条记录时,表 B 中也随着删除一条相关联的记录,那么外键关系中,表 A 的主键是表 B 的外键。这种关系,实际上表 B 是表 A 的从属表(即表 A 是父表),选择对 INSERT 和 UPDATE 强制关系时,如果向表 B 中插入数据,表 A 中必须已经存在对应的记录。选择级联删除相关的字段时,删除表 A 中的一条记录,就会删除对应的表 B 中的一条记录。

我的观点是,外键在初始阶段能加的都加上,只有迫不得已的时候才 disable 或 drop 掉。遇到性能瓶颈的时候,尽量采用其它方式调优,而不要轻易牺牲掉外键。有外键约束的时候,写程序的确会有约束,但从直觉上说这种约束一定程度上揭示了设计或实现上不合理的地方。带着外键写出来的应用更倾向于严谨。产品上线之前如果确实需要通过牺牲外键达到性能上的优化,再捡相对不重要的外键废弃掉。

支持外键的

  1. 你的程序再严谨也有可能出现 BUG。你自己判断不如交给数据库判断,它做得又快又好。大多数人的程序没有考虑并发问题。一旦考虑了就得手工加锁,效率很低。数据可能绕过你的应用程序进入数据库。
  2. 性能问题:难道你自己做就没有开销?一个外键判断分摊到事务级别,开销可以忽略,用户完全没有察觉。如果是批量导入数据,可以先暂时屏蔽外键,事后用 NOVALIDATE 选项快速恢复,前提是你的数据是干净的。
  3. 也有人提到了如果 100 张表可能需要建立 300 个约束,导致性能太差。我要说的仍然是,是否这 300 个外键约束都是业务必须的,如果是,没有办法这就是必须要加的,如果不是,那么大可不必在所有的地方都增加外键。如果在程序中仅对其中的 5、6 张表的 10 来个外键约束进行判断,然后和数据库中的 300 个外键去比较,并评价 Oracle 的外键性能太差,恐怕是有失公允的。

反对外键的
的确外键在大系统中用的很少,在开发初级,设计数据库的时候一般会加入外键,以保证系统设计的完整性和业务需求的完整性,也便于开发人员了解业务规则,在程序中加以控制,很多大系统在系统稳定后,会逐步将外键去掉,以保证性能,将太多的功能强加于数据库,虽然说数据库很强大,但是毕竟很多人不信任数据库的能强大到什么都能干的地步。所以在一个大系统中外键见的少也不足为奇,小系统就无所谓了,用不用外键取决于设计人员,这样的系统也随处可见。

正方观点

  1. 由数据库自身保证数据一致性,完整性,更可靠,因为程序很难 100% 保证数据的完整性,而用外键即使在数据库服务器当机或者出现其他问题的时候,也能够最大限度的保证数据的一致性和完整性。
  2. 有主外键的数据库设计可以增加 ER 图的可读性,这点在数据库设计时非常重要。
  3. 外键在一定程度上说明的业务逻辑,会使设计周到具体全面。

反方观点

  1. 可以用触发器或应用程序保证数据的完整性
  2. 过分强调或者说使用主键/外键会平添开发难度,导致表过多等问题
  3. 不用外键时数据管理简单,操作方便,性能高(导入导出等操作,在 insert, update, delete 数据的时候更快)eg:在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,当存在外键约束的时候,每次要去扫描此记录是否合格,一般还不止一个字段有外键,这样扫描的数量是成级数的增长!我的一个程序入库在3个小时做完,如果加上外键,需要28个小时!

最终结论

  1. 在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
  2. 不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后各个应用通过这个层来访问数据库。
  3. 用外键要适当,不能过分追求。
  4. 不管是否加外键,一定要索引。

联合查询

联合查询union:将两个 select 语句的结果表进行拼接(前提是两个结果表的字段数相同,union 对字段类型无要求,单纯拼接),保留第一个表的字段名,union 默认会去除两个表中的重复数据。

语法select ... union {all | distinct} select ...,其中 all 表示保留两张表中的所有记录,distinct 表示去除两个表中重复的记录,distinct 为默认值,一般我们也无需指定这个参数。

all、distinct 区别:

联合查询的意义有两种:

  • 查询同一张表,例如查询学生信息,要求男生按年龄升序排序,女生按年龄降序排序;
  • 多表查询,多张表的结构是完全一样的,保持的数据结构也是一样的。

例子,男生按照年龄升序,女生按照年龄降序:

子查询

子查询sub query,子查询是在某个查询结果之上进行的,一条 select 语句内部包含了另外一条 select 语句(可以理解为嵌套查询,其实和 shell 中的变量、命令替换差不多的概念)。

表 test0 存储知名网站信息(网站名、网站地址),表 test1 则存储对应的网站的具体信息(创始人、所在省、所在市),我们来看看具体的数据:

先来一个感性的认识,假设我要查询淘宝网的创始人是谁,就可以使用子查询:

怎么理解呢,一步一步来,我要查询的信息是创始人,那么创始人这个字段在哪个表,当然是 test1 了,那么就先写 select founder from test1 where id = ???,那么这个 id 从哪可以知道呢?显然,是从 test0 表中查询 name=’淘宝’ 对应的 id 了,也即 select id from test0 where name='淘宝',再将它们嵌套起来(联想到 shell 脚本就很好理解)。注意,子查询必须使用圆括号 () 括起来,和 shell 中的 $() 一样。

子查询分类
子查询有两种分类方式,一种是按照子查询的结果进行分类,一种是按照子查询的使用位置进行分类。

  • 按结果分类:按照子查询得到的数据进行分类(理论上任何一个查询结果都可以看作一个二维表)
    • 标量 子查询:1 row 1 column,一行一列
    •   列 子查询:N row 1 column,N >= 1,一列多行
    •   行 子查询:1 row N column,N >= 1,一行多列
    •   表 子查询:N row N column,N >= 1,多行多列
    • 每个行子查询列子查询 也是一个表子查询,但是反正却不是
    • 每个标量子查询 也是一个行子查询 和一个列子查询,反之则不是
  • 按位置分类:这里的位置是指子查询表达式的位置,比如:位于 from 后的就是 from 子查询。
    • from 子查询:将内层的查询结果供外层再次查询
    • where 子查询:将内层的查询结果作为外层的比较条件
    • exists 子查询:把外层查询结果拿到内层,看内层的查询是否成立

使用子查询原则

  1. 一个子查询必须放在圆括号中。
  2. 将子查询放在比较条件的右边以增加可读性。
  3. 子查询中一般不包含 ORDER BY 子句,没有意义,除非使用 order by + limit 用来取部分记录。
  4. 在子查询中可以使用两种比较条件:单行运算符(>, >=, <=, <, =, !=, <>)和多行运算符(IN, ANY, ALL)。

表子查询
表子查询一般用在 where 后面,当作一个二维表使用。但是必须使用 as 来定义一个别名:

标量子查询
查询淘宝网的具体信息,创始人、所在省、所在市:

列子查询
查询所有公司总部的位于北京的网站(province):

因为列子查询返回一列多行的结果表,因此也可以将列子查询的结果理解为一个数组,因此不能使用 =!=<><<=>=> 等标量比较运算符,只能使用 inany(同义词 some)、all 这些运算符。in 表示只要操作数与结果列表中的某个值”相等”就算符合条件,anyall 则需要配合具体的比较操作符来明确它的实际意义。any/all 的用法:col_name 比较符 any/all(select ...)= any 等价于 in。any 表示只要有一个符合条件就返回 true,all 表示只有全部都符合条件才会返回 true。比如 > any 表示大于结果列表中的任意一个值就返回 true,而 > all 则表示要大于结果列表中的全部值才返回 true,其实就相当于要大于结果列表中的那个最大值。来看几个简单的例子:

行子查询
查询 age 和 height 都是最大的学生的信息:

exists 子查询
exists (select ...) 表达式放在 where 子句中,当 exists 表达式返回 true 时,说明当前这条记录符合条件(出现在最终结果表中),当 exists 表达式返回 false 时,说明当前这条记录不符合条件(不会出现在最终结果表中),那么什么时候 exists 表达式返回 true 呢?很简单,只要子查询的结果不是 Empty Set 就可以(也就是说子查询中只要有一条记录,整个 exists 表达式的值就为 true,否则为 false)。因为 exists 表达式中的子查询不受外部父查询的影响,所以只要 exists 表达式返回 true,那么父查询就正常进行,如果 exists 表达式返回 false,那么父查询返回空集。

exists 子查询的常见用途:查询罚过款的驾驶员信息。我们来看一个无聊的例子吧:

视图

视图view,是一种有结构(有行有列,二维表),但没有结果(结构中不真实存放数据)的虚拟表,虚拟表的结构来源不是自己定义的,而是从对应的基表(视图的数据来源)中产生的(select 语句)。

创建视图
create [or replace] [algorithm = {merge|temptable|undefined}] view `view_name` as <select_statement>

  • or replace:如果当前视图已存在,则使用新视图替换已有的视图,未指定此选项时则报错。
  • algorithm:使用什么视图算法(视图类型),undefined 表示由系统自动选择,merge 算法会被 mysql 优先考虑,只有当无法创建 merge 视图时才会使用 temptable 视图,这也是默认值。
    • merge:性能好,优先考虑,基本原理可以理解为 C 语言中的宏替换,基本没有开销。但是对视图的定义(也就是 select 语句)有要求,如果不符合要求则无法创建 merge 类型的视图。merge 视图可以进行 select、insert、update、delete 操作,会影响到对应的 base 表。
    • temptable:使用临时表来存储视图的结果(select 语句的执行结果),此后对视图的 select 操作都是对这个临时表进行操作的。注意此类型的视图是无法修改的(insert、update、delete),只能使用 select 进行查询操作。而且临时表中是没有索引的,所以要尽量避免。

注意,虽然 merge 视图允许进行更新操作(insert、update、delete),但是视图最常用的也就是 select 操作,你可以把视图看作为 C/C++ 中的宏定义,主要是为了方便而已。

视图根据数据来源,可以分为单表视图多表视图。使用多表视图时,不允许结果表中的字段同名。

还有一点需要说明一下,视图中的数据如果来自某个表(或多个),那么这些表就是此视图的基表

使用视图
视图就是一个虚拟的表(重启数据库实例后视图依旧存在,不会消失),你完全可以将视图当作一个表来使用,比如进行 select、insert、update、delete 操作,但是最多的其实也就是 select 操作。

因此,我们可以使用 show tables 来查看当前数据库中的所有表(包括视图);使用 show create table VIEW_NAME 来查看视图的创建语句,但是更推荐使用 show create view VIEW_NAME 来查看,虽然它们没有区别;此外还可以使用 desc VIEW_NAMEshow columns from VIEW_NAMEshow full columns from VIEW_NAME 查看视图的字段等详细信息。

查询视图的结果和查询创建视图时 as 后面的 select 语句的结果完全相同。因此,我们也可以认为:创建视图,就是给一条 select 语句起别名,或者说是封装 select 语句。看例子:

修改视图
重命名:与重命名 table 一样,即 rename table VIEW_OLD to VIEW_NEW
其它alter [algorithm={merge|temptable|undefined}] view VIEW_NAME as SELECT_STATEMENT

删除视图
drop view VIEW_NAME1[, VIEW_NAME2 ...],和删除 table 一样的语法。

视图意义

  • 视图可以节省 SQL 语句,将一条复杂的查询语句用视图来进行封装,以后可以直接对视图进行操作;
  • 视图往往在大型项目中使用,而且是多系统使用,可以对外提供有用的数据,但是隐藏关键(或无用)的数据;
  • 视图是对外提供友好型的,不同的视图提供不同的数据,就如专门对外设计的一样;
  • 视图可以更好(或者说,更容易)的进行权限控制。
  • 视图有时会对提高效率有帮助。临时表几乎是不会对性能有帮助,是资源消耗者。
  • 视图最重要的功能就是查询,其他如增、删、改的操作一般不会使用,也不建议通过视图来操作基表的数据。

如果 select 语句包含以下内容,那么视图将不能更新或只能使用 temptable 算法

  • 这里指的可更新是指能进行 insert、update、delete 等 DML 操作
  • 要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系
  • 聚集函数或窗口函数(SUM(), MIN(), MAX(), COUNT(),等等)
  • DISTINCT
  • GROUP BY
  • HAVING
  • UNION or UNION ALL
  • Subquery in the select list
  • 除了 inner join 外的 join 连接
  • ALGORITHM = TEMPTABLE(临时表总是使视图不可更新)
  • 注意,inner join 的多表视图,一次只能更新视图中的单个表

备份

MySQL 中有两种常用的存储引擎(也称为表类型),即 MyISAMInnoDB(默认)。这两种表的备份/还原方式有些不同,MyISAM 存储引擎的表在对应的数据库文件夹下,有 3 个以表名开头的文件,即 table_name.frm(表的结构)、table_name.MYD(表的数据)、table_name.MYI(表的索引),因此我们可以直接拷贝这 3 个文件就能备份一个 MyISAM 表,同理,还原时直接将这 3 个文件放进对应数据库目录即可。但是 InnoDB 类型的表却有些不同,如果使用 共享表空间,那么所有数据库的所有表的数据和索引都保存在 /var/lib/mysql/ibdataN 文件中,每个表的结构定义文件仍然保存在 /var/lib/mysql/db_name/table_name.frm(因为这是 mysql 的规定,不论什么存储引擎,都必须存在 frm 文件,不同系统中的 mysql 的 frm 文件格式是一样的,表的 frm 文件其实就是该表的元数据文件),这时候你就无法再使用 MyISAM 这样的“物理备份”方法了。如果使用 独立表空间(建议),那么每个表在对应的数据库目录下,也会存在 2 个以表名开头的文件:table_name.frm(结构定义)、table_name.ibd(数据、索引),虽然数据和索引没有存储在 $data/ibdataN 文件中,但是你仍然不能直接拷贝这两个文件来进行表的备份、恢复。因为还有其他数据存在别的文件中(具体是什么我也不懂,总之你记住 InnoDB 类型的表不能这么做就行了)。那 InnoDB 表要怎么备份呢?最常用的就是使用 mysql 官方提供的 mysqldump 工具了,mysqldump 是“逻辑备份”,前面所说的物理备份是指单纯的拷贝文件进行备份的方式(速度快,但是局限性大),而这里的逻辑备份是指将数据转换为对应的 SQL 语句(建库、建表、插入数据等等),然后将这些 SQL 语句保存到文件,即完成备份。要恢复时,只需在 mysql 客户端中执行这些 SQL 语句就可以了(速度慢,但是兼容性强,所有类型的表都可以使用此方式进行备份)。

MyISAM 物理备份
创建两个数据库 old_db、new_db,用于测试的表为 test。

然后,我们进入 /var/lib/mysql/old_db 目录,可以看到这些文件:

现在,我们将 test.* 文件 mv 到 new_db 目录下,看看会有什么结果:

打开 mysql,会发现 old_db 数据库下没有了 test 表,它在 new_db 数据库中:

select 没有问题,我们来试试 insert 新数据进去:

也没问题,好,我们再将 test.* mv 回 old_db 目录下:

再使用 select 查询一下 test 表中的数据,你会发现一个问题:

我们刚才在 new_db 中插入的数据 www.tumblr.com 不见了,难道是丢失了?其实不是,造成这个现象的原因是因为 mysql 的查询缓存(query cache),在第二次执行相同的 sql 查询语句时,mysql 会直接从缓存中取出数据,而不会去查询文件。所以我们需要先清空这个查询缓存,使用 flush tables 命令即可清除各种缓存(网上说 reset query cache 可以,但我试过后发现不可以,只有 flush tables 才有效果),清空 query cache 后再使用 select 就没有问题了:

我们来试一下 InnoDB 类型的表在尝试进行物理备份后,会导致什么错误:

进入 $data/old_db 目录,可以看到两个 test.* 文件(因为我使用的是独立表空间):

我们将这两个文件移到到 new_db 目录下,看看会有什么结果:

切换到 mysql 窗口,可以看到 new_db 下的 test 表,但是不能正常使用:

注意:因为各种缓存的存在,导致先前看到的 test 表是 MyISAM,清空缓存后就显现出来了。

SQL 语句 - 数据导出

  • 备份:SELECT 字段 FROM 表名 INTO OUTFILE 文件名 [文件格式]
  • 恢复:LOAD DATA INFILE 文件名 INTO TABLE 表名[(字段列表)] [CHARSET 编码] [文件格式]
  • 备份、恢复中的“文件格式”有这些选项,语法 fields FORMAT... lines FORMAT...
    • 行格式控制,接在 lines 后面:
      • starting by:指定每行以什么开始,默认是 '',空字符串;
      • terminated by:指定每行以什么结束,默认是 '\n',换行符。
    • 字段格式控制,接在 fields 后面:
      • enclosed by:指定字段用什么包裹,默认是 '',空字符串;
      • terminated by:指定字段以什么结束(分隔符),默认是 '\t',Tab 键;

注意:OUTFILE 指定的文件必须事先不存在,不然会提示“文件已存在”的错误。

进入 /tmp 目录,可以看到 test0.dat 纯文本文件,内容如下:

可以发现,INTO OUTFILE 导出的文件中只有数据(自定义格式),没有其他的东西。怎么恢复呢?

我们来自定义 outfile 的文件格式,具体操作:

最后,说明一下如何指定字段列表:

mysqldump 逻辑备份
基本用法,注意 mysqldump 默认将备份 SQL 语句写入到 STDOUT:

其中 OPTIONS 和 mysql 客户端的基本相似,无非是 -h 指定 addr,-P 指定 port,-u 指定用户,-p 指定密码(不给选项值时则进行交互式密码认证)。

例一,备份数据表,sql 文件中没有 create database 语句:

例二,备份数据库,多了个 create database if not exists 语句:

例三,备份全部数据库(除了 information_schema、performance_schema):

有个细节不知道你注意没,mysqldump 生成的 sql 文件中有很多特殊注释,如 /*!40101 STATEMENT */,这是什么东西呢?查阅 MySQL 文档后,才知道它的作用是:如果当前 mysql 数据库管理系统的版本在 40101 之后(含 40101),那么这里面的 STATEMENT 才会被执行,如果不是,或者是其他数据库系统,则会直接忽略它。其实就相当于 C/C++ 中的条件编译代码。

那么我们该如何恢复这些 sql 文件呢?有两种方式,一种是 shell 命令行,一种是 source 指令。

方式一,使用 mysql <dump.sql(shell)

方式二,使用 source dump.sql(mysql)

事务

其实大部分内容我都在【入门】一节中讲解了,这里只提一下几个关键的知识点。

案例:银行的数据库里面存储着用户的账户信息表,当用户 A 向用户 B 转账的时候,正常情况下,A 账户的余额减少,B 账户的余额增加;但是由于某种原因(例如突然断电),当 A 账户的余额减少之后,B 账户的余额并没有增加,这就造成了数据库数据的安全隐患。

解决方案:当 A 账户的余额减少之后,不要立即修改数据表,而是在确认 B 账户的余额增加之后,再修改数据表。

因此引出了一个全新的概念:事务,即:一系列将要发生或正在发生的连续操作。而事务安全,是一种保护连续操作同时完成的机制。事务安全的意义就是,保证数据操作的完整性

事务的基本原理:开启一个事务后,后面的 SQL 操作都会被写入到事务日志,而不会立即执行,只有执行 commit 提交命令后,这些 SQL 语句才会执行(原子操作,要么都执行,要么都不执行),然后这个事务日志会被清空,留给下一个事务进行操作。如果不执行 commit,而是执行 rollback,那么事务日志也会被清空,但是这些 SQL 语句不会被执行(也就是作废了)。注意,在 commit 后进行 rollback 是没有效果的,因为事务日志已经被清空了!目前只有 InnoDB 和 BDB 两个存储引擎支持事务,但 BDB 收费。

事务的四大特性
ACID,是指数据库管理系统(DBMS)在写入或更新资料的过程中,为保证事务(transaction)是正确可靠的,所必须具备的四个特性:原子性(atomicity,或称不可分割性)、一致性(consistency)、隔离性(isolation,又称独立性)、持久性(durability)。

在数据库系统中,一个事务是指:由一系列数据库操作组成的一个完整的逻辑过程。例如银行转帐,从原账户扣除金额,以及向目标账户添加金额,这两个数据库操作的总和,构成一个完整的逻辑过程,不可拆分。这个过程被称为一个事务,具有 ACID 特性。ACID 的概念在 ISO/IEC 10026-1:1992 文件的第四段内有所说明。

  • Atomicity(原子性):一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。
  • Consistency(一致性):在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
  • Isolation(隔离性):数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
  • Durability(持久性):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

我们主要关心的是事务的隔离性,不同的事务隔离性,会不同程度的出现以下问题:

脏读、不可重复读、幻读

  • 脏读:事务 A 读取到了事务 B 还未提交的数据,如果事务 B 正常提交,那也没什么影响,但如果事务 B 执行了 rollback 操作,那么事务 A 获取到的数据就是脏数据,如果事务 A 将这个读取到的脏数据用于其它事务操作,那么就会出现错误的结果,导致事务执行后的结果与预期的不一致。
  • 不可重复读:在同一个事务中,使用相同的条件查询数据库两次,得到的记录数据不一致,之所以会出现这种情况是因为在两次查询之间,另外一个事务对其中的某些记录进行了 update 操作且提交到了数据库。
  • 幻读:在同一个事务中,使用相同的条件查询数据库两次,得到的记录条数不一致,之所以会出现这种情况是因为在两次查询之间,另外一个事务对其中的某些记录进行了 insert/delete 操作且提交到了数据库。

OK,我们知道事务隔离级别有 4 个,如果按照隔离程度从深到浅排列的话,则为 read uncommitted(读未提交)、read committed(读提交)、repeatable read(可重复读)、serializable(串行化),需要强调的一点是,事务的隔离级别越高,数据库的并发性能就越低,大部分数据库的默认隔离级别为 read committed 读提交,比如 Oracle,而 MySQL 的默认隔离级别则为 repeatable read 可重复读,而最低级的读未提交和最高级的串行化基本上没有谁会去使用,因为意义不是很大。

这些隔离级别以及它们会发生的问题如下:

  • 读未提交:脏读、不可重复读、幻读
  • 读提交:不可重复读、幻读
  • 可重复读:幻读
  • 串行化:None

可以看到,除了读未提交外,其他隔离级别都分别解决了一个问题,而串行化则解决了所有问题,但是并发性能也是最低的。

读提交和可重复读的区别:

  • read committed:可以读取到其他事务已提交的数据,但不会缓存读取到的任何数据,只要其它事务提交了数据,就能读取到。所以只要在两次查询之间,其他事务对其中某些记录进行了 update 操作,那么我们再次以相同的条件进行查询时,会看到查询结果不一致的情况,即不可重复读。
  • repeatable read:可以读取到其他事务已提交的数据,且还会缓存已读取的数据,下次读取相同的记录返回的是缓存中的数据(但是新增的数据还是能够读取的到),以此实现可重复读。但如果其他事务进行了 insert 操作,且 insert 的记录也符合当前的查询条件,那么当我们再次以相同的条件进行查询时,会发现记录条数变了,出现了之前没有的数据,这个现象被称为幻读。

MySQL 默认隔离级别的幻读问题
根据前面的知识可以知道,标准的 repeatable read 是会出现幻读的,也即两次相同的查询出现了记录数目不一致的情况,多出了一些数据,感觉就像产生了幻觉一样,这也就是幻读的名称由来吧。但是 mysql 的 repeatable read 隔离级别使用了 MVCC 多版本并发控制技术来避免了绝大部分幻读现象,注意,理论上,RR 级别是会出现幻读的,但是 mysql 使用了 MVCC 技术避免了大部分幻读现象的发生,注意我说的是大部分,待会我会举例说明 MVCC 无法解决到的幻读问题。

MVCC 已解决的幻读 (1)
测试用的数据表为 test,现有的数据如下:

然后开启两个终端,各自都开启了事务,事务 A 查询的结果如下:

事务 B 查询的结果如下:

然后事务 A 插入了一条 id 为 3 且 name 为 google 的记录:

然后我们在事务 B 中再次查询 test 表中的数据:

可以看到,事务 B 并没有查出事务 A 刚刚新增的 id 为 3 的纪录,而是返回与上一次相同的查询结果,没有发生幻读。如果是标准的 RR 级别,那么事务 B 的这次查询是可以看到新增的那条记录的,注意它们之间的区别。

MVCC 已解决的幻读 (2)
重新开启两个终端,测试的数据表依旧为 test,且最初的数据如下:

然后分别在两个终端中开启事务 A 和事务 B,查询结果如下:

然后我们在事务 A 中 delete id 为 3 的记录:

然后我们在事务 B 中再次以相同的条件查询表中的记录:

可以看到,事务 B 的两次查询结果还是一样的,并未发现 id 为 3 的那条记录被删除了,所以也没有发生幻读。

MVCC 未解决的幻读 (1)
测试数据表为 test,最初数据如下:

然后分别开启两个事务,A 和 B,初次查询结果如下:

然后我们在事务 A 中新增一条 id 为 3 的记录:

然后我们在事务 B 中再次以相同的条件查询:

然后我们在事务 B 中也插入一条 id 为 3 的记录:

如果我们选择提交事务 A,事务 B 的这次 insert 就会报错,提示记录已存在,发生幻读:

如果我们选择回滚事务 A,那么事务 B 的这次 insert 就不会报错,正常插入:

MVCC 未解决的幻读 (2)
开启两个事务,初始查询如下:

然后我们在事务 A 中插入一条 id 为 6 的新纪录:

然后我们在事务 B 中再次以相同的条件进行查询:

可以发现事务 B 并没有读取到新增的 id 为 6 的记录,那么我们来插入一条 id 为 6 的记录看看:

如果我们选择提交事务 A,那么就会提示记录已存在,插入失败,发生幻读:

如果我们选择回滚事务 A,那么事务 B 的此次插入就会成功:

所以我们说,MVCC 只解决了部分幻读问题,对于后面两种幻读,MVCC 无法解决。

更新丢失问题
假设存在一个用户 A,他目前的余额为 100,然后用户 B 和用户 C 分别向用户 A 转了 50 元钱,那么两个转账完成后,用户 A 的余额应该为 200,我们用两个事务来分别描述这两个转账操作:

此时 B 和 C 查询出来的 A 的余额都是 100,于是它们都开始各自的转账操作:

注意,这里出现一个有意思的情况,因为 B 和 C 都是将用户 A 的 balance 改为了 150(后修改的那个事务会被阻塞,直到先修改的那个事务提交),所以实际上两次更新之后的结果是,用户 A 的余额依旧为 150,而不是 200!这就是所谓的更新丢失,但这其实是使用者自己的问题,对目前的并发状态过于乐观,都以为对方不会和自己同时修改用户 A 的余额,导致后面的那次更新覆盖了前面的那次更新,用户 A 本来应该得到 200 元,却只得到了 150,还有 50 就这么平白无故的消失了。

要避免这种情况其实很简单,有 3 种方法:

1、第一种,使用 update account set balance=balance+50 where id=1 而不是 update account set balance=100+50 where id=1

2、使用 select ... for update 语法来获取排它锁,注意只有双方都使用 select ... for update 语句才能正常生效,如果一方使用 select ... for update,而一方使用 select ...,那么这个排它锁是不会生效的!千万注意:

注意,只要事务 A 还未提交或回滚,那么事务 B 就会阻塞在 select ... for update 语句,以此保证数据的一致性。

3、添加对应的 where 条件,判断在 select 和 update 之间,数据是否被修改了,如果修改了,那么会因为 where 条件不匹配而更新失败,然后应用程序可以根据是否更新成功来做其他一些回滚操作:

注意事务 B 实际上是没有更新成功的,因为事务 A 更新了 balance 为 250,并且提交了,所以此时的判断条件就失效了,也就意味着数据被更改了,而应用程序可以根据这个 update 是否成功来做其他一些操作,比如回滚。

变量

变量的内容我也是在【入门】一节中详细介绍了,这里补充一下其他知识。

SQL 中有两种常用的查询数据的方式:showselect,show 一般是数据库定义的,功能比较固定;select 则相当于 printf、echo 等函数,比较灵活,可以显示各种数据。

在 MySQL 中,很多地方会默认将 = 处理为比较符号,因此 MySQL 还提供了另外一种赋值符号 :=,它永远不会被解释为比较符号,可以将它看作是 = 复制符号的一个语法糖(但强烈建议使用)。

MySQL 允许我们从 select 字段 from 表名 中给自定义变量赋值(@ 开头的变量),具体的语法:select @变量:=字段 ... from 表名,注意不能使用 = 来赋值,会被解释为比较是否相等的符号。

因为 select 语句找到了 9 个匹配的记录,所以被依次赋值,但只有最后一个美团被保留。当然,如果没有找到一行匹配的记录,那么自定义变量的值将不会被改变,原来是什么现在还是什么(初始值为 NULL,另外,MySQL 不支持“删除变量”的语法,你只能将变量的值设为 NULL 来表示删除)。

除了这个方法外,还可以直接在正常的 select 语句后面加上 into @var_name 来进行赋值,但是它的限制更加严格一些,只允许返回的记录数目为 1 或 0,否则会提示 ERROR(但实际上第一个记录的值依旧会被正常保存到变量中),建议添加 limit 1 和对应的 where 条件来限制结果表的记录数。

触发器

所谓触发器(trigger)就是 JS 中的事件监听器(listener),它们的概念以及作用都是类似的。只不过监听器是在某个事件完成后进行的动作,而触发器可以在该事件进行前进行某些操作。

触发器:trigger,是指事先为某张表绑定的一段代码,当表中的某些内容发生改变(增、删、改)的时候,系统会自动触发代码并执行。

创建触发器

  • trigger_name:触发器名称,同一个数据库中不允许重名。
  • trigger_time:触发的时机,有两个取值,beforeafter
  • trigger_event:触发的事件,有三个取值,insertupdatedelete
  • trigger_body:触发器程序体,SQL 语句(不允许有输出内容),可有多行。

因此存在 6 种触发器:before insertafter insertbefore updateafter updatebefore deleteafter delete,MySQL 规定,在同一个表上不能建立两个相同类型的触发器,所以同一张表最多只能创建 6 个触发器。

  • insert 型触发器:插入记录时激活此触发器,可通过 insert、replace、load data 语句触发
  • update 型触发器:更新记录时激活此触发器,可通过 update 语句触发
  • delete 型触发器:删除记录时激活此触发器,可通过 delete、replace 语句触发

load data:在恢复数据备份文件时,需要用到此命令,它其实就相当于一条一条的 insert 语句;
replace:一般情况下和 insert 语句的操作相同,当要操作的数据包含主键、唯一键时,如果当前插入的数据已存在,那么就会先删除原来的数据,然后再新增当前这条数据。因此 replace 有时等于 insert,有时等于 delete + insert 语句。

最后再来解释一下 trigger_body,这是触发器的主体语句,可以是一行,也可以是多行,多行需要使用 BEGIN…END 语句块表示。一行的很简单没什么可讲,主要说一下多行的语法,也就是 BEGIN…END。因为 BEGIN…END 里面的每条语句都必须以 ; 号结尾,而 MySQL 中每条语句的分隔符也是 ;,所以会导致 MySQL 语法分析器提示 BEGIN 无法匹配 END 错误(智障般的存在),所以我们必须让 MySQL 语法分析器分清语句和 BEGIN…END 里面的语句,幸好,MySQL 提供了 delimiter 命令,它的作用非常简单,那就是改变 SQL 语句的分隔符,语法:delimiter 分隔符,注意没有分号结尾哦。一般我们在使用 BEGIN…END 之前先使用 delimiter $ 来改变分隔符为 $,然后再 BEGIN…END 后再使用 delimiter ; 来将分隔符改回 ;

例 1(单行主体):

例 2(多行主体):

这两个例子中都出现了 new.name,这是什么意思呢?其实 new 就是指代新增的记录行,而 new.name 则表示新增的记录行中的 name 这个字段(在这里就是取值了)。相对的,还有 old 关键字,它表示即将要删除/被替换的记录行,它们的用法类似。注意,newold 都只能在 trigger_body 里面使用。很容易知道,insert 型的触发器只能使用 new 关键字,delete 型的触发器只能使用 old 关键字,update 型的触发器才能使用 old 和 new 关键字。old 是只读的,new 是可写的。

例子:

查看所有的触发器
show triggers [from 库名];(格式不好)
show triggers [from 库名]\G(方便阅读)

查看触发器创建语句
show create trigger 触发器名称;

删除触发器
drop trigger [if exists] 触发器名称;

注意:触发器名称与数据表名称一样,可以使用 dbname.trname 表示。
另外:如果触发器所在的表被删除了,那么对应的触发器也会被一并删除。

触发器的执行顺序

  • 若 BEFORE 触发器执行失败,SQL 不会执行。
  • SQL 执行失败时,AFTER 型触发器不会触发。
  • AFTER 类型的触发器执行失败,SQL 会回滚。

定义局部变量
之前我们定义用户变量是使用 set 关键字进行的,虽然在 begin…end 块中依然可以使用 set 来定义用户变量(生命周期为会话变量,与 mysql 客户端绑定),但是大多数情况下这可能不是我们想要的,因为我们不想对外部环境有什么影响,怎么才可以定义仅作用于 begin…end 语句块内部的局部变量呢?MySQL 提供了 declare 关键字,它的用处就是定义 begin…end 块中的局部变量,有必要强调一下,declare 必须位于 begin…end 的开头,否则会导致语法错误。这是 declare 的语法:
DECLARE var_name [, var_name ...] type [DEFAULT value],如果没有 default,默认为 null
create table 里面的字段声明差不多,每次只能定义同一个类型同一个初始值的变量(可多个)

流程控制
在 MySQL 编程中,代码的执行结构有三种,分别为:

  • 顺序结构;
  • 分支结构;
  • 循环结构。

顺序结构,自不必多说,在本文中,我们着重了解分支结构和循环结构。

IF 分支结构

其中,ELSEIF、ELSE 是可选的,ELSEIF 可以有多个,每个 IF 结构必须以 END IF 结尾。

例子,在插入数据前校验数据是否正确,如果不正确则执行一个明显错误的语句,终止运行:

where 循环结构

例子,tab0 插入一行,tab1 就自动插入 10 行:

MySQL 中没有 continue/break 关键字,但是它有两个等价的关键字:iterate/leave

  • iterate:相当于 continue,结束本轮循环,直接开始下轮循环
  • leave:相当于 break,结束/跳出当前循环,执行循环后的代码

语法:iterate 循环名称leave 循环名称,必须指明循环名称(循环标签)

函数

函数,就是将一段代码封装到一个结构中,在需要执行该段代码的时候,直接调用该结构(函数)执行即可。此操作,实现了代码的复用。在 MySQL 中,函数有两种,分别为:系统函数和自定义函数。

任何函数都有返回值(返回值允许为 NULL,可以看作没有返回值),而且在 MySQL 中任何有返回值的操作都是通过 select 来操作的,因此 MySQL 的函数调用就是通过 select 来实现的(理解错误)。

系统函数
顾名思义,系统函数就是系统定义好的函数,在需要的时候,我们直接调用即可。

  • length(str):获取字符串长度(字节为单位)
  • char_length(str):获取字符串长度(字符为单位)
  • concat(str1, str2, ...):字符串拼接,返回拼接后的字符串
  • substring(str, pos [, len]):提取子串,注意 pos 从 1 开始
  • left(str, len):提取前 len 个字符
  • right(str, len):提取后 len 个字符
  • ascii(char):获取字符的 ascii 码点值,如果传入字符串,则只获取首字符的码点值
  • char(code-point1, code-point2, ...):将对应码点值转换为字符,返回拼接后的字符串
  • locate(substr, string):返回 substr 在 string 中首次匹配的位置,如果没有则返回 0
  • instr(string, substr):和 locate(substr, string) 作用相同,只不过把参数顺序颠倒了
  • lpad(str, len, padstr):将字符串 str 右对齐为 len 长度,不足的补 padstr 字符
  • rpad(str, len, padstr):将字符串 str 左对齐为 len 长度,不足的补 padstr 字符
  • ltrim(str):删除前导空白,返回新串,不改变原串
  • rtrim(str):删除后尾空白,返回新串,不改变原串
  • trim(str):删除前导空白和后尾空白,返回新串,不改变原串
  • space(len):返回由 len 个空格组成的字符串
  • replace(string, oldstr, newstr):字符串替换(全部替换),返回替换后的字符串
  • repeat(str, cnt):返回由 cnt 个 str 组成的字符串(重复)
  • reverse(string):返回 string 相反顺序的字符串(顺序反转)
  • insert(string, pos, len, newstr):将字符串 string 从 pos 开始的 len 长度的子串替换为 newstr,并返回
  • lcase(str):将 str 转换为小写形式
  • ucase(str):将 str 转换为大写形式
  • strcmp(str1, str2):比较两个字符串,默认忽略大小写,返回值与 Java 中的类似,-1、0、1
  • load_file(path):读取文件内容,作为字符串返回。path 必须为绝对路径,并且文件大小不能超过 max_allowed_packet 字节,否则函数返回 null 值。

自定义函数
对于任意一个函数,都包含如下要素:

  • 函数名;
  • 形参列表(可为空);
  • 返回值;
  • 函数体。

定义函数

如果函数体有多行,必须使用 BEGIN…END 块包围,和触发器里面的语法一样,需要使用 delimiter 来改变语句分隔符,不然会报语法错误。如果函数体只有一行(return),则不用使用 BEGIN…END。同样的,函数中不允许有任何输出语句,比如 select * from test0,这个和触发器也是一样的。

函数选项

查看函数
show create function func_name\G
show function status [like 'pattern']

例如:

不难看出,一个 function 其实是属于某个 database 的,就和 table 一样,属于某个 database。但是又有点不同,show tables 只能看到当前数据库中的所有表,而 show function status 则是查看所有数据库中的自定义函数。除此之外,好像没什么差别。命名也是差不多的,db_name.func_name 为全限定名称,如果省略 db_name,则表示当前数据库中的自定义函数。

修改函数
这里的修改是指修改函数的选项 Options,其它的不能修改,比如重命名就不行。语法:

删除函数
drop function [if exists] func_name,如果不是当前 db 的函数,请指明 db 名称。

自定义变量
这个其实在触发器里面已经详细说过了,有两种自定义变量,区别是作用域和生命周期的不同:

  • 静态变量 set @var_name := var_value,函数外仍然生效
  • 局部变量 declare var_name var_type [default var_value],仅当前函数生效

注意:declare 语句必须位于 BEGIN…END 语句块的开头位置,可以有多行。

例子:

存储过程

存储过程,procedure,简称过程,是一种用来处理数据(增删改查)的方式。简单点,我们也可以将其理解为没有返回值的函数(实际上它可以返回值,但与函数的返回方式有些不同)。实际上我觉得 MySQL 中的函数与一般编程语言中的函数有很大的不同,因为 MySQL 函数不能输出内容,只能返回值,这在很多时候都不是很方便。好在我们可以使用存储过程来弥补函数不能输出内容的缺陷,所谓存储过程其实就等价于一般编程语言中的函数(或者叫批处理命令),它可以返回值(通过 out、inout 参数返回),也可以输出内容。不过调用存储过程与调用函数有一些区别,自定义函数的调用与系统函数的调用方法一般在 select 语句中调用。而调用存储过程必须使用专门的关键字 call 来调用。

创建存储过程
自定义函数与存储过程的语法基本上都差不多,就是把 function 换为 procedure 就可以了。

其中 routine_body,如果只有一条语句,那么可以省略 BEGIN…END(也不用更改 delimiter 语句分隔符),如果有多条语句,必须使用 BEGIN…END 语句块包围,而且必须使用 delimiter 更改语句分隔符(比如改为 $,create 后再使用 delimiter 改回 ;),否则 MySQL 会提示语法错误。

再说说 procedure 的参数,[IN|OUT|INOUT] param_name type,与 function 的参数区别是前面多了个 IN|OUT|INOUT 修饰符。它们的意思也很简单:

  • IN 参数:输入参数,也就是只读(read only),默认值
  • OUT 参数:输出参数,也就是只写(write only),会先清空变量的原有值
  • INOUT 参数:输入/输出参数,也就是可读可写(read write),即 IN & OUT

很容易知道,可写参数必须为一个变量(不然无法写入值),可读参数没有要求,字面量和变量都可以。注意,如果 OUT 参数是一个变量,那么该变量的值会先被(开始执行的时候)清空(设为 NULL 值),然后再写入数据,所以在语句块中尝试 select 读取 OUT 变量的值都会返回 NULL。

例子:

当然,也可以没有任何参数,仅仅作为一系列命令的包装(联系到 shell 中的函数):

查看存储过程

  • show create procedure 过程名,查看 procedure 的创建语句、相关细节。
  • show procedure status [like 'pattern'],查看所有 procedure,和 function 一样,procedure 属于某个 database,它们的命名空间也是一样的,反正记住 function 和 procedure 差不就行了。

删除存储过程
drop procedure [if exists] 过程名

修改存储过程
和 function 一样,只能修改存储过程的 options(这些 options 什么用我也不清楚)。

最后提一下存储过程的 out、inout 变量的延迟写入,在存储过程没有结束的时候,对传入变量的修改并不会影响到对应的全局变量;只有在存储过程结束后,才会将对应的变量值赋值给out和inout类型的变量。来看一个例子:

其实也很好理解,在传递变量时,MySQL 实际上会创建一个同名的局部变量(所谓赋值就是内存拷贝),然后存储过程中操作的一直都是这个同名的局部变量,同名的外部变量不受影响,只有存储过程结束时,才会将同名局部变量的最终值赋给同名的外部变量,所以就造成了上面这种现象。