4.mysql-進階
- 2022 年 11 月 17 日
- 筆記
- python全棧開發-mysql 學習
1.事務
將多個操作步驟變成一個事務,任何一個步驟失敗,則回滾到事務的所有步驟之前狀態,大白話:要成功都成功;要失敗都失敗。
如轉賬操作,A扣錢。B收錢,必須兩個步驟都成功,才認為轉賬成功
innodb引擎中支持事務,myisam不支持。
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(32) DEFAULT NULL, `amount` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
事務的具有四大特性(ACID):
-
原子性(Atomicity)
原子性是指事務包含的所有操作不可分割,要麼全部成功,要麼全部失敗回滾。
-
一致性(Consistency)
執行的前後數據的完整性保持一致。
-
隔離性(Isolation)
一個事務執行的過程中,不應該受到其他事務的干擾。
-
持久性(Durability)
事務一旦結束,數據就持久到數據庫
python代碼操作
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb') cursor = conn.cursor() # 開啟事務 conn.begin() try: cursor.execute("update users set amount=1 where id=1")cursor.execute("update tran set amount=2 where id=2") except Exception as e: # 回滾 conn.rollback() else: # 提交 print("提交") conn.commit() cursor.close() conn.close()
2.鎖
在用MySQL時,不知你是否會疑問:同時有很多做更新、插入、刪除動作,MySQL如何保證數據不出錯呢?
MySQL中自帶了鎖的功能,可以幫助我們實現開發過程中遇到的同時處理數據的情況。對於數據庫中的鎖,從鎖的範圍來講有:
– 表級鎖,即A操作表時,其他人對整個表都不能操作,等待A操作完之後,才能繼續。
– 行級鎖,即A操作表時,其他人對指定的行數據不能操作,其他行可以操作,等待A操作完之後,才能繼續。
MYISAM支持表鎖,不支持行鎖;
InnoDB引擎支持行鎖和表鎖。
即:在MYISAM下如果要加鎖,無論怎麼加都會是表鎖。
在InnoDB引擎支持下如果是基於索引查詢的數據則是行級鎖,否則就是表鎖。
所以,一般情況下我們會選擇使用innodb引擎,並且在 搜索 時也會使用索引(命中索引)。
接下來的操作就基於innodb引擎來操作:
CREATE TABLE `L1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `count` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在innodb引擎中,update、insert、delete的行為內部都會先申請鎖(排它鎖),申請到之後才執行相關操作,最後再釋放鎖。
所以,當多個人同時像數據庫執行:insert、update、delete等操作時,內部加鎖後會排隊逐一執行。
而select則默認不會申請鎖。
2.1 排它鎖
import pymysql import threading def task(): conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb') cursor = conn.cursor(pymysql.cursors.DictCursor) # cursor = conn.cursor() # 開啟事務 conn.begin() cursor.execute("select id,age from tran where id=2 for update") # fetchall ( {"id":1,"age":10},{"id":2,"age":10}, ) ((1,10),(2,10)) # {"id":1,"age":10} (1,10) result = cursor.fetchone() current_age = result['age'] if current_age > 0: cursor.execute("update tran set age=age-1 where id=2") else: print("已售罄") conn.commit() cursor.close() conn.close() def run(): for i in range(5): t = threading.Thread(target=task) t.start() if __name__ == '__main__': run()
2.2 共享鎖
共享鎖( lock in share mode
),可以讀,但不允許寫。
加鎖之後,後續其他事物可以可以進行讀,但不允許寫(update、delete、insert)
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
3.數據庫連接池
3.1 連接池創建
在操作數據庫時需要使用數據庫連接池。數據庫池可以避免頻繁的連接和斷開數據庫帶來的損耗
pip install pymysql
pip install dbutils
import threading import pymysql from dbutils.pooled_db import PooledDB MYSQL_DB_POOL = PooledDB( creator=pymysql, # 使用鏈接數據庫的模塊 maxconnections=5, # 連接池允許的最大連接數,0和None表示不限制連接數 mincached=2, # 初始化時,鏈接池中至少創建的空閑的鏈接,0表示不創建 maxcached=3, # 鏈接池中最多閑置的鏈接,0和None不限制 blocking=True, # 連接池中如果沒有可用連接後,是否阻塞等待。True,等待;False,不等待然後報錯 setsession=[], # 開始會話前執行的命令列表。如:["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服務端,檢查是否服務可用。 # 如:0 = None = never, 1 = default = whenever it is requested, # 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='root123', database='userdb', charset='utf8' ) def task(): # 去連接池獲取一個連接 conn = MYSQL_DB_POOL.connection() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute('select sleep(2)') result = cursor.fetchall() print(result) cursor.close() # 將連接交換給連接池 conn.close() def run(): for i in range(10): t = threading.Thread(target=task) t.start() if __name__ == '__main__': run()
3.2 SQL工具類的使用
3.2.1 基於模塊創建單例模式
# db.py import pymysql from dbutils.pooled_db import PooledDB class DBHelper(object): def __init__(self): # TODO 此處配置,可以去配置文件中讀取。 self.pool = PooledDB( creator=pymysql, # 使用鏈接數據庫的模塊 maxconnections=5, # 連接池允許的最大連接數,0和None表示不限制連接數 mincached=2, # 初始化時,鏈接池中至少創建的空閑的鏈接,0表示不創建 maxcached=3, # 鏈接池中最多閑置的鏈接,0和None不限制 blocking=True, # 連接池中如果沒有可用連接後,是否阻塞等待。True,等待;False,不等待然後報錯 setsession=[], # 開始會話前執行的命令列表。如:["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服務端,檢查是否服務可用。# 如:0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='root123', database='userdb', charset='utf8' ) def get_conn_cursor(self): conn = self.pool.connection() cursor = conn.cursor(pymysql.cursors.DictCursor) return conn, cursor def close_conn_cursor(self, *args): for item in args: item.close() def exec(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) conn.commit() self.close_conn_cursor(conn, cursor) def fetch_one(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) result = cursor.fetchone() self.close_conn_cursor(conn, cursor) return result def fetch_all(self, sql, **kwargs): conn, cursor = self.get_conn_cursor() cursor.execute(sql, kwargs) result = cursor.fetchall() self.close_conn_cursor(conn, cursor) return result db = DBHelper()
3.2.2 基於上下文使用數據庫池
如果你想要讓他也支持 with 上下文管理。
# db_context.py import threading import pymysql from dbutils.pooled_db import PooledDB POOL = PooledDB( creator=pymysql, # 使用鏈接數據庫的模塊 maxconnections=5, # 連接池允許的最大連接數,0和None表示不限制連接數 mincached=2, # 初始化時,鏈接池中至少創建的空閑的鏈接,0表示不創建 maxcached=3, # 鏈接池中最多閑置的鏈接,0和None不限制 blocking=True, # 連接池中如果沒有可用連接後,是否阻塞等待。True,等待;False,不等待然後報錯 setsession=[], # 開始會話前執行的命令列表。如:["set datestyle to ...", "set time zone ..."] ping=0, host='127.0.0.1', port=3306, user='root', password='root123', database='userdb', charset='utf8' ) class Connect(object): def __init__(self): self.conn = conn = POOL.connection() self.cursor = conn.cursor(pymysql.cursors.DictCursor) def __enter__(self): return self def __exit__(self, exc_type, exc_val, exc_tb): self.cursor.close() self.conn.close() def exec(self, sql, **kwargs): self.cursor.execute(sql, kwargs) self.conn.commit() def fetch_one(self, sql, **kwargs): self.cursor.execute(sql, kwargs) result = self.cursor.fetchone() return result def fetch_all(self, sql, **kwargs): self.cursor.execute(sql, kwargs) result = self.cursor.fetchall() return result
4.索引
在數據庫中索引最核心的功能就是:**加速查找**
4.1 索引的原理
索引的底層是基於B+Tree的數據結構存儲的
據庫的索引是基於上述B+Tree的數據結構實現,但在創建數據庫表時,如果指定不同的引擎,底層使用的B+Tree結構的原理有些不同。
-
myisam引擎,非聚簇索引(數據 和 索引結構 分開存儲)
-
innodb引擎,聚簇索引(數據 和 主鍵索引結構存儲在一起)
在企業開發中一般都會使用 innodb 引擎(內部支持事務、行級鎖、外鍵等特點),在MySQL5.5版本之後默認引擎也是innodb
4.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;
4.1.2
create table 表名( id int not null auto_increment primary key, name varchar(32) not null, age int )engine=innodb default charset=utf8;
4.2 常見的索引
**建議不要隨意添加索引,因為索引也是需要維護的,太多的話反而會降低系統的性能。**
開發過程中常見的索引類型有:
- 主鍵索引:加速查找、不能為空、不能重複。
create table 表名( id int not null auto_increment primary key, -- 主鍵 name varchar(32) not null );
- 唯一索引:加速查找、不能重複。
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 primary key, name varchar(32) not null, email varchar(64) not null, index ix_email (email), -- 普通索引 index ix_name (name),);
- 組合索引
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
4.3 索引失效
會有一些特殊的情況,讓我們無法命中索引(即使創建了索引),這也是需要大家在開發中要注意的。
- 類型不一樣
select * from info where name = 123; -- 未命中 特殊的主鍵: select * from info where id = "123"; -- 命中
- 使用不等於
select * from info where name != "kunmzhao"; -- 未命中 特殊的主鍵: select * from big where id != 123; -- 命中
- or的使用
當or條件中有未建立索引的列才失效select * from big where id = 123 or password="xx"; -- 未命中
- like的使用
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 "wuw-%"; -- 命中
- 排序的使用
當根據索引排序時候,選擇的映射如果不是索引,則不走索引select * from big order by name asc; -- 未命中 select * from big order by name desc; -- 未命中 特別的主鍵: select * from big order by id desc; -- 命中
- 最左前綴原則, 如果是聯合索引,要遵循最左前綴原則。
如果聯合索引為:(name,password) name and password -- 命中 name -- 命中 password -- 未命中 name or password -- 未命中
5.函數
MySQL中提供了很多函數,為我們的SQL操作提供便利,例如:
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 使用一個負值。 TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str , 其中所有remstr 前綴和/或後綴都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項,在未指定情況下,可刪除空格。
6.存儲過程
儲過程,是一個存儲在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;