存儲過程和函數的相關操作.

在對儲存過程和函數操作時，需要首先確認用戶是否具有相應的權限。例如，創建存儲過程或者函數需要CREATE ROUTINE權限，修改或者刪除存儲過程或者函數需要ALTER ROUT

INE權限，執行過程或者函數需要EXECUTE權限

創建一個新的過程 film_in_stock,該過程用來檢查 film_id和store_id對應的inventory是否滿足要求，并且返回滿足的inventory_id 以及滿足要求的記錄數
CREATE PROCEDURE film_in_stock(in p_fim_id int,in p_store_id int,out p_film_count int)
READS sql data
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 $$

通常在創建過程和函數之前，都會通過DELIMITE $$命令將語句的結束符從'；'修改成其他符號，這里使用‘$$’，這樣在過程和函數中的
';'就不會被MySql，解釋成語句的結束而錯誤。在存儲過程或者函數創建完成 通過‘DELIMITER;'命令在將結束符改回成';'
調用過程
CALL film_in_stock(2,2,@a);
存儲過程的好處在于處理邏輯都封裝在數據庫端，調用者不需要了解中間的處理邏輯，一旦邏輯改變，只需要修改存儲過程，對調用者的程序沒有影響

刪除存儲過程或者函數
一次只能刪除一個存儲過程或者函數，刪除需要ALTER ROUTINE權限

drop procedure film_in_stock;

查看存儲過程或者函數狀態

show procedure status like 'film_in_stock';

查看存儲過程的函數定義
show create procedure film_in_stock

變量使用
存儲過程和函數中可以使用變量，在MySql 5.1版本中，變量不區分大小寫

變量的定義
通過DECLARE可以定義一個局部變量，該變量的作用范圍只能在BEGIN...END中，可以用在嵌套塊中
定義一個DATE類型的變量
DECLARE last_month_start date;
變量賦值 可以直接賦值，或者通過查詢賦值。直接賦值使用set，可以賦常量或者賦表達式
set var_name=expr [,var_name=expr]...
set last_month_start=date_sub(current_date(),interval month);
select col_name[,...] into var_name[,...] table_expr;

定義條件和處理

delimiter $$create procedure actor_insert()begin
 declare continue handler for sqlstate '23000' set @x2=1;
 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');
 set @x=3;end ;$$

調用處理函數時遇到主鍵重的錯誤會按照定義的處理方式去處理，由于定義的是CONTINUE 會繼續執行下面的語句
還支持EXIT表示終止

光標使用

聲明光標
declare cursor_name cursor for select_statement
open光標
open cursor_name
fetch光標
fetch cursor_name into var_name[,var_name]...
close光標
close cursor_name

delimiter $$
create procedure payment_stat()
begin
 declare i_staff_id int;
 declare d_amount decimal(5,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;
 $$
變量，條件，處理程序，光標都是通過DECLARE定義的，她們之間是有先后順序要求的。
變量和條件必須在最前面聲明，然后才能是光標的聲明，最后才可以是處理程序的聲明

控制語句

case 
 when i_staff_id =2 then
 set @x1=@x1+d_amount; else
 set @x2=@x2+d_amount;loop 和leave結合create procedure actor_insert()begin
 set @x=0;
 ins:loop
  set @x=@x+1;  if @x=100 then
  leave ins;  end if;
  insert into actor(first_name,last_name) values('Test','201');  end loop ins;end;
$$

inerate 語句作用是跳過當前循環的剩下語句，直接進入下一輪循環create procedure actor_insert()begin
 set @x=0;
 ins:loop
 set @x=@x+1; if @x=10 then
 leave ins;
 elseif mod(@x,2)=0 then
 iterate ins; end if;
 insert into actor(actor_id,first_name,last_name) values(@x+200,'test',@x); end loop ins;end;
$$repeat 語句 有條件的循環控制語句，當滿足條件的時候退出循環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;whiledelimiter $$create procedure loop_demo()begin
 set @x=1,@x1=1;
 repeat
   set @x=@x+1；  until @x>0 end repeat;  while @x<1 do
   set @x=@x+1;  end while; end;
 $$//創建事件調度器CREATE EVEN test_event_1 ON SCHEDULE
EVERY 5 SECONDDOINSERT INTO dept(deptno,deptname)
VALUES(3,'3');//查看本地調度器狀態
 show variables like '%scheduler%'; //打開調度器
 set global event_scheduler=1; //查看后臺進程
 show processlist; //創建一個新的定時器 定時清空表，防止表變大，這類觸發器非常適合去定期清空臨時表或者日志表
 create event trunc_test on schedule every 1 minute do truncate table test;

 禁用調度器或者刪除
 alter event test_event_1 disable;
 drop event test_event_1;

SQL Mode

在MySql中，SQLMode常用來解決下面幾類問題

• 通過設置SQL Mode，可以完成不同嚴格程度的數據校驗，有效的保障數據準確性。

• 通過設置SQL Mode，為ANSI模式，來保證大多數SQL符合標準的Sql語法，這樣應用在不同數據庫之間進行遷移時，則不需要對業務SQL進行較大的修改

• 在不同數據庫之間進行數據遷移之前，通過設置SQL Mode可以使MySQL上的數據更方便地遷移到目標數據庫中

查看 SQL Mode命令select @@sql_mode

插入一個出國實際定義值的大小varchar(10)insert into value('123400000000000000000000000000000');//查看warning內容show warningsselect * from t 這里對插入的數據
進行截取前10位

設置SQL Mode為 嚴格模式set session sql_mode='STRICT_TRANS_TABLES'再次插入insert into value('123400000000000000000000000000000'); 直接給出ERROR，而不是
warning

SQL Mode常見功能
校驗日期是合法性set seesion sql_mode='ANSI'insert into t values('2007-04-31')
結果是 插入值變成'0000-00-00 00:00:00' 并且系統給出warning 而在TRADITIONAL模式下，直接提示日期非法，拒絕插入，同時Mode（x,0）也會報錯

qidon NO_BACKSLASH_ESCAPES模式，使反斜杠成為普通字符，在導入數據時，如果數據含有反斜杠字符,你們啟動NO_BACKSLASH_ESCAPES模式，保證數據的正確性

啟動PIPES_AS_CONCAT。將  視為字符串連接符，在Oracle等數據庫中，  被視為字符串的連接操作符，所以在其他數據庫中含有  操作符的sql在MySql將無法執行，為了解決這個
問題mysql提供了PIPES_AS_CONCAT模式、

MySql分區

MySql從5.1版本開始支持分區，分區是指按照一定的規則，數據庫把一個表分解成多個更小的，更容易管理的部分。就訪問數據庫的應用而言，邏輯上只有一個表或一個索引，但是實際上

這個表可能由數10個物理分區對象組成，每個分區都是一個獨立的對象，可以獨自處理，可以作為表的一部分進行處理。分區對應用而言是完全透明的，不影響應用的業務邏輯優點

• 和單個磁盤或者文件系統分區相比，可以存儲更多數據

• 優化查詢。在where子句中包含分區條件，可以只掃描必要的一個或多個分區來提高查詢效率；同時在涉及SUM（）和COUNT（）這類聚合函數的查詢時，可以容易的在每個分區上并行

  處理，最終只需要匯總所有分區的結果

• 對于已經過期或者不需要保存的數據，可以通過刪除與這些數據有關的分區來快速刪除數據

• 跨多個磁盤來分散數據查詢，以獲得更大的查詢吞吐量

分區有利于管理非常大的表，它采用 分而治之的邏輯，分區引入分區鍵的概念，分區鍵用于根據某個區間鍵，特定值列表或者HASH函數執行數據的聚集，讓數據根據規則分布在不同的分區

中，讓一個大對象變成一些小對象

show VARIABLES like '%partition%' 查看是否支持分區

Mysql支持大部分存儲引起如MyISAM，INNODb,Memory等存儲引擎，創建分區，在5.1版本中，同一個分區表的所以分區必須使用同一個存儲引擎；
在同要給表上，不能對一個分區使用MyISAM引擎和Innodb引擎，但是在同一個MySQL服務器服務器上，甚至同一個數據庫中，對于不同的分區表使用不同的存儲引擎

分區類型

• range分區：基于一個給定連續區間范圍，把數據分配到不同的分區。

• LIST分區：類似RANGE分區，區別在LIST分區是基于枚舉出的值列表分區，RANGE是基于給定的連續區間范圍分區

• HASH分區：基于給定的分區個數，把數據分配到不同的分區

• KEY分區：類似HASH分區

range分區

CREATE TABLE emp(
    id int not null,
    ename varchar(30),
    hired date not null DEFAULT '1970-01-01',
    separated date NOT null DEFAULT '9999-12-21',
    job varchar(30) not null,
    store_id int not null
)
partition by range(store_id)(
    PARTITION p0 VALUES less than (10),
    PARTITION p1 VALUES less than (20),
    PARTITION p2 VALUES less than (30)
);
//上述的分區方案將storid,1-9分到p0區，10-19分到p1區，等如果插入大于30，會出現錯誤，因為沒有規則保護大于30的

INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',19);//可以

//Table has no partition for value 40
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',40);

添加分區
alter  table emp add partition(partition p3 values less than maxvalue);
maxvalue表示最大的可能的整數值

mysql 支持在values less than 語句中加入表達式
比如以日期作為分區
CREATE TABLE emp(
    id int not null,
    ename varchar(30),
    hired date not null DEFAULT '1970-01-01',
    separated date NOT null DEFAULT '9999-12-21',
    job varchar(30) not null,
    store_id int not null
)
partition by range(year(separated ))(
    PARTITION p0 VALUES less than (1995),
    PARTITION p1 VALUES less than (2000),
    PARTITION p2 VALUES less than (2005)
);
MySQl 5.5改進了range分區給你，通過支持非整數分區，創建日期分區就不需要通過函數進行轉換
partition by range(separated )(
    PARTITION p0 VALUES less than ('1996-01-01'),
    PARTITION p1 VALUES less than ('2001-01-01'),
    PARTITION p2 VALUES less than ('2006-01-01')
);

List分區

create table expenses（
  expense_date date not null,
  category int,
  amount decimal(10,3)
）partition by list (category)(
   partition p0 values in(3,5),
   partition p1 values in(1,10),
   partition p2 values in(4,9),
   partition p3 values in(2),
   partition p4 values in(6)
)

LIST分區不存在類似于VALUES LESS THAN MAXVALUE這樣的值 在MYSQL5.5支持非整數分區

Columns分區

create table rc3(a int,b int)
parition by range columns(a,b)(
 parition p01 values less than(0,10),
 parition p01 values less than(10,10),
 parition p01 values less than(10,20),
 parition p01 values less than(maxvalue,maxvalue)
)

Hash分區

create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,
store_id int not null) partition by hash(store_id)partitions 4;

這里創建了一個常規的hash 使用 partition by hash（expr）其中expr是某列值或一個整數值的表達式返回值。 partition num 對分區類型，分區鍵，分區個數進行定義，上述基于
store_id列hash分區，表被分為4個分區

我們可以計算出它被保存在哪個分區中假設，假設記錄的分區編號為N，那么N=MOD（expr，num），例如emp表中有4個分區，插入一個store_id為234的 mod（234,4）=2,倍保存在第二個
分區

相關推薦：

關于mysql 基礎知識的總結

PHP 和 MySQL 基礎教程（一）

PHP 和 MySQL 基礎教程（二）

PHP 和 MySQL 基礎教程（三）

PHP 和 MySQL 基礎教程（四）