第21章 創(chuàng)建和操縱表

21.1 創(chuàng)建表

MySQL不僅用于表數(shù)據(jù)操縱,而且還可以用來(lái)執(zhí)行數(shù)據(jù)庫(kù)和表的所有操作,包括表本身的創(chuàng)建和處理.

一般有兩種創(chuàng)建表的方法:

• 使用具有交互式創(chuàng)建和管理表的工具(如第2章討論的工具);
• 表也可以直接用MySQL語(yǔ)句操縱.

為了用程序創(chuàng)建表,可使用SQL的CREATE TABLE語(yǔ)句.值得注意的是,在使用交互式工具時(shí),實(shí)際上使用的是MySQL語(yǔ)句.但是,這些語(yǔ)句不是用戶編寫的,界面工具會(huì)自動(dòng)生成并執(zhí)行相應(yīng)的MySQL語(yǔ)句

21.1.1 表創(chuàng)建基礎(chǔ)

為利用CREATE TABLE創(chuàng)建表,必須給出下列信息:

• 新表的名字,在關(guān)鍵字CREATE TABLE之后給出;
• 表列的名字和定義,用逗號(hào)分隔.

CREATE TABLE語(yǔ)句也可能會(huì)包括其他關(guān)鍵字或選項(xiàng),但至少要包括表的 名字和列的細(xì)節(jié).

語(yǔ)句格式化
CREATE TABLE語(yǔ)句就是語(yǔ)句格式化的一個(gè)很好的例子,它被安排在多個(gè)行上,其中的列定義進(jìn)行了恰當(dāng)?shù)目s進(jìn),以便閱讀和編輯.以何種縮進(jìn)格式安排SQL語(yǔ)句沒(méi)有規(guī)定,但我強(qiáng)烈推薦采用某種縮進(jìn)格式.

處理現(xiàn)有的表
在創(chuàng)建新表時(shí),指定的表名必須不存在,否則將出錯(cuò).如果要防止意外覆蓋已有的表,SQL要求首先手工刪除該表(請(qǐng)參閱后面的小節(jié)),然后再重建它,而不是簡(jiǎn)單地用創(chuàng)建表語(yǔ)句覆蓋它.
如果你僅想在一個(gè)表不存在時(shí)創(chuàng)建它,應(yīng)該在表名后給出IF NOT EXISTS.這樣做不檢查已有表的模式是否與你打算創(chuàng)建的表模式相匹配.它只是查看表名是否存在,并且僅在表名不存在時(shí)創(chuàng)建它.

NULL和空串
理解NULL
不要把NULL值與空串相混淆.NULL值是沒(méi)有值,它不是空串.如果指定''(兩個(gè)單引號(hào),其間沒(méi)有字符),這在NOT NULL列中是允許的.空串是一個(gè)有效的值,它不是無(wú)值.NULL值用關(guān)鍵字NULL而不是空串指定.

例子:
CREATE TABLE customers(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
cust_city char(50) NULL,
cust_state char(5) NULL,
cust_zip char(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
cust_email char(255) NULL,
PRIMARY KEY(cust_id)
)ENGINE=InnoDB;

21.1.2 使用NULL值

NULL值就是沒(méi)有值或缺值.允許NULL值的列也允許在插入行時(shí)不給出該列的值.
不允許NULL值的列不接受該列沒(méi)有值的行,換句話說(shuō),在插入或更新行時(shí),該列必須有值.
每個(gè)表列或者是NULL列,或者是NOT NULL列,這種狀態(tài)在創(chuàng)建時(shí)由表的定義規(guī)定.

例子:
CREATE TABLE order(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL,
cust_id int NOT NULL,
PRIMARY KEY(order_num)
)ENGINE=InnoDB;

CREATE TABLE vendors(
vend_id int NOT NULL AUTO_INCREMENT,
vend_name char(50) NOT NULL,
vend_address char(50) NULL,
vend_city char(50) NULL,
vend_state char(5) NULL,
vend_zip char(10) NULL,
vend_country char(50) NULL,
PRIMARY KEY(vend_id)
)ENGINE=InnoDB;

21.1.3 主鍵再介紹

主鍵值必須唯一.即,表中的每個(gè)行必須具有唯一的主鍵值.如果主鍵使用單個(gè)列,則它的值必須唯一.如果使用多個(gè)列,則這些列的組合值必須唯一.

主鍵可以在創(chuàng)建表時(shí)定義,或者在創(chuàng)建表之后定義.

主鍵和NULL值
第1章介紹過(guò),主鍵為其值唯一標(biāo)識(shí)表中每個(gè)行的列.主鍵中只能使用不允許NULL值的列.允許NULL值的列不能作為唯一標(biāo)識(shí).

21.1.4 使用AUTO_INCREMENT

AUTO_INCREMENT告訴MySQL,本列每當(dāng)增加一行時(shí)自動(dòng)增量.每次執(zhí)行一個(gè)INSERT操作時(shí),MySQL自動(dòng)對(duì)該列增量(從而才有這個(gè)關(guān)鍵字AUTO_INCREMENT),給該列賦予下一個(gè)可用的值.這樣給每個(gè)行分配一個(gè)唯一的cust_id,從而可以用作主鍵值.

每個(gè)表只允許一個(gè)AUTO_INCREMENT列,而且它必須被索引(如,通過(guò)使它成為主鍵).

覆蓋AUTO_INCREMENT
如果一個(gè)列被指定為AUTO_INCREMENT,則它需要使用特殊的值嗎?你可以簡(jiǎn)單地在INSERT語(yǔ)句中指定一個(gè)值,只要它是唯一的(至今尚未使用過(guò))即可,該值將被用來(lái)替代自動(dòng)生成的值.后續(xù)的增量將開(kāi)始使用該手工插入的值.

確定AUTO_INCREMENT值讓MySQL生成(通過(guò)自動(dòng)增量)主 鍵的一個(gè)缺點(diǎn)是你不知道這些值都是誰(shuí).

考慮這個(gè)場(chǎng)景:你正在增加一個(gè)新訂單.這要求在orders表中創(chuàng)建一行,然后在orderitms表中對(duì)訂購(gòu)的每項(xiàng)物品創(chuàng)建一行.order_num在orderitems表中與訂單細(xì)節(jié)一起存儲(chǔ).這就是為什么orders表和orderitems表為相互關(guān)聯(lián)的表的原因.這顯然要求你在插入orders行之后,插入orderitems行之前知道生成的order_num.
那么,如何在使用AUTO_INCREMENT列時(shí)獲得這個(gè)值呢?可使用last_insert_id()函數(shù)獲得這個(gè)值,如下所示:
SELECT last_insert_id()
此語(yǔ)句返回最后一個(gè)AUTO_INCREMENT值,然后可以將它用于后續(xù)的MySQL語(yǔ)句.

21.1.5 指定默認(rèn)值

如果在插入行時(shí)沒(méi)有給出值,MySQL允許指定此時(shí)使用的默認(rèn)值.默認(rèn)值用CREATE TABLE語(yǔ)句的列定義中的DEFAULT關(guān)鍵字指定.

不允許函數(shù) 與大多數(shù)DBMS不一樣,MySQL不允許使用函數(shù)作為默認(rèn)值,它只支持常量.

使用默認(rèn)值而不是NULL值 許多數(shù)據(jù)庫(kù)開(kāi)發(fā)人員使用默認(rèn)值而不是NULL列,特別是對(duì)用于計(jì)算或數(shù)據(jù)分組的列更是如此.

例子:
CREATE TABLE orderitems(
order_num int NOT NULL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY(order_num, order_item)
)ENGINE=InnoDB;

21.1.6 引擎類型

MySQL與其他DBMS不一樣,它具有多種引擎.它打包多個(gè)引擎,這些引擎都隱藏在MySQL服務(wù)器內(nèi),全都能執(zhí)行CREATE TABLE和SELECT等命令.

為什么要發(fā)行多種引擎呢?因?yàn)樗鼈兙哂懈髯圆煌墓δ芎吞匦?為不同的任務(wù)選擇正確的引擎能獲得良好的功能和靈活性.

當(dāng)然,你完全可以忽略這些數(shù)據(jù)庫(kù)引擎.如果省略ENGINE=語(yǔ)句,則使用默認(rèn)引擎(很可能是InnoDB),多數(shù)SQL語(yǔ)句都會(huì)默認(rèn)使用它.但并不是所有語(yǔ)句都默認(rèn)使用它,這就是為什么ENGINE=語(yǔ)句很重要的原因.

以下是幾個(gè)需要知道的引擎:

• InnoDB是一個(gè)可靠的事務(wù)處理引擎,它不支持全文本搜索;
• MEMORY在功能等同于MyISAM,但由于數(shù)據(jù)存儲(chǔ)在內(nèi)存(不是磁盤) 中,速度很快(特別適合于臨時(shí)表);
• MyISAM是一個(gè)性能極高的引擎,它支持全文本搜索, 但不支持事務(wù)處理.

外鍵不能跨引擎
混用引擎類型有一個(gè)大缺陷.外鍵不能跨引擎,即使用一個(gè)引擎的表不能引用具有使用不同引擎的表的外鍵.

那么,你應(yīng)該使用哪個(gè)引擎?這有賴于你需要什么樣的特性.MyISAM由于其性能和特性可能是最受歡迎的引擎.但如果你不需要可靠的事務(wù)處理,可以使用其他引擎.

21.2 更新表

為更新表定義,可使用ALTER TABLE語(yǔ)句.但是,理想狀態(tài)下,當(dāng)表中存儲(chǔ)數(shù)據(jù)以后,該表就不應(yīng)該再被更新.在表的設(shè)計(jì)過(guò)程中需要花費(fèi)大量時(shí)間來(lái)考慮,以便后期不對(duì)該表進(jìn)行大的改動(dòng).

為了使用ALTER TABLE更改表結(jié)構(gòu),必須給出下面的信息:

• 在ALTER TABLE之后給出要更改的表名(該表必須存在,否則將出錯(cuò));
• 所做更改的列表.

ALTER TABLE的一種常見(jiàn)用途是定義外鍵.

復(fù)雜的表結(jié)構(gòu)更改一般需要手動(dòng)刪除過(guò)程,它涉及以下步驟:

• 用新的列布局創(chuàng)建一個(gè)新表;
• 使用INSERT SELECT語(yǔ)句從舊表復(fù)制數(shù)據(jù)到新表.如果有必要,可使用轉(zhuǎn)換函數(shù)和計(jì)算字段;
• 檢驗(yàn)包含所需數(shù)據(jù)的新表;
• 重命名舊表(如果確定,可以刪除它);
• 用舊表原來(lái)的名字重命名新表;
• 根據(jù)需要,重新創(chuàng)建觸發(fā)器、存儲(chǔ)過(guò)程、索引和外鍵.

小心使用ALTER TABLE
使用ALTER TABLE要極為小心,應(yīng)該在進(jìn)行改動(dòng)前做一個(gè)完整的備份(模式和數(shù)據(jù)的備份).數(shù)據(jù)庫(kù)表的更改不能撤銷,如果增加了不需要的列,可能不能刪除它們.類似地,如果刪除了不應(yīng)該刪除的列,可能會(huì)丟失該列中的所有數(shù)據(jù).

例子:
給表添加一個(gè)列
ALTER TABLE vendors ADD vend_phone CHAR(20);

刪除表中的一列
ALTER TABLE vendors DROP COLUME vend_phone;

21.3 刪除表

刪除表(刪除整個(gè)表而不是其內(nèi)容)非常簡(jiǎn)單,使用DROP TABLE語(yǔ)句即可

刪除表沒(méi)有確認(rèn),也不能撤銷,執(zhí)行這條語(yǔ)句將永久刪除該表.

例子:
刪除customers2表
DROP TABLE customers2;

21.4 重命名表

使用RENAME TABLE語(yǔ)句可以重命名一個(gè)表

例子:
將表customers2重命名為customers
RENAME TABLE customers2 TO customers;

對(duì)多個(gè)表進(jìn)行重命名
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;

21.5 小結(jié)

第22章 使用視圖

22.1 視圖

視圖是虛擬的表.與包含數(shù)據(jù)的表不一樣,視圖只包含使用時(shí)動(dòng)態(tài)檢索數(shù)據(jù)的查詢.

例子:
SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id=order_cust_id AND orderitems.order_num=order.order_num AND proid='TNT2';

22.1.1 為什么使用視圖

視圖的一些常見(jiàn)應(yīng)用.

• 重用SQL語(yǔ)句.
• 簡(jiǎn)化復(fù)雜的SQL操作.在編寫查詢后,可以方便地重用它而不必知道它的基本查詢細(xì)節(jié).
• 使用表的組成部分而不是整個(gè)表.
• 保護(hù)數(shù)據(jù).可以給用戶授予表的特定部分的訪問(wèn)權(quán)限而不是整個(gè)表的訪問(wèn)權(quán)限.
• 更改數(shù)據(jù)格式和表示.視圖可返回與底層表的表示和格式不同的數(shù)據(jù).

在視圖創(chuàng)建之后,可以用與表基本相同的方式利用它們.可以對(duì)視圖執(zhí)行SELECT操作,過(guò)濾和排序數(shù)據(jù),將視圖聯(lián)結(jié)到其他視圖或表,甚至能添加和更新數(shù)據(jù).

重要的是知道視圖僅僅是用來(lái)查看存儲(chǔ)在別處的數(shù)據(jù)的一種設(shè)施. 視圖本身不包含數(shù)據(jù),因此它們返回的數(shù)據(jù)是從其他表中檢索出來(lái)的. 在添加或更改這些表中的數(shù)據(jù)時(shí),視圖將返回改變過(guò)的數(shù)據(jù).

性能問(wèn)題
因?yàn)橐晥D不包含數(shù)據(jù),所以每次使用視圖時(shí),都必須處理查詢執(zhí)行時(shí)所需的任一個(gè)檢索.如果你用多個(gè)聯(lián)結(jié)和過(guò)濾創(chuàng)建了復(fù)雜的視圖或者嵌套了視圖,可能會(huì)發(fā)現(xiàn)性能下降得很厲害.因此,在部署使用了大量視圖的應(yīng)用前,應(yīng)該進(jìn)行測(cè)試.

22.1.2 視圖的規(guī)則和限制

關(guān)于視圖創(chuàng)建和使用的一些最常見(jiàn)的規(guī)則和限制.

• 與表一樣,視圖必須唯一命名(不能給視圖取與別的視圖或表相 同的名字).
• 對(duì)于可以創(chuàng)建的視圖數(shù)目沒(méi)有限制.
• 為了創(chuàng)建視圖,必須具有足夠的訪問(wèn)權(quán)限.這些限制通常由數(shù)據(jù)庫(kù)管理人員授予.
• 視圖可以嵌套,即可以利用從其他視圖中檢索數(shù)據(jù)的查詢來(lái)構(gòu)造一個(gè)視圖.
• ORDER BY可以用在視圖中,但如果從該視圖檢索數(shù)據(jù)SELECT中也含有ORDER BY,那么該視圖中的ORDER BY將被覆蓋.
• 視圖不能索引,也不能有關(guān)聯(lián)的觸發(fā)器或默認(rèn)值.
• 視圖可以和表一起使用.例如,編寫一條聯(lián)結(jié)表和視圖的SELECT語(yǔ)句.

22.2 使用視圖
在理解什么是視圖(以及管理它們的規(guī)則及約束)后,我們來(lái)看一下視圖的創(chuàng)建.

• 視圖用CREATE VIEW語(yǔ)句來(lái)創(chuàng)建.
• 使用SHOW CREATE VIEW viewname;來(lái)查看創(chuàng)建視圖的語(yǔ)句.
• 用DROP刪除視圖,其語(yǔ)法為DROP VIEW viewname;.
• 更新視圖時(shí),可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW.如果要更新的視圖不存在,則第2條更新語(yǔ)句會(huì)創(chuàng)建一個(gè)視圖;如果要更新的視圖存在,則第2條更新語(yǔ)句會(huì)替換原有視圖.

22.2.1 利用視圖簡(jiǎn)化復(fù)雜的聯(lián)結(jié)

視圖的最常見(jiàn)的應(yīng)用之一是隱藏復(fù)雜的SQL,這通常都會(huì)涉及聯(lián)結(jié).

視圖極大地簡(jiǎn)化了復(fù)雜SQL語(yǔ)句的使用.利用視圖,可一次性編寫基礎(chǔ)的SQL,然后根據(jù)需要多次使用.

創(chuàng)建可重用的視圖 創(chuàng)建不受特定數(shù)據(jù)限制的視圖是一種好辦法.

例子:
返回已訂購(gòu)了任意產(chǎn)品的所有客戶的列表
CREATE VIEW productcusomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num = orders.order_num;

檢索訂購(gòu)了產(chǎn)品TNT2的客戶
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id='TNT2';

22.2.2 用視圖重新格式化檢索出的數(shù)據(jù)

視圖的另一常見(jiàn)用途是重新格式化檢索出的數(shù)據(jù).

例子:
搜索結(jié)果
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;

創(chuàng)建視圖
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;

從視圖中查詢數(shù)據(jù)
SELECT * FROM vendorlocations;

22.2.3 用視圖過(guò)濾不想要的數(shù)據(jù)

視圖對(duì)于應(yīng)用普通的WHERE子句也很有用.

HERE子句與WHERE子句
如果從視圖檢索數(shù)據(jù)時(shí)使用了一條WHERE子句,則兩組子句(一組在視圖中,另一組是傳遞給視圖的)將自動(dòng)組合.

例子:
創(chuàng)建視圖
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

從視圖中查詢數(shù)據(jù)
SELECT * FROM customeremaillist;

22.2.4 使用視圖與計(jì)算字段

視圖非常容易創(chuàng)建,而且很好使用.正確使用,視圖可極大地簡(jiǎn)化復(fù)雜的數(shù)據(jù)處理.

例子:
檢索某個(gè)特定訂單中的物品,計(jì)算每種物品的總價(jià)格:
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems WHERE order_num=20005;

創(chuàng)建視圖
create VIEW orderitemsexpanded AS
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems;

從視圖中檢索數(shù)據(jù)
SELECT * FROM orderitemsexpanded WHERE order_num=20005;

22.2.5 更新視圖

迄今為止的所有視圖都是和SELECT語(yǔ)句使用的.然而,視圖的數(shù)據(jù)能否更新?答案視情況而定.
通常,視圖是可更新的(即,可以對(duì)它們使用INSERT、UPDATE和 DELETE).
更新一個(gè)視圖將更新其基表.如果你對(duì)視圖增加或刪除行,實(shí)際上是對(duì)其基表增加或刪除行.
但是,并非所有視圖都是可更新的.基本上可以說(shuō),如果MySQL不能正確地確定被更新的基數(shù)據(jù),則不允許更新(包括插入和刪除).這實(shí)際上意味著,如果視圖定義中有以下操作,則不能進(jìn)行視圖的更新:

• 分組(使用GROUP BY和HAVING);
• 聯(lián)結(jié);
• 子查詢;
• 并;
• 聚集函數(shù)(Min()、Count()、Sum()等);
• DISTINCT;
• 導(dǎo)出(計(jì)算)列.
  視圖主要用于數(shù)據(jù)檢索.

將視圖用于檢索
一般,應(yīng)該將視圖用于檢索(SELECT語(yǔ)句)而不用于更新(INSERT、UPDATE和DELETE).

22.3 小結(jié)

第23章 使用存儲(chǔ)過(guò)程

23.1 存儲(chǔ)過(guò)程

迄今為止,使用的大多數(shù)SQL語(yǔ)句都是針對(duì)一個(gè)或多個(gè)表的單條語(yǔ)句.并非所有操作都這么簡(jiǎn)單,經(jīng)常會(huì)有一個(gè)完整的操作需要多條語(yǔ)句才能完成.
這時(shí)可以創(chuàng)建存儲(chǔ)過(guò)程.存儲(chǔ)過(guò)程簡(jiǎn)單來(lái)說(shuō),就是為以后的使用而保存的一條或多條MySQL語(yǔ)句的集合.可將其視為批文件,雖然它們的作用不僅限于批處理.

23.2 為什么要使用存儲(chǔ)過(guò)程

既然我們知道了什么是存儲(chǔ)過(guò)程,那么為什么要使用它們呢?有許多理由,下面列出一些主要的理由.

• 通過(guò)把處理封裝在容易使用的單元中,簡(jiǎn)化復(fù)雜的操作.
• 由于不要求反復(fù)建立一系列處理步驟,這保證了數(shù)據(jù)的完整性. 如果所有開(kāi)發(fā)人員和應(yīng)用程序都使用同一存儲(chǔ)過(guò)程,則所使用的代碼都是相同的.這一點(diǎn)的延伸就是防止錯(cuò)誤.需要執(zhí)行的步驟越多,出錯(cuò)的可能性就越大.防止錯(cuò)誤保證了數(shù)據(jù)的一致性.
• 簡(jiǎn)化對(duì)變動(dòng)的管理.如果表名、列名或業(yè)務(wù)邏輯(或別的內(nèi)容)有變化,只需要更改存儲(chǔ)過(guò)程的代碼.使用它的人員甚至不需要知道這些變化.這一點(diǎn)的延伸就是安全性.通過(guò)存儲(chǔ)過(guò)程限制對(duì)基礎(chǔ)數(shù)據(jù)的訪問(wèn)減少了數(shù)據(jù)訛誤(無(wú)意識(shí)的或別的原因所導(dǎo)致的數(shù)據(jù)訛誤)的機(jī)會(huì).
• 提高性能.因?yàn)槭褂么鎯?chǔ)過(guò)程比使用單獨(dú)的SQL語(yǔ)句要快.
• 存在一些只能用在單個(gè)請(qǐng)求中的MySQL元素和特性,存儲(chǔ)過(guò)程可 以使用它們來(lái)編寫功能更強(qiáng)更靈活的代碼

換句話說(shuō),使用存儲(chǔ)過(guò)程有3個(gè)主要的好處,即·簡(jiǎn)單、安全、高性能.· 顯然,它們都很重要.

不過(guò),在將SQL代碼轉(zhuǎn)換為存儲(chǔ)過(guò)程前,也必須知道它的一些缺陷.

• 一般來(lái)說(shuō),存儲(chǔ)過(guò)程的編寫比基本SQL語(yǔ)句復(fù)雜,編寫存儲(chǔ)過(guò)程需要更高的技能,更豐富的經(jīng)驗(yàn).
• 你可能沒(méi)有創(chuàng)建存儲(chǔ)過(guò)程的安全訪問(wèn)權(quán)限.許多數(shù)據(jù)庫(kù)管理員限制存儲(chǔ)過(guò)程的創(chuàng)建權(quán)限,允許用戶使用存儲(chǔ)過(guò)程,但不允許他們創(chuàng)建存儲(chǔ)過(guò)程.
  盡管有這些缺陷,存儲(chǔ)過(guò)程還是非常有用的,并且應(yīng)該盡可能地使用.

不能編寫存儲(chǔ)過(guò)程?
你依然可以使用 MySQL將編寫存儲(chǔ)過(guò)程的安全和訪問(wèn)與執(zhí)行存儲(chǔ)過(guò)程的安全和訪問(wèn)區(qū)分開(kāi)來(lái).這是好事情.即使你不能(或不想)編寫自己的存儲(chǔ)過(guò)程,也仍然可以在適當(dāng)?shù)臅r(shí)候執(zhí)行別的存儲(chǔ)過(guò)程.

23.3 使用存儲(chǔ)過(guò)程

23.3.1 執(zhí)行存儲(chǔ)過(guò)程

MySQL稱存儲(chǔ)過(guò)程的執(zhí)行為調(diào)用,因此MySQL執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句為CALL.CALL接受存儲(chǔ)過(guò)程的名字以及需要傳遞給它的任意參數(shù).

例子:
CALL productpricing(@pricelow, @pricehigh, @priceaverage);

解決辦法是臨時(shí)更改命令行實(shí)用程序的語(yǔ)句分隔符://
其中,DELIMITER //告訴命令行實(shí)用程序使用//作為新的語(yǔ) 句結(jié)束分隔符,可以看到標(biāo)志存儲(chǔ)過(guò)程結(jié)束的END定義為END //而不是END;.這樣,存儲(chǔ)過(guò)程體內(nèi)的;仍然保持不動(dòng),并且 正確地傳遞給數(shù)據(jù)庫(kù)引擎.最后,為恢復(fù)為原來(lái)的語(yǔ)句分隔符,可使用DELIMITER ;. 除\符號(hào)外,任何字符都可以用作語(yǔ)句分隔符.

23.3.2 創(chuàng)建存儲(chǔ)過(guò)程

例子:
創(chuàng)建存儲(chǔ)過(guò)程
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage FROM products;
END;
調(diào)用存儲(chǔ)過(guò)程
CALL productpricing();

23.3.3 刪除存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程在創(chuàng)建之后,被保存在服務(wù)器上以供使用,直至被刪除.
刪除命令從服務(wù)器中刪除存儲(chǔ)過(guò)程.

例子:
DROP PROCEDURE productpricing;
這條語(yǔ)句刪除剛創(chuàng)建的存儲(chǔ)過(guò)程.請(qǐng)注意沒(méi)有使用后面的(),只給出存儲(chǔ)過(guò)程名.

僅當(dāng)存在時(shí)刪除 如果指定的過(guò)程不存在,則DROPPROCEDURE將產(chǎn)生一個(gè)錯(cuò)誤.當(dāng)過(guò)程存在想刪除它時(shí)(如果過(guò)程不存在也不產(chǎn)生錯(cuò)誤)可使用DROP PROCEDURE IF EXISTS.

23.3.4 使用參數(shù)

變量(variable)內(nèi)存中一個(gè)特定的位置,用來(lái)臨時(shí)存儲(chǔ)數(shù)據(jù).
變量名 所有MySQL變量都必須以@開(kāi)始.
一般,存儲(chǔ)過(guò)程并不顯示結(jié)果,而是把結(jié)果返回給你指定的變量.

例子:
CREATE PRODURE productpricing(
OUT p1 DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT MIN(prod_price) INTO p1 FROM products;
SELECT MAX(prod_price) INTO ph FROM products;
SELECT Avg(prod_price) INTO pa FROM pruductsl
END;

此存儲(chǔ)過(guò)程接受3個(gè)參數(shù):pl存儲(chǔ)產(chǎn)品最低價(jià)格,ph存儲(chǔ)產(chǎn)品最高價(jià)格,pa存儲(chǔ)產(chǎn)品平均價(jià)格.每個(gè)參數(shù)必須具有指定的類型,這里使用十進(jìn)制值.關(guān)鍵字OUT指出相應(yīng)的參數(shù)用來(lái)從存儲(chǔ)過(guò)程傳出一個(gè)值(返回給調(diào)用者).MySQL支持IN(傳遞給存儲(chǔ)過(guò)程)、OUT(從存儲(chǔ)過(guò)程傳出,如這里所用)和INOUT(對(duì)存儲(chǔ)過(guò)程傳入和傳出)類型的參數(shù).存儲(chǔ)過(guò)程的代碼位于BEGIN和END語(yǔ)句內(nèi),如前所見(jiàn),它們是一系列SELECT語(yǔ)句,用來(lái)檢索值,然后保存到相應(yīng)的變量(通過(guò)指定INTO關(guān)鍵字).

調(diào)用存儲(chǔ)過(guò)程
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
在調(diào)用時(shí),這條語(yǔ)句并不顯示任何數(shù)據(jù).它返回以后可以顯示(或在其他處理中使用)的變量.

顯示檢索出的變量?jī)r(jià)格
SELECT @pricelow, @pricehigh, @priceaverage;

使用OUT和IN參數(shù)創(chuàng)建存儲(chǔ)過(guò)程
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAl(8,2)
)
BEGIN
SELECT Sum(item_price*quantity) FROM orderitems
WHERE order_num=onumber INTO ototal;
END;

調(diào)用存儲(chǔ)過(guò)程:
CALL ordertotal(2005, @total);
SELECT @total;

23.3.5 建立智能存儲(chǔ)過(guò)程

迄今為止使用的所有存儲(chǔ)過(guò)程基本上都是封裝MySQL簡(jiǎn)單的SELECT 語(yǔ)句.雖然它們?nèi)际怯行У拇鎯?chǔ)過(guò)程例子,但它們所能完成的工作你 直接用這些被封裝的語(yǔ)句就能完成.只有在存儲(chǔ)過(guò)程內(nèi)包含業(yè)務(wù)規(guī)則和智能處理時(shí),它們的威力才真正顯現(xiàn)出來(lái).

COMMENT關(guān)鍵字
本例子中的存儲(chǔ)過(guò)程在CREATEPROCEDURE語(yǔ)句中包含了一個(gè)COMMENT值.它不是必需的,但如果給出,將在SHOW PROCEDURE STATUS的結(jié)果中顯示.

IF語(yǔ)句
這個(gè)例子給出了MySQL的IF語(yǔ)句的基本用法.IF語(yǔ) 句還支持ELSEIF和ELSE子句

例子:
創(chuàng)建復(fù)雜的存儲(chǔ)過(guò)程
-- Name:ordertotal

-- Parameters:
-- onumber=order number
-- taxable=0 if not taxable, 1 if taxable
-- ototal=order total variable

CREATE PRODURE ordertotal(
IN onumber INT,
IN taxable BOOLEAM;
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'

BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8,2);
-- Declare tax percentage
DECLARE taxrate INT DEFAULT 6;

-- Get the order total
SELECT Sum(item_price*quantity)
FROM orderitems
WHERE order_num=onumber
INTO total;

-- Is this taxable?
IF taxable THEN
-- Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;

-- And finally, save to out variable
SELECT total INTO ototal;

END;

23.3.6 檢查存儲(chǔ)過(guò)程

為顯示用來(lái)創(chuàng)建一個(gè)存儲(chǔ)過(guò)程的CREATE語(yǔ)句,使用SHOW CREATE PROCEDURE語(yǔ)句

為了獲得包括何時(shí)、由誰(shuí)創(chuàng)建等詳細(xì)信息的存儲(chǔ)過(guò)程列表,使用SHOW PROCEDURE STATUS.

例子:
SHOW CREATE PRODURE ordertotal;

限制過(guò)程狀態(tài)結(jié)果
SHOWPROCEDURESTATUS列出所有存儲(chǔ)過(guò)程.為限制其輸出,可使用LIKE指定一個(gè)過(guò)濾模式
如:SHOW PRODURE STATUS LIKE 'ordertotal';

23.4 小結(jié)

第24章 使用游標(biāo)

24.1 游標(biāo)

游標(biāo)(cursor)是一個(gè)存儲(chǔ)在MySQL服務(wù)器上的數(shù)據(jù)庫(kù)查詢,它不是一條SELECT語(yǔ)句,而是被該語(yǔ)句檢索出來(lái)的結(jié)果集.在存儲(chǔ)了游標(biāo)之后,應(yīng)用程序可以根據(jù)需要滾動(dòng)或?yàn)g覽其中的數(shù)據(jù).

游標(biāo)主要用于交互式應(yīng)用,其中用戶需要滾動(dòng)屏幕上的數(shù)據(jù),并對(duì)數(shù)據(jù)進(jìn)行瀏覽或做出更改.

只能用于存儲(chǔ)過(guò)程 不像多數(shù)DBMS,MySQL游標(biāo)只能用于存儲(chǔ)過(guò)程(和函數(shù)).

24.2 使用游標(biāo)

使用游標(biāo)涉及幾個(gè)明確的步驟.

• 在能夠使用游標(biāo)前,必須聲明(定義)它.這個(gè)過(guò)程實(shí)際上沒(méi)有 檢索數(shù)據(jù),它只是定義要使用的SELECT語(yǔ)句.
• 一旦聲明后,必須打開(kāi)游標(biāo)以供使用.這個(gè)過(guò)程用前面定義的 SELECT語(yǔ)句把數(shù)據(jù)實(shí)際檢索出來(lái).
• 對(duì)于填有數(shù)據(jù)的游標(biāo),根據(jù)需要取出(檢索)各行.
• 在結(jié)束游標(biāo)使用時(shí),必須關(guān)閉游標(biāo).
  在聲明游標(biāo)后,可根據(jù)需要頻繁地打開(kāi)和關(guān)閉游標(biāo).在游標(biāo)打開(kāi)后, 可根據(jù)需要頻繁地執(zhí)行取操作.

24.2.1 創(chuàng)建游標(biāo)

游標(biāo)用DECLARE語(yǔ)句創(chuàng)建.DECLARE命名游標(biāo),并定義 相應(yīng)的SELECT語(yǔ)句,根據(jù)需要帶WHERE和其他子句.

在定義游標(biāo)之后,可以打開(kāi)它.

例子:
CREATE PRODURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;

24.2.2 打開(kāi)和關(guān)閉游標(biāo)

游標(biāo)用OPEN CURSOR語(yǔ)句來(lái)打開(kāi)

在處理OPEN語(yǔ)句時(shí)執(zhí)行查詢,存儲(chǔ)檢索出的數(shù)據(jù)以供瀏覽和滾動(dòng).

游標(biāo)處理完成后,應(yīng)當(dāng)使用如下語(yǔ)句關(guān)閉游標(biāo): CLOSE CURSOR

CLOSE釋放游標(biāo)使用的所有內(nèi)部?jī)?nèi)存和資源,因此在每個(gè)游標(biāo)不再需要時(shí)都應(yīng)該關(guān)閉.

在一個(gè)游標(biāo)關(guān)閉后,如果沒(méi)有重新打開(kāi),則不能使用它.但是,使用聲明過(guò)的游標(biāo)不需要再次聲明,用OPEN語(yǔ)句打開(kāi)它就可以了

隱含關(guān)閉 如果你不明確關(guān)閉游標(biāo),MySQL將會(huì)在到達(dá)END語(yǔ)句時(shí)自動(dòng)關(guān)閉它.

例子:
CREATE PRODURE processorders()
BEGIN
-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

-- Open the cursor
OPEN ordernumbers;

-- Close the cursor
CLOSE ordernumbers;

END;
這個(gè)存儲(chǔ)過(guò)程聲明、打開(kāi)和關(guān)閉一個(gè)游標(biāo).但對(duì)檢索出的數(shù)據(jù)什么也沒(méi)做.

24.2.3 使用游標(biāo)數(shù)據(jù)

在一個(gè)游標(biāo)被打開(kāi)后,可以使用FETCH語(yǔ)句分別訪問(wèn)它的每一行.FETCH指定檢索什么數(shù)據(jù)(所需的列),檢索出來(lái)的數(shù)據(jù)存儲(chǔ)在什么地方.它還向前移動(dòng)游標(biāo)中的內(nèi)部行指針,使下一條FETCH語(yǔ)句檢索下一行(不重復(fù)讀取同一行).

例子:
從游標(biāo)中檢索單個(gè)行
CREATE PRODURE processorders()
BEGIN
-- Declare local variables
DECLARE o INT;

-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

-- Open the cursor
OPEN ordernumbers INTO o;

-- Close the cursor
CLOSE ordernumbers;

END;

循環(huán)檢索數(shù)據(jù),從第一行到最后一行
CREATE PRODURE processorders()
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;

-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

-- Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

-- Open the cursor
OPEN ordernumbers;

-- Loop through all rows
REPEAT
    -- Get order number
    FETCH ordernumbers INTO o;
-- End of loop
UNTIL done END REPEAT;

-- Close the cursor
CLOSE ordernumbers;

END;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
這條語(yǔ)句定義了一個(gè)CONTINUE HANDLER,它是在條件出現(xiàn)時(shí)被執(zhí)行的代碼.這里,它指出當(dāng)SQLSTATE '02000'出現(xiàn)時(shí),SET done=1.SQLSTATE '02000'是一個(gè)未找到條件,當(dāng)REPEAT由于沒(méi)有更多的行供循環(huán)而不能繼續(xù)時(shí),出現(xiàn)這個(gè)條件.

重復(fù)或循環(huán)?
除這里使用的REPEAT語(yǔ)句外,MySQL還支持循環(huán)語(yǔ)句,它可用來(lái)重復(fù)執(zhí)行代碼,直到使用LEAVE語(yǔ)句手動(dòng)退出為止.通常REPEAT語(yǔ)句的語(yǔ)法使它更適合于對(duì)游標(biāo)進(jìn)行循環(huán).

CREATE PRODURE processorders()
BEGIN
-- Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8,2);

-- Declare the cursor
DECLARE ordernumbers CURSOR
FOR 
SELECT CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

-- Create a table to store the results
CREATE TABLE IF NOT EXISTS ordertotals
     (order_num INT, total DECIMAL(8,2));

-- Open the cursor
OPEN ordernumbers;

-- Loop through all rows
REPEAT

    -- Get order number
    FETCH ordernumbers INTO o;
    -- Get the total for this order
CALL ordertotal(o, 1, t);
-- Insert order and total into ordertotals
INSERT INTO ordertotals(order_num, total) VALUES(o,t);

-- End of loop
UNTIL done END REPEAR;

-- Close the cursor
CLOSE ordernumbers;

END;

使用
SELECT * FROM ordertotals;

24.3 小結(jié)

第25章 使用觸發(fā)器

25.1 觸發(fā)器

觸發(fā)器是MySQL響應(yīng)以下任意語(yǔ)句而自動(dòng)執(zhí)行的一條MySQL語(yǔ)句(或位于BEGIN和END語(yǔ)句之間的一組語(yǔ)句):

• DELETE;
• INSERT;
• UPDATE.
  其他MySQL語(yǔ)句不支持觸發(fā)器.

25.2 創(chuàng)建觸發(fā)器

在創(chuàng)建觸發(fā)器時(shí),需要給出4條信息:

• 唯一的觸發(fā)器名;
• 觸發(fā)器關(guān)聯(lián)的表;
• 觸發(fā)器應(yīng)該響應(yīng)的活動(dòng)(DELETE、INSERT或UPDATE);
• 觸發(fā)器何時(shí)執(zhí)行(處理之前或之后).

保持每個(gè)數(shù)據(jù)庫(kù)的觸發(fā)器名唯一
在MySQL5中,觸發(fā)器名必須在每個(gè)表中唯一,但不是在每個(gè)數(shù)據(jù)庫(kù)中唯一.這表示同一數(shù)據(jù)庫(kù)中的兩個(gè)表可具有相同名字的觸發(fā)器.這在其他每個(gè)數(shù)據(jù)庫(kù)觸發(fā)器名必須唯一的DBMS中是不允許的,而且以后的MySQL版本很可能會(huì)使命名規(guī)則更為嚴(yán)格.因此,現(xiàn)在最好是在數(shù)據(jù)庫(kù)范圍內(nèi)使用唯一的觸發(fā)器名.

僅支持表 只有表才支持觸發(fā)器,視圖不支持(臨時(shí)表也不支持).

觸發(fā)器按每個(gè)表每個(gè)事件每次地定義,每個(gè)表每個(gè)事件每次只允許一個(gè)觸發(fā)器.因此,每個(gè)表最多支持6個(gè)觸發(fā)器(每條INSERT、UPDATE和DELETE的之前和之后).單一觸發(fā)器不能與多個(gè)事件或多個(gè)表關(guān)聯(lián),所以,如果你需要一個(gè)對(duì)INSERT和UPDATE操作執(zhí)行的觸發(fā)器,則應(yīng)該定義兩個(gè)觸發(fā)器.

觸發(fā)器失敗
如果BEFORE觸發(fā)器失敗,則MySQL將不執(zhí)行請(qǐng)求的操作.此外,如果BEFORE觸發(fā)器或語(yǔ)句本身失敗,MySQL將不執(zhí)行AFTER觸發(fā)器(如果有的話).

例子:
CREATE TRIGGER newproduct AFTER ON products FOR EACH ROW SELECT 'Product added';

25.3 刪除觸發(fā)器

觸發(fā)器不能更新或覆蓋.為了修改一個(gè)觸發(fā)器,必須先刪除它, 然后再重新創(chuàng)建.

現(xiàn)在,刪除觸發(fā)器的語(yǔ)法應(yīng)該很明顯了.為了刪除一個(gè)觸發(fā)器,可使用DROP TRIGGER語(yǔ)句

例子:DROP TRIGGER newproduct;

25.4 使用觸發(fā)器

INSERT觸發(fā)器在INSERT語(yǔ)句執(zhí)行之前或之后執(zhí)行.需要知道以下幾點(diǎn):

• 在INSERT觸發(fā)器代碼內(nèi),可引用一個(gè)名為NEW的虛擬表,訪問(wèn)被插入的行;
• 在BEFORE INSERT觸發(fā)器中,NEW中的值也可以被更新(允許更改被插入的值);
• 對(duì)于AUTO_INCREMENT列,NEW在INSERT執(zhí)行之前包含0,在INSERT執(zhí)行之后包含新的自動(dòng)生成值.

BEFORE或AFTER?
通常,將BEFORE用于數(shù)據(jù)驗(yàn)證和凈化(目的是保證插入表中的數(shù)據(jù)確實(shí)是需要的數(shù)據(jù)).本提示也適用于UPDATE觸發(fā)器.

例子:
創(chuàng)建一個(gè)名為neworder的觸發(fā)器,它按照AFTER INSERT ON orders執(zhí)行.在插入一個(gè)新訂單到orders表時(shí),MySQL生 成一個(gè)新訂單號(hào)并保存到order_num中.觸發(fā)器從NEW. order_num取得 這個(gè)值并返回它.此觸發(fā)器必須按照AFTER INSERT執(zhí)行,因?yàn)樵贐EFORE INSERT語(yǔ)句執(zhí)行之前,新order_num還沒(méi)有生成.對(duì)于orders的每次插 入使用這個(gè)觸發(fā)器將總是返回新的訂單號(hào).
CREATE TROGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT new.order_num;

25.4.2 DELETE觸發(fā)器

DELETE觸發(fā)器在DELETE語(yǔ)句執(zhí)行之前或之后執(zhí)行.需要知道以下兩點(diǎn):

• 在DELETE觸發(fā)器代碼內(nèi),你可以引用一個(gè)名為OLD的虛擬表,訪問(wèn)被刪除的行;
• OLD中的值全都是只讀的,不能更新.

多語(yǔ)句觸發(fā)器
正如所見(jiàn),觸發(fā)器deleteorder使用BEGIN和END語(yǔ)句標(biāo)記觸發(fā)器體.這在此例子中并不是必需的,不過(guò)也沒(méi)有害處.使用BEGIN END塊的好處是觸發(fā)器能容納多條SQL語(yǔ)句(在BEGIN END塊中一條挨著一條).

例子:
使用OLD保存將要被刪除的行到一個(gè)存檔表中:
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id)
END;

在任意訂單被刪除前將執(zhí)行此觸發(fā)器.它使用一條INSERT語(yǔ)句將OLD中的值(要被刪除的訂單)保存到一個(gè)名為archive_ orders的存檔表中(為實(shí)際使用這個(gè)例子,你需要用與orders相同的列 創(chuàng)建一個(gè)名為archive_orders的表).

25.4.3 UPDATE觸發(fā)器

UPDATE觸發(fā)器在UPDATE語(yǔ)句執(zhí)行之前或之后執(zhí)行.需要知道以下幾點(diǎn):

• 在UPDATE觸發(fā)器代碼中,你可以引用一個(gè)名為OLD的虛擬表訪問(wèn)以前(UPDATE語(yǔ)句前)的值,引用一個(gè)名為NEW的虛擬表訪問(wèn)新更新的值;
• 在BEFORE UPDATE觸發(fā)器中,NEW中的值可能也被更新(允許更改將要用于UPDATE語(yǔ)句中的值);
• OLD中的值全都是只讀的,不能更新.

例子:
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);

25.4.4 關(guān)于觸發(fā)器的進(jìn)一步介紹

在結(jié)束本章之前,我們?cè)俳榻B一些使用觸發(fā)器時(shí)需要記住的重點(diǎn).

• 與其他DBMS相比,MySQL 5中支持的觸發(fā)器相當(dāng)初級(jí).未來(lái)的MySQL版本中有一些改進(jìn)和增強(qiáng)觸發(fā)器支持的計(jì)劃.
• 創(chuàng)建觸發(fā)器可能需要特殊的安全訪問(wèn)權(quán)限,但是,觸發(fā)器的執(zhí)行是自動(dòng)的.如果INSERT、UPDATE或DELETE語(yǔ)句能夠執(zhí)行,則相關(guān)的觸發(fā)器也能執(zhí)行.
• 應(yīng)該用觸發(fā)器來(lái)保證數(shù)據(jù)的一致性(大小寫、格式等).在觸發(fā)器中執(zhí)行這種類型的處理的優(yōu)點(diǎn)是它總是進(jìn)行這種處理,而且是透明地進(jìn)行,與客戶機(jī)應(yīng)用無(wú)關(guān).
• 觸發(fā)器的一種非常有意義的使用是創(chuàng)建審計(jì)跟蹤.使用觸發(fā)器,把更改(如果需要,甚至還有之前和之后的狀態(tài))記錄到另一個(gè)表非常容易.
• 遺憾的是,MySQL觸發(fā)器中不支持CALL語(yǔ)句.這表示不能從觸發(fā)器內(nèi)調(diào)用存儲(chǔ)過(guò)程.所需的存儲(chǔ)過(guò)程代碼需要復(fù)制到觸發(fā)器內(nèi).

25.5 小結(jié)

第26章 管理事務(wù)處理

26.1 事務(wù)處理
事務(wù)處理(transaction processing)可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性,它保證成批的MySQL操作要么完全執(zhí)行,要么完全不執(zhí)行.

事務(wù)處理是一種機(jī)制,用來(lái)管理必須成批執(zhí)行的MySQL操作,以保證數(shù)據(jù)庫(kù)不包含不完整的操作結(jié)果.利用事務(wù)處理,可以保證一組操作不會(huì)中途停止,它們或者作為整體執(zhí)行,或者完全不執(zhí)行(除非明確指示).如果沒(méi)有錯(cuò)誤發(fā)生,整組語(yǔ)句提交給(寫到)數(shù)據(jù)庫(kù)表.如果發(fā)生錯(cuò)誤,則進(jìn)行回退(撤銷)以恢復(fù)數(shù)據(jù)庫(kù)到某個(gè)已知且安全的狀態(tài).

在使用事務(wù)和事務(wù)處理時(shí),有幾個(gè)關(guān)鍵詞匯反復(fù)出現(xiàn).下面是關(guān)于 事務(wù)處理需要知道的幾個(gè)術(shù)語(yǔ):

• 事務(wù)(transaction)指一組SQL語(yǔ)句;
• 回退(rollback)指撤銷指定SQL語(yǔ)句的過(guò)程;
• 提交(commit)指將未存儲(chǔ)的SQL語(yǔ)句結(jié)果寫入數(shù)據(jù)庫(kù)表;
• 保留點(diǎn)(savepoint)指事務(wù)處理中設(shè)置的臨時(shí)占位符(place-holder),你可以對(duì)它發(fā)布回退(與回退整個(gè)事務(wù)處理不同).

26.2 控制事務(wù)處理

管理事務(wù)處理的關(guān)鍵在于將SQL語(yǔ)句組分解為邏輯塊,并明確規(guī)定數(shù)據(jù)何時(shí)應(yīng)該回退,何時(shí)不應(yīng)該回退.

MySQL使用下面的語(yǔ)句來(lái)標(biāo)識(shí)事務(wù)的開(kāi)始:START TRANSACTION

26.2.1 使用ROLLBACK

ROLLBACK命令用來(lái)回退(撤銷)MySQL語(yǔ)句
ROLLBACK只能在一個(gè)事務(wù)處理內(nèi)使用(在執(zhí)行一條START TRANSACTION命令之后).

哪些語(yǔ)句可以回退?
事務(wù)處理用來(lái)管理INSERT、UPDATE和 DELETE語(yǔ)句.你不能回退SELECT語(yǔ)句.
不能回退CREATE或DROP操作.事務(wù)處理塊中可以使用這兩條語(yǔ)句,但如果你執(zhí)行回退,它們不會(huì)被撤銷.

例子:
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

首先執(zhí)行一條SELECT以顯示該表不為空.
然后開(kāi)始一 個(gè)事務(wù)處理,用一條DELETE語(yǔ)句刪除ordertotals中的所有行.
另一條SELECT語(yǔ)句驗(yàn)證ordertotals確實(shí)為空.
這時(shí)用一條ROLLBACK語(yǔ)句回退START TRANSACTION之后的所有語(yǔ)句,
最后一條SELECT語(yǔ)句顯示該表不為空.

26.2.2 使用COMMIT

一般的MySQL語(yǔ)句都是直接針對(duì)數(shù)據(jù)庫(kù)表執(zhí)行和編寫的.這就是所謂的隱含提交(implicit commit),即提交(寫或保存)操作是自動(dòng)進(jìn)行的.

但是,在事務(wù)處理塊中,提交不會(huì)隱含地進(jìn)行.為進(jìn)行明確的提交,使用COMMIT語(yǔ)句.

隱含事務(wù)關(guān)閉
當(dāng)COMMIT或ROLLBACK語(yǔ)句執(zhí)行后,事務(wù)會(huì)自動(dòng)關(guān)閉(將來(lái)的更改會(huì)隱含提交).

例子:
START TRANCTION
DELETE FROM orderitems WHERE order_num=20010;
DELETE FROM orders WHERE order_num=20010;
COMMIT;

26.2.3 使用保留點(diǎn)

簡(jiǎn)單的ROLLBACK和COMMIT語(yǔ)句就可以寫入或撤銷整個(gè)事務(wù)處理.但是,只是對(duì)簡(jiǎn)單的事務(wù)處理才能這樣做,更復(fù)雜的事務(wù)處理可能需要部分提交或回退.

為了支持回退部分事務(wù)處理,必須能在事務(wù)處理塊中合適的位置放置占位符.這樣,如果需要回退,可以回退到某個(gè)占位符.

這些占位符稱為保留點(diǎn).為了創(chuàng)建占位符,可如下使用SAVEPOINT 語(yǔ)句: SAVEPOINT delete1;
每個(gè)保留點(diǎn)都取標(biāo)識(shí)它的唯一名字,以便在回退時(shí),MySQL知道要 回退到何處.
為了回退到本例給出的保留點(diǎn),可如下進(jìn)行:ROLLBACK TO delete1;

保留點(diǎn)越多越好
可以在MySQL代碼中設(shè)置任意多的保留點(diǎn),越多越好.為什么呢?因?yàn)楸Ａ酎c(diǎn)越多,你就越能按自己的意愿靈活地進(jìn)行回退.

釋放保留點(diǎn)
保留點(diǎn)在事務(wù)處理完成(執(zhí)行一條ROLLBACK或 COMMIT)后自動(dòng)釋放.自MySQL 5以來(lái),也可以用RELEASE SAVEPOINT明確地釋放保留點(diǎn).

26.2.4 更改默認(rèn)的提交行為

正如所述,默認(rèn)的MySQL行為是自動(dòng)提交所有更改.換句話說(shuō),任何時(shí)候你執(zhí)行一條MySQL語(yǔ)句,該語(yǔ)句實(shí)際上都是針對(duì)表執(zhí)行的,而且所做的更改立即生效.為指示MySQL不自動(dòng)提交更改,需要使用以下語(yǔ)句:SET autocommit=0;

autocommit標(biāo)志決定是否自動(dòng)提交更改,不管有沒(méi)有COMMIT語(yǔ)句.

設(shè)置autocommit為0(假)指示MySQL不自動(dòng)提交更改(直到autocommit被設(shè)置為真為止).

標(biāo)志為連接專用 autocommit標(biāo)志是針對(duì)每個(gè)連接而不是服務(wù)器的.

26.3 小結(jié)

第27章 全球化和本地化

27.1 字符集和校對(duì)順序

數(shù)據(jù)庫(kù)表被用來(lái)存儲(chǔ)和檢索數(shù)據(jù).不同的語(yǔ)言和字符集需要以不同的方式存儲(chǔ)和檢索.
因此,MySQL需要適應(yīng)不同的字符集(不同的字母 和字符),適應(yīng)不同的排序和檢索數(shù)據(jù)的方法.
在討論多種語(yǔ)言和字符集時(shí),將會(huì)遇到以下重要術(shù)語(yǔ):

• 字符集為字母和符號(hào)的集合;
• 編碼為某個(gè)字符集成員的內(nèi)部表示;
• 校對(duì)為規(guī)定字符如何比較的指令.

在MySQL的正常數(shù)據(jù)庫(kù)活動(dòng)(SELECT、INSERT等)中,不需要操心太多的東西.使用何種字符集和校對(duì)的決定在服務(wù)器、數(shù)據(jù)庫(kù)和表級(jí)進(jìn)行.

27.2 使用字符集和校對(duì)順序

MySQL支持眾多的字符集.
為查看所支持的字符集完整列表,使用以下語(yǔ)句:SHOW CHARACTER SET;
這條語(yǔ)句顯示所有可用的字符集以及每個(gè)字符集的描述和默認(rèn)校對(duì).

為了查看所支持校對(duì)的完整列表,使用以下語(yǔ)句:SHOW COLLATION;
此語(yǔ)句顯示所有可用的校對(duì),以及它們適用的字符集.可以看到有的字符集具有不止一種校對(duì).

通常系統(tǒng)管理在安裝時(shí)定義一個(gè)默認(rèn)的字符集和校對(duì).此外,也可以在創(chuàng)建數(shù)據(jù)庫(kù)時(shí),指定默認(rèn)的字符集和校對(duì).
為了確定所用的字符集和校對(duì),可以使用以下語(yǔ)句:
SHOW VARIABLES LIKE 'character%';
SHOW VARIAVLES LIKE 'collation%';

實(shí)際上,字符集很少是服務(wù)器范圍(甚至數(shù)據(jù)庫(kù)范圍)的設(shè)置.不同的表,甚至不同的列都可能需要不同的字符集,而且兩者都 可以在創(chuàng)建表時(shí)指定.

一般,MySQL如下確定使用什么樣的字符集和校對(duì).

• 如果指定CHARACTER SET和COLLATE兩者,則使用這些值.
• 如果只指定CHARACTER SET,則使用此字符集及其默認(rèn)的校對(duì)(如SHOW CHARACTER SET的結(jié)果中所示).
• 如果既不指定CHARACTER SET,也不指定COLLATE,則使用數(shù)據(jù)庫(kù)默認(rèn).

除了能指定字符集和校對(duì)的表范圍外,MySQL還允許對(duì)每個(gè)列設(shè)置.

臨時(shí)區(qū)分大小寫
上面的SELECT語(yǔ)句演示了在通常不區(qū)分大小寫的表上進(jìn)行區(qū)分大小寫搜索的一種技術(shù).當(dāng)然,反過(guò)來(lái)也是可以的.

SELECT的其他COLLATE子句
除了這里看到的在ORDERBY子句中使用以外,COLLATE還可以用于GROUP BY、HAVING、聚集函數(shù)、別名等.

最后,值得注意的是,如果絕對(duì)需要,串可以在字符集之間進(jìn)行轉(zhuǎn) 換.為此,使用Cast()或Convert()函數(shù).

例子:
為了給表指定字符集和校對(duì)
CREATE TABLE mytable(
column1 INT,
column2 VARCHAR(10)
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
此語(yǔ)句創(chuàng)建一個(gè)包含兩列的表,并且指定一個(gè)字符集和一個(gè)校 對(duì)順序.

允許對(duì)每個(gè)列設(shè)置
CREATE TABLE mytable(
column1 INT,
column2 VARCHAR(10),
column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;
對(duì)整個(gè)表以及一個(gè)特定的列指定了CHARACTER SET和COLLATE.

查詢字符集
SELECT * FROM customers ORDER BY lastname, firstname COLLATE latin1_general_ci;

27.3 小結(jié)

第28章 安全管理

28.1 訪問(wèn)控制

MySQL服務(wù)器的安全基礎(chǔ)是:用戶應(yīng)該對(duì)他們需要的數(shù)據(jù)具有適當(dāng)?shù)脑L問(wèn)權(quán),既不能多也不能少.
換句話說(shuō),用戶不能對(duì)過(guò)多的數(shù)據(jù)具有過(guò)多的訪問(wèn)權(quán).

防止無(wú)意的錯(cuò)誤
重要的是注意到,訪問(wèn)控制的目的不僅僅是防止用戶的惡意企圖.數(shù)據(jù)夢(mèng)魘更為常見(jiàn)的是無(wú)意識(shí)錯(cuò)誤的結(jié)果,如錯(cuò)打MySQL語(yǔ)句,在不合適的數(shù)據(jù)庫(kù)中操作或其他一些用戶錯(cuò)誤.通過(guò)保證用戶不能執(zhí)行他們不應(yīng)該執(zhí)行的語(yǔ)句,訪問(wèn)控制有助于避免這些情況的發(fā)生.

不要使用root
應(yīng)該嚴(yán)肅對(duì)待root登錄的使用.僅在絕對(duì)需要時(shí)使用它(或許在你不能登錄其他管理賬號(hào)時(shí)使用).不應(yīng)該在日常的MySQL操作中使用root.

28.2 管理用戶

MySQL用戶賬號(hào)和信息存儲(chǔ)在名為mysql的MySQL數(shù)據(jù)庫(kù)中.一般不需要直接訪問(wèn)mysql數(shù)據(jù)庫(kù)和表,但有時(shí)需要直接訪問(wèn).需要直接訪問(wèn)它的時(shí)機(jī)之一是在需要獲得所有用戶賬號(hào)列表時(shí).

用多個(gè)客戶機(jī)進(jìn)行試驗(yàn) 試驗(yàn)對(duì)用戶賬號(hào)和權(quán)限進(jìn)行更改的最好辦法是打開(kāi)多個(gè)數(shù)據(jù)庫(kù)客戶機(jī)(如mysql命令行實(shí)用程序的多個(gè)副本),一個(gè)作為管理登錄,其他作為被測(cè)試的用戶登錄.

例如：select user from user;

28.2.1 創(chuàng)建用戶賬號(hào)

為了創(chuàng)建一個(gè)新用戶賬號(hào),使用CREATE USER語(yǔ)句

指定散列口令
IDENTIFIEDBY指定的口令為純文本,MySQL將在保存到user表之前對(duì)其進(jìn)行加密.為了作為散列值指定口令,使用IDENTIFIED BY PASSWORD.

使用GRANT或INSERT
GRANT語(yǔ)句(稍后介紹)也可以創(chuàng)建用戶賬號(hào),但一般來(lái)說(shuō)CREATE USER是最清楚和最簡(jiǎn)單的句子.此外,也可以通過(guò)直接插入行到user表來(lái)增加用戶,不過(guò)為安全起見(jiàn),一般不建議這樣做.MySQL用來(lái)存儲(chǔ)用戶賬號(hào)信息的表(以及表模式等)極為重要,對(duì)它們的任何毀壞都可能嚴(yán)重地傷害到MySQL服務(wù)器.因此,相對(duì)于直接處理來(lái)說(shuō),最好是用標(biāo)記和函數(shù)來(lái)處理這些表.

為重新命名一個(gè)用戶賬號(hào),使用RENAME USER語(yǔ)句

MySQL5之前
僅MySQL5或之后的版本支持RENAME USER.為了在以前的MySQL中重命名一個(gè)用戶,可使用UPDATE直接更新user表.

例子:
CREATE USER ben IDENTIFY BY 'p@wOrd'給出了 一個(gè)口令.如果你再次列出用戶賬號(hào),將會(huì)在輸出中看到新賬號(hào).

RENAME USER ben TO bforta;

28.2.2 刪除用戶賬號(hào)

為了刪除一個(gè)用戶賬號(hào)(以及相關(guān)的權(quán)限),使用DROP USER語(yǔ)句

MySQL5之前
自MySQL5以來(lái),DROPUSER刪除用戶賬號(hào)和所有相關(guān)的賬號(hào)權(quán)限.在MySQL 5以前,DROP USER只能用來(lái)刪除用戶賬號(hào),不能刪除相關(guān)的權(quán)限.因此,如果使用舊版本的MySQL,需要先用REVOKE刪除與賬號(hào)相關(guān)的權(quán)限,然后再用DROP USER刪除賬號(hào).

例子:
DROP USER bforta;

28.2.3 設(shè)置訪問(wèn)權(quán)限

在創(chuàng)建用戶賬號(hào)后,必須接著分配訪問(wèn)權(quán)限.新創(chuàng)建的用戶賬號(hào)沒(méi)有訪問(wèn)權(quán)限.它們能登錄MySQL,但不能看到數(shù)據(jù),不能執(zhí)行任何數(shù)據(jù)庫(kù)操作.
為看到賦予用戶賬號(hào)的權(quán)限,使用SHOW GRANTS FOR username;

用戶定義為user@host
MySQL的權(quán)限用用戶名和主機(jī)名結(jié)合定義.如果不指定主機(jī)名,則使用默認(rèn)的主機(jī)名%(授予用戶訪問(wèn)權(quán)限而不管主機(jī)名).

為設(shè)置權(quán)限,使用GRANT語(yǔ)句.
GRANT要求你至少給出以下信息:

• 要授予的權(quán)限;
• 被授予訪問(wèn)權(quán)限的數(shù)據(jù)庫(kù)或表;
• 用戶名.

每個(gè)GRANT添加(或更新)用戶的一個(gè)權(quán)限.MySQL讀取所有授權(quán),并根據(jù)它們確定權(quán)限.

GRANT的反操作為REVOKE,用它來(lái)撤銷特定的權(quán)限.
被撤銷的訪問(wèn)權(quán)限必須存在,否則會(huì)出錯(cuò).

GRANT和REVOKE可在幾個(gè)層次上控制訪問(wèn)權(quán)限:

• 整個(gè)服務(wù)器,使用GRANT ALL和REVOKE ALL;
• 整個(gè)數(shù)據(jù)庫(kù),使用ON database.*;
• 特定的表,使用ON database.table;
• 特定的列;
• 特定的存儲(chǔ)過(guò)程.

未來(lái)的授權(quán)
在使用GRANT和REVOKE時(shí),用戶賬號(hào)必須存在,但對(duì)所涉及的對(duì)象沒(méi)有這個(gè)要求.這允許管理員在創(chuàng)建數(shù)據(jù)庫(kù)和表之前設(shè)計(jì)和實(shí)現(xiàn)安全措施.
這樣做的副作用是,當(dāng)某個(gè)數(shù)據(jù)庫(kù)或表被刪除時(shí)(用DROP語(yǔ)句),相關(guān)的訪問(wèn)權(quán)限仍然存在.而且,如果將來(lái)重新創(chuàng)建該數(shù)據(jù)庫(kù)或表,這些權(quán)限仍然起作用.

簡(jiǎn)化多次授權(quán)
可通過(guò)列出各權(quán)限并用逗號(hào)分隔,將多條 GRANT語(yǔ)句串在一起,如下所示:
GRANT SELECT, INSERT ON crashcourse.* TO bforta;

權(quán)限
---|---
ALL|除GRANT OPTION外的所有權(quán)限
ALTER|使用ALTER TABLE
ALTER ROUTINE|使用ALTER PROCEDURE和DROP PROCEDURE
CREATE|使用CREATE TABLE
CREATE ROUTINE|使用CREATE PROCEDURE
CREATE TEMPORARY TABLE|使用CREATE TEMPORARY TABLE
CREATE USER|使用CREATE USER、DROP USER、RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW|使用CREATE VIEW
DELETE|使用DELETE
DROP|使用DROP TABLE
EXECUTE|使用CALL和存儲(chǔ)過(guò)程
FILE|使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION|使用GRANT和REVOKE
INDEX|使用CREATE INDEX和DROP INDEX
INSERT使用INSERT
LOCK TABLES|使用LOCK TABLES
PROCESS|使用SHOW FULL PROCESSLIST
RELOAD|使用FLUSH
REPLICATION CLIENT|服務(wù)器位置的訪問(wèn)
REPLICATION SLAVE|由復(fù)制從屬使用
SELECT|使用SELECT
SHOW DATABASES|使用SHOW DATABASES
SHOW VIEW|使用SHOW CREATE VIEW
SHUTDOWN|使用mysqladmin shutdown(用來(lái)關(guān)閉MySQL)
SUPER|使用CHANGE MASTER、KILL、LOGS、PURGE、MASTER 和SET GLOBAL.還允許mysqladmin調(diào)試登錄
UPDATE|使用UPDATE
USAGE|無(wú)訪問(wèn)權(quán)限

例子:
SHOW GRANTS FOR bforta;

GRANT SELECT ON crashcourse.* TO bforta;
允許用戶在crashcourse.*(crashcourse數(shù)據(jù)庫(kù)的所有表)上使用SELECT.

查看一下權(quán)限
SHOW GRANTS FOR bforta;

REVOKE SELECT ON crashcourse.* TO bforta;
REVOKE語(yǔ)句取消剛賦予用戶bforta的SELECT訪問(wèn)權(quán)限.

28.2.4 更改口令

為了更改用戶口令,可使用SET PASSWORD語(yǔ)句.
新口令必須如下加密:SET PASSWORD FOR bforta=Password('123456');
SET PASSWORD更新用戶口令.新口令必須傳遞到Password()函數(shù)進(jìn)行加密.
設(shè)置自己的口令:SET PASSWORD=Password('123456')
在不指定用戶名時(shí),SET PASSWORD更新當(dāng)前登錄用戶的口令.

28.3 小結(jié)

第29章 數(shù)據(jù)庫(kù)維護(hù)

29.1 備份數(shù)據(jù)

像所有數(shù)據(jù)一樣,MySQL的數(shù)據(jù)也必須經(jīng)常備份.由于MySQL數(shù)據(jù)庫(kù)是基于磁盤的文件,普通的備份系統(tǒng)和例程就能備份MySQL的數(shù)據(jù).但是,由于這些文件總是處于打開(kāi)和使用狀態(tài),普通的文件副本備份不一定總是有效.

下面列出這個(gè)問(wèn)題的可能解決方案.

• 使用命令行實(shí)用程序mysqldump轉(zhuǎn)儲(chǔ)所有數(shù)據(jù)庫(kù)內(nèi)容到某個(gè)外部文件.在進(jìn)行常規(guī)備份前這個(gè)實(shí)用程序應(yīng)該正常運(yùn)行,以便能正確地備份轉(zhuǎn)儲(chǔ)文件.
• 可用命令行實(shí)用程序mysqlhotcopy從一個(gè)數(shù)據(jù)庫(kù)復(fù)制所有數(shù)據(jù)(并非所有數(shù)據(jù)庫(kù)引擎都支持這個(gè)實(shí)用程序).
• 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE轉(zhuǎn)儲(chǔ)所有數(shù)據(jù)到某個(gè)外部文件.這兩條語(yǔ)句都接受將要?jiǎng)?chuàng)建的系統(tǒng)文件名,此系統(tǒng)文件必須不存在,否則會(huì)出錯(cuò).數(shù)據(jù)可以用RESTORE TABLE來(lái)復(fù)原.

首先刷新未寫數(shù)據(jù)
為了保證所有數(shù)據(jù)被寫到磁盤(包括索引數(shù)據(jù)),可能需要在進(jìn)行備份前使用FLUSH TABLES語(yǔ)句.

29.2 進(jìn)行數(shù)據(jù)庫(kù)維護(hù)

MySQL提供了一系列的語(yǔ)句,可以(應(yīng)該)用來(lái)保證數(shù)據(jù)庫(kù)正確和正常運(yùn)行.

以下是你應(yīng)該知道的一些語(yǔ)句.

• ANALYZE TABLE,用來(lái)檢查表鍵是否正確.
• CHECK TABLE用來(lái)針對(duì)許多問(wèn)題對(duì)表進(jìn)行檢查.在MyISAM表上還對(duì)索引進(jìn)行檢查.CHECK TABLE支持一系列的用于MyISAM表的方式. CHANGED檢查自最后一次檢查以來(lái)改動(dòng)過(guò)的表.EXTENDED執(zhí)行最徹底的檢查,FAST只檢查未正常關(guān)閉的表,MEDIUM檢查所有被刪除的鏈接并進(jìn)行鍵檢驗(yàn),QUICK只進(jìn)行快速掃描.
• 如果MyISAM表訪問(wèn)產(chǎn)生不正確和不一致的結(jié)果,可能需要用REPAIR TABLE來(lái)修復(fù)相應(yīng)的表.這條語(yǔ)句不應(yīng)該經(jīng)常使用,如果需要經(jīng)常使用,可能會(huì)有更大的問(wèn)題要解決.
• 如果從一個(gè)表中刪除大量數(shù)據(jù),應(yīng)該使用OPTIMIZE TABLE來(lái)收回所用的空間,從而優(yōu)化表的性能.

例子:
analyze table orders;
check table orders, oderitems;
repair table orders;
optimize table orders;

29.3 診斷啟動(dòng)問(wèn)題

服務(wù)器啟動(dòng)問(wèn)題通常在對(duì)MySQL配置或服務(wù)器本身進(jìn)行更改時(shí)出 現(xiàn).MySQL在這個(gè)問(wèn)題發(fā)生時(shí)報(bào)告錯(cuò)誤,但由于多數(shù)MySQL服務(wù)器是作為系統(tǒng)進(jìn)程或服務(wù)自動(dòng)啟動(dòng)的,這些消息可能看不到.

在排除系統(tǒng)啟動(dòng)問(wèn)題時(shí),首先應(yīng)該盡量用手動(dòng)啟動(dòng)服務(wù)器.MySQL服務(wù)器自身通過(guò)在命令行上執(zhí)行mysqld啟動(dòng).

下面是幾個(gè)重要的mysqld 命令行選項(xiàng):

• --help顯示幫助——一個(gè)選項(xiàng)列表;
• --safe-mode裝載減去某些最佳配置的服務(wù)器;
• --verbose顯示全文本消息(為獲得更詳細(xì)的幫助消息與--help聯(lián)合使用);
• --version顯示版本信息然后退出.
  幾個(gè)另外的命令行選項(xiàng)(與日志文件的使用有關(guān))在下一節(jié)列出.

29.4 查看日志文件

MySQL維護(hù)管理員依賴的一系列日志文件.主要的日志文件有以下幾種.

• 錯(cuò)誤日志.它包含啟動(dòng)和關(guān)閉問(wèn)題以及任意關(guān)鍵錯(cuò)誤的細(xì)節(jié).此日志通常名為hostname.err,位于data目錄中.此日志名可用--log-error命令行選項(xiàng)更改.
• 查詢?nèi)罩?它記錄所有MySQL活動(dòng),在診斷問(wèn)題時(shí)非常有用.此日志文件可能會(huì)很快地變得非常大,因此不應(yīng)該長(zhǎng)期使用它.此日志通常名為hostname.log,位于data目錄中.此名字可以用--log命令行選項(xiàng)更改.
• 二進(jìn)制日志.它記錄更新過(guò)數(shù)據(jù)(或者可能更新過(guò)數(shù)據(jù))的所有語(yǔ)句.此日志通常名為hostname-bin,位于data目錄內(nèi).此名字可以用--log-bin命令行選項(xiàng)更改.注意,這個(gè)日志文件是MySQL 5中添加的,以前的MySQL版本中使用的是更新日志.
• 緩慢查詢?nèi)罩?顧名思義,此日志記錄執(zhí)行緩慢的任何查詢.這個(gè)日志在確定數(shù)據(jù)庫(kù)何處需要優(yōu)化很有用.此日志通常名為 hostname-slow.log,位于data目錄中.此名字可以用--log-slow-queries命令行選項(xiàng)更改.

在使用日志時(shí),可用FLUSH LOGS語(yǔ)句來(lái)刷新和重新開(kāi)始所有日志文件.

29.5 小結(jié)

第30章 改善性能

30.1 改善性能

數(shù)據(jù)庫(kù)管理員把他們生命中的相當(dāng)一部份時(shí)間花在了調(diào)整、試驗(yàn)以改善DBMS性能之上.在診斷應(yīng)用的滯緩現(xiàn)象和性能問(wèn)題時(shí),性能不良的數(shù)據(jù)庫(kù)(以及數(shù)據(jù)庫(kù)查詢)通常是最常見(jiàn)的禍因.

可以看出,下面的內(nèi)容并不能完全決定MySQL的性能.我們只是想回顧一下前面各章的重點(diǎn),提供進(jìn)行性能優(yōu)化探討和分析的一個(gè)出發(fā)點(diǎn).

• 首先,MySQL(與所有DBMS一樣)具有特定的硬件建議.在學(xué)習(xí)和研究MySQL時(shí),使用任何舊的計(jì)算機(jī)作為服務(wù)器都可以.但對(duì)用于生產(chǎn)的服務(wù)器來(lái)說(shuō),應(yīng)該堅(jiān)持遵循這些硬件建議.
• 一般來(lái)說(shuō),關(guān)鍵的生產(chǎn)DBMS應(yīng)該運(yùn)行在自己的專用服務(wù)器上.
• MySQL是用一系列的默認(rèn)設(shè)置預(yù)先配置的,從這些設(shè)置開(kāi)始通常是很好的.但過(guò)一段時(shí)間后你可能需要調(diào)整內(nèi)存分配、緩沖區(qū)大小等.(為查看當(dāng)前設(shè)置,可使用SHOW VARIABLES;和SHOW STATUS;.)
• MySQL一個(gè)多用戶多線程的DBMS,換言之,它經(jīng)常同時(shí)執(zhí)行多個(gè)任務(wù).如果這些任務(wù)中的某一個(gè)執(zhí)行緩慢,則所有請(qǐng)求都會(huì)執(zhí)行緩慢.如果你遇到顯著的性能不良,可使用SHOW PROCESSLIST顯示所有活動(dòng)進(jìn)程(以及它們的線程ID和執(zhí)行時(shí)間).你還可以用KILL命令終結(jié)某個(gè)特定的進(jìn)程(使用這個(gè)命令需要作為管理員登錄).
• 總是有不止一種方法編寫同一條SELECT語(yǔ)句.應(yīng)該試驗(yàn)聯(lián)結(jié)、并、子查詢等,找出最佳的方法.
• 使用EXPLAIN語(yǔ)句讓MySQL解釋它將如何執(zhí)行一條SELECT語(yǔ)句.
• 一般來(lái)說(shuō),存儲(chǔ)過(guò)程執(zhí)行得比一條一條地執(zhí)行其中的各條MySQL語(yǔ)句快.
• 應(yīng)該總是使用正確的數(shù)據(jù)類型.
• 決不要檢索比需求還要多的數(shù)據(jù).換言之,不要用SELECT *(除非你真正需要每個(gè)列).
• 有的操作(包括INSERT)支持一個(gè)可選的DELAYED關(guān)鍵字,如果使用它,將把控制立即返回給調(diào)用程序,并且一旦有可能就實(shí)際執(zhí)行該操作.
• 在導(dǎo)入數(shù)據(jù)時(shí),應(yīng)該關(guān)閉自動(dòng)提交.你可能還想刪除索引(包括FULLTEXT索引),然后在導(dǎo)入完成后再重建它們.
• 必須索引數(shù)據(jù)庫(kù)表以改善數(shù)據(jù)檢索的性能.確定索引什么不是一件微不足道的任務(wù),需要分析使用的SELECT語(yǔ)句以找出重復(fù)的WHERE和ORDER BY子句.如果一個(gè)簡(jiǎn)單的WHERE子句返回結(jié)果所花的時(shí)間太長(zhǎng),則可以斷定其中使用的列(或幾個(gè)列)就是需要索引的對(duì)象.
• 你的SELECT語(yǔ)句中有一系列復(fù)雜的OR條件嗎?通過(guò)使用多條SELECT語(yǔ)句和連接它們的UNION語(yǔ)句,你能看到極大的性能改進(jìn).
• 索引改善數(shù)據(jù)檢索的性能,但損害數(shù)據(jù)插入、刪除和更新的性能.如果你有一些表,它們收集數(shù)據(jù)且不經(jīng)常被搜索,則在有必要之前不要索引它們.(索引可根據(jù)需要添加和刪除.)
• LIKE很慢.一般來(lái)說(shuō),最好是使用FULLTEXT而不是LIKE.
• 數(shù)據(jù)庫(kù)是不斷變化的實(shí)體.一組優(yōu)化良好的表一會(huì)兒后可能就面目全非了.由于表的使用和內(nèi)容的更改,理想的優(yōu)化和配置也會(huì)改變.
• 最重要的規(guī)則就是,每條規(guī)則在某些條件下都會(huì)被打破.

30.2 小結(jié)

MySQL語(yǔ)句的語(yǔ)法

C.1 ALTERTABLE:ALTER TABLE用來(lái)更新已存在表的模式.
C.2 COMMIT:COMMIT用來(lái)將事務(wù)處理寫到數(shù)據(jù)庫(kù).
C.3 CREATEINDEX:CREATE INDEX用于在一個(gè)或多個(gè)列上創(chuàng)建索引.
C.4 CREATEPROCEDURE:CREATE PROCEDURE用于創(chuàng)建存儲(chǔ)過(guò)程.
C.5 CREATETABLE:CREATE TABLE用于創(chuàng)建新數(shù)據(jù)庫(kù)表.
C.6 CREATEUSER:CREATE USER 用于向系統(tǒng)中添加新的用戶賬戶.
C.7 CREATEVIEW:CREATE VIEW用來(lái)創(chuàng)建一個(gè)或多個(gè)表上的新視圖.
C.8 DELETE:DELETE從表中刪除一行或多行.
C.9 DROP:DROP永久地刪除數(shù)據(jù)庫(kù)對(duì)象(表、視圖、索引等).
C.10 INSERT:INSERT給表增加一行.
C.11 INSERTSELECT:INSERT SELECT插入SELECT的結(jié)果到一個(gè)表.
C.12 ROLLBACK:ROLLBACK用于撤銷一個(gè)事務(wù)處理塊.
C.13 SAVEPOINT:SAVEPOINT為使用ROLLBACK語(yǔ)句設(shè)立保留點(diǎn).
C.14 SELECT:SELECT用于從一個(gè)或多個(gè)表(視圖)中檢索數(shù)據(jù).
C.15 STARTTRANSACTION:START TRANSACTION表示一個(gè)新的事務(wù)處理塊的開(kāi)始.
C.16 UPDATE:UPDATE更新表中一行或多行.

MySQL數(shù)據(jù)類型

數(shù)據(jù)類型是定義列中可以存儲(chǔ)什么數(shù)據(jù)以及該數(shù)據(jù) 實(shí)際怎樣存儲(chǔ)的基本規(guī)則.
數(shù)據(jù)類型用于以下目的.

• 數(shù)據(jù)類型允許限制可存儲(chǔ)在列中的數(shù)據(jù).例如,數(shù)值數(shù)據(jù)類型列只能接受數(shù)值.
• 數(shù)據(jù)類型允許在內(nèi)部更有效地存儲(chǔ)數(shù)據(jù).可以用一種比文本串更簡(jiǎn)潔的格式存儲(chǔ)數(shù)值和日期時(shí)間值.
• 數(shù)據(jù)類型允許變換排序順序.如果所有數(shù)據(jù)都作為串處理,則1位于10之前,而10又位于2之前(串以字典順序排序,從左邊開(kāi)始比較,一次一個(gè)字符).作為數(shù)值數(shù)據(jù)類型,數(shù)值才能正確排序.
  在設(shè)計(jì)表時(shí),應(yīng)該特別重視所用的數(shù)據(jù)類型.使用錯(cuò)誤的數(shù)據(jù)類型 可能會(huì)嚴(yán)重地影響應(yīng)用程序的功能和性能.更改包含數(shù)據(jù)的列不是一件 小事(而且這樣做可能會(huì)導(dǎo)致數(shù)據(jù)丟失).

D.1 串?dāng)?shù)據(jù)類型

最常用的數(shù)據(jù)類型是串?dāng)?shù)據(jù)類型.它們存儲(chǔ)串,如名字、地址、電 話號(hào)碼、郵政編碼等.有兩種基本的串類型,分別為定長(zhǎng)串和變長(zhǎng)串.
定長(zhǎng)串接受長(zhǎng)度固定的字符串,其長(zhǎng)度是在創(chuàng)建表時(shí)指定的.CHAR屬于定長(zhǎng)串類型.
變長(zhǎng)串存儲(chǔ)可變長(zhǎng)度的文本.有些變長(zhǎng)數(shù)據(jù)類型具有最大的定長(zhǎng),而有些則是完全變長(zhǎng)的.不管是哪種,只有指定的數(shù)據(jù)得到保存(額外的數(shù)據(jù)不保存)TEXT屬于變長(zhǎng)串類型.

既然變長(zhǎng)數(shù)據(jù)類型這樣靈活,為什么還要使用定長(zhǎng)數(shù)據(jù)類型?回答是因?yàn)樾阅?
MySQL處理定長(zhǎng)列遠(yuǎn)比處理變長(zhǎng)列快得多.此外,MySQL不允許對(duì)變長(zhǎng)列(或一個(gè)列的可變部分)進(jìn)行索引.這也會(huì)極大地影響性能.

串?dāng)?shù)據(jù)類型

使用引號(hào) 不管使用何種形式的串?dāng)?shù)據(jù)類型,串值都必須括在 引號(hào)內(nèi)(通常單引號(hào)更好).

當(dāng)數(shù)值不是數(shù)值時(shí)
你可能會(huì)認(rèn)為電話號(hào)碼和郵政編碼應(yīng)該存儲(chǔ)在數(shù)值字段中(數(shù)值字段只存儲(chǔ)數(shù)值數(shù)據(jù)),但是,這樣做卻是不可取的.如果在數(shù)值字段中存儲(chǔ)郵政編碼01234,則保存的將是數(shù)值1234,實(shí)際上丟失了一位數(shù)字. 需要遵守的基本規(guī)則是:如果數(shù)值是計(jì)算(求和、平均等)中使用的數(shù)值,則應(yīng)該存儲(chǔ)在數(shù)值數(shù)據(jù)類型列中.如果作為字符串(可能只包含數(shù)字)使用,則應(yīng)該保存在串?dāng)?shù)據(jù)類型列中.

D.2 數(shù)值數(shù)據(jù)類型

數(shù)值數(shù)據(jù)類型存儲(chǔ)數(shù)值.MySQL支持多種數(shù)值數(shù)據(jù)類型,每種存儲(chǔ)的數(shù)值具有不同的取值范圍.顯然,支持的取值范圍越大,所需存儲(chǔ)空間越多.此外,有的數(shù)值數(shù)據(jù)類型支持使用十進(jìn)制小數(shù)點(diǎn)(和小數(shù)),而有的則只支持整數(shù).

有符號(hào)或無(wú)符號(hào)
所有數(shù)值數(shù)據(jù)類型(除BIT和BOOLEAN外)都可以有符號(hào)或無(wú)符號(hào).有符號(hào)數(shù)值列可以存儲(chǔ)正或負(fù)的數(shù)值,無(wú)符號(hào)數(shù)值列只能存儲(chǔ)正數(shù).默認(rèn)情況為有符號(hào),但如果你知道自己不需要存儲(chǔ)負(fù)值,可以使用UNSIGNED關(guān)鍵字,這樣做將允許你存儲(chǔ)兩倍大小的值.

數(shù)值數(shù)據(jù)類型

不使用引號(hào) 與串不一樣,數(shù)值不應(yīng)該括在引號(hào)內(nèi).

存儲(chǔ)貨幣數(shù)據(jù)類型 MySQL中沒(méi)有專門存儲(chǔ)貨幣的數(shù)據(jù)類型,一般情況下使用DECIMAL(8, 2)

D.3 日期和時(shí)間數(shù)據(jù)類型

MySQL使用專門的數(shù)據(jù)類型來(lái)存儲(chǔ)日期和時(shí)間值.

日期和時(shí)間數(shù)據(jù)類型

D.4 二進(jìn)制數(shù)據(jù)類型

二進(jìn)制數(shù)據(jù)類型可存儲(chǔ)任何數(shù)據(jù)(甚至包括二進(jìn)制信息),如圖像、多媒體、字處理文檔等.

二進(jìn)制數(shù)據(jù)類型