mysql（一）

一、存储引擎

       插件式存储引擎，包括MyISAM，InnoDB，BDB，MEMORY,MERGE,EXAMPLE,NDB cluster,ARCHIVE,CSV,BLACKHOLE，FEDERATED，其中InnoDB和BDB提供安全事务表，其他的存储引擎都是非安全事务表。

a、查看默认的存储引擎

show variables like 'default_storage_engine';

b、查看支持的存储引擎

show variables like 'have%'

show ENGINES \G;

创建新表时指定存储引擎

create table ai (
   i bigint(20) not null auto_increment, 
   primary key (i) 
 )engine=MyIsam default charset=gbk;

使用alter table修改一个存在的表的存储引擎

alter table ai engine=innodb;

7.2 各种存储引擎的特性

7.2.1 MyISAM

        不支持事务、不支持外键、支持表锁，其优势是访问的速度快，对事务完整性没有要求或者以Select、Insert为主的应用基本上都可以使用这个引擎来创建表

       每个MyISAM在磁盘上存储成3个文件，其文件名和表明都相同，但扩展名分别为

       .frm：存储表定义

       .MYD：存储数据（DATA）

       .MYI：存储索引（INDEX）

       MyISAM类型的表可能损坏，MyISAM提供表修复工具：CHECK TABLE检查表的健康；REPAIR TABLE修复一个损坏的MyISAM表。

       支持三种不同的存储格式：静态表（固定长度）：存储迅速，容易换成，出现故障容易恢复；占用空间大，空格问题

                                                  动态表（包含变长字段）：占用空间少；频繁更新和删除记录会产生碎片（使用OPTIMIZE TABLE改善）

                                                   压缩表myisampack，占据空间小，只读

7.2.2 InnoDB

         提供了具有提交、回滚和崩溃恢复能力的事务安全，但处理效率较差，占用更多的磁盘空间以保存数据和索引

1、外键约束

父表

 create table country( 
    country_id smallint unsigned not null auto_increment, country varchar(50) not null, 
    last_update TIMESTAMP not null default current_timestamp on update current_timestamp,--当更新这条记录的时候，这条记录的这个字段将会改变。即时间变为了更新时候的时间。
    primary key(country_id) 
)engine=innodb default charset=utf8;

子表

 create table city(
    city_id smallint unsigned not null auto_increment,
    city varchar(10) not null,
    country_id smallint unsigned not null,
    last_update timestamp not null default current_timestamp on update current_timestamp,
    primary key(city_id),
    KEY idx_fk_country_id(country_id),
    CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id)on delete restrict on update cascade
 )engine=innoDB default charset=utf8;

restrict和NO ACTION相同：限制在子表有关联记录的情况下父表不能更新

CASECASE：父表在更新和删除时，更新或者删除子表对应记录

SET NULL：父表在更新或者删除时，子表对应字段被set null

insert into country(
     country_id,
     country)
     values(
     1,
     'afghanistan');

insert into city(
     city_id,
     city,
     country_id)
     values(
     251,
     'Kabul',
     1);

select * from country where country_id = 1;
select * from city where country_id = 1;

 delete from country where country_id = 1;

      如果某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除

     在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查

     同样，在执行LOAD DATA和ALTER TABLE操作时，可以通过暂时关闭外键约束来加快处理的速度

SET FOREIGN_KEY_CHECKS = 0;SET FOREIGN_KEY_CHECKS = 1;

查看外键信息

show table status like 'city' \G

2、存储方式

      （1）使用共享表空间存储：创建的表结构保存在.frm中数据和索引保存在innodb_data_dir和innodb_data_path定义的表空间中，可以是多个文件

      （2）使用多表空间存储，这种方式创建的表结构依然保存在.frm文件中，但是每个表的数据和索引单独保存在.ibd中，如果是个分区表，则每个分区对应单独的ibd文件。

       多表空间的数据文件没有大小限制，不需要设置初始大小，也不要设置文件的最大限值、扩展大小等

7.2.3 MEMORY

          使用内存中的内容来创建表，每个MEMORY表只实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常快，因为数据放在内存中，并且默认使用hash索引，但是一旦服务关闭，表中的数据就会丢失掉。

create table tab_memory engine = memory
     select city_id,
     city,
     country_id
     from city group by city_id;

给memory表创建索引时，可以指定使用HASH索引还是BTREE索引

create index mem_hash using hash on tab_memory(city_id);
show index from tab_memory \G

     服务器需要足够的内存来维持同一时间使用的memory表，当不再需要memory表的内容之时，要释放memory表使用的内存，应执行delete from或truncate table，或者整个地删除表（使用drop table）

     每个Memory表中可以防止的数据量的大小，受到max_heap_table_size系统变量的约束，其系统出书值是16MB，可以根据需要加大。可以定义表时通过MAX_ROWS指定表的最大行数

     Memory类型的存储引擎主要用于内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效的对中间结果进行分析并得到最终的统计结果。

     对memory表的更新操作要谨慎，因为数据没有实际写入到磁盘中，所以一定要对下次重新启动服务器后如何获得这些修改后的数据有所考虑

7.2.4 MERGE

         MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身没有数据，对MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上式对内部的MyISAM表进行的。

        对MERGE表进行DROP操作只是删除MERGE的定义，对内部的表没有任何影响。

        MERGE表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm表存储表定义，另一个.MRG表包含组合表的信息（MERGE表由哪些表组成、插入新的数据时的依据）可以通过修改.MRG文件来修改MERGE表，但是修改后要通过FLUSH TABLES刷新。

create table payment_2006(
     country_id smallint,
     payment_date datetime,
     amount DECIMAL(15,2),
     KEY idx_fk_country_id(country_id)
     )engine=myisam;

create table payment_2007(
     country_id smallint,
     payment_date datetime,
     amount DECIMAL(15,2),
     KEY idx_fk_country_id(country_id)
     )engine=myisam;

create table payment_all(
     country_id smallint,
     payment_date datetime,
     amount DECIMAL(15,2),
     INDEX(country_id)
     )engine=merge union=(payment_2006,payment_2007) INSERT_METHOD=LAST;
     -- 使用FIRST或LAST值使得插入操作被相应的作用在第一个或者最后一个表上

insert into payment_2006 values(1,'2006-05-01',100000),(2,'2006-08-15',150000);

select * from payment_2006;
select * from payment_2007;
select * from payment_all;

在MERGE表payment_all中插入一条记录，犹如MERGE表的定义是INSERT_METHOD=LAST,虽然插入的记录时2006的，还是会插入到最后一个表payment_2007中；

insert into payment_all values(3,'2006-03-21'112200);

MERGE表不能只能的将记录写到对应的记录中

可以使用MERGE表来透明地对多个表进行查询和更新操作，而对按照时间记录的操作日志则可以透明地进行插入操作。

7.3 如何选择合适的存储引擎

      MyISAM：如果应用是以以读操作和插入操作为主，只有很少个更新操作，并且对事务的完整性、并发性要求不是很高。MyISAM是在Web，数据仓储和其他应用环境下最常使用的存储引擎之一。

      InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作。InnoDB存储引擎除了有效的降低了删除和更新导致的锁定，还可以确保事务的完整提交（commit）和回滚（rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择。

     MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。缺陷是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。

    MERGE：用于将一些等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。有点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。适合诸如数据仓储等VLDB环境十分合适。

二、索引

2.1 概述

     所有MySQL列类型都可以被索引，对相关列使用索引是提高select操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度，每种存储引擎对每个表至少支持16个索引，总索引长度至少为256字节。

      MyISAM和InnoDB默认创建的索引是BTREE索引。MEMORY默认使用HASH索引

      MySQL不支持函数索引但支持前缀索引

      MySQL支持全文（FULLTEXT）索引，可以用于全文搜索（目前只有MyISAM支持，并且只限于CHAR、VARCHAR和TEXT列）。

      创建索引

 create index cityname on city(city(10));

explain select * from city where city ='Fuzhou' \G

EXPLAIN语法详解

possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。

key：key列显示MySQL实际决定使用的键(索引)。如果没有选择索引，键是NULL。

ref：ref列显示使用哪个列或常数与key一起从表中选择行。
rows：rows列显示MySQL认为它执行查询时必须检查的行数。
Extra：该列包含MySQL解决查询的详细信息。
Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
       Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
       range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
       Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
       Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
       Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
       Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。
      Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描。
      Using index for group-by：类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或         DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。

using index 是覆盖索引，说明你的查询语句可以只通过查询索引里的信息就能得到结果。using where 则表示需要查询磁盘里存储的数据，速度会慢很多

drop index cityname on city;

2.2  设计索引的原则

       搜索的索引列，不一定是要选择的列。最适合索引的列是出现在where子句的列，或连接子句中指定的列，而不是出现select关键字后选择列表中的列。

       使用唯一索引。考虑某列中值的分布，索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用于记录性别的列，致函由‘M’和‘F’，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。

       使用短索引。可以节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘IO较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此MySQL也可以在内存中容纳更多的值，这样就增加来找到行而不用读取索引中较多块的可能行。

       利用最左前缀。在创建一个n列索引时，可利用索引的最左边的列集来匹配行。

       不要过度索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须更新，有时可能重构，因此索引越多，所花的时间越长。

       InnoDB表应尽量自己指定主键。对于InnoDB的表，记录默认会按照一定顺序保存，如果由明确定义的主键，则按照主键顺序保存；如果没有主键，但由唯一索引，则按照唯一索引的顺序保存；如果既没有主键又没有唯一索引，则表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行访问的速度是最快的。当表中同时由几个列都是惟一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。

2.3  BTREE索引和HASH索引

       MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引。

       HASH索引

•        只使用=或<=>操作符的等式比较
•        优化器不能使用HASH索引来加速ORDER BY操作
•        MYSQL不能确定在两个值之间大约有多少行，如果将一个MyISAM表改为HASH索引的MEMORY表，会影响一些查询的执行效率。
•        只能使用整个关键字来搜索一行

    BTREE索引

     当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE ‘pattern’，都可以使用相关列上的索引

eg. 使用于hash和btree索引的查询

select * from t1 where key_col = 1 or key_col in (15,18,20);

eg. 只适用于btree

select * from t1 where key_col > 1 and key_col<10;
select * from t1 where key_col like 'ab%' or key_col between 'lisa' and 'simon';

eg. 只有btree索引可以通过索引访问

 create table city_memory(
    city_id smallint unsigned not null auto_increment,
    city varchar(10) not null,
    country_id smallint unsigned not null,
    last_update timestamp not null default current_timestamp on update current_timestamp,
    primary key(city_id),
    KEY idx_fk_country_id(country_id),
    CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id)on delete restrict on update cascade
 )engine=memory default charset=utf8;
insert into city_memory select * from city;
explain select *from city where country_id > 1 and country_id < 10 \G

而hash索引实际上是全表扫描的       

三、视图

       视图（View）是从一个或多个表（或视图）导出的表。视图与表（有时为与视图区别，也称表为基本表——Base Table）不同，视图是一个虚表，即视图所对应的数据不进行实际存储，数据库中只存储视图的定义，在对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。

视图的优势

       简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

      安全：使用视图的用户只能访问他们被允许查询的结果集、对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

      数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列明，则可以通过修改视图来解决，不会造成对访问者的影响。

3.1 视图操作

      创建视图

      使用CREATE OR REPLACE VIEW语句可以修改视图。视图存在时，可以对视图进行修改；视图不存在时，可以创建视图。如果当前数据库中已经存在指定名称的视图时，没有该关键字，将会提示错误信息；如果使用了OR REPLACE关键字，则当前正在创建的视图会覆盖掉原来同名的视图。

     

 create or replace view staff_list as
     select s.staff_id,s.first_name,s.last_name,a.address
     from staff as s, address as a
     where s.address_id = a.address_id;

包含以下关键字的视图视图不能更新

       包含关键字：聚合函数（SUM、MIN、MAX、COUNT等），DISTINCT、GROUP BY、HAVING、UNION、或UNION ALL

       常量视图

       SELECT中包含子查询

       JOIN

       FROM一个不能更新的视图

eg 

-- 包含聚合函数
create or replace view payment_sum_view as 
    select staff_id, sum(amount)
    from payment group by staff_id;


-- 常量视图
create or replace view pi as select 3.141596 as pi;


-- select中包含子查询
 create view city_view as 
     select (select city from city where city_id = 1);

WITH[CASCADED|LOCAL]CHECK OPTION决定了是否允许更新数据使记录不再满足视图的条件。

LOCAL：只要满足本视图的条件就可以更新

CASCADED：则必须满足所有针对该视图的所有条件才可以更新

如果没有明确是LOCAL还是CASCADED，则默认是CASCADED

eg. 对payment表创建两层视图，并进行更新操作

create or replace view payment_view as
     select payment_id, amount from payment
     where amount < 10 with check option;


create or replace view payment_view as 
     select payment_id, amount from payment 
     where amount < 10 with check option;

create or replace view payment_view2 as 
     select payment_id, amount from payment
     where amount > 5 with cascaded check option;

select * from payment_view1 limit 1;

 update payment_view1 set amount=10
     where payment_id=3;

update payment_view1 set amount=1 where payment_id=3;

删除视图

      用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限

drop view staff_list;

查看视图

show tables;

show table status like 'staff_list' \G

查看视图表的定义

show create view staff_list \G

四、存储过程和函数

        存储过程和函数是实现经过编译并存储在数据库中的一段SQL语句的集合，

存储过程的优点：
　　 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
　　2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
　　3.存储过程可以重复使用,可减少数据库开发人员的工作量
　　4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

存储过程和函数的区别：

       1.函数必须有返回值，而存储过程没有。

       2.存储过程的参数可以使用IN、OUT、和INOUT类型；而函数的参数只能是IN类型的

存储过程和函数的相关操作

      对存储过此和或函数进行操作时，需要首先确定用户是否具有相应的权限。例如，创建存储过程或者函数需要create routin权限，修改或者删除存绰过程或者函数需要alter routine权限，执行存储过程或者函数需要execute权限

      MySql的存储过程和函数中允许包括DDL语句，也允许在存储过程中执行提交（commit）或者回滚（rollback），但是存储过程和函数中不允许执行LOAD DATA INFILE语句。执行存储过程和函数中可以调用其他的过程或者函数

注：load data infile语句从一个文本文件中以很高的速度读入一个表中。使用这个命令之前，mysqld进程（服务）必须已经在运行。为了安全原因，当读取位于服务器上的文本文件时，文件必须处于数据库目录或可被所有人读取。另外，为了对服务器上文件使用load data infile，在服务器主机上你必须有file的权限。

delimiter $$

create procedure film_in_stock(IN p_film_id INT,IN p_store_id INT,OUT p_film_count INT) 
BEGIN    
        select inventory_id     
        from inventory    
        where film_id = p_film_id     
        and store_id = p_store_id  
        and inventory_in_stock(inventory_id);  
        select FOUND_ROWS() into p_film_count; 
END$$

delimiter ;

 call film_in_stock(2,2,@a)

select @a;

注： @--系统变量

select inventory_id
    -> from inventory
    -> where film_id = 2
    -> and store_id = 2
    -> and inventory_in_stock(inventory_id);

注：存储过程和直接执行SQL的效果相同，但是存储过程的好处在于处理逻辑都封装在数据库端

       创建视图时，存储过程和杉树的create语法不支持create or replace对存储过程和函数进行修改，如果需要对已有的存储过程和函数进行修改，需要执行alter语法。

       其他语法说明：

       

{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
-- 这些特征提供子程序使用数据的内在信息，这些特征值目前只提供给服务器。
-- CONTAINS SQL 表示子程序不包含读或写数据的语句
-- NOSQL 表示子程序不包含SQL语句
READS SQL DATA表示子程序包含读数据的语句
MODIFIES SQL DATA 表示子程序包含写数据的语句
默认值是CONTAINS SQL

SET SECURITY {DEFINER | INVOKER}
-- 可以用来指定子程序该用创建子程序者的许可来执行，还是用调用者的许可来执行

使用SQL SECRITY

create procedure film_in_stock_definer(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) 
   SQL SECURITY DEFINER  -- 使用创建者的权限执行
   BEGIN     
      select inventory_id     
      from inventory     
      where film_id = p_film_id     
      and store_id = p_store_id     
      and inventory_in_stock(inventory_id);            select FOUND_ROWS() INTO p_film_count; 
END$$

create procedure film_in_stock_definer(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT) 
   SQL SECURITY DEFINER  -- 使用调用者的权限执行
   BEGIN     
      select inventory_id     
      from inventory     
      where film_id = p_film_id     
      and store_id = p_store_id     
      and inventory_in_stock(inventory_id);            select FOUND_ROWS() INTO p_film_count; 
END$$

给lisa赋予执行存储过程的权限（select,update，delete权限同）

GRANT execute on sakila.* to 'lisa'@'localhost'$$

exit

然后使用lisa登录

select count(*) from inventory;

call film_in_stock_definer(2,2,@a);

      film_in_stock_definer是以创建者的权限执行的，因为是root创建爱你的所以可以访问inventory表的内容，film_instock是以调用者的权限执行的，lisa用户没有访问inventory表的权限，所以会提示权限不足。

删除存储过程或者函数

drop procedure film_in_stock;

查看存储过程或者函数的状态

mysql> show procedure status like 'film_in_stock' \G

查看存储过程或者函数的定义

show create procedure film_in_stock \G

变量的使用

BEGIN
     declare last_month_start date;
     -- .....
END $$

定义条件和处理

条件的定义和处理可以用来定义在处理过程中遇到问题时响应的处理步骤

delimiter $$
create procedure actor_insert() 
BEGIN    
    declare continue handler for sqlstate '23000' set @x2 = 2; -- 条件处理，
    set @x = 1; 
    insert into actor(actor_id, first_name, last_name) values(201, 'Test', '201');
    set @x = 2; insert into actor(actor_id, first_name, last_name) values(1, 'Test', '1'); --主键重错误ERROR 1062(23000)
    set @x = 3; 
END;$$

select @x2, @x;

如果没有进行条件处理，那么在主键重的时候会抛出异常并退出

条用条件处理的过程，再遇到主键重错误时，会按照定义的方式进行处理，由于例子中定义的是continue，所以会继续执行下面的语句。

handler_type现在还支持continue和exit两种

continue：继续执行下面的语句

exit：终止执行

定义条件处理的其他形式

--捕获mysql error code
declare continue handler for 1062 set @x2=1

--实现定义 condition name
declare DuplicateKey condition for sqlstate '23000';
decalre continue handler for DuplicateKey set @x2 = 1;

-- 捕获SQLEXCEPTION
declare continue handler for sqlexception set @x2=1;

光标的使用

       询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果集中的记录。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

create procedure payment_stat() 
BEGIN     
     declare i_staff_id int;     
     declare d_amount decimal(15,2);
     declare cur_payment cursor for select staff_id, amount from payment;
     declare exit handler for not found close cur_payment;
     set @x1 = 0;
     set @x2 = 0;
     open cur_payment;  -- 打开光标
     repeat
        fetch cur_payment into i_staff_id, d_amount;-- 逐行读取光标
        if i_staff_id = 2 then
            set @x1 = @x1 + d_amount;
        else
            set @x2 = @x2 + d_amount;
        end if;
     until 0 end repeat;
     close cur_payment;-- 关闭光标
END;$$

call payment_stat();
select @x1,@x2;

五、触发器

       触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

        触发器只能创建在永久表上，不能对临时表创建触发器。

        对同一个表触发时间的相同触发事件，只能定义一个触发器。（在oracle数据库中，可以定义成两个不同update触发器，更新不同的字段时，触发单独的触发器，但在mysql中，只能定义成一个触发器，在触发器中通过判断更新的字段进行对应的处理）

        使用别名old和new来引用触发器中发生变化的记录内容

        只支持行级触发，不支持语句触发

CREATE TABLE film_text (
        film_id SMALLINT NOT NULL ,  
        title VARCHAR ( 255 ) NOT NULL ,
        description TEXT ,     
        PRIMARY KEY    ( film_id ) , 
        FULLTEXT KEY idx_title_description ( title , description )
) ENGINE = MyISAM DEFAULT CHARSET = utf8 ;

delimiter $$
create trigger ins_film 
after insert on film for each row  
begin 
     insert into film_text(film_id, title, description) values(new.film_id, new.title, new.description);
end $$
delimiter ;


insert into film values 
      (1001, 'ACADEMY DINOSAUR', 
       'A Epic Drama of a Feminist and a Mad Secientist who must Battle a Teacher in The Canadian Rockies',
       2006,1,NULL,6,'0.99',86,'20.99','PG','Deleted Scenes.Behind the Scenes','2006-02-15 05:03:42');



select * from film_text where film_id = 1001 \G

     对于有重复记录，需要进行update操作的insert，触发器触发的顺序是before insert、before update、after update；对于没有重复记录的insert，就是简单的执行insert操作，触发的顺序是before insert、after insert。对于实际执行update操作的记录，仍然会执行before insert触发器的内容。 

删除触发器

drop trigger ins_film;

查看触发器

show triggers \G

触发器的使用

触发器语句的执行有以下两个限制

      （1）触发器不能调用将数据返回客户端的存储程序，也不能使用采用call语句的动态sql语句，但是允许存储程序通过参数将数据返回触发程序（即存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器），但是不能调用直接返回数据的过程。

      （2）不能再触发器中使用以显式或者隐式开始或结束事务的语句，如START TRANSACTION、COMMIT、或ROLLBACK

MySQL的触发器是安装before触发器、行操作、after触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作，如果是对事务表进行的操作，那么会整个作为一个事务被回滚；对非事务表的操作，那么已经更新记录将无法回滚。

六  事务控制和锁定语句

        MySQL支持对MyISAM和MEMORY存储引擎的表进行表级锁定，对BDB存储引擎的表进行页级锁定，对InnoDB存储引擎的表进行行级锁定。默认情况下，表锁和行锁都是自动获得的，不需要额外的命令。但是在有的情况下，用户需要使用事务控制和锁定语句来完成。

6.1 Lock Table 和 Unlock Table

      LOCK TABLES可以锁定用于当前线程的表，如果表被其他线程锁定，则当前线程会等待。

      UNLOCK TABLES可以释放当前线程获得的任何锁定，当前线程执行另一个LOCK TABLES时，或与服务器的连接被关闭时，所有由当前线程锁定的表都被隐含的解锁。

      eg-1  当session1获得表film_text的 read锁定

lock table film_text read;

session1可以查询该表的记录

select film_id, title from film_text;

session2也可以对表进行查询

但是session2要对film_text进行更新，则等待

当session1 unlock tables,session2才执行成功，共等待了2分7秒

6.2 事务控制

      MySQL通过SET AUTOCOMMIT、START TRANSACTION、COMMIT和ROLLBACK等支持本地事务。

      默认情况下，MYSQL是自动提交的。

      START TRANSACTION和BEGIN开始一项信的事务

      COMMIT和ROLLBACK提交和回滚事务

      CHAIN和RELEASE子句分别用来定义在事务提交和回滚之后的操作。CHAIN立即启动一个新事物，并且和刚才的事务具有相同的隔离级别；RELEASE则会断开和客户的连接。

      SET AUTOCOMMIT可以修改当前连接的提交方式，若设置SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交和回滚。

eg-1 用start transaction开始的事务在提交后自动回到自动提交的方式

        如果在提交的时候使用commit and chain，那么会在提交后立即开始一个新的事务

eg-2  如果在锁表期间用start transaction命令开始一个新的事务，会造成一个隐含的unlock tables被执行。

           因此，在同一个事务中，最好不使用不同存储引擎的表，否则rollback时需要对非事务类型的表进行特别的处理，因为COMMIT，因为COMMIT、ROLLBACK只能对事务类型的表进行提交和回滚。

        通常，只对提交的事务记录到二进制的日志中，但是如果一个事务中包含非事务类型的表，那么回滚操作也会被季度到二进制文件中，以确保非事务类型表的更新可以被复制到从数据库中。

       在事务中通过定义savepoint，指定回滚事务的一个部分。

       可以定义多个不同的savepoint，如果定义了相同名字的savepoint，则后面定义的savepoint会覆盖之前的定义。

       对于不再使用的savepoint，可以通过release savepoint删除。

eg-3 通过定义savepoint来指定需要回滚的事务的位置。

七  安全问题

7.1 简介 

       SQL注入（SQL Injection）：利用某些数据库的外部接口将用户数据插入到实际的数据库操作语言当中，从而达到入侵数据库乃至操作系统的目的。

      产生原因：主要是由于程序对用户输入的数据没有进行严格的过滤，导致非法数据库查询语句的执行。

      危害：攻击者可以利用它读取、修改或者删除数据库内的数据，获取数据库中的用户名和密码等敏感信息，甚至可以获得数据库管理员的权限，而且，SQL注入很难防范。网站管理员无法通过安装系统补丁或者简单的安全配置进行自我保护，一般的防火墙也无法拦截SQL注入攻击。

eg SQL注入全过程

7.2 应对措施

 （1）PrepareSatement+Bind-Variable

          可以使得输入参数中的单引号'，注释/*和#被正常转义

 （2） 使用应用程序提供的转换函数

            很多应用程序接口都提供了对特殊字符进行转换的函数，恰当的使用这些函数，可以防止应用程序用户输入使用应用程序生成不期望的语句。

 （3） 自己定义函数进行校验

          如果现有的转换函数仍然不能满足要求，则需要自己编写函数进行输入校验。输入验证的途径分为以下几种

•           整理数据使之变得有效；
•           拒绝已知的非法输入；
•           只接受已知的合法输入；

       最好的解决方法：正则表达式。只要过滤非法的符号组合就可以阻止已知形式的攻击。