簡單了解mysql基礎知識

下面一起來了解下mysql基礎知識，相信大家看完肯定會受益匪淺，文字在精不在多，希望mysql基礎知識這篇短內容是你想要的。                                                   

在windows 下 啟動Mysql  cd到mysql\bin 目錄下 在dos窗口下 啟動\關閉mysql服務

//啟動mysql服務
mysqld --console
//關閉mysql服務
mysqladmin -uroot shutdown

SQL分類

SQL 主要語句可以劃分為一下3類

• DDL:數據定義語言，這些語句定義不同的數據段、數據庫、表、列、索引等數據庫對象。常用語句關鍵字主要包括create,drop,alter等

• DML:數據操作語句，用于添加、刪除、更新和查詢數據庫記錄，并檢查數據完整性。常用語句關鍵字主要包括 insert,delete,update和select等。

• DCL數據控制語句，用于控制不同數據段直接的許可和訪問級別的語句。這些語句定義了數據庫、表、字段、用戶的訪問權限和安全級別。主要 的語句包括關鍵字grant、revoke等

DDL語句

是對數據庫內部的對象進行創建 、刪除、修改等操作語言，它和DML語句最大的區別是DML只是對表內部數據操作，而不涉及表的定義、結構的修改，更不會涉及其他對象。DDL更多地由數據庫管理員（DBA）使用。

連接mysql云服務器 
mysql -uroot -p 
創建數據庫test1
create database test1;
顯示有哪些數據庫
show databases;
//mysql  自動創建的表有
information_schema:主要存儲了系統中的一些數據庫信息，比如用戶表信息、列信息、權限信息、字符集信息、分區信息等等
cluster：存儲了系統的集群信息
mysql：存儲了系統的用戶權限信息。
test：系統自動創建的測試數據庫，任何用戶都可以訪問
選擇數據庫
use test1
顯示test1數據庫中創建的所有表
show tables
刪除數據庫
drop database test1;
創建表
create table emp(ename varchar(10),hiredata date,sal decimal(10,2),deptno int(2));
查看表定義
desc emp;
查看創建表的定義
show create table emp;
刪除表
drop table emp;
修改表
alter table emp modify ename varchar(20);
增加表字段
alter table emp add column age int(3);
刪除表字段
alter table emp drop column age;
字段改名
alter table emp change age age1 int(4);
change 和modify都可以修改表的定義，不同的是change后面需要寫兩次列名，不方便，但是change的優點是可以修改列名稱，則modify則不能
修改字段排序
alter table emp add birth date after ename;
alter table emp modify age int(3) first;
更改表名
alter table emp rename emp1;

DML語句

是指對數據庫中表記錄的操作，主要包括表記錄的插入（insert）、更新（update）、刪除（delete）和查詢（select）。

插入記錄
insert into emp(ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000',1);
也可以不用指定字段名稱，但是values后面的順序要和字段的排列順序一致
inset into emp('zzx1','2000-01-01','2000',1);
含可空字段、非空但是含有默認值的字段、自增字段、可以不用再insert后的字段列表里面出現，values后面只寫對應字段名稱的value，沒寫的字段可以自動設置為null、默認值、自增的下一個數字

批量增加用逗號隔開
insert into dept values(5,'xxx'),(8,'xxx');

更新記錄
update emp set sal=4000 where ename='xxx';

刪除記錄
delete from emp where ename='doney';

查詢記錄
select * from emp;
*表示所有記錄，也可以用逗號隔開的字段來選擇查詢

查詢不重復的記錄
select distinct deptno from emp;

條件查詢
用where關鍵字來實現，可以使用<>!=等多條件可以使用or、and等

排序和限制
desc和asc是排序關鍵字，desc是降序、asc是升序排列 ORDER BY 排序,默認是升序
select * from emp order by sal;
如果排序字段的值一樣，則值相同的字段按照第二個排序字段進行排序，如果只有一個排序字段，則相同字段將會無序排序
select * from emp order by deptno,sal desc;
限制
select * from emp order by sal limit 3;
//前者是起始偏移量，后者是顯示行數
select * from emp order by sal limit 1,3;

limit 和order by 一起使用來做分頁

聚合
用戶做一下些匯總操作

sum(求和),count(*)(記錄數)，max(最大值)，min（最小值）
with rollup 是可選語法，表示是否對分類聚合后的結果進行再匯總
having 表示對分類后的結果在進行條件的過濾。

select deptno,count(1) from emp group by deptno having count(1)>=1;

表連接

大類上分為外連接和內連接
外鏈接 又分為左連接和右連接

左連接:包含所以的左邊表中的記錄甚至是右邊表中沒有和它匹配的記錄。
右連接：同上

select ename,detname from emp left join dept on emp.deptno=dept.deptno;
左連接和右連接可以相互轉換

子查詢

select * from emp where deptno in(select deptno from dept);
如果子查詢記錄唯一，可以使用=替代in
select * from emp where deptno =(select deptno from dept limit 1);

記錄查詢
將兩個表的數據按照一定的查詢出來后，將結果合在一起顯示

union all 是將結果集合并在一起，而union是將union all后的結果在進行一次distinct，去除重復

select deptno from emp union all select deptno from dept;
select demtno from emp union select deptno from dept;

? xxx 來查看

如果要查看類別 ? data types 具體的 ? int 
查看語法 如 ? create table

數據類型

對于整形數據，MySql還支持在類型名稱后面的小括號設置寬度，默認設置為int(11),配合zerofill,
當數字位數不夠的時候，用字符‘0’填充
alter table t1 modify id1 int zerofill

對于小數，MySql 分為兩種，浮點數和定點數。浮點數包括float和double，而定點數只有decimal，定點數在Mysql內部是以字符串形式存放，比浮點數更精確，適合用于貨幣等精度高的數據

浮點數和定點數可以用類型名稱加（M，D) M是幾位，D是位于小數點后面幾位。

日期類型

• DATE來表示年月日

• DATETIME來表示年月日時分秒

• TIME來表示時分秒

• 當前系統時間，通常用TIMESTAMP來表示

TIMESTAMP
創建一個字段為TIMESTAMP類型，系統自動創建了默認值為CURRENT_TIMESTAMP（系統日期）。同時MySql規定TIMESTAMP類型字段一列只能有一個默認值current_timestamp。如果修改會報錯.

TIMESTAMP 另一個重要特點是與時區有關。當插入時間時，先轉換為本地時區后存放，而從數據庫取出時，同樣會將日期轉換為本地時區后顯示，這樣兩個時區的用戶看到同一個時區可能就不一樣

查看當前時區
show variables like 'time_zone';
修改時區
set time_zone='+9.00';

DATETIME插入的格式

YYYY-MM-DD HH:MM:SS 或YY-MM-DD HH:MM:SS 的字符串允許任何標點符號用來做時間部分的間隔符
如92@12@31 11^30^45
YYYYMMDDHHMMSS 或YYMMDDHHMMSS的格式沒有間隔符的字符串

字符串類型

CHAR和VARCHAR類型
兩者的主要區別是存儲方式不同：CHAR列的長度固定為創建表時聲明的長度，長度可以為0-255；二VARCHAR列中的值是可變長度。同時在檢索的時候，CHAR列刪除尾部的空格 ，而VARCHAR保留空格，由于CHAR是固定長度，所以它的處理速度別VARCHAR快很多，但是其缺點是浪費內存，在使用中VARCHAR被更多的使用

create table vc (v varchar(4),c char(4))
insert into vc values('ab  ','ab  ');
selelct length(v),length(c) from vc
//4,2

枚舉

create table vc (v varchar(4),c char(4))
insert into vc values('ab  ','ab  ');
selelct length(v),length(c) from vc
//4,2

set類型
set類型可以一次選取多個成員

create table t2 (col set('a','b','c','d'));
INSERT into t2 VALUE ('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
對于（a,d，a)這個包含重復成員的集合只取一次 結果為’a,d'

運算符

p==/==除法獲取商
MOD==%==除法獲取余數

=和<=>區別

不能用于null比較，后者可以

between 使用格式 a between min and max 等價于 a>=min and a<=max
in的使用格式 a in(value1,value2...);
like 使用格式如 a like %123%,當字符串含有123則返回1 否則返回0
REGEXP 使用格式 str REGEXP str_pat 當str字符串中含有str_pat 相匹配的字符串，則返回1

位運算

常用函數

字符串函數

數字函數

日期和時間函數

流程函數

實例

create table salary(userid int ,salary decimal(9,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
select * from salary
select if(salary>2000,'high','low') from salary;
select ifnull(salary,0) from salary;
select case when salary <=2000 then 'low' else 'high' end from salary;
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;

其他函數

MySql引擎
MySql支持的存儲引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事務安全表，用戶可以選擇不同的數據存儲引擎來提高應用的效率

創建表如果不指定存儲引擎，系統默認使用默認存儲引擎，MySql5.5之前的默認引擎是MyISAM，5.5之后改為InnoDB。如果要修改默認的存儲引擎，可以在參數文件中設置default-table-type.

create table salary(userid int ,salary decimal(9,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
select * from salary
select if(salary>2000,'high','low') from salary;
select ifnull(salary,0) from salary;
select case when salary <=2000 then 'low' else 'high' end from salary;
select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;

MyISAM
MyISAM 不支持事務、也不 不支持外鍵，其優點是速度快，對事務完整性沒有要求。以SELECT和INSERT為主的應用基本上都就可以使用這個表

InnoDB
InnoDB存儲引擎提供了具有提交、回滾和崩潰恢復能力的事務安全。但是對比MyISAM的存儲引擎，InnoDB寫的處理效率差一些，并且會占用更多的磁盤空間以保留數據和索引。

create table autoincre_demo (i smallint not null auto_increment,name varchar(10),primary key(i))engine=innodb;insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3')

如果插入空或者0，則實際插入的將是自動增長后的值。
可以通過以下語句強制設置自動增加列的初始值，默認從1開始，但是該強制的默認值是保留到內存中，如果數據庫從起，這個強制的默認值會丟失，就需要數據庫啟動后重新設置

ALTER TABLE *** auto_increment =n

MEMORY
memory 存儲引擎使用存在于內存中的內容來創建表，每個MEMORY表實際對應一個磁盤文件，格式是.fm,MEMORY表的訪問非常快，因為它的數據是放在內存中，并且默認使用HASH索引，但是一旦服務關閉，表中的數據就會

alter table t2 engine=memory;
show TABLE status like 't2'
給memory表創建索引。可以指定hash索引還是btree索引
create index mem_hash using hash on tab_memory(city_id);

在啟動MySql服務的時候使用–init-file選項，把INSERT INTO … SELECT或LOAD DATA INFILE這樣的語句放入這個文件中，就可以在服務啟動時從持久穩固的數據源裝載表
云服務器需要足夠的內存來維持同一時間使用的MEMORY表，當不需要MEMORY表的內容，要釋放MEMORY表的內存，執行DELETE FROM或 TRUNCATE TABLE 或者是DROP TABLE
每個MEMORY表中可以放置的數據量的大小，受max_heap_table_size系統變量的約束，初始值是16mb，可以根據需要加大、
MEMORY類型的存儲引擎主要用在那些內容變化不平凡的表，或作為統計操作的中間結果表，便于高效的對中間結果進行分析并得到最終的統計結果。

TokuDB
TokuDB是第三方的存儲引擎，是一個高性能、支持事務處理的MySql和MariaDB的存儲引擎，具有高擴展性、高壓縮、高效率的寫入性能，支持大多數在線的DDL操作
TokuDB 特別適用的場景

• 日志數據，因為日志數據通常插入頻繁且儲存量大

• 歷史數據，通常不會有在寫的操作，可以利用TokuDB的高壓縮特性進行存儲

• 在線DDL頻繁的場景

幾種常用存儲引擎的適用環境

• MyISAM:如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務的完整性，并發性要求不高，那么選擇這個引擎非常合適

• Innodb：用于事務的處理，支持外鍵。如果應用對事務的完整性較高的要求，在并發條件下要求數據的一致性，數據除了插入和查詢外，還包括很多的更新和刪除操作，那么Innodb存儲引擎比較適合

• MEMORY：將所有數據都存在RAM中，如果需要快速定位記錄和其他類似數據的環境下，可以提供極快的訪問，缺陷在于對表大小的限制，太大的表無法緩存在內存中，其次是要確保表的數據是可恢復的.

• MERGE:用于將一系列等同MyISAM表以邏輯方式組合在一起，并作為一個對象引用它們。MERGE表的優點在于可以突破單個MyISAM表大小的限制，并且通過將不同的表分布在多個磁盤上，可以有效的改善MERGE表的訪問效率

Text與BLOB
如果保存少量字符串會選擇CHAR和VARCHAR 但是保存較大文本時，選擇text或blob，兩者主要差別是blob能用來保存二進制數據如圖片；而text只能保存字符數據

BLOB與TEXT引起的性能問題，特別是在執行大量的刪除數據時，刪除操作會留下很大的空洞，以后填入這些空洞的記錄在插入的性能上會有影響，建立定期使用OPTIMIZE TABLE對這類表進行碎屏整理

optimize table t

使用合成的索引來提供大文本字段的查詢性能

合成索引就是根據大文本字段的內容建立一個散列值，并把值存儲在單獨的數據列中，接下來就是通過檢索散列值找到數據行，但是只能做到精確匹配不能使用范圍搜索。可以使用MD5，SHA1，CRC32 等生成散列值，使用精確匹配，在一定程度上減少了I/O，提高了查詢效率。如果散列算法生成的字符串帶有尾部空格，就不要存儲在CHAR或VARCHAR列中，它會受尾部空格的影響

如果需要對BLOB或CLOB字段進行模糊查詢，MySQL提高前綴索引，也就是只為字段的前n列創建索引
desc select * from t where context like 'beijing%' \G;

注意事項

• 在不必要的時候避免檢索大型的BLOB或TEXT：如SELECT * 查詢，盡量從符合條件的數據行中檢索BLOB或TEXT指

• 把BLOB或TEXT列分離到單獨表中：在某些環境下，如果把這些數據列移動到第二張數據表中，可以把原數據表中的數據列轉換為固定長度的數據行格式，減少主表的碎片，可以得到固定長度數據行的性能優勢。還可以在運行SELECT * 查詢的時候不會通過網絡傳輸大量的BLOB或TEXT指

設計索引的原則

• 搜索的索引列，不一定是所要選擇的列。最適合索引的列是出現在where字句中的列，或連接字句中指定的列，而不是出現在select關鍵字后的列表中的列

• 使用唯一索引.考慮到某列中的值分布，索引的列基礎越大，索引的效果越好。入存放出生日期的列具有各部相同的值，很容易區分，但是記錄性別的列，只含有男和女對此類進行索引沒有多大好處

• 使用短檢索。如果對字符串進行檢索，應該指定一個前綴長度。例如：一個CHAR(200)列，如果前10個或20個字符內，多數值是唯一的，那么就不要對整個列進行檢索。對前10個或20個字符進行檢索能夠節省大量索引空間，是查詢更快。

• 利用最左前綴。在創建一個n列索引時，實際是創建了MySQL可利用的n個索引。多列索引可起幾個索引的作用，因為可利用索引最左邊的列級來匹配。

• 不要過度索引。每個索引都是占用額外的磁盤空間，并降低寫操作的性能。在修改表內容的時候，索引必須進行相應的更新，有時候需要重構。如果有一個索引很少被用到，那么會不必要的減緩表的修改速度。此外，mysql在生成一個執行計劃時，要考慮各個索引，這也要花費時間。創建多余的索引給查詢優化帶來了更多的工作

• 對于Innodb，記錄默認會按照一定的順序排序，如果有明確的定義主鍵，則按照主鍵排序順序保存。

存儲過程和函數

什么是存儲過程和函數

存儲過程和函數是事先經過編譯并存儲在數據庫中的一段SQL語句的集合，調用存儲過程和函數
可以簡化應用開發人員的很多工作，減少數據在數據庫和應用云服務器之間的傳輸，對于提供數據處理的效率是有好處的。

存儲過程很函數的區別在于函數必須有返回值，而存儲過程沒有，儲存過程的參數可以使用IN，OUT，INOUT類型，而函數的參數只能是IN類型的。如果有函數從其他類型的數據庫遷移到MySQL，那么就可能因此需要將函數改造成存儲過程。

存儲過程和函數的相關操作

在對儲存過程和函數操作時，需要首先確認用戶是否具有相應的權限。例如，創建存儲過程或者函數需要CREATE ROUTINE權限，修改或者刪除存儲過程或者函數需要ALTER ROUTINE權限，執行過程或者函數需要EXECUTE權限

創建一個新的過程 film_in_stock,該過程用來檢查 film_id和store_id對應的inventory是否滿足要求，并且返回滿足的inventory_id 以及滿足要求的記錄數

CREATE PROCEDURE film_in_stock(in p_fim_id int,in p_store_id int,out p_film_count int)
READS sql data
begin
  select inventory_id
  from inventory
  where film_id =p_film_id
  and store_id=p_store_id
  and inventory_in_stock(inventory_id);
  SELECT found_rows() into p_film_count;
end $$

通常在創建過程和函數之前，都會通過DELIMITE $$命令將語句的結束符從'；'修改成其他符號，這里使用‘$$’，這樣在過程和函數中的';'就不會被MySql，解釋成語句的結束而錯誤。在存儲過程或者函數創建完成 通過‘DELIMITER;'命令在將結束符改回成';'

調用過程

CALL film_in_stock(2,2,@a);

存儲過程的好處在于處理邏輯都封裝在數據庫端，調用者不需要了解中間的處理邏輯，一旦邏輯改變，只需要修改存儲過程，對調用者的程序沒有影響

刪除存儲過程或者函數

一次只能刪除一個存儲過程或者函數，刪除需要ALTER ROUTINE權限

drop procedure film_in_stock;

查看存儲過程或者函數狀態

show procedure status like 'film_in_stock';

查看存儲過程的函數定義

show create procedure film_in_stock

變量使用

存儲過程和函數中可以使用變量，在MySql 5.1版本中，變量不區分大小寫

變量的定義

通過DECLARE可以定義一個局部變量，該變量的作用范圍只能在BEGIN...END中，可以用在嵌套塊中

定義一個DATE類型的變量

DECLARE last_month_start date;

變量賦值 可以直接賦值，或者通過查詢賦值。直接賦值使用set，可以賦常量或者賦表達式

set var_name=expr [,var_name=expr]...
set last_month_start=date_sub(current_date(),interval month);
select col_name[,...] into var_name[,...] table_expr;

定義條件和處理

delimiter $$
create procedure actor_insert()
begin
 declare continue handler for sqlstate '23000' set @x2=1;
 set @x=1;
 insert into actor(actor_id,first_name,last_name) values(201,'test','201');
 set @x=2;
 insert into actor(actor_id,first_name,last_name) values(1,'test','1');
 set @x=3;
end ;
$$

調用處理函數時遇到主鍵重的錯誤會按照定義的處理方式去處理，由于定義的是CONTINUE 會繼續執行下面的語句

還支持EXIT表示終止

光標使用

聲明光標
declare cursor_name cursor for select_statement
open光標
open cursor_name
fetch光標
fetch cursor_name into var_name[,var_name]...
close光標
close cursor_name
delimiter $$
create procedure payment_stat()
begin
 declare i_staff_id int;
 declare d_amount decimal(5,2);
 declare cur_payment cursor for select staff_id,amount from payment;
 declare exit handler for not found close cur_payment;
  set @x1=0;
  set @x2=0;
  open cur_payment;
 REPEAT
   FETCH cur_payment into i_staff_id,d_amount;
    if i_staff_id =2 then
    set @x1=@x1+d_amount;
    else
    set @x2=@x2+d_amount;
    end if;
 until 0 end repeat;
 close cur_payment;
 end;
 $$

變量，條件，處理程序，光標都是通過DECLARE定義的，她們之間是有先后順序要求的。變量和條件必須在最前面聲明，然后才能是光標的聲明，最后才可以是處理程序的聲明

控制語句

case 
 when i_staff_id =2 then
 set @x1=@x1+d_amount;
 else
 set @x2=@x2+d_amount;

loop 和leave結合

create procedure actor_insert()
begin
 set @x=0;
 ins:loop
  set @x=@x+1;
  if @x=100 then
  leave ins;
  end if;
  insert into actor(first_name,last_name) values('Test','201');
  end loop ins;
end;
$$

inerate 語句作用是跳過當前循環的剩下語句，直接進入下一輪循環

create procedure actor_insert()
begin
 set @x=0;
 ins:loop
 set @x=@x+1;
 if @x=10 then
 leave ins;
 elseif mod(@x,2)=0 then
 iterate ins;
 end if;
 insert into actor(actor_id,first_name,last_name) values(@x+200,'test',@x);
 end loop ins;
end;
$$

repeat 語句 有條件的循環控制語句，當滿足條件的時候退出循環
repeat
  fetch cur_payment into i_staff_id,d_amount;
  if i_staff_id =2 then
   set @x1=@x1+d_amount;
  else
   set @x2=@x2+d_amount;
  end if;
 until 0 end repeat;

while
delimiter $$
create procedure loop_demo()
begin
 set @x=1,@x1=1;
 repeat
   set @x=@x+1；
  until @x>0 end repeat;
  while @x<1 do
   set @x=@x+1;
  end while;
 end;
 $$

//創建事件調度器
CREATE EVEN test_event_1 ON SCHEDULE
EVERY 5 SECOND
DO
INSERT INTO dept(deptno,deptname)
VALUES(3,'3');
//查看本地調度器狀態
 show variables like '%scheduler%';
 //打開調度器
 set global event_scheduler=1;
 //查看后臺進程
 show processlist;
 //創建一個新的定時器 定時清空表，防止表變大，這類觸發器非常適合去定期清空臨時表或者日志表
 create event trunc_test
 on schedule every 1 minute
 do truncate table test;

 禁用調度器或者刪除
 alter event test_event_1 disable;
 drop event test_event_1;

事務控制和鎖定語句

• MySQL支持對MyISAM和MEMORY存儲引擎的表進行表級鎖定，對InnoDB存儲引擎的表進行行集鎖定。默認情況下是自動獲得。

• LOCK TABLES 可以用于鎖定當前線程獲得的表，如果表被其他線程鎖定，當前線程一直等待到可以獲取現有鎖定為止。

• UNLOCK TABLES 可以釋放當前線程獲得的任何鎖定，當前線程執行另一個LOCK TABLES時，或當與云服務器的連接被關閉時，所有由當前線程鎖定的表被隱式地解鎖。

事務控制

mysql 通過set autocommit ,start transaction ,commit ,rollback等語句支持本地事務。默認情況下，mysql是自動提交(autocommit)的，如果需要明確的commit和rollback來提交和回滾事務，那么就需要通過明確的事務控制命令來開始事務，這是和Oracle的事務管理明顯不同的地方。

1. start transaction 或 begin語句可以開始一項新的事務

2. commit 和rollback用來提交或者回滾事務。

3. chain和release字句分別用來定義在事務提交或回滾之后的操作，chain會立即啟動一個新事務，并且和剛才的事務具有相同的隔離級別，release則會斷開和客戶端的連接。

4. set autocommit可以修改當前連接的提交方式，如果設置了set autocommit=0，則設置之后的所有事務讀需要通過明確的命令進行提交或者回滾。

如果只是對某些語句需要進行事務控制，則使用start transaction語句開始一個事務比較方便，這樣事務結束之后可以自動回到自動提交的方式，如果希望所有的事務都不是自動提交的，那么通過修改autocommit來控制事務比較方便。

start transation和commit and chain

如果在鎖表的期間，用start transaction 命令開始一個新的事務，會造成一個unlock tables被執行

因此，在同一個事務中，最好不使用不同的存儲引擎，否則rollback時需要對非事務類型的表進行特別的處理。因為，commit ,rollback 只能對事務類型的表進行提交和回滾。
  通常情況下，只對提交的事務記錄到二進制的日志中，但是如果一個事務中包含非事務類型的表，那么回滾操作也會被記錄到二進制日志中，以確保非事務類型表的更新可以被復制到從數據庫(slave)中。
在事務中可以通過定義savepoint，指定回滾事務部分，但是不能指定提交事務的一個部分。對于復雜的應用，可以定義多個不同的savepoint,滿足不同的條件時，回滾不同的savepoint,需要注意的是，如果定義了相同名字的savepoint，則后面定義的savepoint會覆蓋之前的定義。對于不需要使用的savepoint，可以通過release savepoint命令刪除。
事務回滾

分布式事務的使用

MySql從5.0.3開始支持分布式事務，當前分布式事務只支持InnoDb存儲引擎。一個分布式事務會涉及多個行動，這些行動本身是事務性。所有行動都必須一起成功完成，或者一起被回滾

在mysql中，使用分布式事務的應用程序涉及一個或多個資源管理器和一個事務管理器。

1. 資源管理器(rm)用于提供通向事務資源的途徑。數據庫云服務器是一種資源管理器，該管理器必須可以提交或回滾由rm管理的事務。如：多臺mysql數據庫作為多臺資源管理器或者幾臺mysql云服務器和幾臺oracle云服務器作為資源管理器。

2. 事務管理器（tm）用于協調作為一個分布式事務一部分的事務。tm與管理每個事務的rm s進行通信。在分布式事務中，各個單個事務均是分布式事務的“分支事務”。分布式事務和各個分支通過一種命名方法進行標示。

執行分布式的過程分為兩階段提交，發生時間有分布式事務的各個分支需要進行的行動已經被執行之后

1. 在第一階段，所有分支唄預備好，即它們被TM告知要準備提交。通常，這意味著用于管理分支的每個RM會記錄對于被穩定保存的分支的行動。分支指示是否它們可以這么做，這些結果被用于第二階段

2. 在第二階段，TM告知Rms是否要提交或回滾，如果在預備分支時，所有的分支指示它們將能夠提交，則所有的分支被告知要提交。如果在預備時，有任何分支指示它將不能提交，則所有分支唄告知回滾。

語法

xa start xid 用于啟動一個帶給定xid值的xa事務。每個xa事務必須有一個唯一的xid值，因此該值當前不能被其他xa事務使用

xa grtid[,beual[,formatId]] grtid 是一個分布式事務比較符，相同的分布式事務應該使用相同的gtrid，這樣可以明確知道XA事務屬于哪個分布式事務

bequal 是一個分支限定符，默認值是空值。對于一個分布式事務中的每個分支事務，bqual指是唯一的

formatId是一個數值，要用來標志 由gtrid和bqual值使用的格式，默認是1

xa end xid[suspend [for migrate]]
xa prepare xid

使事務進入prepare 狀態，也就是兩階段提交的第一個提交階段

xa commit xid[one phase]
xa rollback xid

用來提交和回滾具體的分支事務

xa recover 返回當前數據庫中處于PREPARE狀態的分支事務的具體信息

分布式的關鍵在于如何確保分布式事務的完整性，以及在某個分支出現問題時的故障解決，xa的相關命令就是提供給應用如何在多個獨立的數據庫之間進行分布式事務的管理，包括啟動一個分支事務、使事務進入準備階段以及事務的實際提交回滾操作等，

例子

如果分支事務在執行到prepare狀態是，數據庫異常，且不能再支持啟動，需要備份和binlog來回復數據，

SQL Mode

在MySql中，SQLMode常用來解決下面幾類問題

1. 通過設置SQL Mode，可以完成不同嚴格程度的數據校驗，有效的保障數據準確性。

2. 通過設置SQL Mode，為ANSI模式，來保證大多數SQL符合標準的Sql語法，這樣應用在不同數據庫之間進行遷移時，則不需要對業務SQL進行較大的修改

3. 在不同數據庫之間進行數據遷移之前，通過設置SQL Mode可以使MySQL上的數據更方便地遷移到目標數據庫中

查看 SQL Mode命令

select @@sql_mode

插入一個出國實際定義值的大小varchar(10)

insert into value('123400000000000000000000000000000');
//查看warning內容
show warnings
select * from t 這里對插入的數據進行截取前10位

設置SQL Mode為 嚴格模式

set session sql_mode='STRICT_TRANS_TABLES'

再次插入insert into value('123400000000000000000000000000000'); 直接給出ERROR，而不是warning

SQL Mode常見功能

校驗日期是合法性

set seesion sql_mode='ANSI'
insert into t values('2007-04-31')

結果是 插入值變成'0000-00-00 00:00:00' 并且系統給出warning 而在TRADITIONAL模式下，直接提示日期非法，拒絕插入，同時Mode（x,0）也會報錯

qidon NO_BACKSLASH_ESCAPES模式，使反斜杠成為普通字符，在導入數據時，如果數據含有反斜杠字符,你們啟動NO_BACKSLASH_ESCAPES模式，保證數據的正確性

啟動PIPES_AS_CONCAT。將||視為字符串連接符，在Oracle等數據庫中，||被視為字符串的連接操作符，所以在其他數據庫中含有||操作符的sql在MySql將無法執行，為了解決這個問題mysql提供了PIPES_AS_CONCAT模式、

MySql分區

MySql從5.1版本開始支持分區，分區是指按照一定的規則，數據庫把一個表分解成多個更小的，更容易管理的部分。就訪問數據庫的應用而言，邏輯上只有一個表或一個索引，但是實際上這個表可能由數10個物理分區對象組成，每個分區都是一個獨立的對象，可以獨自處理，可以作為表的一部分進行處理。分區對應用而言是完全透明的，不影響應用的業務邏輯

優點

• 和單個磁盤或者文件系統分區相比，可以存儲更多數據

• 優化查詢。在where子句中包含分區條件，可以只掃描必要的一個或多個分區來提高查詢效率；同時在涉及SUM（）和COUNT（）這類聚合函數的查詢時，可以容易的在每個分區上并行處理，最終只需要匯總所有分區的結果

• 對于已經過期或者不需要保存的數據，可以通過刪除與這些數據有關的分區來快速刪除數據

• 跨多個磁盤來分散數據查詢，以獲得更大的查詢吞吐量

分區有利于管理非常大的表，它采用 分而治之的邏輯，分區引入分區鍵的概念，分區鍵用于根據某個區間鍵，特定值列表或者HASH函數執行數據的聚集，讓數據根據規則分布在不同的分區中，讓一個大對象變成一些小對象

show VARIABLES like '%partition%' 查看是否支持分區

Mysql支持大部分存儲引起如MyISAM，INNODb,Memory等存儲引擎，創建分區，在5.1版本中，同一個分區表的所以分區必須使用同一個存儲引擎；在同要給表上，不能對一個分區使用MyISAM引擎和Innodb引擎，但是在同一個MySQL云服務器上，甚至同一個數據庫中，對于不同的分區表使用不同的存儲引擎

分區類型

• range分區：基于一個給定連續區間范圍，把數據分配到不同的分區。

• LIST分區：類似RANGE分區，區別在LIST分區是基于枚舉出的值列表分區，RANGE是基于給定的連續區間范圍分區

• HASH分區：基于給定的分區個數，把數據分配到不同的分區

• KEY分區：類似HASH分區

在5.1版本中，RANGE分區，LIST分區，HASH分區要求分區鍵都是int類型，key分區，可以使用其他類型（除了BLOB和TEXT類除外）作為分區鍵
分區表的主鍵/唯一鍵必須包含分區鍵，不能使用主鍵/唯一鍵，要么分區表的主鍵/唯一鍵都必須包含分區鍵，分區的名字是不區分大小寫的

range分區

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-21',
    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)
);
//上述的分區方案將storid,1-9分到p0區，10-19分到p1區，等如果插入大于30，會出現錯誤，因為沒有規則保護大于30的

INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',19);//可以

//Table has no partition for value 40
INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',40);

添加分區
alter  table emp add partition(partition p3 values less than maxvalue);
maxvalue表示最大的可能的整數值

mysql 支持在values less than 語句中加入表達式
比如以日期作為分區
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-21',
    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)
);
MySQl 5.5改進了range分區給你，通過支持非整數分區，創建日期分區就不需要通過函數進行轉換
partition by range(separated )(
    PARTITION p0 VALUES less than ('1996-01-01'),
    PARTITION p1 VALUES less than ('2001-01-01'),
    PARTITION p2 VALUES less than ('2006-01-01')
);

rang分區的功能適用一下情況
- 當需要刪除過期的數據，只需要簡單的alter table emp drop partition p0 來刪除p0 分區中的數據。對于具有上百萬條記錄的表來說，刪除分區要比運行一個delete語句有效的多
- 經常運行包含分區鍵的查詢，MyySql可以很快地確定只有某一個或者某些分區需要掃描，因為其他分區不可能包含有符合該where字句的任何記錄。例如檢索id大于25的記錄數，MySql只需要掃描p2分區即可

explain partition select count(1) from emp where store_id>=25

List分區
List分區是建立離散的值列表告訴數據庫特定的值屬于哪個分區，LIST分區在很多方面類似于RANGE分區，區別在于LIST分區是從屬于一個枚舉列表，RANGE分區是從屬于一個連續區間值的集合

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

LIST分區不存在類似于VALUES LESS THAN MAXVALUE這樣的值 在MYSQL5.5支持非整數分

Columns分區
Column分區是5.5引入的分區類型，引入Columns分區解決了MySQL5.5版本之前RANGE和LIST分區值值支持整數分區，從而導致需要額外的函數計算得到整數值或者通過額外的轉換表來轉換為整數在分區的問題
Column分區可以細分為RANGE Columns分區和LIST Columns分區，RANGE Columns分區和LIST Columns分區都支持整數，日期時間，字符串三大數據類型
對于Range分區和List分區，Colums分區的亮點除了支持數據類型增加之外，還支持多列分區

create table rc3(a int,b int)
parition by range columns(a,b)(
 parition p01 values less than(0,10),
 parition p01 values less than(10,10),
 parition p01 values less than(10,20),
 parition p01 values less than(maxvalue,maxvalue)
)

Hash分區
hash分區主要是分數熱點讀，確保數據在預先確定個數的分區中盡可能平均分布。對一個表執行HASH分區時，Mysql會對分區間應用一個散列函數，以確定數在n個分區中的那個分區中。
mysql支持兩種hash分區，常規的hash分區和線性hash分區，常規的hash使用取模算法，線性hash分區使用的一個線性的2的冪的運算法則

create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by hash(store_id)partitions 4;

這里創建了一個常規的hash 使用 partition by hash（expr）其中expr是某列值或一個整數值的表達式返回值。 partition num 對分區類型，分區鍵，分區個數進行定義，上述基于store_id列hash分區，表被分為4個分區

我們可以計算出它被保存在哪個分區中假設，假設記錄的分區編號為N，那么N=MOD（expr，num），例如emp表中有4個分區，插入一個store_id為234的 mod（234,4）=2,倍保存在第二個分區

表達式‘expr’可以是MySQL中有效的任何函數或者是其他表達式，只要它們返回一個既非常數也非隨機數的整數。每當插入更新刪除一行數據，這個表達式就需要計算一次，意味著非常復雜的表達式可能會引起性能問題
常規的HASH分區通過去模的方式去講數據平均分布在每個分區上，讓每個分區管理的數據都減少，提高了查詢的效率；可是當我們需要增加分區或者合并分區的時候，問題就出現了，假設原來是5個常規hash分區，現在需要新增一個常規hash分區，原來的去模算法是mod（expr,5)根據余數0-4分布在5個分區上，現在新增一個分區，取模算法變為mod（expr，6）根據余數0-5分區在6個分區中，原來5個分區的數據大部分都需要通過重新計算重新分區，常規的hash在分區管理上帶來的代價太大了。不適合靈活變動分區的需求，Mysql提供了線性hash分區

create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by linear hash(store_id)partitions 4;

計算編號為n的分區
首先找到下一個大于等于num的2的冪，這個值設為v，v的計算公司
v=power(2,ceiling(log(2,num)))
 =power(2,ceiling(log(2,4)))
 =power(2,ceiling(2))
 =power(2,2)
 =4
其次設置n=f（column_list)&(v-1),現在計算store_id=234對應的n值
n=f(column_list)&(4-1)
 =234&(4-1)
 =2
當n》=num設置n=n&（v-1)
對于store_id=234由于n=2《4，所以直接判斷這個會被存放到第二分區

線性hash分區的優點在于，在分區維護上（包含增加，刪除，合并，拆分分區）時，Mysql能夠處理得更加迅速；缺點是對比常規hash分區的時候，線性hash分布不太均衡

key分區
按照key分區進行分區非常類似于按照hash進行分區，只不過hash分區允許使用用戶自定義的表達式，而KEY分區不行使用用戶自定義的表達式，需要使用MySQl云服務器提供的hash函數；同時hash分區只支持整數分區，而key分區支持除了blob
or text類型外其他類型的列作為分區鍵

create table emp(id int not null.ename varchar(30),hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar(30) not null,store_id int not null) partition by key (job)partitions 4;

如果不知道分區鍵，默認為主鍵，沒有主鍵會選擇非空唯一鍵作為分區鍵

子分區
子分區是分區表對每個分區的再次分割。又被稱為復合分區，mysql5.1開始支持對已經通過range或者list分區了的表在進行子分區

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 (1900),partition p0 values less than (2000),partition p0 values less than (maxvalue))

在分區中的null值
在mysql不禁止分區鍵上使用null，分區鍵可能是一個字段或者一個用戶定義的表達式，一般情況下，mysql的分區把null當做零值，或者一個最小值處理

分區管理

刪除分區
alter table emp_date drop partition p2;
增加分區
alter table emp_date add partition(partition p5 value less than(2025))

拆分p3分區，分為p2和p3分區
分區
alter table emp_date reorganize partition p3 into(partition p2 values less than(2005),parition p3 values less than (2015));

合并分區
alter table emp_date reogranize partition p1,p2,p3 into(partition p1 values less than (2015))

重新定義list分區時，只能重新定義相鄰的分區，不能跳過list分區進行重新定義

hash&key管理

不能以range和list分區表刪除分區的方式，而是跳過alter table coalesce partition 來合并或分區
以原先4個分區為例
alter table emp coalesce partition 2 //減少分區到2個

alter table emp coalesc partition 8 //不能增加分區

要增加分區
alter table emp add partition partitions 8;

看完mysql基礎知識這篇文章后，很多讀者朋友肯定會想要了解更多的相關內容，如需獲取更多的行業信息，可以關注我們的行業資訊欄目。