第一篇:MySQL 系列(一) 生產標准線上環境安裝配置案例及棘手問題解決
第二篇:MySQL 系列(二) 你不知道的數據庫操作
第三篇:MySQL 系列(三)你不知道的 視圖、觸發器、存儲過程、函數、事務、索引、語句
前天有一個五年java開發經驗工程師,問我存儲過程怎麼寫?
My god, 你這個也太 low 了!
本章內容:
視圖是查詢命令結果構成的一個虛擬表(非真實存在),其本質是【根據SQL語句獲取動態的數據集,並為其命名】,用戶使用時只需使用【名稱】即可獲取結果集合,並可以當作表來查詢使用。
--格式:CREATE VIEW 視圖名稱 AS SQL語句 CREATE VIEW v1 AS SELET nid, name FROM A WHERE nid > 4
--格式:DROP VIEW 視圖名稱 DROP VIEW v1
-- 格式:ALTER VIEW 視圖名稱 AS SQL語句 ALTER VIEW v1 AS SELET A.nid, B. NAME FROM A LEFT JOIN B ON A.id = B.nid LEFT JOIN C ON A.id = C.nid WHERE A.id > 2 AND C.nid < 5
視圖的使用和普通表一樣,由於視圖是虛擬表,所以無法對其真實表進行創建、更新和刪除操作,僅做查詢用。
select * from v1
對某個表進行【增/刪/改】操作的前後觸發一些操作即為觸發器,如果希望觸發增刪改的行為之前或之後做操作時,可以使用觸發器,觸發器用於自定義用戶對表的行進行【增/刪/改】前後的行為。
# 插入前 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
創建觸發器基本語法是以下code:
但有一點要注意,觸發器內關鍵字NEW表示即將插入的數據行,OLD表示即將刪除的數據行。
# 插入前觸發器 delimiter // CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. NAME == 'nick' THEN INSERT INTO tb2 (NAME) VALUES ('aa') END END// delimiter ;
# 插入後觸發器 delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN IF NEW. num = 666 THEN INSERT INTO tb2 (NAME) VALUES ('666'), ('666') ; ELSEIF NEW. num = 555 THEN INSERT INTO tb2 (NAME) VALUES ('555'), ('555') ; END IF; END// delimiter ;
DROP TRIGGER tri_after_insert_tb1;
觸發器是由於對表的增、刪、改操作時被動執行的
insert into tb1(num) values(666)
存儲過程是一個SQL語句集合,類似函數,需要主動調用。
# 無參數存儲過程 # 創建存儲過程 delimiter // create procedure p1() BEGIN select * from t1; END// delimiter ; # 執行存儲過程 call p1()
都說了類似函數,那必須得可以接收參數,且參數有三類:
# 有參數存儲過程 # 創建存儲過程 delimiter \\ # 結尾分號改為\\ create procedure p1( 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 ; # 執行存儲過程 DECLARE @t1 INT default 3; DECLARE @t2 INT; CALL p1 (1, 2 ,@t1, @t2); SELECT @t1,@t2;
drop procedure proc_name;
執行為函數名加括號;
DECLARE代表創建一個局部變量
# 無參數 call proc_name() # 有參數,全in call proc_name(1,2) # 有參數,有in,out,inout DECLARE @t1 INT; DECLARE @t2 INT default 3; call proc_name(1,2,@t1,@t2)#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 執行存儲過程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 獲取執行完存儲的參數 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall() conn.commit() cursor.close() conn.close() print(result) pymysql執行存儲過程
該講講真正的函數了,與存儲過程不同的是有return值
delimiter \\ create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END \\ delimiter ;
drop function func_name;
# 獲取返回值 declare @i VARCHAR(32); select UPPER('nick') into @i; SELECT @i; # 在查詢中使用 select f1(11,nid) ,name from tb2;
當然MySQL也不例外,有著非常好用的內置函數
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'
更多函數:猛擊這裡 OR 猛擊這裡
事務用於將某些操作的多個SQL語句作為原子性操作,只有出現錯誤,即可回滾到原來的狀態,從而保證數據庫數據完整性。
delimiter 為改變結束符號,默認";"
# 支持事務的存儲過程 delimiter \\ create PROCEDURE p1( 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 tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 0; END\\ delimiter ;
# 執行存儲過程 DECLARE @i TINYINT; call p1(@i); select @i;
索引是表的索引目錄,在查找內容之前先查目錄中查找索引位置,從而快速定位查詢數據;
可以理解成新華字典中的索引;
索引會保存在額外的文件中。
一般的索引種類及功能:
索引合並:使用多個單列索引組合查詢搜索
覆蓋索引:select的數據列只用從索引中就能夠取得,不必讀取數據行,換句話說查詢列要被所建的索引覆蓋
# 創建表 + 索引 create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) )
# 創建索引 create index index_name on table_name(column_name)
# 刪除索引 drop index_name on table_name;
# 查看索引 show index from table_name;
#注意:對於創建索引時如果是BLOB 和 TEXT 類型,必須指定length。 create index ix_extra on in1(extra(32));
# 創建表 + 唯一索引 create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) )
# 創建唯一索引 create unique index 索引名 on 表名(列名)
# 刪除唯一索引 drop unique index 索引名 on 表名
# 創建表 + 創建主鍵 create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) OR create table in1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(ni1), index ix_name (name) )
# 創建主鍵 alter table 表名 add primary key(列名);
# 刪除主鍵 alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key;
組合索引是多個列組合成一個索引來查詢
應用場景:頻繁的同時使用多列來進行查詢,如:where name = 'nick' and age = 18。
# 創建表 create table mess( nid int not null auto_increment primary key, name varchar(32) not null, age int not null ) # 創建組合索引 create index ix_name_age on mess(name,age);
如上創建組合索引之後,查詢一定要注意:
注意:同時搜索多個條件時,組合索引的性能效率好過於多個單一索引合並。
# 查看索引 show index from 表名 # 查看執行時間 set profiling = 1; # 開啟profiling SQL... # 執行SQL語句 show profiles; # 查看結果
# like '%xx',避免%_寫在開頭 select * from tb1 where name like '%n'; # 使用函數 select * from tb1 where reverse(name) = 'nick'; # or select * from tb1 where nid = 1 or email = '[email protected]'; 注:當or條件中有未建立索引的列才失效,否則會走索引 # 類型不一致 如果列是字符串類型,傳入條件是必須用引號引起來。 select * from tb1 where name = 999; # !=,不等於 select * from tb1 where name != 'nick' 注:如果是主鍵,則還是會走索引 select * from tb1 where nid != 123 # >,大於 select * from tb1 where name > 'nick' 注:如果是主鍵或索引是整數類型,則還是會走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123 # order by select email from tb1 order by name desc; 當根據索引排序時候,選擇的映射如果不是索引,則不走索引 注:如果對主鍵排序,則還是走索引: select * from tb1 order by nid desc; # 組合索引最左前綴 如果組合索引為:(name,email),查詢使用: name and email -- 使用索引 name -- 使用索引 email -- 不使用索引
# 避免使用select * # count(1)或count(列) 代替 count(*) # 創建表時盡量時 char 代替 varchar # 表的字段順序固定長度的字段優先 # 組合索引代替多個單列索引(經常使用多個條件查詢時) # 盡量使用短索引 # 使用連接(JOIN)來代替子查詢(Sub-Queries) # 連表時注意條件類型需一致 # 索引散列值(重復少)不適合建索引,例:性別不適合
explain + 查詢SQL
用於顯示SQL執行信息參數,根據參考信息可以進行SQL優化
mysql> explain select * from suoning; +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | suoning | ALL | NULL | NULL | NULL | NULL | 4 | | +----+-------------+---------+------+---------------+------+---------+------+------+-------+ 1 row in set (1.67 sec)
id 查詢順序標識 如:mysql> explain select * from (select nid,name from tb1 where nid < 10) as B; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL | | 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 特別的:如果使用union連接氣值可能為null select_type 查詢類型 SIMPLE 簡單查詢 PRIMARY 最外層查詢 SUBQUERY 映射為子查詢 DERIVED 子查詢 UNION 聯合 UNION RESULT 使用聯合的結果 ... table 正在訪問的表名 type 查詢時的訪問方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表掃描,對於數據表從頭到尾找一遍 select * from tb1; 特別的:如果有limit限制,則找到之後就不在繼續向下掃描 select * from tb1 where email = '[email protected]' select * from tb1 where email = '[email protected]' limit 1; 雖然上述兩個語句都會進行全表掃描,第二句使用了limit,則找到一個後就不再繼續掃描。 INDEX 全索引掃描,對索引從頭到尾找一遍 select nid from tb1; RANGE 對索引列進行范圍查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意:!= 和 > 符號 INDEX_MERGE 合並索引,使用多個單列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根據索引查找一個或多個值 select * from tb1 where name = 'seven'; EQ_REF 連接時使用primary key 或 unique類型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一個匹配行,因為僅有一行,在這行的列值可被優化器剩余部分認為是常數,const表很快,因為它們只讀取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系統 表僅有一行(=系統表)。這是const聯接類型的一個特例。 select * from (select nid from tb1 where nid = 1) as A; possible_keys 可能使用的索引 key 真實使用的 key_len MySQL中使用索引字節長度 rows mysql估計為了找到所需的行而要讀取的行數 ------ 只是預估值 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列中索引的位圖,並且是冗余的。 詳細
日志可以記錄哪些查詢語句慢,沒有走索引,用戶發現問題,從而解決優化
a、配置MySQL自動記錄慢日志
修改配置文件 my.ini,沒有則添加
slow_query_log = OFF # 是否開啟慢日志記錄 long_query_time = 2 # 時間限制,超過此時間,則記錄 slow_query_log_file = /usr/slow.log # 日志文件 log_queries_not_using_indexes = OFF # 為使用索引的搜索是否記錄 # 查看當前配置信息: show variables like '%query%' # 修改當前配置: set global 變量名 = 值
b、查看MySQL慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
--verbose 版本 --debug 調試 --help 幫助 -v 版本 -d 調試模式 -s ORDER 排序方式 what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r 反轉順序,默認文件倒序拍。reverse the sort order (largest last instead of first) -t NUM 顯示前N條just show the top n queries -a 不要將SQL中數字轉換成N,字符串轉換成S。don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN 正則匹配;grep: only consider stmts that include this string -h HOSTNAME mysql機器名或者IP;hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l 總時間中不減去鎖定時間;don't subtract lock time from total time
自然所有的東西都離不開基本的語法語句啦,循環語句與判斷語句
if then\ elseif then\ else\ end if
# if條件語句 delimiter \\ CREATE PROCEDURE proc_if () BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF; END\\ delimiter ;
分為三種循環語句:while循環、repeat循環、loop循環
# while循環 delimiter \\ CREATE PROCEDURE proc_while () BEGIN DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT num ; SET num = num + 1 ; END WHILE ; END\\ delimiter ;
# repeat循環,相當於 do while delimiter \\ CREATE PROCEDURE proc_repeat () BEGIN DECLARE i INT ; SET i = 0 ; repeat select i; set i = i + 1; until i >= 5 end repeat; END\\ delimiter ;
# loop delimiter \\ CREATE PROCEDURE proc_loop () BEGIN declare i int default 0; loop_label: loop select i; set i=i+1; if i>=5 then leave loop_label; end if; end loop; END\\ delimiter ;
# 動態執行SQL delimiter \\ DROP PROCEDURE IF EXISTS proc_sql \\ CREATE PROCEDURE proc_sql () BEGIN declare p1 int; set p1 = 11; set @p1 = p1; PREPARE prod FROM 'select * from tb2 where nid > ?'; EXECUTE prod USING @p1; DEALLOCATE prepare prod; END\\ delimiter ;
# 方式二 delimiter \\ DROP PROCEDURE IF EXISTS proc_sql \\ # 如果不存在則添加,否則不做操作 CREATE PROCEDURE proc_sql ( in strSQL char(128), in nidd int ) BEGIN set @sqll = strSQL; set @p1 = nidd; PREPARE prod FROM @sqll; EXECUTE prod USING @p1; DEALLOCATE prepare prod; END\\ delimiter ; call proc_sql('select * from suoning2 where id > ?','2');