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

来源:https://www.cnblogs.com/sbhglqy/p/18160427
-Advertisement-
Play Games

1. 索引 在資料庫中索引最核心的作用是：加速查找。 例如：在含有300w條數據的表中查詢，無索引需要700秒，而利用索引可能僅需1秒。 mysql> select * from big where password="81f98021-6927-433a-8f0d-0f5ac274f96e"; + ...


1. 索引

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

mysql> select * from big where password="81f98021-6927-433a-8f0d-0f5ac274f96e";
+----+---------+---------------+--------------------------------------+------+
| id | name    | email         | password                             | age  |
+----+---------+---------------+--------------------------------------+------+
| 11 | wu-13-1 | [email protected] | 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 | [email protected] | 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 | [email protected] | 81f98021-6927-433a-8f0d-0f5ac274f96e |    9 |
+----+---------+---------------+--------------------------------------+------+
1 row in set (0.00 sec)

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

1.1 索引原理

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

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

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;

image
image
image

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;

image
image
image
image

在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數據結構存儲（聚簇索引）。
image

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

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

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 表名;

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

案例：博客系統

image

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

1.3 操作表

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

image

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

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

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

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

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 = "[email protected]";
select * from big where name = "武沛齊";
select * from big where name = "kelly" and password="ffsijfs";
...

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

  • 類型不一致

    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 != "[email protected]";  -- 未命中

特殊的主鍵：
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語句;

image

其中比較重要的是 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="[email protected]";	-- 唯一索引

  • 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語句會按照邏輯執行。
image

  • 創建存儲過程

    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.觸發器

image

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

# 插入前
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","[email protected]")

  • 在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語句的集合，可以出發複雜的情況，最終可以返回結果 + 數據集。
  • 視圖，一個虛擬的表。
  • 觸發器，在表中數據行執行前後自定義一些操作。

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 鴻蒙HarmonyOS實戰-ArkUI事件（觸屏事件）
    前言 觸屏事件是指通過觸摸屏幕來進行操作和交互的事件。常見的觸屏事件包括點擊（tap）、雙擊（double tap）、長按（long press）、滑動（swipe）、拖動（drag）等。觸屏事件通常用於移動設備和平板電腦等具有觸摸屏幕的設備上，用戶可以通過觸摸屏幕上的不同區域或者以不同的方式進 ...
  • 探索 DTD 在 XML 中的作用及解析：深入理解文檔類型定義
    DTD 是文檔類型定義（Document Type Definition）的縮寫。DTD 定義了 XML 文檔的結構以及合法的元素和屬性。 為什麼使用 DTD 通過使用 DTD，獨立的團體可以就數據交換的標準 DTD 達成一致。 應用程式可以使用 DTD 來驗證 XML 數據的有效性。 內部 DTD ...
  • vue3 快速入門系列 —— 狀態管理 pinia
    其他章節請看： vue3 快速入門 系列 Pinia vue3 狀態管理這裡選擇 pinia。 雖然 vuex4 已支持 Vue 3 的 Composition API，但是 vue3 官網推薦新的應用使用 pinia —— vue3 pinia 集中式狀態管理 redux、mobx、vuex、pi ...
  • uView（u-collapse）摺疊 展開 高度問題 無法撐開 nextTick
    a-textarea(textarea)出現模糊問題的可能解決方案 項目介紹：本項目是一個vue3+ant-design-vue4.x開發，是一個客服機器人的組件。其它項目通過iframe+js文件來引入(iframe的內容就是表單，入口按鈕是通過js文件進行dom操作創建)。 通過js監聽頁面寬度 ...
  • C++多態與虛擬：運算符重載（Operator Overloading）
    運算符重載：與function overloading異曲同工的是，C++提供所謂的Operator overloading。所謂operators是像 +（加）-（減）*（乘）/（除）>>（位右移）<<（位左移）之類的符號，代表一種動作。 面對operators，我們應該把他想像是一種函數，只不過 ...
  • MySQL-07.InnoDB數據存儲結構
    C-07.InnoDB數據存儲結構 1.資料庫的存儲結構：頁 索引結構給我們提供了高效的索引方式，不過索引信息以及數據記錄都是保存在文件上的，確切說是存儲在頁結構中。另一方面，索引是在存儲引擎中實現的，MySQL伺服器上的存儲引擎負責對錶中數據的讀取和寫入工作。不同存儲引擎中存放的格式一般是不同的， ...
  • web server apache tomcat11-22-logging 日誌
    前言 整理這個官方翻譯的系列，原因是網上大部分的 tomcat 版本比較舊，此版本為 v11 最新的版本。 開源項目 從零手寫實現 tomcat minicat 別稱【嗅虎】心有猛虎，輕嗅薔薇。 系列文章 web server apache tomcat11-01-官方文檔入門介紹 web serv ...
  • 2024年最好用的10款ER圖神器！
    分享10款ER圖工具，詳細分析他們的功能特點、價格和適用場景，可以根據你的需求進行選擇。ER圖（Entity-Relationship Diagram）是資料庫設計中常用的一種模型，用於描述實體之間的關係。這種圖形化的表示方法旨在幫助人們理解和設計資料庫結構，它們在資料庫開發和設計中非常有用。 1 ...
一周排行
    -Advertisement-
    Play Games
  • 如何基於surging跨網關跨語言進行緩存降級
    基於.NET Framework 4.8 開發的深度學習模型部署測試平臺，提供了YOLO框架的主流系列模型，包括YOLOv8~v9，以及其系列下的Det、Seg、Pose、Obb、Cls等應用場景，同時支持圖像與視頻檢測。模型部署引擎使用的是OpenVINO™、TensorRT、ONNX runti... ...
  • 十年沉澱，重啟開發之路
    十年沉澱，重啟開發之路 十年前，我沉浸在開發的海洋中，每日與代碼為伍，與演算法共舞。那時的我，滿懷激情，對技術的追求近乎狂熱。然而，隨著歲月的流逝，生活的忙碌逐漸占據了我的大部分時間，讓我無暇顧及技術的沉澱與積累。 十年間，我經歷了職業生涯的起伏和變遷。從初出茅廬的菜鳥到逐漸嶄露頭角的開發者，我見證了 ...
  • C#.NET體系圖文概述—2024最全總結
    C# 是一種簡單、現代、面向對象和類型安全的編程語言。.NET 是由 Microsoft 創建的開發平臺，平臺包含了語言規範、工具、運行，支持開發各種應用，如Web、移動、桌面等。.NET框架有多個實現，如.NET Framework、.NET Core（及後續的.NET 5+版本），以及社區版本M... ...
  • 班級擂臺（光榮）榜 - 極簡教育小工具
    前言 本文介紹瞭如何使用三菱提供的MX Component插件實現對三菱PLC軟元件數據的讀寫，記錄了使用電腦模擬，模擬PLC，直至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1. PLC開發編程環境GX Works2，GX Works2下載鏈接 https:// ...
  • web server apache tomcat11-31-websocket
    前言 整理這個官方翻譯的系列，原因是網上大部分的 tomcat 版本比較舊，此版本為 v11 最新的版本。 開源項目 從零手寫實現 tomcat minicat 別稱【嗅虎】心有猛虎，輕嗅薔薇。 系列文章 web server apache tomcat11-01-官方文檔入門介紹 web serv ...
  • day31-jQuery
    1、jQuery介紹 jQuery是什麼 jQuery是一個快速、簡潔的JavaScript框架，是繼Prototype之後又一個優秀的JavaScript代碼庫（或JavaScript框架）。jQuery設計的宗旨是“write Less，Do More”，即倡導寫更少的代碼，做更多的事情。它封裝 ...
  • aardio爬蟲) 實戰篇：逆向有道翻譯web介面
    前言 之前的文章把js引擎(aardio封裝庫) 微軟開源的js引擎(ChakraCore))寫好了，這篇文章整點js代碼來測一下bug。測試網站：https://fanyi.youdao.com/index.html#/ 逆向思路 逆向思路可以看有道翻譯js逆向（MD5加密，AES加密）附完整源碼 ...
  • 面試官：這就是你理解的Java多線程基礎？
    引言 現代的操作系統（Windows，Linux，Mac OS）等都可以同時打開多個軟體（任務），這些軟體在我們的感知上是同時運行的，例如我們可以一邊瀏覽網頁，一邊聽音樂。而CPU執行代碼同一時間只能執行一條，但即使我們的電腦是單核CPU也可以同時運行多個任務，如下圖所示，這是因為我們的 CPU 的 ...
  • Python文本統計與分析從基礎到進階
    掌握使用Python進行文本英文統計的基本方法，並瞭解如何進一步優化和擴展這些方法，以應對更複雜的文本分析任務。 ...
  • Redis多數據源，看這篇就夠了
    背景 Redis多數據源常見的場景： 分區數據處理：當數據量增長時，單個Redis實例可能無法處理所有的數據。通過使用多個Redis數據源，可以將數據分區存儲在不同的實例中，使得數據處理更加高效。 多租戶應用程式：對於多租戶應用程式，每個租戶可以擁有自己的Redis數據源，以確保數據隔離和安全性。 ...
所有分類