Mysql性能調優(三)-有解無憂

前言

??最近的文章均是給大家介紹mysql的性能優化，前兩篇文章我們分別介紹了索引的相關知識，主要包括：索引的基本概念、參考索引的優缺點、索引的底層結構、索引的分類以及應用索引的設計原則及其語法，接下來的文章又介紹了視圖以及觸發器，分別介紹視圖以及觸發器的概念、創建、修改以及查看和洗掉的相關內容，
??本文將給大家介紹存盤程序與存盤函式的相關內容，這里主要包括存盤程序的基本概念、創建、呼叫、洗掉存盤程序，另外還介紹了在mysql中變數的申明以及賦值、應用，最后將給大家介紹mysql中存盤程序中的三種回圈結構，并且，給大家介紹游標的相關內容，
??首先給大家介紹存盤程序的相關內容，

一、存盤程序

1、存盤程序與存盤函式概述

??存盤程序和函式是事先經過編譯并存盤在資料庫中的一段SQL陳述句集合，呼叫存盤程序和函式可以簡化應用開發人員的很多作業，減少資料在資料庫和應用服務器之間的傳輸，對于提高資料處理的效率是由好處的，
??其實存盤程序和存盤函式最關鍵的區別在于：函式必須有回傳值，而存盤程序沒有，

函式：是一個有回傳值的程序；
程序：是一個沒有回傳值的函式；

2、創建存盤程序

??接下來給大家介紹存盤程序的創建語法，

create procedure procedure_name([proc_parameter[,...]])
begin
	--SQL陳述句
end;

??我們通過一個案例來具體說明存盤程序的創建，方便大家更好的理解存盤程序的語法，

delimiter $
create procedure pro_test1()
begin
	select 'hello mysql';
end $
delimiter ;

??這里需要我們注意的是：我們在案例中用到了一個關鍵字——“delimiter”，該關鍵字用來宣告sql陳述句的分隔符，這個關鍵字其實就是告知mysql解釋器，該命令是否已經結束了，mysql是否可執行了，一般在默認情況下：delimiter是;，在命令列客戶端中，如果有一行命令以分號結束，那么回車后，mysql將會執行該命令，

3、呼叫存盤程序

??我們創建好存盤程序之后，可以再mysql中呼叫存盤程序，呼叫語法如下：

call procedure_name()

4、查看存盤程序

??我們在呼叫存盤程序之后，就可以在MySQL中查看存盤程序，以下就是存盤查看陳述句的如下：

--首先我們查詢db_name資料庫中的所有的存盤程序；
select name from mysql.proc where db = 'db_name';
--查詢存盤程序的狀態資訊
show procedure status;
--查詢某個存盤程序的定義
show create procedure test.pro_test1 \G;

5、洗掉存盤程序

??存盤程序與其他的mysql陳述句一樣，可以創建，可以查看，當然也可以洗掉，接下來，我們介紹mysql中存盤程序的洗掉，洗掉的語法如下：

drop procedure [if exists] sp_name;

6、語法

??通過前面的介紹，相信大家對存盤程序有一定的了解，其實，我們學過編程的可以知道，存盤程序也是可以編程的，這就意味著我們可以同樣地在創建存盤的程序也可以使用我們常見的變數、運算式以及控制結構，可以完成比較復雜的功能，

(1)、變數

declare
??我們首先給大家介紹存盤程序中的變數，變數的宣告用declare關鍵字，我們可以通過declare可以定義一個區域變數，但是該變數的作用范圍只能在begin…end塊中，主要的語法如下：

declare var_name[,...] type [default value]

??我們介紹了declare的語法，我們通過一個案例來說明declare的用法，

delimiter $
create procedure pro_test2()
begin
	declare num int default 5;
	select num+ 10;
end$
delimiter ;

set
??我們前面介紹了declare定義一個區域變數，接下來，我們介紹set直接賦值，可以賦常量或者賦運算式，具體的語法如下：

set var_name = expr [, var_name = expr]...

??我們介紹了set的語法，我們通過一個案例來說明set的用法，

delimiter $
create procedure pro_test3()
begin
	declare name varchar(20);
	set name = 'mysql';
	select name;
end$
delimiter ;

??當然，我們可以通過select…into方法進行賦值操作，接下來，我們可以通過一個案例來說明用法：

delimiter $
create procedure pro_test5()
begin
	declare countnum int;
	select count(*) into countnum from city;
	select countnum;
end$
delimiter ;

(2)、if條件判斷

??前面主要介紹了存盤程序中的變數，主要包括定義一個變數declare以及賦值關鍵字set，接下來，給大家介紹if條件的判斷陳述句，語法結構如下：

if search_condition then statement_list
	[elseif search_condition then statement_list]
	[else statement_list]
end if

??接下來我們通過一個案例來呈現if條件判斷陳述句的實作，為了方便大家理解，具體的案例如下：

??根據定義的身高變數，判定當前身高的所屬的身材型別，

180及以上 ——————> 身材高挑
170-180 ——————> 標準身材
170以上 ——————> 一般身材

??具體存盤程序如下:

create procedure pro_test4()
begin
	declare height int default 175;
	declare description varchar(50) default '';
	if height >= 180 then set description = '身材高挑';
	elseif height >= 170 and height < 180 then set description = '標準身材';
	else set description = '一般身材';
end if;
select concat('身高', height, '對應的身材型別為:',description);
end $;

??我們可以通過呼叫陳述句來實作存盤程序，具體實作如下：

call pro_test6();

??具體的結果如下：

(3)、傳遞引數

??前面我們也提到過，存盤程序也可以編程，也有函式的，我們學過編程語言的，無論是java也好還是c語言也罷，其中函式均可以傳引數的，具體語法的格式如下：

create procedure procedure_name([in/out/inout]引數名 引數型別)

??根據我們剛才介紹的傳遞引數的語法中，有in、out以及inout，因此，我們接下來給大家介紹他們三者的含義：

in：該引數可以作為輸入，也就是需要呼叫方傳入值，這里我們如果不特殊指定就是默認值，
out：該引數作為輸出，也就是該引數可以作為回傳值
inout：既可以作為輸入引數，也可以作為輸出引數

??還是之前我們提到的身高問題來分別為大家實作in、out與inout的使用方法，由于前面的我們提到的身材型別，因此，就不在重新描述，
??首先，讓我們用in來實作身高問題，具體的實作陳述句如下：

create procedure pro_test5(in height int)
begin
	declare description varchar(50) default '';
	if height >= 180 then set description = '身材高挑';
	elseif height >= 170 and height < 180 then set description = '標準身材';
	else set description = '一般身材';
end if;
select concat('身高', height, '對應的身材型別為:',description);
end $;

??接下來讓我們out輸出實作身高問題

create procedure pro_test5(in height int, out description varchar(100))
begin
	declare description varchar(50) default '';
	if height >= 180 then set description = '身材高挑';
	elseif height >= 170 and height < 180 then set description = '標準身材';
	else set description = '一般身材';
end if;
end $;

??接下來，我們呼叫這個函式

call pro_test5(168, @description)$
select @description$

??這里需要我們注意的是在呼叫中出現了一個@description的關鍵字，接下來給大家介紹相關的知識：

?? @description：這種變數要在變數名稱前面加“@”符號，這種符號叫做用戶會話變數，代表整個會話程序他都是有作用的，這個類似于全域變數一樣，
??@@global.sort_buffer_size:這種在變數前加上“@@”符號，叫做系統變數，

(4)、case結構

??前面介紹了if條件判斷，接下來我們介紹case結構判斷，具體的語法結構如下：

--**方法一**：
case case_value
	when when_value then statement_list
	[when when_value then statement_list]...
	[else statement_list]
end case;
--方法二：
case
	when search_condition then statement_list
	[when search_condition then statement_list]...
	[else statement_list]
end case

??以上是case的基本語法，接下來我們通過一個案例來說明case的用法；具體的案例如下：

??給定一個月份，然后計算出所在的季度

create procedure pro_test7(mon int)
begin
	declare result varchar(10);
	case
		when mon >=1 and mon <= 3 then
			set result = '第一季度';
		when mon >=4 and mon <= 6 then
			set result = '第二季度';
		when mon >=7 and mon <= 9 then
			set result = '第三季度';
		else 
			set result = '第四季度';
	end case;
	select concat('傳遞的月份為：', mon, ',計算出的結果為：', result) as content;
end$

(5)、while回圈

??前面介紹了條件格式；其中包括我們常見的if陳述句和case陳述句，接下來，給大家介紹回圈陳述句，其中主要包括while、repeat和loop函式，首先給大家介紹while回圈，具體語法如下：

while search_condition do
	statement_list
end while;

??接下來我們通過一個案例來說明case的用法；具體的案例如下：

??計算從1加到n的值 --累加

??具體的實作如下：

create procedure pro_test8(n int)
begin
	declare total int default 0;
	declare num int default 1;
	while num <= n do 
		set total = total + num;
		set num = num + 1;
	end while;
	select total;
end$

(6)、repeat結構

??我們剛才為大家介紹了while回圈，接下來給大家介紹repeat結構，repeat結構是有條件的回圈控制陳述句，當滿足條件的時候退出回圈，接下里給大家介紹repeat與while兩種回圈的結構的區別：

while是滿足條件才執行的
repeat是滿足條件就退出回圈

??具體的語法如下：

repeat 
	statement_lsit
	until search_condition
end repeat;

??接下來我們通過一個案例來說明repeat的用法；這個案例和前面的案例是一個樣的，因此實作如下：

create procedure pro_test9(n int)
begin
	declare total int default 0;
	repeat
		set total = total + n;
		set n = n - 1;
		until n = 0
	end repeat;
	select total;
end$

(7)、loop陳述句

??我們介紹最后一種回圈陳述句loop函式，loop可以實作簡單的回圈，退出回圈的條件使用其他陳述句的定義，通常可以使用leave陳述句實作，具體的語法實作如下：

[begin_label:] LOOP
	statement_list
end loop[end_label]

??這里需要我們注意的是：如果不在statement_list中增加退出回圈的陳述句，那么loop陳述句可以用來實作簡單的死回圈，

(8)、leave陳述句

??我們剛才介紹了loop回圈陳述句，也提到了loop回圈結構要與leave陳述句搭配使用，其中的leave陳述句是用來退出loop中的回圈的，一般情況，要和begin…end或者回圈一起使用，接下來我們用loop和leave的簡單例子，具體實作如下：

create procedure pro_test10(n int)
begin
	declare total int default 0;
	c:loop
		set total = total + n;
		set n = n - 1;
		if n <= 0 then
			leave c;
		end if;
	end loop c;
	select total;
end$

(9)、游標/游標

??我們前面介紹了三種回圈結構，下來給大家介紹一種特別重要的資料型別：游標，游標是用來存盤查詢結果集的資料型別，在存盤程序和函式中可以使用游標對結果進行回圈處理，游標的使用包括游標的宣告、open、fetch和close，其中的語法如下：

--宣告游標：
declare cursor_name cursor for select_statement;
--open游標
open cursor_name;
--fetch游標
fetch cursor_name into var_name [, var_name]...
--close游標
close cursor_name;

??介紹完游標的基本語法的時候，接下來，我們通過一個案例來具體實作游標的應用，首先我們初始化的腳本，即首先創建一張表，并且插入一些資料，

create table emp(
	id int(11) not null auto_increment,
	name varchar(50) not null comment '姓名',
	age int(11) comment '年齡',
	salary int(11) comment '薪水',
	primary key(`id`)
)engine=innodb default charset=utf8;
insert into emp(id, name, age, salary) values (null, 'stefan', 16, 28000),(null, 'napoleon', 26, 18000),(null, 'porrty', 18, 8000),(null, '張三', 26, 2600);

??建立好表也插入了資料，接著我們可以查看emp表中的資料，并逐行獲取進行展示，

create procedure pro_test11()
begin
	declare e_id int(11);
	declare e_name varchar(50);
	declare e_age int(11);
	declare e_salary int(11),
	declare emp_result cursor for select * from emp;
	declare exit handler for not found set has_data=0;
	open emp_result;
	repeat 
		fetch emp_result into e_id, e_name, e_age, e_salary;
		select concat('id=', e_id, ',name=',e_name, ', age=', e_age, ',薪資為:', e_salary);
		until has_data = 0
	end repeat;
	close emp_result;
end$

??我們的存盤程序已經全部介紹完了，存盤程序比較重要，也是一個很高級的寫法，能夠希望大家很好的掌握，接下來，給大家介紹存盤函式，

二、存盤函式

??前面介紹了存盤程序，接下來我們介紹存盤函式的語法結構：

create function function_name([param type ...])
returns type
begin
	...
end;

??我們接下來定義一個存盤函式，獲取滿足條件(city)的總記錄數，接下來實作如下：

delimiter $
create function count_city(countryId int)
returns int
begin
	declare cnum int;
	select count(*) into cnum from city where country_id = countryId;
	return cnum;
end$
delimiter ;

??接下來，我們呼叫該存盤函式，具體實作如下：

select count_city(1);
select count_city(2);

??由于存盤函式比較簡單，因此，我們在介紹的時候比較簡單，

總結

??我們最近的文章均會給大家介紹mysql的性能，前兩篇文章我們分別介紹了索引的相關知識以及視圖以及觸發器，本文給大家介紹了存盤程序與存盤函式的相關內容，存盤程序的介紹主要包括：基本概念、創建、呼叫、洗掉存盤程序，另外還介紹了在mysql中變數的申明以及賦值、應用，另外還給大家介紹mysql中存盤程序中的三種回圈結構，其次給大家介紹游標的相關內容，最后，我們介紹了存盤函式的語法，因此，mysql是很重要的一個技能，幾乎計算機中的每個崗位都需要一個mysq技能，因此，需要我們特別的掌握，生命不息，奮斗不止，我們每天努力，好好學習，不斷提高自己的能力，相信自己一定會學有所獲，加油！！！

轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/200148.html

標籤：其他

上一篇：PostgreSQL 配置優化

下一篇：Jetson Xavier NX 學習（二）更換源