day25-索引和函式及儲存過程

1. 索引

在資料庫中索引最核心的作用是：加速查詢。 例如：在含有300w條資料的表中查詢，無索引需要700秒，而利用索引可能僅需1秒。

mysql> select * from big where password="81f98021-6927-433a-8f0d-0f5ac274f96e";
+----+---------+---------------+--------------------------------------+------+
| id | name    | email         | password                             | age  |
+----+---------+---------------+--------------------------------------+------+
| 11 | wu-13-1 | w-13-1@qq.com | 81f98021-6927-433a-8f0d-0f5ac274f96e |    9 |
+----+---------+---------------+--------------------------------------+------+
1 row in set (0.70 sec)

mysql> select * from big where id=11;
+----+---------+---------------+--------------------------------------+------+
| id | name    | email         | password                             | age  |
+----+---------+---------------+--------------------------------------+------+
| 11 | wu-13-1 | w-13-1@qq.com | 81f98021-6927-433a-8f0d-0f5ac274f96e |    9 |
+----+---------+---------------+--------------------------------------+------+
1 row in set (0.00 sec)

mysql> select * from big where name="wu-13-1";
+----+---------+---------------+--------------------------------------+------+
| id | name    | email         | password                             | age  |
+----+---------+---------------+--------------------------------------+------+
| 11 | wu-13-1 | w-13-1@qq.com | 81f98021-6927-433a-8f0d-0f5ac274f96e |    9 |
+----+---------+---------------+--------------------------------------+------+
1 row in set (0.00 sec)

在開發過程中會為哪些 經常會被搜尋的列 建立索引，以提高程式的響應速度。例如：查詢手機號、郵箱、使用者名稱等。

1.1 索引原理

為什麼加上索引之後速度能有這麼大的提升呢？ 因為索引的底層是基於B+Tree的資料結構儲存的。

很明顯，如果有了索引結構的查詢效率比表中逐行查詢的速度要快很多且資料量越大越明顯。

B+Tree結構連線：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

資料庫的索引是基於上述B+Tree的資料結構實現，但在建立資料庫表時，如果指定不同的引擎，底層使用的B+Tree結構的原理有些不同。

• myisam引擎，非聚簇索引（資料 和 索引結構 分開儲存）

• innodb引擎，聚簇索引（資料 和 主鍵索引結構儲存在一起）

1.1.1 非聚簇索引（mysiam引擎）

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=myisam default charset=utf8;

1.1.2 聚簇索引（innodb引擎）

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=innodb default charset=utf8;

在MySQL檔案儲存中的體現：

root@192 userdb # pwd
/usr/local/mysql/data/userdb
root@192 userdb # ls -l
total 1412928
-rw-r-----  1 _mysql  _mysql       8684 May 15 22:51 big.frm，表結構。
-rw-r-----  1 _mysql  _mysql  717225984 May 15 22:51 big.ibd，資料和索引結構。
-rw-r-----  1 _mysql  _mysql       8588 May 16 11:38 goods.frm
-rw-r-----  1 _mysql  _mysql      98304 May 16 11:39 goods.ibd
-rw-r-----  1 _mysql  _mysql       8586 May 26 10:57 t2.frm，表結構
-rw-r-----  1 _mysql  _mysql          0 May 26 10:57 t2.MYD，資料
-rw-r-----  1 _mysql  _mysql       1024 May 26 10:57 t2.MYI，索引結構

上述 聚簇索引 和 非聚簇索引 底層均利用了B+Tree結構結構，只不過內部資料儲存有些不同罷了。

在企業開發中一般都會使用 innodb 引擎（內部支援事務、行級鎖、外來鍵等特點），在MySQL5.5版本之後預設引擎也是innodb。

mysql> show create table users \G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  `ctime` datetime DEFAULT NULL,
  `age` int(11) DEFAULT '5',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show index from users \G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 3
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE   -- 雖然顯示BTree，但底層資料結構基於B+Tree。
      Comment:
Index_comment:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

innodb引擎，一般建立的索引：聚簇索引。

1.2 常見索引

在innodb引擎下，索引底層都是基於B+Tree資料結構儲存（聚簇索引）。

在開發過程中常見的索引型別有：

• 主鍵索引：加速查詢、不能為空、不能重複。 + 聯合主鍵索引
• 唯一索引：加速查詢、不能重複。 + 聯合唯一索引
• 普通索引：加速查詢。 + 聯合索引

1.2.1 主鍵和聯合主鍵索引

create table 表名(
    id int not null auto_increment primary key,   -- 主鍵
    name varchar(32) not null
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(id)
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(列1,列2)          -- 如果有多列，稱為聯合主鍵（不常用且myisam引擎支援）
);

alter table 表名 add primary key(列名);

alter table 表名 drop primary key;

注意：刪除索引時可能會報錯，自增列必須定義為鍵。

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

alter table 表 change id id int not null;

create table t7(
    id int not null,
    name varchar(32) not null,
    primary key(id)
);

alter table t6 drop primary key;

1.2.2 唯一和聯合唯一索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_name (name),
    unique ix_email (email),
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    unique (列1,列2)               -- 如果有多列，稱為聯合唯一索引。
);

create unique index 索引名 on 表名(列名);

drop unique index 索引名 on 表名;

1.2.3 索引和聯合索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (email),
    index ix_name (name),
);

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (name,email)     -- 如果有多列，稱為聯合索引。
);

create index 索引名 on 表名(列名);

drop index 索引名 on 表名;

在專案開發的設計表結構的環節，大家需要根據業務需求的特點來決定是否建立相應的索引。

案例：部落格系統

• 每張表id列都建立 自增 + 主鍵。
• 使用者表
  • 使用者名稱 + 密碼 建立聯合索引。
  • 手機號，建立唯一索引。
  • 郵箱，建立唯一索引。
• 推薦表
  • user_id和article_id建立聯合唯一索引。

1.3 操作表

在表中建立索引後，查詢時一定要命中索引。

在資料庫的表中建立索引之後優缺點如下：

• 優點：查詢速度快、約束（唯一、主鍵、聯合唯一）
• 缺點：插入、刪除、更新速度比較慢，因為每次操作都需要調整整個B+Tree的資料結構關係。

所以，在表中不要無節制的去建立索引啊。。。

在開發中，我們會對錶中經常被搜尋的列建立索引，從而提高程式的響應速度。

CREATE TABLE `big` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(32) DEFAULT NULL,
    `email` varchar(64) DEFAULT NULL,
    `password` varchar(64) DEFAULT NULL,
    `age` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),                       -- 主鍵索引
    UNIQUE KEY `big_unique_email` (`email`),  -- 唯一索引
    index `ix_name_pwd` (`name`,`password`)     -- 聯合索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8

一般情況下，我們針對只要透過索引列去搜搜都可以 命中 索引（透過索引結構加速查詢）。

select * from big where id = 5;
select * from big where id > 5;
select * from big where email = "wupeiqi@live.com";
select * from big where name = "武沛齊";
select * from big where name = "kelly" and password="ffsijfs";
...

但是，還是會有一些特殊的情況，讓我們無法命中索引（即使建立了索引），這也是需要大家在開發中要注意的。

• 型別不一致

  select * from big where name = 123;		-- 未命中
  select * from big where email = 123;	-- 未命中
  
  特殊的主鍵：
  select * from big where id = "123";	-- 命中
  

• 使用不等於

  select * from big where name != "武沛齊";				-- 未命中
  select * from big where email != "wupeiqi@live.com";  -- 未命中
  
  特殊的主鍵：
  select * from big where id != 123;	-- 命中
  

• or，當or條件中有未建立索引的列才失效。

  select * from big where id = 123 or password="xx";			-- 未命中
  select * from big where name = "wupeiqi" or password="xx";	-- 未命中
  特別的：
  select * from big where id = 10 or password="xx" and name="xx"; -- 命中
  

• 排序，當根據索引排序時候，選擇的對映如果不是索引，則不走索引。

  select * from big order by name asc;     -- 未命中
  select * from big order by name desc;    -- 未命中
  
  特別的主鍵：
  select * from big order by id desc;  -- 命中
  

• like，模糊匹配時。

  select * from big where name like "%u-12-19999";	-- 未命中
  select * from big where name like "_u-12-19999";	-- 未命中
  select * from big where name like "wu-%-10";		-- 未命中
  
  特別的：
  select * from big where name like "wu-1111-%";	-- 命中
  select * from big where name like "wu-%";		-- 命中
  

• 使用函式

  select * from big where reverse(name) = "wupeiqi";  -- 未命中
  
  特別的：
  select * from big where name = reverse("wupeiqi");  -- 命中
  

• 最左字首，如果是聯合索引，要遵循最左字首原則。

  如果聯合索引為：(name,password)
      name and password       -- 命中
      name                 	-- 命中
      password                -- 未命中
      name or password       	-- 未命中
  

常見的無法命中索引的情況就是上述的示例。

對於大家來說會現在的最大的問題是，記不住，哪怎麼辦呢？接下來看執行計劃。

1.4 執行計劃

MySQL中提供了執行計劃，讓你能夠預判SQL的執行（只能給到一定的參考，不一定完全能預判準確）。

explain + SQL語句;

其中比較重要的是 type，他是SQL效能比較重要的標誌，效能從低到高依次：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

• ALL，全表掃描，資料表從頭到尾找一遍。(一般未命中索引，都是會執行權標掃描)

  select * from big;
  
  特別的：如果有limit，則找到之後就不在繼續向下掃描.
  	select * from big limit 1;
  

• INDEX，全索引掃描，對索引從頭到尾找一遍

  explain select id from big;
  explain select name from big;
  

• RANGE，對索引列進行範圍查詢

  explain select * from big where id > 10;
  explain select * from big where id in (11,22,33);
  explain select * from big where id between 10 and 20;
  explain select * from big where name > "wupeiqi" ;
  

• INDEX_MERGE，合併索引，使用多個單列索引搜尋

  explain select * from big where id = 10 or name="武沛齊";
  

• REF，根據 索引 直接去查詢（非鍵）。

  select *  from big where name = '武沛齊';
  

• EQ_REF，連表操作時常見。

  explain select big.name,users.id from big left join users on big.age = users.id;
  

• CONST，常量，表最多有一個匹配行,因為僅有一行,在這行的列值可被最佳化器剩餘部分認為是常數,const表很快。

  explain select * from big where id=11;					-- 主鍵
  explain select * from big where email="w-11-0@qq.com";	-- 唯一索引
  

• SYSTEM，系統，表僅有一行(=系統表)。這是const聯接型別的一個特例。

   explain select * from (select * from big where id=1 limit 1) as A;
  

其他列：

id，查詢順序標識

z，查詢型別
    SIMPLE          簡單查詢
    PRIMARY         最外層查詢
    SUBQUERY        對映為子查詢
    DERIVED         子查詢
    UNION           聯合
    UNION RESULT    使用聯合的結果
    ...
    
table，正在訪問的表名

partitions，涉及的分割槽（MySQL支援將資料劃分到不同的idb檔案中，詳單與資料的拆分）。 一個特別大的檔案拆分成多個小檔案（分割槽）。

possible_keys，查詢涉及到的欄位上若存在索引，則該索引將被列出，即：可能使用的索引。
key，顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。例如：有索引但未命中，則possible_keys顯示、key則顯示NULL。

key_len，表示索引欄位的最大可能長度。(型別位元組長度 + 變長2 + 可空1)，例如：key_len=195，型別varchar(64)，195=64*3+2+1

ref，連表時顯示的關聯資訊。例如：A和B連表，顯示連表的欄位資訊。

rows，估計讀取的資料行數（只是預估值）
	explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c";
	explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c" limit 1;
filtered，返回結果的行佔需要讀到的行的百分比。
	explain select * from big where id=1;  -- 100，只讀了一個1行，返回結果也是1行。
	explain select * from big where password="27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3";  -- 10，讀取了10行，返回了1行。
	注意：密碼27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3在第10行
	
extra，該列包含MySQL解決查詢的詳細資訊。
    “Using index”
    此值表示mysql將使用覆蓋索引，以避免訪問表。不要把覆蓋索引和index訪問型別弄混了。
    “Using where”
    這意味著mysql伺服器將在儲存引擎檢索行後再進行過濾，許多where條件裡涉及索引中的列，當（並且如果）它讀取索引時，就能被儲存引擎檢驗，因此不是所有帶where子句的查詢都會顯示“Using where”。有時“Using where”的出現就是一個暗示：查詢可受益於不同的索引。
    “Using temporary”
    這意味著mysql在對查詢結果排序時會使用一個臨時表。
    “Using filesort”
    這意味著mysql會對結果使用一個外部索引排序，而不是按索引次序從表裡讀取行。mysql有兩種檔案排序演算法，這兩種排序方式都可以在記憶體或者磁碟上完成，explain不會告訴你mysql將使用哪一種檔案排序，也不會告訴你排序會在記憶體裡還是磁碟上完成。
    “Range checked for each record(index map: N)”
    這個意味著沒有好用的索引，新的索引將在聯接的每一行上重新估算，N是顯示在possible_keys列中索引的點陣圖，並且是冗餘的。

小結

上述索引相關的內容講的比較多，大家在開發過程中重點應該掌握的是：

• 根據情況建立合適的索引（加速查詢）。
• 有索引，則查詢時要命中索引。

2. 函式

MySQL中提供了很多函式，為我們的SQL操作提供便利，例如：

mysql> select * from d1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 武沛齊    |
|  3 | xxx       |
|  4 | pyyu      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select count(id), max(id),min(id),avg(id) from d1;
+-----------+---------+---------+---------+
| count(id) | max(id) | min(id) | avg(id) |
+-----------+---------+---------+---------+
|         3 |       4 |       1 |  2.6667 |
+-----------+---------+---------+---------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select id,reverse(name) from d1;
+----+---------------+
| id | reverse(name) |
+----+---------------+
|  1 | 齊沛武        |
|  3 | xxx           |
|  4 | uyyp          |
+----+---------------+
3 rows in set (0.00 sec)

mysql> select id, reverse(name),concat(name,name), NOW(), DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s')  from d1;
+----+---------------+--------------------+---------------------+-----------------------------------------+
| id | reverse(name) | concat(name,name)  | NOW()               | DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s') |
+----+---------------+--------------------+---------------------+-----------------------------------------+
|  1 | 齊沛武        | 武沛齊武沛齊       | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
|  3 | xxx           | xxxxxx             | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
|  4 | uyyp          | pyyupyyu           | 2021-05-27 09:18:07 | 2021-05-27 09:18:07                     |
+----+---------------+--------------------+---------------------+-----------------------------------------+
3 rows in set (0.00 sec)

mysql> select concat("alex","sb");
+---------------------+
| concat("alex","sb") |
+---------------------+
| alexsb              |
+---------------------+
1 row in set (0.00 sec)

mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

部分函式列表：

CHAR_LENGTH(str)
    返回值為字串str 的長度，長度的單位為字元。一個多位元組字元算作一個單字元。
    對於一個包含五個二位元組字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。

CONCAT(str1,str2,...)
    字串拼接
    如有任何一個引數為NULL ，則返回值為 NULL。
CONCAT_WS(separator,str1,str2,...)
    字串拼接（自定義連線符）
    CONCAT_WS()不會忽略任何空字串。 (然而會忽略所有的 NULL）。

CONV(N,from_base,to_base)
    進位制轉換
    例如：
        SELECT CONV('a',16,2); 表示將 a 由16進位制轉換為2進位制字串表示

FORMAT(X,D)
    將數字X 的格式寫為'#,###,###.##',以四捨五入的方式保留小數點後 D 位， 並將結果以字串的形式返回。若  D 為 0, 則返回結果不帶有小數點，或不含小數部分。
    例如：
        SELECT FORMAT(12332.1,4); 結果為： '12,332.1000'
INSERT(str,pos,len,newstr)
    在str的指定位置插入字串
        pos：要替換位置其實位置
        len：替換的長度
        newstr：新字串
    特別的：
        如果pos超過原字串長度，則返回原字串
        如果len超過原字串長度，則由新字串完全替換
INSTR(str,substr)
    返回字串 str 中子字串的第一個出現位置。

LEFT(str,len)
    返回字串str 從開始的len位置的子序列字元。

LOWER(str)
    變小寫

UPPER(str)
    變大寫

LTRIM(str)
    返回字串 str ，其引導空格字元被刪除。
RTRIM(str)
    返回字串 str ，結尾空格字元被刪去。
SUBSTRING(str,pos,len)
    獲取字串子序列

LOCATE(substr,str,pos)
    獲取子序列索引位置

REPEAT(str,count)
    返回一個由重複的字串str 組成的字串，字串str的數目等於count 。
    若 count <= 0,則返回一個空字串。
    若str 或 count 為 NULL，則返回 NULL 。
REPLACE(str,from_str,to_str)
    返回字串str 以及所有被字串to_str替代的字串from_str 。
REVERSE(str)
    返回字串 str ，順序和字元順序相反。
RIGHT(str,len)
    從字串str 開始，返回從後邊開始len個字元組成的子序列

SPACE(N)
    返回一個由N空格組成的字串。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
    不帶有len 引數的格式從字串str返回一個子字串，起始於位置 pos。帶有len引數的格式從字串str返回一個長度同len字元相同的子字串，起始於位置 pos。 使用 FROM的格式為標準 SQL 語法。也可能對pos使用一個負值。假若這樣，則子字串的位置起始於字串結尾的pos 字元，而不是字串的開頭位置。在以下格式的函式中可以對pos 使用一個負值。

    mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'

    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'

    mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

    mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'

    mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'

    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
    返回字串 str ， 其中所有remstr 字首和/或字尾都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項，在未指定情況下，可刪除空格。

    mysql> SELECT TRIM('  bar   ');
            -> 'bar'

    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
            -> 'barxxx'

    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
            -> 'bar'

    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
            -> 'barx'   

更多函式：https://dev.mysql.com/doc/refman/5.7/en/functions.html

當然，MySQL中也支援讓你去自定義函式。

• 建立函式

  delimiter $$
  create function f1(
      i1 int,
      i2 int)
  returns int
  BEGIN
      declare num int;
      declare maxId int;
      select max(id) from big into maxId;
      
      set num = i1 + i2 + maxId;
      return(num);
  END $$
  delimiter ;
  

• 執行函式

  select f1(11,22);
  
  select f1(11,id),name from d1;
  

• 刪除函式

  drop function f1;
  

3. 儲存過程

儲存過程，是一個儲存在MySQL中的SQL語句集合，當主動去呼叫儲存過程時，其中內部的SQL語句會按照邏輯執行。

• 建立儲存過程

  delimiter $$
  create procedure p1()
  BEGIN
      select * from d1;
  END $$
  delimiter ;
  

• 執行儲存過程

  call p1();
  

  #!/usr/bin/env python
  # -*- coding:utf-8 -*-
  import pymysql
  
  conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
  cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
  # 執行儲存過程
  cursor.callproc('p1')
  result = cursor.fetchall()
  
  cursor.close()
  conn.close()
  
  print(result)
  

• 刪除儲存過程

  drop procedure proc_name;
  

3.1 引數型別

儲存過程的引數可以有如下三種：

• in，僅用於傳入引數用
• out，僅用於返回值用
• inout，既可以傳入又可以當作返回值

delimiter $$
create procedure p2(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end $$
delimiter ;

set @t1 =4;
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 執行儲存過程
cursor.callproc('p2',args=(1, 22, 3, 4))

# 獲取執行完儲存的引數
cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
result = cursor.fetchall()
# {"@_p2_0":11 }

cursor.close()
conn.close()

print(result)

3.2 返回值 & 結果集

delimiter $$
create procedure p3(
    in n1 int,
    inout n2 int,
    out n3 int
)
begin
    set n2 = n1 + 100;
    set n3 = n2 + n1 + 100;
    select * from d1;
end $$
delimiter ;

set @t1 =4;
set @t2 = 0;
CALL p3 (1,@t1, @t2);
SELECT @t1,@t2;

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 執行儲存過程
cursor.callproc('p3',args=(22, 3, 4))
table = cursor.fetchall() # 得到執行儲存過中的結果集

# 獲取執行完儲存的引數
cursor.execute("select @_p3_0,@_p3_1,@_p3_2")
rets = cursor.fetchall()

cursor.close()
conn.close()

print(table)
print(rets)

3.3 事務 & 異常

事務，成功都成功，失敗都失敗。

delimiter $$
create PROCEDURE p4(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION;  -- 開啟事務
    delete from d1;
    insert into tb(name)values('seven');
  COMMIT;  -- 提交事務
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END $$
delimiter ; 

set @ret =100;
CALL p4(@ret);
SELECT @ret;

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 執行儲存過程
cursor.callproc('p4',args=(100))

# 獲取執行完儲存的引數
cursor.execute("select @_p4_0")
rets = cursor.fetchall()

cursor.close()
conn.close()

print(table)
print(rets)

3.4 遊標

delimiter $$
create procedure p5()
begin 
    declare sid int;
    declare sname varchar(50); 
    declare done int default false;


    declare my_cursor CURSOR FOR select id,name from d1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open my_cursor;
        xxoo: LOOP
            fetch my_cursor into sid,sname;
            IF done then 
                leave xxoo;
            END IF;
            insert into t1(name) values(sname);
        end loop xxoo;
    close my_cursor;
end $$
delimiter ; 

call p5();

4.檢視

檢視其實是一個虛擬表（非真實存在），其本質是【根據SQL語句獲取動態的資料集，併為其命名】，使用者使用時只需使用【名稱】即可獲取結果集，並可以將其當作表來使用。

SELECT
    *
FROM
    (SELECT nid,name FROM tb1 WHERE nid > 2) AS A
WHERE
    A.name > 'alex';

• 建立檢視

  create view v1 as select id,name from d1 where id > 1;
  

• 使用檢視

  select * from v1;
  
  -- select * from (select id,name from d1 where id > 1) as v1;
  

• 刪除檢視

  drop view v1;
  

• 修改檢視

  alter view v1 as SQL語句
  

注意：基於檢視只能查詢，針對檢視不能執行 增加、修改、刪除。 如果源表發生變化，檢視表也會發生變化。

5.觸發器

對某個表進行【增/刪/改】操作的前後如果希望觸發某個特定的行為時，可以使用觸發器。

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入後
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 刪除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 刪除後
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新後
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

DROP TRIGGER tri_after_insert_tb1;

示例：

• 在 t1 表中插入資料之前，先在 t2 表中插入一行資料。

  delimiter $$
  CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW
  BEGIN
      -- NEW.id  NEW.name  NEW.email
      -- INSERT INTO t2 (name) VALUES();
      IF NEW.name = 'alex' THEN
          INSERT INTO t2 (name) VALUES(NEW.id);
      END IF;
  
  END $$
  delimiter ;
  

  insert into t1(id,name,email)values(1,"alex","xxx@qq.com")
  

• 在t1表中刪除資料之後，再在t2表中插入一行資料。

  delimiter $$
  CREATE TRIGGER tri_after_insert_t1 AFTER DELETE ON t1 FOR EACH ROW
  BEGIN
  
  IF OLD.name = 'alex' THEN
      INSERT INTO t2 (name) VALUES(OLD.id);
  END IF;
  
  END $$
  delimiter ;
  

特別的：NEW表示新資料，OLD表示原來的資料。

總結

對於Python開發人員，其實在開發過程中觸發器、檢視、儲存過程用的很少（以前搞C#經常寫儲存過程），最常用的其實就是正確的使用索引以及常見的函式。

• 索引，加速查詢 & 約束。
  • innodb和myisam的區別，聚簇索引 和 非聚簇索引。
  • 常見的索引：主鍵、唯一、普通。
  • 命中索引
  • 執行計劃
• 函式，提供了一些常見操作 & 配合SQL語句，執行後返回結果。
• 儲存過程，一個SQL語句的集合，可以出發複雜的情況，最終可以返回結果 + 資料集。
• 檢視，一個虛擬的表。
• 觸發器，在表中資料行執行前後自定義一些操作。