|
5.1
MySQL對ANSI SQL92擴充
MySQL包含了一些可能在其他SQL數(shù)據(jù)庫找不到的擴充。要注意如果你使用他們，你的代碼將不與其他SQL服務(wù)器兼容。在一些情況下，你可以編寫包括MySQL擴展的代碼，但是仍然是可移植的，通過使用/*!
... */形式的注釋。在這種情況下，MySQL將進行詞法分析并且執(zhí)行在注釋內(nèi)的代碼，好像它是任何其它MySQL語句，但是其他SQL服務(wù)器將忽略擴展。例如：
SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...
如果你在'!'后增加一個版本數(shù)字，該語法將僅在MySQL版本是等于或比使用的版本數(shù)字新時才執(zhí)行：
CREATE /*!32302 TEMPORARY */ TABLE (a int);
上面的意思是如果你有3.23.02或更新，那么MySQL將使用TEMPORARY關(guān)鍵詞。
MySQL擴展被列在下面：
字段類型MEDIUMINT、SET、ENUM和不同的BLOB和TEXT類型。
字段屬性AUTO_INCREMENT、BINARY、UNSIGNED和ZEROFILL。
缺省地，所有的字符串比較是忽略大小寫的，由當(dāng)前的字符集決定了(缺省為ISO-8859-1
Latin1)排序順序。如果你不喜歡這樣，你應(yīng)該用BINARY屬性或使用BINARY強制符聲明列，它導(dǎo)致根據(jù)MySQL服務(wù)器主機的ASCII順序進行排序。
MySQL將每個數(shù)據(jù)庫映射一個MySQL數(shù)據(jù)目錄下面的目錄，將數(shù)據(jù)庫表映射到數(shù)據(jù)庫目錄下的數(shù)據(jù)庫文件名。這有2個含意：
在區(qū)分大小寫文件名的操作系統(tǒng)(象大多數(shù) Unix
系統(tǒng)一樣)上的MySQL中數(shù)據(jù)庫名字和表名是區(qū)分大小寫的。如果你有困難記得表名，接受一個一致的約定，例如總是用小寫名字創(chuàng)建數(shù)據(jù)庫和表。
數(shù)據(jù)庫、表、索引、列或別名可以以數(shù)字開始(但是不能僅由數(shù)字組成)。
你可以使用標(biāo)準(zhǔn)的系統(tǒng)命令備份、重命名、移動、刪除和拷貝表。例如，重命名一個表，重命名“.MYD”、“.MYI”和“.frm”文件為相應(yīng)的表。
在SQL語句中，你可以用db_name.tbl_name語法訪問不同數(shù)據(jù)庫中的表。一些SQL服務(wù)器提供同樣的功能但是稱它們?yōu)檫@User
space（用戶空間）。MySQL不支持類似在create
table ralph.my_table...IN my_tablespace中的表空間。
LIKE在數(shù)字列上被允許。
在一SELECT語句里面使用INTO OUTFILE和STRAIGHT_JOIN。見7.12 SELECT句法.
在一個SELECT語句中SQL_SMALL_RESULT選項。
EXPLAIN SELECT得到如何聯(lián)結(jié)表的描述。
在一個CREATE TABLE語句里面使用索引、在字段前綴上的索引和使用INDEX或KEY。見7.7 CREATE TABLE 句法。
CREATE TABLE使用TEMPORARY或IF NOT EXISTS。
使用COUNT(DISTINCT list)，這里“l(fā)ist”超過一個元素。
在一個ALTER TABLE語句里面使用CHANGE col_name、DROP
col_name或DROP INDEX。見7.8
ALTER TABLE句法。
在一個ALTER TABLE里面語句使用IGNORE。
在一個ALTER TABLE語句中使用多重ADD、ALTER、DROP或CHANGE子句。
使用帶關(guān)鍵詞IF EXISTS的DROP TABLE。
你能用單個DROP TABLE語句拋棄多個表。
DELETE語句的LIMIT子句。
INSERT和REPLACE語句的DELAYED子句。
INSERT, REPLACE, DELETE和UPDATE語句的LOW_PRIORITY子句。
使用LOAD DATA INFILE。在多數(shù)情況下，這句法與Oracle的LOAD
DATA INFILE兼容。見7.16 LOAD
DATA INFILE 句法。
OPTIMIZE TABLE語句。見7.9
OPTIMIZE TABLE句法。
SHOW語句。見7.21 SHOW句法(得到表、列等的信息)。
字符串可以被“"”或“'”包圍，而不只是“'”。
使用“\”轉(zhuǎn)義字符。
SET OPTION語句。見7.25
SET OPTION句法。
你不需要命名所有在GROUP BY部分的被選擇的列。這為一些很特定的情況給出更好的性能，而不是一般的查詢。見7.4.13 用于GROUP BY子句的函數(shù)。
為了方便來自于SQL環(huán)境其他為用戶，MySQL對許多函數(shù)支持別名。例如，所有的字符串功能都支持ANSI
SQL句法和 ODBC句法。
MySQL理解||和&&意味著邏輯的OR和AND，就像在C程序語言中。在MySQL中，||和OR是同義詞，&&和AND是同義詞。正因為這個好的句法，MySQL對字符串并置的不支持ANSI
SQL ||操作符；相反使用CONCAT()，因為CONCAT()接受任何數(shù)量的參數(shù)，很容易把||操作符使用變換到MySQL。
CREATE DATABASE或DROP DATABASE。見7.5 CREATE DATABASE句法。
%操作符是MOD()一個同義詞，即，N % M等價于MOD(N,M)。%支持C程序員并與PostgreSQL兼容。
=, <>, <=,<, >=,>,
<<, >>, <=>, AND, OR或LIKE操作符可以放在SELECT語句的FROM左邊用于比較列。例如：
mysql> SELECT col1=1 AND col2=2 FROM tbl_name;
LAST_INSERT_ID()函數(shù)。見20.4.29
mysql_insert_id()。
擴展的正則表達式操作符REGEXP和NOT REGEXP。
CONCAT()或CHAR()有一個參數(shù)或超過2個參數(shù)。（在MySQL中，這些函數(shù)可取任何數(shù)量的參數(shù)。）
BIT_COUNT(), CASE, ELT(), FROM_DAYS(),
FORMAT(), IF(), PASSWORD(), ENCRYPT(),
md5(), ENCODE(), DECODE(), PERIOD_ADD(),
PERIOD_DIFF(), TO_DAYS(),或WEEKDAY()函數(shù)。
使用TRIM()整修子串。ANSI SQL 只支持單個字符的刪除。
GROUP BY函數(shù)STD(), BIT_OR()和BIT_AND()。
使用REPLACE而不是DELETE+INSERT。見7.15 REPLACE句法。
FLUSH flush_option語句。
在一個語句用:=設(shè)置變量的可能性： SELECT @a:=SUM(total),@b=COUNT(*),@a/@b AS avg FROM test_table;
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
5.2 以ANSI模式運行MySQL
如果你用--ansi選項啟動mysqld，MySQL的下列行為改變。
||是字符串并置而不是OR。
可在一個函數(shù)名字之間與“(”有任何數(shù)量的空格。這也使所有的功能名字成為保留詞。
"將是一個標(biāo)識符引號字符(象MySQL `引號字符一樣)而不是一個字符串引號字符。
REAL將是FLOAT一個同義詞，不是DOUBLE一個同義詞。
5.3 MySQL相比ANSI
SQL92的差別
我們嘗試使得MySQL遵照ANSI SQL標(biāo)準(zhǔn)和ODBC SQL標(biāo)準(zhǔn)，但是在一些情況下，MySQL做一些不同的事情：
--只是一個注釋，如果后面跟一個白空字符。見5.4.7 `--'作為一個注釋的開始。
對于VARCHAR列，當(dāng)值被存儲時，拖后的空格被刪除。見E MySQL已知的錯誤和設(shè)計缺限。
在一些情況下，CHAR列偷偷地被改變?yōu)閂ARCHAR列。見7.7.1 平靜的列指定變化。
當(dāng)你刪除一個表時，對表的權(quán)限不自動地廢除。你必須明確地發(fā)出一個REVOKE來廢除對一個表的權(quán)限。見7.26 GRANT和REVOKE句法。
5.4 MySQL缺乏的功能
下列功能在當(dāng)前的MySQL版本是沒有的。對于一張優(yōu)先級表指出何時新擴展可以加入MySQL,
你應(yīng)該咨詢在線MySQL TODO 表。這是本手冊最新的TODO表版本。見F 我們想要在未來加入到MySQL的事情列表(TODO)。
5.4.1
子選擇
在MySQL中下列語句還不能工作：
SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2);
然而，在很多情況下，你可以重寫查詢，而不用子選擇：
SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id IS NULL
對于更復(fù)雜的子查詢，通常你可以創(chuàng)建臨時的表保存子查詢。然而在一些情況下，這種選擇將行不通。最經(jīng)常遇到的情形是DELETE語句，對于它標(biāo)準(zhǔn)SQL不支持聯(lián)結(jié)(join)(除了在子選擇)。對于這種情況，有2個可用選擇，直到子選擇被MySQL支持。
第一個選擇是使用一種過程化的程序語言(例如Perl或PHP)來提交一個SELECT查詢獲得要被刪除記錄主鍵，并然后使用這些值構(gòu)造DELETE語句(DELETE
FROM ... WHERE ... IN (key1, key2, ...))。
第二個選擇是使用交互式SQL自動構(gòu)造一套DELETE語句，使用MySQL擴展CONCAT()（代替標(biāo)準(zhǔn)||操作符)。例如：
SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', tab1.pkid, ';')
FROM tab1, tab2
WHERE tab1.col1 = tab2.col2;
你可以把這個查詢放在一個腳本文件并且從它重定向輸入到mysql命令行解釋器，將其輸出作為管道返回給解釋器的第2個實例：
prompt> mysql --skip-column-names mydb < myscript.sql | mysql mydb
MySQL僅支持INSERT ... SELECT ...和REPLACE ...
SELECT ...，獨立的子選擇將可能在3.24.0得到，然而，在其他環(huán)境下，你現(xiàn)在可以使用函數(shù)IN()。
5.4.2
SELECT INTO TABLE
MySQL還不支持Oracle SQL的擴展：SELECT ... INTO TABLE ....，相反MySQL支持ANSI
SQL句法INSERT INTO ... SELECT ...，基本上他們是一樣的。
另外，你可使用SELECT INTO OUTFILE...或CREATE TABLE ...
SELECT解決你的問題。
5.4.3
事務(wù)處理
不支持事務(wù)處理。MySQL將在短時間內(nèi)支持原子(atomic)操作，它象沒有回卷的事務(wù)。用原子操作，你能執(zhí)行一組INSERT/SELECT/whatever
命令并且保證沒有其他線程介入。在本文中，你通常不會需要回卷。目前，你可通過使用LOCK
TABLES和UNLOCK TABLES命令阻止其他線程的干擾。見7.24 LOCK TABLES/UNLOCK TABLES句法。
5.4.4
存儲過程和觸發(fā)器
一個存儲過程是能在服務(wù)器中編譯并存儲的一套SQL命令。一旦這樣做了，顧客不需要一直重新發(fā)出全部查詢，而可以參考存儲過程。因為查詢僅需一次詞法分析并且較少的信息需要在服務(wù)器和客戶之間傳送，因此這提供了更好的性能。你與可以通過擁有在服務(wù)器中的函數(shù)庫提升概念上的層次。
一個觸發(fā)器是當(dāng)一個特別的事件發(fā)生時，被調(diào)用的一個存儲過程。例如，你可以安裝一個存儲過程，它在每次從一個交易表刪除一條記錄時觸發(fā)，并且當(dāng)它所有交易被刪除時，自動地從一個客戶表中刪除相應(yīng)的客戶。
計劃修改的語言將能處理存儲過程，但是沒有觸發(fā)器。觸發(fā)器通常使每件事情變慢，即使對他們不需要的查詢。
為了倆解什么時候MySQL可能得到存儲過程，見F 我們想在未來加入到MySQL的事情列表(TODO)。
5.4.5
外鍵
注意，在SQL中外鍵不用于聯(lián)結(jié)表，而主要用于檢查參考完整性(RI)。如果你想要得到用一個SELECT語句從多個表得到結(jié)果，
你通過聯(lián)結(jié)表做！
SELECT * from table1,table2 where table1.id = table2.id;
見7.13 JOIN句法。見8.3.5 使用外鍵。
在MySQL里存在FOREIGN KEY句法僅僅為了與其他SQL供應(yīng)商的CREATE
TABLE命令相兼容；它不做任何事情。沒有ON DELETE ...的FOREIGN
KEY句法主要用于文檔目的。一些ODBC應(yīng)用程序可以使用它自動生成WHERE子句，但是這通常很容易的覆蓋。
FOREIGN KEY有時用作一個約束檢查，但是如果行以正確的順序被插入表，該檢查實際上是不必要的。MySQL僅僅支持這些子句(不考慮是否他們工作!)，因為一些應(yīng)用程序要求他們存在。
在MySQL中，你可以解決ON DELETE ...沒被實現(xiàn)的問題，，在你從一個用外鍵的表刪除記錄時，通過為一個應(yīng)用程序增加適當(dāng)?shù)腄ELETE語句即可。實際上，這很快(在一些情況下更快)并且比使用外鍵更比便于移植。
在不久的將來我們將擴充FOREIGN KEY實現(xiàn)，以便至少信息將在表說明文件中保存并且可以由mysqldump和ODBC檢索。
5.4.5.1
不使用外鍵的理由
有很多與FOREIGN KEY有關(guān)的問題我們不知道從哪兒開始：
外鍵使生活更復(fù)雜，因為外鍵的定義必須存儲在一個數(shù)據(jù)庫中并且實現(xiàn)他們將破壞使用能被移動、拷貝和刪除文件的全部“好方法”。
速度影響對INSERT和UPDATE語句是可怕的，并且在這種情況下幾乎所有的FOREIGN
KEY檢查都是無用的，因為不管怎樣你通常以正確的順序在正確的表中插入記錄。
當(dāng)更新一張表時，也有在許多表上保存鎖的需求，因為副作用可以串聯(lián)通過全部數(shù)據(jù)庫。首先從一張表中刪除記錄并且隨后從其他表中刪除他們，這更快。
你再也不可以通過做一個全面的表刪除并隨后恢復(fù)所有的記錄的方法來恢復(fù)一張表(從新來源或從一個備份)。
如果你有外鍵，你不能傾倒和恢復(fù)表，除非你以一個非常特定的做這些。
很容易做一個“允許的”的循環(huán)定義使得不可能用一個單個create語句重建每一個表，就算定義可行又可用。
FOREIGN KEY唯一好的方面是它給ODBC和一些其他客戶程序檢查一張表如何被連接的能力，并且使用它們顯示出連接圖表并幫助構(gòu)造應(yīng)用。
MySQL不久將存儲FOREIGN KEY定義以便一個客戶能詢問并收到原來的連接如何進行的一個答案。當(dāng)前的“.frm
文件格式?jīng)]有它應(yīng)有的地位。
5.4.6 視圖
MySQL不支持視圖，但是它在TODO上。
5.4.7 '--'作為一個注釋的開始
有些其他SQL數(shù)據(jù)庫使用'--'開始注釋。MySQL有“#”作為開始數(shù)注釋的字符，即使mysql命令行工具刪除以'--'開始的所有行。你也可以在MySQL中使用C注釋風(fēng)格/*
this is a comment */。見7.29
注釋句法。
MySQL3.23.3和以上版本支持'--'注釋風(fēng)格，只要注釋跟在一個空格之后。這是因為這種退化的注釋風(fēng)格已經(jīng)引起用像下列代碼那樣的自動生成的SQL查詢的許多問題，這里我們自動地為!payment!插入支付值：
UPDATE tbl_name SET credit=credit-!payment!
你想出當(dāng)payment的值是負的時將發(fā)生什么嗎？
因為1--1在SQL中是合法的，我們認為'--'開始注釋是可怕的。
然而在MySQL 3.23中，你可使用：1-- This is a comment
如果你正在運行一個比3.23早的MySQL的版本，下列的討論才涉及你：
如果你在一個文本文件中有一個SQL程序，它包含'--'注釋，你應(yīng)該使用：
shell> replace " --" " #" < text-file-with-funny-comments.sql \
| mysql database
而不是通常：
shell> mysql database < text-file-with-funny-comments.sql
你也可以“現(xiàn)場”編輯命令文件將'--'注釋改為'#'注釋：
shell> replace " --" " #" -- text-file-with-funny-comments.sql
用這個命令改回他們：
shell> replace " #" " --" -- text-file-with-funny-comments.sql
5.5 MySQL 遵循什么標(biāo)準(zhǔn)？
Entry level SQL92。ODBC level 0-2。
5.6
怎樣處理沒有COMMIT/ROLLBACK
MySQL不支持COMMIT-ROLLBACK。問題是有效地處理COMMIT-ROLLBACK將需要完全不同于MySQL今天使用的表布局。MySQL也將需要額外的線程在表上做自動清除工作，而且磁盤用量將更高。這將使MySQL比現(xiàn)今慢上大約2-4倍。MySQL比幾乎所有其他SQL數(shù)據(jù)庫都快(一般至少快2-3倍)。原因之一就是缺少COMMIT-ROLLBACK。
目前，我們是更多地實現(xiàn)SQL服務(wù)器語言(象存儲過程)，有了它，你將確實很少需要COMMIT-ROLLBACK，這也將得到更好的性能。
通常需要事務(wù)的循環(huán)可以借助LOCK TABLES進行編碼，并且當(dāng)你能即時地更新記錄時，你不需要光標(biāo)(cursor)。
我們在TODO上有事務(wù)和光標(biāo)，然而并非相當(dāng)優(yōu)先。如果我們實現(xiàn)這些，將作為CREATE
TABLE的選項，那意味著COMMIT-ROLLBACK將僅工作在那些表上，以便速度損失僅僅強加在那些表上。
我們在TcX有一個更大的需求，一個比100%通用數(shù)據(jù)庫的真正快速的數(shù)據(jù)庫。無論何時我們發(fā)現(xiàn)一個方法來實現(xiàn)這些特征而沒有任何速度損失，我們將可能做它。暫時，有許多更重要的事情要做。檢查TODO，看我們此時如何將事情優(yōu)先排列。（有的較高級別支持的客戶可以改變它，因此事情是可以重新優(yōu)先化的。）
當(dāng)前的問題實際上是ROLLBACK，沒有ROLLBACK，你能用LOCK
TABLES做任何COMMIT動作。為了支持ROLLBACK，MySQL將必須被改變以存儲所有的舊記錄，如果發(fā)出ROLLBACK，它們被更新的并且將任何東西恢復(fù)到起點。對于簡單的情形，這不是難做的
(當(dāng)前isamlog可以用于此目的)，但是為ALTER/DROP/CREATE TABLE實現(xiàn)ROLLBACK將是更困難的。
避免使用ROLLBACK，你可以使用下列策略：
使用LOCK TABLES ...鎖住所有你想要存取的數(shù)據(jù)庫表。
測試條件。
如果一切無誤，更新。
使用UNLOCK TABLES釋放你的鎖。
這通常比使用可能帶ROLLBACK的交易是一個更快的方法，盡管不總是這樣。這個解決方案不能處理的唯一狀況是當(dāng)某人在更新當(dāng)中殺死線程時。在這種情況下，所有的鎖將被釋放，但是一些更改不能被執(zhí)行。
你也可使函數(shù)以單個操作更新記錄。你能通過使用下列技術(shù)得到一個很有效率的應(yīng)用程序：
相對他們的當(dāng)前的值修改字段
僅僅更新那些實際上更改的字段
例如，當(dāng)我們正在更新一些客戶信息時，我們僅僅更新那些改變了的客戶數(shù)據(jù)并只測試沒有任何數(shù)據(jù)的改變，或數(shù)據(jù)取決于改變的數(shù)據(jù)，與原來的行相比變化了。對于改變了的數(shù)據(jù)的測試用WHERE子句在UPDATE語句中完成。如果記錄沒被更新，我們給客戶一條消息：“你改變了的一些數(shù)據(jù)已被其他用戶改變了”，然后我們在一個窗口中顯示新行對照舊行，因此用戶能決定他該使用哪個版本的客戶記錄。
這給了我們類似于“列鎖定”的東西，但是實際上甚至更好，因為我們僅僅更新某些列，使用相對于他們的當(dāng)前值的值。這意味著典型的UPDATE語句看上去象這些一樣?xùn)|西：
UPDATE tablename SET pay_back=pay_back+'relative change';
UPDATE customer
SET
customer_date='current_date',
address='new address',
phone='new phone',
money_he_owes_us=money_he_owes_us+'new_money'
WHERE
customer_id=id AND address='old address' AND phone='old phone';
正如你能看到的，這是很有效的并且就算其他客戶已經(jīng)改變了pay_back或money_he_owes_us列的也能工作。
在許多情況下，為管理一些表格的唯一標(biāo)識符目的，用戶已經(jīng)想要ROLLBACK或LOCK
TABLES。這可用一個AUTO_INCREMENT列和一個SQL函數(shù)LAST_INSERT_ID()或C
API函數(shù)mysql_insert_id()更高效地處理。見20.4.29 mysql_insert_id()。
在TcX，我們從來沒有任何對行級鎖定的需求，因為我們總是能通過編碼解決它。一些情況下需要確實行鎖定，但是他們是很少見的。如果你想要行級鎖定，你可以在表中使用標(biāo)志列并且這樣做：
UPDATE tbl_name SET row_flag=1 WHERE id=ID;
如果行被找到發(fā)現(xiàn)并且row_flag在原來的行已經(jīng)不是1，對受影響的行數(shù)MySQL返回1。
你可以想到它，因為MySQL把上面的查詢變?yōu)椋?
UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;
|
