久久伊人干,91n久久久

MySQL學習筆記（5）

本文主要內容：事務、鎖、SQL Mode、分區(qū)(這個有點意思)

事務控制和鎖定語句

LOCK TABLE和UNLOCK TABLE

LOCK TABLES可鎖定用于當前線程的表，如果表被其他線程鎖定，則當前線程會等待知道可以獲取所有鎖定為止。
UNLOCK TABLES可以釋放當前線程獲得的任何鎖定。

LOCK TABLES
    tab_name [AS alias]{READ [LOCAL]|[LOW_PRIORITY] WRITE}
    [,tbl_name [AS alias] {READ [LOCAL]|[LOW_PRIOEITY] WRITE}]...
    
UNLOCK TABLES

在數(shù)據(jù)導出時加-x參數(shù)可以鎖全表

事務控制

START TRANSACTION |BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT={0|1}

默認情況下MySQL是自動提交的

START TRANSACTION或BEGIN開始一項事務
COMMIT和ROLLBACK用于提交或者回滾事務
CHAIN和RELEASE字句用來定義事務提交或者回滾之后的操作，CHAIN會立即開啟一個新事務，并和剛才的事務具有相同的隔離級別，RELEASE則會斷開和客戶端的連接
SET AUTOCOMMIT修改當前連接提交方式

在進行一些敏感操作時，可是開個事務，以防刪庫跑路的風險。

敏感操作請記得在終端操作之前
start transaction;
...
commit / rollback;

分布式事務

MySQL使用分布式事務的應用程序涉及一個或多個資源管理器和一個事務管理器

資源管理器（RM）用于提供通向事務資源的途徑，數(shù)據(jù)庫服務器就是一個資源管理器。
事務管理器（TM）用于協(xié)調作為一個分布式事務一部分的事務。

執(zhí)行分布式事務過程使用兩階段提交

第一階段，所有分支被預備好。即他們被TM告知要準備提交。通常以為著用于管理分支的每個RM會記錄對于被穩(wěn)定保存的分支的行動。
第二階段，TM告知RMs是否要提交或回滾

如果一個事務資源只由一個事務資源組成（單一分支）,則該資源可以被告知同事進行預備和提交

分布式事務（XA事務）的SQL語法：

XA {START|BEGIN} xid [JOIN|RESUME]

xid: gtrid [,bqual[,formatID]]

使事務進入PREPARE狀態(tài)，兩階段提交的第一個提交階段
XA END xid [SUSPEND[FOR MIGRATE]]
XA PREPARE xid

提交或者回滾分布式事務，為第二階段
XA COMMIT xid [ONE PHASE]
XA ROLLBACK xid

返回當前數(shù)據(jù)庫中處于PREPARE狀態(tài)的分支事務的詳細信息
XA RECOVER

eg:
xa start 'test','dept';

insert into dept values(3,'xiaobai');

xa end 'test','dept';

xa prepare 'test','dept';

xa recover \G

xa commit 'test','dept'

啟用mysqlbinlog，用于恢復數(shù)據(jù)用

SQL中的安全問題

在日常開發(fā)中，開發(fā)人員一般只關心SQL是否能實現(xiàn)預期的功能，而對于SQL的安全問題一般不太重視。最常見的就是SQL注入的安全威脅。

預防SQL注入措施：

使用預編譯語句綁定變量
使用應用程序提供的轉換函數(shù)
自己定義函數(shù)進行校驗
- 整理數(shù)據(jù)使之有效
- 拒絕已知的非法數(shù)據(jù)
- 只接受已知的合法輸入

SQL Model及相關問題

SQL Mode定義了MySQL應支持的SQL語法、數(shù)據(jù)校驗等，這樣可以更容易地在不同的環(huán)境中使用MySQL。

在MySQL中，SQL Model常用來解決以下問題：

通過設置SQL Model，可以完成不同嚴格程度的數(shù)據(jù)校驗，有效地保障數(shù)據(jù)準確性
通過設置SQL Model為ANSI模式，來保證大多數(shù)SQL符合標準的SQL語法，這樣應用在不同數(shù)據(jù)庫之間進行遷移時，不需要對業(yè)務SQL進行較大修改
不同數(shù)據(jù)庫間遷移，通過設置SQL Mode可以使MySQL上的數(shù)據(jù)更方便地遷移到目標數(shù)據(jù)庫中

查看SQL Mode：select @@sql_mode

修改sql_mode：SET [SESSION|GLOBAL] sql_mode='modes'

啟動時指定：--sql-mode="modes"

SQL Mode的常見功能

檢驗日期數(shù)據(jù)合法性，在ANSI模式下，非法日期可以插入，但值為"0000-00-00 00:00:00",系統(tǒng)提示warning，在TRADITIONAL模式下，會拒絕插入并報錯。
在INSERT或UPDATE過程中，如果SQL Mode處于TRADITIONAL嚴格模式，運行MOD(x,0)會產生錯誤，而在非嚴格該模式返回NULL
啟用NO_BACKSLASH_ESCAPES模式，使反斜線成為普通字符，在導入數(shù)據(jù)時或許會用到。
啟用PIPES_AS_CONCAT模式，將“||”視為字符串連接操作符，在Oracle數(shù)據(jù)庫中“||”被作為連接操作符，在其他數(shù)據(jù)庫中則無法執(zhí)行。

常見的SQL Mode

MySQL的SQL Mode（具體版本建議參考相關版本官方網(wǎng)文檔）

sql_mode值	描述
ANSI	等同于REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE和ANSI組合模式，該模式使語法和行為更符合標準的SQL
STRICT_TRANS_TABLES	適用于事務表和非事務表，它是嚴格模式，不允許非法日期，也不允許超過字段長度的值插入字段中，對于插入不正確的值給出錯誤而不是警告
TRADITIONAL	等同于STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、TRANDITIONAL和NO_AUTO_CREATE_USER組合模式，也為嚴格模式。可應用在事務表和非事務表，用于事務表時，只要出現(xiàn)錯誤就會立即回滾

SQL Mode在遷移中如何使用

MySQL提供了很多數(shù)據(jù)庫的組合模式名稱，在異構數(shù)據(jù)庫之間遷移數(shù)據(jù)時可以嘗試使用這些模式來導出適合于目標數(shù)據(jù)庫格式的數(shù)據(jù)

組合后的模式名稱	組合中的各個sql_mode
DB2	PIPLES_AS_CONCAT 、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS
MAXDB	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER
MSSQL	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER
ORACLE	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER、NO_AUTO_CREATE_USER
POSTGRESQL	PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS

SQL Mode的嚴格模式為MySQL提供了很好的數(shù)據(jù)校驗功能，保證了數(shù)據(jù)的準確性，TRANDITIONAL和STRICT_TRANS_TABLES是常用的兩種嚴格模式。

MySQL分區(qū)

MySQL從5.1開始支持分區(qū)。分區(qū)指根據(jù)一定規(guī)則，數(shù)據(jù)庫把一個表分解成多個更小更容易管理的部分。就數(shù)據(jù)庫的應用而言，邏輯上只有一個表或一個索引，但實際該表可能由多個物理分區(qū)對象組成，每個分區(qū)都是一個獨立對象，可以獨自處理，可以作為表的一部分處理。

MySQL分區(qū)優(yōu)點：

和單磁盤或文件系統(tǒng)分區(qū)比，可以存儲更多數(shù)據(jù)
優(yōu)化查詢，在Where字句中包含分區(qū)條件時，可以只掃描必要的一個或多個分區(qū)；同時在涉及SUM()和COUNT()這類聚合函數(shù)的查詢時，可以容易地在每個分區(qū)上并行處理。
對已過期或不需要的數(shù)據(jù)，可以通過刪除分區(qū)來快速刪除
跨磁盤分散數(shù)據(jù)查詢，獲得更大的查詢吞吐量

分區(qū)概述

分區(qū)有利管理大表，引入了分區(qū)鍵的概念，分區(qū)鍵用于根據(jù)區(qū)間值、特定值列表或HASH函數(shù)值執(zhí)行數(shù)據(jù)的聚集，讓數(shù)據(jù)更具規(guī)則分布在不同的分區(qū)中，讓大對象變成一些小對象。

查看MySQL是否支持分區(qū)：SHOW variables like %partition%

和非分區(qū)表設置存儲引擎一樣，分區(qū)表設置存儲引擎，只能用[STORAGE] ENGINE字句。如下創(chuàng)建一個使用InnoDB引擎并有6個HASH分區(qū)的表：

create table emp(empid int,salary decimal(7,2),birth_date DATE)
engine=innodb
prrtition by hash(month(birth_date))
partition 6;

分區(qū)類型（MySQL5.1）

RANGE分區(qū)：給予一個給定連續(xù)區(qū)間范圍，吧數(shù)據(jù)分配到不同分區(qū)。
LIST分區(qū)：類似RANGE分區(qū)，LIST分區(qū)是基于枚舉出的值列表分區(qū)，RANGE是基于給定的連續(xù)區(qū)間范圍分區(qū)。
HASH分區(qū)：基于給定的分區(qū)個數(shù)，把數(shù)據(jù)分配到不同的分區(qū)。
KEY分區(qū)：類似HASH分區(qū)。

在MySQL5.1版本中，RANGE、LIST、HASH要求分區(qū)鍵必須是INT，KEY可以是其他類型（BLOB或TEXT除外）。無論哪種MySQL分區(qū)類型，不能使用主鍵/唯一鍵字段之外的其他字段分區(qū)。

Range分區(qū)

區(qū)間連續(xù)不能互相重疊，使用VALUES LESS THAN操作符進行分區(qū)定義。

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-31',
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)
)

以上在store_id在1~9的在分區(qū)p0中，以此類推，每個分區(qū)按順序進行定義，從最低到最高。如出現(xiàn)id大于30的商品,會報錯，服務器不知道把記錄保存在哪里

可以在設置分區(qū)使用VALUES LESS THAN MAXVALUE子句，該子句提供給所有大于明確指定的最高值的值，MAXVALUE表示最大的可能的整數(shù)值。

MySQL支持在VALUES LESS THAN子句中使用表達式，如：

CREATE TABLE emp_date(
id int not null,
ename varchar(30),
separated date not null default '9999-12-31',
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)
)

MySQL5.1要在日期或者字符串上進行分區(qū)，需要使用函數(shù)轉換，5.5改進了功能，提供RANGE COLUMNS分區(qū)支持非整數(shù)分區(qū)，創(chuàng)建日期分區(qū)就不需要轉換了。

RANGE分區(qū)功能特別適用于以下情況：

當數(shù)據(jù)過期刪除時，只需要簡單ALTER TABLE emp DROP PARTITION p0來刪除p0分區(qū)數(shù)據(jù)
經常運行包括分區(qū)鍵的查詢，如檢索商品ID大于25的記錄，MySQL掃描p2分區(qū)即可。

List分區(qū)

LIST分區(qū)是建立離散的值來分區(qū)，LIST分區(qū)是一個枚舉列表的值得集合，RANGE分區(qū)是一個連續(xù)區(qū)間值得集合。例如：

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)
)

如果插入的列值不在包含分區(qū)值列表中，那么INSERT操作會失敗并報錯。

5.5版本支持非整數(shù)分區(qū)，如：

CREATE TABLE expenses(
    expense_date DATE NOT NULL,
    category VARCHAR(30),
    amount DECIMAL(10,3)
)PARTITION BY LIST COLUMNS(category)(
    PARTITION p0 VALUES IN ('loding','food'),
    PARTITION p1 VALUES IN ('flights','ground transportation'),
    PARTITION p2 VALUES IN ('lesiure','customer entertainment'),
    PARTITION p3 VALUES IN ('communications'),
    PARTITION p4 VALUES IN ('fees')
)

Columns分區(qū)

Columns分區(qū)在MySQL5.5引入的分區(qū)類型，結局了RANGE和LIST只支持整數(shù)分區(qū)的問題，除此之外，Columns分區(qū)還支持多列分區(qū)。如：

CREATE TABLE rc3(
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a,b)(
    PARTITION p01 VALUES LESS THAN (0,10),
    PARTITION p02 VALUES LESS THAN (10,10),
    PARTITION p03 VALUES LESS THAN (10,20),
    PARTITION p03 VALUES LESS THAN (10,MAXVALUE),
    PARTITION p03 VALUES LESS THAN (MAXVALUES,MAXVALUE),
)

其中元組分開比較大小 (10,9)<(10,10)

Hash分區(qū)

HASH分區(qū)主要用來分散熱點度，確保數(shù)據(jù)在預先確定個數(shù)的分區(qū)中盡可能平均分布。MySQL支持常規(guī)HASH分區(qū)和線性HASH分區(qū)，常規(guī)使用取模算法，線性使用一個線性的2的冪的運算法則。如：

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-31',
    job VARCHAR(30) NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY HASH(store_id) PARTITIONS 4;

以上表有4個分區(qū)，數(shù)據(jù)將保存記錄的分區(qū)編號為N，N=MOD(expr,num)。以上expr為store_id，num為4。

常規(guī)HASH在新增分區(qū)或者合并分區(qū)時候，大部分數(shù)據(jù)需要重新計算，管理代價太大，不適合靈活變動的需求，這個時候就有了線性的分區(qū)PARTITION BY LINEAR，其在分區(qū)維護時能處理更加迅速。但是數(shù)據(jù)分布不如常規(guī)HASH分布不太均勻。

Key分區(qū)

類似于HASH分區(qū)，只不過HASH分區(qū)允許使用用戶自定義的表達式，而Key分區(qū)需要使用MySQL服務器提供的HASH函數(shù)；KEY分區(qū)支持除BLOB or Text類型外其他類型的列作為分區(qū)鍵。如：

CREATE TABLE emp(
    id int not null,
    ename varchar(30),
    hired date nogt null default '1970-01-01',
    separated date not null default '9999-12-31',
    job varchar(30) not null
)
PARTITION BY KEY(job) PARTITIONS 4;

創(chuàng)建Key分區(qū)不指定分區(qū)鍵時默認首先使用主鍵作為分區(qū)鍵，沒有主鍵時，選擇非空唯一鍵作為分區(qū)鍵。沒有主鍵和唯一鍵時，必須指定分區(qū)鍵。

和HASH分區(qū)類似，在KEY分區(qū)中使用LINEAR可通過取模算法分散熱點數(shù)據(jù)讀寫。

子分區(qū)和分區(qū)中處理NULL值方式

分區(qū)表中對每個分區(qū)的再次分割，即復合分區(qū)。適合保存非常大量的數(shù)據(jù)記錄。如：

CREATE TABLE ts(id int,purchased date)
partition by range(year(purchased))
subpartition by hash(to_days(purchased))
subpartitions 2
(
    partition p0 values less than (1999),
    partition p1 values less than (2000),
    partition p2 values less than maxvalue
)

在RANGE分區(qū)中，NULL作為最小值處理；LIST必須出現(xiàn)在枚舉列表中；HASH/KEY分區(qū)，NULL值被當做零值處理。

分區(qū)管理

MySQL提供添加、刪除、重定義、合并、拆分分區(qū)命令。

RANGE & LIST分區(qū)管理

刪除分區(qū)：ALTER TABLE tablename DROP PARTITION pname
增加分區(qū)：ALTER TABLE tbl_name ADD PARTITION(expr)
重新定義分區(qū)：ALTER TABLE REORGANIZE PARTITION INFO

刪除分區(qū)：
alter table emp_date drop partition p2;
增加分區(qū)：
alter table emp_date add partition (aprtititon p4 values less than (2030))

alter table expenses add partition(partition p6 values in (6,11))
重定義分區(qū)合并：
alter table emp_date reorganize partition p1,p2,p3 into (
partition p1 values less than (2015)
)

重新定義分區(qū)只能重新定義相鄰的分區(qū)，重新定義的分區(qū)區(qū)間必須和原來分區(qū)區(qū)間覆蓋相同，也不能使用重新定義分區(qū)來改變表分區(qū)的類型。

HASH & KEY分區(qū)

修改操作：ALTER TABLE COALESCE PARTITION

減少分區(qū)：
alter table emp coalesce partition 2
增加8個分區(qū)：
alter table emp add partititon partititons 8

[Github傳送門][1]
[1]: https://github.com/Melody12ab/db_mysql_note

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL事務、鎖、SQLMode、分區(qū)

MySQL事務、鎖、SQLMode、分區(qū)

事務控制和鎖定語句

LOCK TABLE和UNLOCK TABLE

事務控制

分布式事務

SQL中的安全問題

SQL Model及相關問題

SQL Mode的常見功能

常見的SQL Mode

SQL Mode在遷移中如何使用

MySQL分區(qū)

分區(qū)概述

分區(qū)類型（MySQL5.1）

Range分區(qū)

List分區(qū)

Columns分區(qū)

Hash分區(qū)

Key分區(qū)

子分區(qū)和分區(qū)中處理NULL值方式

分區(qū)管理

HASH & KEY分區(qū)

相關閱讀更多精彩內容

友情鏈接更多精彩內容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySQL事務、鎖、SQLMode、分區(qū)

事務控制和鎖定語句

LOCK TABLE和UNLOCK TABLE

事務控制

分布式事務

SQL中的安全問題

SQL Model及相關問題

SQL Mode的常見功能

常見的SQL Mode

SQL Mode在遷移中如何使用

MySQL分區(qū)

分區(qū)概述

分區(qū)類型（MySQL5.1）

Range分區(qū)

List分區(qū)

Columns分區(qū)

Hash分區(qū)

Key分區(qū)

子分區(qū)和分區(qū)中處理NULL值方式

分區(qū)管理

HASH & KEY分區(qū)

相關閱讀更多精彩內容

友情鏈接更多精彩內容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL事務、鎖、SQLMode、分區(qū)