2. MySQL基础知识

MySQL概述

MySQL是⼀个单进程多线程、⽀持多⽤户、基于客户机/服务器（Client/Server， C/S）的关系数据库管理系统。与其他数据库管理系统（DBMS）相⽐，MySQL具有体积⼩、易于安装、运⾏速度快、功能⻬全、成本低廉以及开源等特点。⽬前，MySQL已经得到了⼴泛的使⽤，并成为了很多企业⾸选的关系数据库管理系统。 MySQL拥有很多优势，其中包括以下⼏点。 1. 性能⾼效：MySQL被设计为⼀个单进程多线程架构的数据库管理 系统，保证了 MySQL使⽤较少的系统资源（例如CPU、内存），且能 为数据库⽤户提供⾼效 的服务。 2. 跨平台⽀持：MySQL可运⾏在当前⼏乎所有的操作系统上，例如Linux、 Unix、Windows以及Mac等操作系统。这意味着在某个操作系统上实现的 MySQL数据库可以轻松地部署到其他操作系统上。 3. 简单易⽤：MySQL的结构体系简单易⽤、易于部署，且易于定制，其独特的插 件式（pluggable）存储引擎结构为企业客户提供了⼴泛的灵活性，赋予了数据 库管理系统以卓越的紧致性和稳定性。 4. 开源：MySQL是世界上最受欢迎的开源数据库，源代码随时可访问，开发⼈员 可以根据⾃身需要量身定制MySQL。MySQL开源的特点吸引了很多⾼素质和有 经验的开发团队完善MySQL数据库管理系统。 5. ⽀持多⽤户：MySQL是⼀个⽀持多⽤户的数据库管理系统，确保多⽤户下数据 库资源的安全访问控制。MySQL的安全管理实现了合法账户可以访问合法的数 据库资源，并拒绝⾮法⽤户访问⾮法数据库资 源。

MySQL字符集与字符序

不同的字符集⽀持不同地区的字符，例如latin1⽀持⻄欧字符、希腊字符等，gbk⽀ 持中⽂简体字符，big5⽀持中⽂繁体字符，utf8⼏乎⽀持世界上所有国家的字符。 每种字符集占⽤的存储空间不相同。由于希腊字符数较少，占⽤⼀个字节（8位）的 存储空间即可表示所有的latin1字符；中⽂简体字符较多，占⽤两个字节（16位）的 存储空间才可以表示所有的gbk字符；utf8字符数最多，通常需要占⽤三个字节 （24位）的存储空间才可以表示世界上所有国家的所有字符（例如中⽂简体、中⽂ 繁体、阿拉伯⽂、俄⽂等）。 字符序(collation)是指在同⼀字符集内字符之间的⽐较规则。只有确定字符序后，才 能在⼀个字符集上定义什么是等价的字符，以及字符之间的⼤⼩关系。⼀个字符集 可以包含多种字符序，每个字符集有⼀个默认的字符序(defaultcollation)，每个字 符序唯⼀对应⼀种字符集。 MySQL字符序命名规则是：以字符序对应的字符集名称开头，以国家名居中（或以 general居中），以ci、cs或bin结尾。以ci结尾的字符序表示⼤⼩写不敏感，以cs结 尾的字符序表示⼤⼩写敏感，以bin结尾的字符序表示按⼆进制编码值⽐较。例如， latin1字符集有latin1_swedish_ci、latin1_general_cs、latin1_bin等字符序，其中在字 符序latin1_swedish_ci规则中，字符’a’和’A’是等价的。 MySQL数据库管理 创建数据库’testdb’ CREATE database testdb; 或 CREATE database testdb DEFAULT CHARACTER SET utf8 COLLATutf8_general_ci; 查看服务器中所有的数据库 show databases; 删除数据库’testdb’ drop databases testdb;

MySQL表管理

MyISAM和InnoDB存储引擎 与其他数据库管理系统不同，MySQL提供了插件式（pluggable）的存储引擎，存储引擎是基于表的。同⼀个数据库，不同的表，存储引擎可以不同。甚⾄，同⼀个 数据库表在不同的场合可以应⽤不同的存储引擎。 ⽬前，MySQL的存储引擎⾄少10种，使⽤MySQL命令“showengines;”即可查看 MySQL服务实例⽀持的存储引擎，其中，InnoDB是默认的（default）存储引擎。 事实上，从5.5版本开始，MySQL已将默认存储引擎从MyISAM更改为InnoDB。 MySQL中的每⼀种存储引擎都有各⾃的特点。对于不同业务类型的表，为了提升性 能，数据库开发⼈员应该选⽤更合适的存储引擎。MySQL常⽤的存储引擎有InnoDB 存储引擎以及MyISAM存储引擎。 * InnoDB存储引擎 与其他存储引擎相⽐，InnoDB存储引擎是事务（transaction）安全的，并且⽀持外 键（foreign key）。如果某张表主要提供OLTP（联机事务处理 on-line transaction processing）⽀持，需要执⾏⼤量的增、删、改操作（即insert、delete、update语 句），出于事务安全⽅⾯的考虑，InnoDB存储引擎是更好的选择。对于⽀持事务的 InnoDB表，影响速度的主要原因是打开了⾃动提交（autocommit）选项，或者程 序没有显示调⽤“begin transaction;”（开始事务）和“commit;”（提交事务），导 致每条insert、delete或者update语句都⾃动开始事务和提交事务，严重影响了更新 语句（insert、delete、update语句）的执⾏效率。让多条更新语句形成⼀个事务， 可以⼤⼤提⾼更新 操作的性能（有关事务的概念将在后续章节进⾏详细讲解）。 从MySQL 5.6版本开始，InnoDB存储引擎的表已经⽀持全⽂索引，这将⼤幅提升 InnoDB存储引擎的⽂本检索能⼒。对于⼤多数数据库表⽽⾔，InnoDB存储引擎已经 够⽤。由于“选课系统”的5张数据库表经常需要执⾏更新操作，因此有必要将这5张 表设置为InnoDB存储引擎。本书所创建的数据库表，如果不作特殊声明，都将使⽤ InnoDB存储引擎。 * MyISAM存储引擎 如果某张表主要提供OLAP（联机分析处理OLAP On-Line Analytical Processing） ⽀持，建议选⽤MyISAM存储引擎。MyISAM具有检查和修复表的⼤多数⼯具。 MyISAM表可以被压缩，⽽且最早⽀持全⽂索引，但MyISAM表不是事务安全的，也 不⽀持外键（foreignkey）。如果某张表需要执⾏⼤量的select语句，出于性能⽅⾯ 的考虑，MyISAM存储引擎是更好的选择。 当然任何⼀种存储引擎都不是万能的，不同业务类型的表需要选择不同的存储引 擎，只有这样才能将MySQL的性能优势发挥⾄极致。 创建数据库表 创建表命令 CREATE TABLE person ( Id varchar(40), name varchar(100) COMMENT '姓名', gender varchar(2) COMMENT '性别', birthday datetime ) ENGINE=INNODB DEFAULT CHARSET=utf8 ; 显示表结构 desc my_table;

InnoDB表空间

InnoDB表空间分为共享表空间与独享表空间。 共享表空间 1. MySQL服务实例承载的所有数据库的所有InnoDB表的数据信息、索引信息、 各种元数据信息以及事务的回滚（UNDO）信息，全部存放在共享表空间⽂件 中。 2. 独享表空间 如果将全局系统变量innodb_file_per_table的值设置为 ON（innodb_file_per_table的默认值为OFF），那么之后再创建InnoDB存储引 擎的新表时，这些表的数据信息、索引信息将保存到独享表空间⽂件中。 系统变量 启动MySQL服务，⽣成MySQL服务实例期间，MySQL将为MySQL服务器内存中的 系统变量赋值，这些系统变量定义了当MySQL服务实例的属性、特征。这些系统 变量的值要么是编译MySQL时参数的默认值，要么是my.ini配置⽂件中的参数值。 在MySQL数据库中，变量分为系统变量（以“@@”开头）以及⽤户⾃定义变量。系 统变量分为全局系统变量以及会话系统变量。 系统变量分为全局系统变量（global）以及会话系统变量（session），有时也把全 局系统变量简称为全局变量，有时也把会话系统变量称为local变量或者系统会话变 量。MySQL服务成功启动后，如果没有MySQL客户机连接MySQL服务器，那么 MySQL服务器内存中的系统变量全部是全局系统变量（有393个之多）。每⼀个 MySQL客户机成功连接MySQL服务器后，都会产⽣与之对应的会话，会话期间， MySQL服务实例会在MySQL服务器内存中⽣成与该会话对应的会话系统变量，这些 会话系统变量的初始值是全局系统变量值的复制。