学习预览:
(一)mysql 运维基础篇(Linux云计算从入门到精通)
(三)mysql 触发器、存储过程和函数(数据库运维基础补充)
(五)mysql数据备份—物理备份(完备+lvm快照+xtrabackup)+逻辑备份(mysqldump+导入导出)
(六)mysql复制技术—M-S主从配置(传统+GTID)+M-M-S-S主从配置(GTID)
(七)mysql中间件mycat配置和部署(基于M-M-S-S)
(八) 数据库集群技术—Galera Cluster安装与配置
(九)数据库集群技术Galera+mycat(数据库运维学习终章)
CONTEND
二、mysql存储过程procedure和函数function
一、MySQL触发器Triggers
1.1 触发器简介
触发器( trigger )是一个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert , delete,update )时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
例如,当学生表中增加了一个学生的信息时,学生的总数就应该同时改变。因此可以针对学生表创建一个触发器,每次增加一个学生记录时,就执行 一次学生总数的计算操作,从而保证学生总数与记录数的一致性。
1.2 创建触发器
(1)先举个简单的示例帮助我们理解这个触发器:创建两个表student和student_total,表student每次插入一个学生数据或删除学生数据,student_total作相应的修改:
--创建表并插入数据
create table student ( id int unsigned auto_increment primary key not null, name varchar(50) not null, age int not null default 18);
create table student_total ( total_num int );
insert into student_total value(0);
--下面开始创建触发器,针对insert操作
delimiter $
create trigger stu_insert after insert on student for each row begin update student_total set total_num=total_num+1; end$
--针对delete操作
create trigger stu_delete after delete on student for each row begin update student_total set total_num=total_num-1; end$
--把分隔符修改回来然后开始插入,删除测试下
delimiter ;
insert into student(name) values ('alice'),('bob');
select * from student_total;
+-----------+
| total_num |
+-----------+
| 2 |
+-----------+
delete from student where name='alice';
select * from student_total;
+-----------+
| total_num |
+-----------+
| 1 |
+-----------+
(2) 进阶一下,创建tab1和tab2,创建触发器(insert、delete和update)接着插入、删除更新数据试试
create table tab1(id int auto_increment primary key,name varchar(50) not null, sex enum('male','female') default 'male' , age int not null);
create table tab2( id int primary key auto_increment, name varchar(50), salary double(10,2) default 5000);
这边要强调一点,当你删除或者更新的时候,后面一定跟的是主键。比如id就是主键,否则会造成一些问题如误删数据等,这个要谨慎! (还有删除后面跟的是主键就可以了,但更新的话要设置所有的字段都更新哦)
create trigger tab1_insert after insert on tab1 for each row begin insert into tab2 values (new.id,new.name,5000); end$
create trigger tab1_delete after delete on tab1 for each row begin delete from tab2 where id=old.id; end$
create trigger tab1_update after update on tab1 for each row begin update tab2 set id=new.id,name=new.name where id=old.id; end$
insert into tab1 values (1,'alice','male',19),(2,'bob','male',20),(4,'Gwen','female',23);
select * from tab1;
+----+-------+--------+-----+
| id | name | sex | age |
+----+-------+--------+-----+
| 1 | alice | male | 19 |
| 2 | bob | male | 20 |
| 4 | Gwen | female | 23 |
+----+-------+--------+-----+
select * from tab2;
+----+-------+---------+
| id | name | salary |
+----+-------+---------+
| 1 | alice | 5000.00 |
| 2 | bob | 5000.00 |
| 4 | Gwen | 5000.00 |
+----+-------+---------+
delete from tab1 where id=4;
select * from tab2;
+----+-------+---------+
| id | name | salary |
+----+-------+---------+
| 1 | alice | 5000.00 |
| 2 | bob | 5000.00 |
+----+-------+---------+
update tab1 set name='aliceCC' where id =1;
select * from tab2;
+----+---------+---------+
| id | name | salary |
+----+---------+---------+
| 1 | aliceCC | 5000.00 |
| 2 | bob | 5000.00 |
+----+---------+---------+
(3)再来一个示例,帮助我们再进一步去理解这个触发器。创建 t1和t2 表,如下:
create table t1 (id int auto_increment primary key, name varchar(50), salary double(10,2));
create table t2(
-> total_num int not null,
-> total_salary double(10,2));
insert into t2 values(0,0);
select * from t2;
+-----------+--------------+
| total_num | total_salary |
+-----------+--------------+
| 0 | 0.00 |
+-----------+--------------+
delimiter $
create trigger total_num_salary after insert on t1 for each row begin update t2 set total_num=total_num+1,total_salary=total_salary+new.salary;end$
create trigger total_num_salary2 after delete on t1 for each row begin update t2 set total_num=total_num-1,total_salary=total_salary-old.salary; end$
delimiter ;
insert into t1(name,salary) values
-> ('alice',6000),
-> ('bob',6500),
-> ('jack',10000);
select * from t2;
+-----------+--------------+
| total_num | total_salary |
+-----------+--------------+
| 3 | 22500.00 |
+-----------+--------------+
delete from t1 where name='jack';
select * from t2;
+-----------+--------------+
| total_num | total_salary |
+-----------+--------------+
| 2 | 12500.00 |
+-----------+--------------+
1.3 触发器的管理(查看,删除)
这个比较简单,记住语法格式就好了。
- 查看操作
- 删除操作
二、mysql存储过程procedure和函数function
2.1 procedure和function概述:
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。
存储过程和函数的区别:
- 函数必须有返回值,而存储过程没有。
- 存储过程的参数可以是IN、OUT、INOUT类型,函数的参数只能是IN
优点:
- 存储过程只在创建时进行编译;而SQL语句每执行一次就编译一次,所以使用存储过程可以提高数据库执行速度。
- 简化复杂操作,结合事务一起封装。
- 复用性好
- 安全性高,可指定存储过程的使用权。
说明:
- 并发量少的情况下,很少使用存储过程。
- 并发量高的情况下,为了提高效率,用存储过程比较多。
2.2、创建与调用存储过程
创建存储过程语法:下面举出一些简单的例子
(1)无参数的形式
前面我就利用了存储过程批量插入数据,在回顾下,只是我们没有利用参数传递,那就很不灵活了,如果下次插入1000条呢?
#下面这就是创建存储过程,实现批量插入数据,这个后面我会写博客介绍的,在这里就是实现批量插入数据的功能
create procedure autoinsert()
begin
declare i int default 1;
while(i<200000)do
insert into company.t1 values(i,'ggg');
set i=i+1;
end while;
end$$
delimiter ; #把分隔符修改回来
call autoinsert(); #执行这个函数,开始插入数据
(2)参数形式:IN,利用它可以传入参数方便之后修改,变得灵活!重新看一个例子。md5()是个函数,计算哈希值的。
delimiter $$
create procedure autoinsert3(IN a int)
-> begin
-> declare i int default 1;
-> while(i<=a)do
-> insert into student.autoinsert values(i,md5(i));
-> set i=i+1;
-> end while;
-> end$$
delimiter ;
call autoinsert3(10);
select * from autoinsert;
+------+----------------------------------+
| id | name |
+------+----------------------------------+
| 1 | c4ca4238a0b923820dcc509a6f75849b |
| 2 | c81e728d9d4c2f636f067f89cc14862c |
| 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 |
| 4 | a87ff679a2f3e71d9181a67b7542122c |
| 5 | e4da3b7fbbce2345d7772b0674a318d5 |
| 6 | 1679091c5a880faf6fb5e6087eb1b2dc |
| 7 | 8f14e45fceea167a5a36dedd4bea2543 |
| 8 | c9f0f895fb98ab9159f51fd0297e236d |
| 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 |
| 10 | d3d9446802a44259755d38e6d163e820 |
+------+----------------------------------+
(3)out 用法参例:
delimiter $$
create procedure p2 (out param1 int) --创建存储过程
-> begin
-> select count(*) into param1 from mysql.user;
-> end$$
delimiter ;
select @a; --查看a变量是否为空,为空后面让它传参
call p2(@a); --调用参数
select @a; --可以直接用变量查询和后面的select语句是一样的
+------+
| @a |
+------+
| 4 |
+------+
select count(*) from mysql.user;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
(4)in和out同时用,参例如下:
#统计指定部门的员工数
mysql> delimiter $$
mysql> create procedure count_num(in p1 varchar(50),out p2 int)
-> begin
-> select count(*) into p2 from company.employee
-> where post=p1;
-> end$$
mysql> delimiter ;
mysql> call count_num('sale',@b);
mysql> select @b;
+------+
| @b |
+------+
| 4 |
+------+
#统计指定部门工资超过例如2000的总人数
mysql> delimiter $$
mysql> create procedure count_num1(in p1 varchar(50),in p2 float(10,2),out p3 int)
-> begin
-> select count(*) into p3 from company.employee
-> where post=p1 and salary>=p2;
-> end$$
mysql> call count_num1('hr',2000,@c)$$
mysql> select @C$$
+------+
| @C |
+------+
| 1 |
+------+
(5)inout参例
create procedure inout_test(inout p1 int) begin if(p1 is not null)then set p1=p1+1; else set p1=100; end if; end$$
call inout_test(@e)$$
select(@e)$$
+------+
| (@e) |
+------+
| 100 |
+------+
call inout_test(@e)$$
select(@e)$$
+------+
| (@e) |
+------+
| 101 |
+------+
2.3 创建函数
在我创建函数的时候出现以下错误,百度了下修改下就可以了
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
set global log_bin_trust_function_creators=TRUE;
参例:
create function name_from_emp(a int) returns varchar(50) begin return(select name from employee where id=a); end$$
select name_from_emp(5)$$
+------------------+
| name_from_emp(5) |
+------------------+
| jack |
+------------------+
select * from employee where name=name_from_emp(5)$$ --查看全部信息也可调用这个函数
+----+------+------+------------+------------+-----------------+---------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+------+------+------------+------------+-----------------+---------+--------+--------+
| 5 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
+----+------+------+------------+------------+-----------------+---------+--------+--------
2.4 存储过程和函数的维护
这个和之前的table,index等差不多,如下:
在创建存储过程的时候,多了变量这个概念,mysql变量的术语一般用得着是用户变量和全局变量:
1.用户变量:以"@"开始,形式为"@变量名",由客户端定义的变量。
用户变量跟mysq|客户端是绑定的, 设置的变量只对当前用户使用的客户端生效,当用户断开连接自动释放。
2.全局变量:定义时如下两种形式, set GLOBAL变量名或者set @@global.变量名
对所有客户端生效,但只有具有super管理员的权限才可以设置全局变量。
这个做个了解,后面还会用的着的。