《MySQL 必知必会》C25-C28

第二十五章 使用触发器

1 触发器

MySQL语句在需要时被执行，存储过程也是如此，但是如果想要某条语句在事件发生时自动执行，如：
1，每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写
2，每当订购一个产品时，都从库存数量中减去订购的数量
3，无论何时删除一行，都在某个存档表中保留一个副本

上述例子的共同之处是它们都需要在某个表发生变化时自动处理，这确切的讲就是触发器。触发器是MySQL相应delete，insert，update而自动执行的一条MySQL语句，只有这3条语句支持触发器

2 创建触发器

创建触发器时，需要给出4条信息：
1，唯一的触发器名
2，触发器关联的表
3，触发器应该响应的活动(delete，insert，update)
4，触发器何时执行(处理前或处理后)

需要保证每个数据库的触发器名唯一

触发器使用create trigger语句创建：

create trigger newproduct after insert on products
for each row select 'product added';

create trigger用来创建一个名为newproduct的新触发器，触发器可在一个操作发生之前或之后执行，after insert用来表明触发器将在insert语句成功执行后执行，这个触发器还指定了for each row，因此代码对每个插入行执行，文本productnadded将对每个插入的行显示一次

只有表才能使用触发器，视图和临时表不支持使用触发器

触发器按每个表每个事件每次地定义，每个表每个事件每次只允许一个触发器，因此一个表最多支持6个触发器，单一触发器不能和多个事件或多个表关联，所以如果需要一个对insert和update操作执行的触发器，则应该定义两个触发器

3 删除触发器

删除一个触发器，可以使用drop trigger语句：

drop trigger newproduct;

触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，然后再重新创建

4 使用触发器

(1) insert触发器

insert触发器再insert语句执行之前或之后发生，需要明白几点

1，在insert触发器代码中，可引用一个名为new的虚拟表，访问被插入的行
2，在before insert触发器中，new中的值可以被更新(允许更改被插入的行)
3，对于auto_increment列，new在insert执行之前为0，在insert执行后包含新的自动生成值

create trigger neworder after insert on orders
for each row select new.order_num;

此代码创建一个名为neworder的触发器
它按照after insert on orders执行，在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中，触发器从new.order_num中取得这个值并返回它，此触发器必须按照after insert执行，因此在before insert执行之前，新order_num还没有生成，对于orders的每次插入使用这个触发器讲总是返回新的订单号

通常讲before用于数据验证和净化(先保证处理的数据是需要的数据)

(2) delete触发器

delete触发器在delete语句执行之前或执行后执行

1，在delete触发器代码内，可引用一个名为old的虚拟表，访问被删除的行
2，old中的值都是只读的，不能更新

create trigger deleteorder before delete on orders
for each row
begin
	insert into archive_orders(order_num, order_date, cust_id)
	values(old.order_num,old.order_date,old.cust_id);
end;

上述deleteorder触发器在任意行被删除前，使用一条insert语句将old中的值(要删除的订单)保存到一个名为archive的存档表中

触发器中也可以使用多条begin end语句，它的好处是使触发器能容纳多条SQL语句

(3) update触发器

update触发器在update语句执行前或执行后执行

1，在update触发器代码中，可以引用一个名为old的虚拟表访问以前(执行update语句前)的值，可以引用一个名为new的虚拟表访问更新后(执行update语句后)的值
2，在before update触发器中，new中的值也可能被更新(允许更改将要用于update语句中的值)
3，old中的值全都是只读的，不可更新

上述语句完成了保证了州名的缩写总是大写，显然任何数据净化都需要在update之前完成

5 进一步了解触发器

1，创建触发器可能需要特殊的安全访问权限，但是触发器的执行是自动的，如果insert，update，delete语句能够执行，则相关的触发器也能执行

2，应该用触发器来保证数据的一致性(大小写，格式统一)，在触发器中执行这种类型的处理是它总是进行这种处理，而且是透明地进行，与客户机无关

3，触发器的一种非常有意义的作用是创建审计跟踪，使用触发器，可以把更改的行记录到另一个表中(如果需要，甚至还有之前和之后的状态)

第二十六章 管理事物处理

1 事物处理

并非所有的引擎都支持事务处理，MySQL支持几种基本的数据库引擎，MyISAM和InnoDB是两种最常用的引擎，但MyISAM不支持事物处理，而InnoDB支持，如果你的应用中需要事物处理，则一定要选择正确的引擎来创建表

事物处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行

回顾前面的orders表，订单存储在orders和orderitems两个表中，orders存储实际的订单，而orderitems存储订单的各项物品，这两个表用主键的唯一ID相关联，这两个表又与包含客户和产品信息的其它表相关联

给系统添加新订单的过程如下：
1，检查数据库中是否存在相应的客户，如果不存在则添加他
2，检索客户的ID
3，添加一行到orders表中，把它和客户ID关联
4，检索orders表中赋予的新订单ID
5，对于订购的每个物品在orderitems表中添加一行，通过检索出来的ID把它与orders表相关联

现在假如由于某种数据库故障(如超出磁盘空间，安全限制，表锁)等阻止了这个过程，数据库中的数据会出现几种情况

1，如果故障发生在添加了客户后，orders表添加之前，不会有什么问题，某些客户没有订单是合法的，在排错后重新执行此过程时，所插入的客户记录将被检索和使用，可以有效地从故障发生的地方开始继续执行

2，如果故障发生在orders行添加之后，orderitems行添加之前，会导致数据库中出现一个空订单

3，如果系统在添加orderitems行中出现错误，结果是数据库中存在不完整的订单

为了避免这些问题的出现，就需要使用事物处理，事物处理是一种机制，用来管理成批的MySQL操作，以保证数据库不包含不完整的操作结果，利用事物处理，可以保证一组操作不会中途停止，它们作为整体一起执行，或者全部不执行

如果没有错误发生，整租语句提交给数据库表，如果发生了错误，则进行回退以恢复数据库到某个已经且安全的状态

使用事物处理的几个术语：
1，事物：指一组SQL语句
2，回退：指撤销指定SQL语句的过程
3，提交：指将未存储的SQL语句结果写入数据库表
4，保留点：指事物处理中设置的临时占位符，可以对它发布回退(与回退整个事物处理不同)

2 控制事物处理

管理事物处理的关键在于，将SQL语句分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退

MySQL使用以下语句标志事物的开始：

start transaction

(1) 使用rollback

MySQL的rollback命令用来回退(撤销)MySQL语句

select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;

首先执行一条select语句显示该表不为空，然后开始一个事物处理，用一条delete语句删除ordertotals中的所有行，另一条select语句验证ordertotals已经为空，rollback语句回退start transaction之后的所有语句，最后一条select语句显示该表不为空

rollback语句只能在一个事物处理内使用

事物处理用来管理insert，update，delete语句，不能回退select语句。不能回退create或drop操作，事物处理块中可以使用这两条语句，但如果执行回退，它们不会被撤销

(2) 使用commit

一般的MySQL语句但是直接对数据库表执行和编写的，这就是所谓的隐含提交，即提交操作是自动进行的，但是事物处理过程中，提交不会隐含地进行，需要使用commit语句进行明确地提交

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

上述例子涉及到了两个表，所以使用事务处理块来保证订单不被部分删除，最后的commit语句不仅在不出错时写出更改，如果第一条delete起作用，但第二条失败，则delete不会提交(实际上它被自动撤销)

当commit或rollback执行后，事物会自动关闭，将来的更改会隐含提交

(3) 使用保留点

简单的rollback和commit语句可以写入或撤销整个事物处理，但只能处理简单的事物处理，更复杂的事物处理可能需要部分提交或回退

为了支持部分提交或回退，必须能在事物处理块中找到合适的位置放置占位符，这样当需要回退时，可以回退到某个占位符，这些占位符称为保留点

创建保留点，可使用：

savepoint delete1;

每个保留点都有它的唯一名字，以便在回退时，MySQL知道要回退到何处

rollback delete1；

保留点越多越好，因为保留点越多，就能越灵活地进行回退

保留点在事物处理完成后自动释放

(4) 更改默认的提交行为

默认的MySQL行为是自动提交所有更改，即任何时候执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立刻生效，为指示MySQL不自动提交，需要使用：

set autocommit = 0；

autocommit标志决定是否自动提交更改，不管有没有commit语句。设置autocommit为0(假)指示MySQL不自动提交更改，直到autocommit被设置为1

第二十七章 全球化和本地化

1 字符集和校对顺序

数据库被用来存储和检索数据，不同的语言和字符集需要以不同的方式存储和检索，因此MySQL需要适应不同的字符集，适应不同的排序和检索数据的方法

几个重要术语：
1，字符集：为字母和符号的集合
2，编码：为某个字符集成员的内部表示
3，校对：为规定字符如何比较的指令

在MySQL的正常数据库活动(select，insert等)中，不需要操心太多。使用何种字符集和校对的决定在服务器，数据库和表级进行

2 使用字符集和校对顺序

MySQL支持众多的字符集，为查看所支持的字符集完整列表

show character set;

这条语句显示所有可用的字符集以及每个字符集的描述和默认校对

为了查看所支持的完整校对列表

show collation;

该语句显示所有可用校对，以及它们适用的字符集

通常系统管理在安装时定义一个默认字符集和校对。此处，也可以在创建数据库时，指定默认的字符集和校对

为了确认所用的字符集和校对，可以使用：

show variables like 'character%';
show variables like 'collation%';

实际上字符集很少是服务器或数据库范围的设置。不同的表，甚至不同的列都可能需要不同的字符集，而且两者都可以在创建表时指定

为了给表指定字符集和校对，可以使用带子句的create table

create table mytable
(
	columnn1 int,
	columnn2 varchar(10)
) default character set hebrew
collate hebrew_general_ci;

此语句创建了一个包含两个列的表，并且指定一个字符集和一个校对顺序，上述的例子指定了character set 和collate两者，一般MySQL如下确定使用什么样的字符集和校对
1，如果指定了character set和collate两者，则使用这些值
2，如果只指定character set，则使用此字符集及其默认的校对
3，如果不指定character，也不指定collate，则使用数据库默认

除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们：

create table mytable
(
	columnn1 int,
	columnn2 varchar(10),
	columnn3 varchar(10) character set latin1 
		collate latin1_general_ci;
) default character set hebrew
collate hebrew_general_ci;

这里对整个表以及一个特定的列指定了character set 和collate

校对在对用order by子句检索出来的数据排序时起重要作用，如果需要用与创建表不同的校对顺序排序特定的select语句时，可以在select语句自身中进行

select * 
from customers
order by lastname, firstname collate iatin1_general_cs;

此select语句使用collate指定一个备用的校对顺序

可以使用cast()或convert()函数进行串和字符集之间的转换

第二十八章 安全管理

1 访问控制

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少，即用户不能对过多的数据具有过多的访问权

考虑以下情况：
1，多数用户只需要对表进行读写，但少数用户需要创建删除表
2，某些用户需要读表，但可能不需要更新表
3，允许某些用户添加数据，但不允许他们删除数据
4，管理员可能需要处理用户账号的权限
5，根据用户登录的地点限制某些功能的访问

通过上述的思考，即需要给用户提供他们所需的访问权，且仅提供他们所需的访问权，这就是访问控制，管理访问控制需要创建和管理用户账号

访问控制的目的不仅仅是防止用户的恶意企图，通过保证用户不能执行他们不该执行的语句，访问控制有助于避免操作不当导致的数据库混乱

迄今为止对数据库的操作都是提高root进行的，但是在日常工作中，绝不能使用root，应该创建一系列的账号，有的用户管理，有的供用户使用，有的供开发维护人员使用等等

2 管理用户

MySQL用户账号和信息存储在名为mysql的MySQL数据库中，一般不需要直接访问mysql数据库和表，但有时需要直接访问

直接访问的时机之一是需要获得所有用户账号列表：

mysql数据库中有一个名为user的表，它包含所有的用户账号，user表中有一个名为user的列，它存储用户登录名

(1) 创建用户账号

可以使用create user语句创建一个新用户账号：

创建时需要给出新用户名和口令，identified指定的口令为纯文本，MySQL将在保存到user表之前对其加密

除了使用create user外，还可以使用grant语句创建新用户，或直接插入行到user中来添加新用户

为了重新命名一个用户账号，可使用 rename user语句

(2) 删除用户账号

删除一个用户账号，可使用drop user语句：

(3) 设置访问权限

在创建用户账号后，必须接着分配访问权限，新创建的用户账号没有访问权限，他们能登录MySQL，但不能看到数据，不能执行任何数据库操作

为了看到赋予用户账号的权限，可使用show grant for语句：

输出结果显示有一个权限 usage on，usage表示根本没有权限，此结果表示在任意数据库和任意表上对任何东西没有权限

MySQL的权限用户名和主机名结合定义，如果不使用主机名，则使用默认的主机名

为了设置用户权限，使用grant语句需要注意:
1，要授予的权限
2，被授予访问权限的数据库或表
3，用户名

此grant语句允许用户在crashcourse数据库的所有表上使用select，用户由此得到了crashcourse数据库中所有表的只读访问权限

每个grant添加或更新用户的一个权限，MySQL读取所有的授权，并根据它们确定权限

grant的反操作是revokr，用它来撤销特定的权限：

使用revoke时，被撤销的权限必须存在，否则会出错

grant和revoke可在几个层次上控制访问权限：
1，整个服务器，使用grant all和revoke all;
2，整个数据库，使用on database.*;
3，特定的表，使用on database.table;
4，特定的行
5，特定的存储过程

使用grant和revoke可以对用户对数据库的访问做出有效的控制

(4) 更改口令

更改用户口令，可以使用set password语句：

set password还可以用来设置自己的口令，在不指定用户名时，set password更新当前登录用户的口令

set password = password('123456');