MySQL中文參考手冊

譯者：晏子 (clyan@sohu.com）      主頁：http://linuxdb.yeah.net

第一章, 前一章, 下一章, 最後一章，目錄.

5 MySQL與標准的兼容性？

5.1   MySQL對ANSI SQL92擴充

MySQL包含了一些可能在其他SQL數據庫找不到的擴充。要注意如果你使用他們，你的代碼將不與其他SQL服務器兼容。在一些情況下，你可以編寫包括MySQL擴展的代碼，但是仍然是可移植的，通過使用/*! ... */形式的注釋。在這種情況下，MySQL將進行詞法分析並且執行在注釋內的代碼，好像它是任何其它MySQL語句，但是其他SQL服務器將忽略擴展。例如：

SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ... 

如果你在'!'後增加一個版本數字，該語法將僅在MySQL版本是等于或比使用的版本數字新時才執行：

CREATE /*!32302 TEMPORARY */ TABLE (a int);

上面的意思是如果你有3.23.02或更新，那麼MySQL將使用TEMPORARY關鍵詞。

MySQL擴展被列在下面：

• 字段類型MEDIUMINT、SET、ENUM和不同的BLOB和TEXT類型。
• 字段屬性AUTO_INCREMENT、BINARY、UNSIGNED和ZEROFILL。
• 缺省地，所有的字符串比較是忽略大小寫的，由當前的字符集決定了(缺省為ISO-8859-1 Latin1)排序順序。如果你不喜歡這樣，你應該用BINARY屬性或使用BINARY強制符聲明列，它導致根據MySQL服務器主機的ASCII順序進行排序。
• MySQL將每個數據庫映射一個MySQL數據目錄下面的目錄，將數據庫表映射到數據庫目錄下的數據庫文件名。這有2個含意：
  • 在區分大小寫文件名的操作系統(象大多數 Unix 系統一樣)上的MySQL中數據庫名字和表名是區分大小寫的。如果你有困難記得表名，接受一個一致的約定，例如總是用小寫名字創建數據庫和表。
  • 數據庫、表、索引、列或別名可以以數字開始(但是不能僅由數字組成)。
  • 你可以使用標准的系統命令備份、重命名、移動、刪除和拷貝表。例如，重命名一個表，重命名“.MYD”、“.MYI”和“.frm”文件為相應的表。
• 在SQL語句中，你可以用db_name.tbl_name語法訪問不同數據庫中的表。一些SQL服務器提供同樣的功能但是稱它們為這User space（用戶空間）。MySQL不支持類似在create table ralph.my_table...IN my_tablespace中的表空間。
• LIKE在數字列上被允許。
• 在一SELECT語句里面使用INTO OUTFILE和STRAIGHT_JOIN。見7.12 SELECT句法.
• 在一個SELECT語句中SQL_SMALL_RESULT選項。
• EXPLAIN SELECT得到如何聯結表的描述。
• 在一個CREATE TABLE語句里面使用索引、在字段前綴上的索引和使用INDEX或KEY。見7.7 CREATE TABLE 句法。
• CREATE TABLE使用TEMPORARY或IF NOT EXISTS。
• 使用COUNT(DISTINCT list)，這里“list”超過一個元素。
• 在一個ALTER TABLE語句里面使用CHANGE col_name、DROP col_name或DROP INDEX。見7.8 ALTER TABLE句法。
• 在一個ALTER TABLE里面語句使用IGNORE。
• 在一個ALTER TABLE語句中使用多重ADD、ALTER、DROP或CHANGE子句。
• 使用帶關鍵詞IF EXISTS的DROP TABLE。
• 你能用單個DROP TABLE語句拋棄多個表。
• DELETE語句的LIMIT子句。
• INSERT和REPLACE語句的DELAYED子句。
• INSERT, REPLACE, DELETE和UPDATE語句的LOW_PRIORITY子句。
• 使用LOAD DATA INFILE。在多數情況下，這句法與Oracle的LOAD DATA INFILE兼容。見7.16 LOAD DATA INFILE 句法。
• OPTIMIZE TABLE語句。見7.9 OPTIMIZE TABLE句法。
• SHOW語句。見7.21 SHOW句法(得到表、列等的信息)。
• 字符串可以被“"”或“'”包圍，而不只是“'”。
• 使用“\”轉義字符。
• SET OPTION語句。見7.25 SET OPTION句法。
• 你不需要命名所有在GROUP BY部分的被選擇的列。這為一些很特定的情況給出更好的性能，而不是一般的查詢。見7.4.13 用于GROUP BY子句的函數。
• 為了方便來自于SQL環境其他為用戶，MySQL對許多函數支持別名。例如，所有的字符串功能都支持ANSI SQL句法和 ODBC句法。
• MySQL理解||和&&意味著邏輯的OR和AND，就像在C程序語言中。在MySQL中，||和OR是同義詞，&&和AND是同義詞。正因為這個好的句法，MySQL對字符串並置的不支持ANSI SQL ||操作符；相反使用CONCAT()，因為CONCAT()接受任何數量的參數，很容易把||操作符使用變換到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()函數。見20.4.29 mysql_insert_id()。
• 擴展的正則表達式操作符REGEXP和NOT REGEXP。
• CONCAT()或CHAR()有一個參數或超過2個參數。（在MySQL中，這些函數可取任何數量的參數。）
• BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), md5(), ENCODE(), DECODE(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(),或WEEKDAY()函數。
• 使用TRIM()整修子串。ANSI SQL 只支持單個字符的刪除。
• GROUP BY函數STD(), BIT_OR()和BIT_AND()。
• 使用REPLACE而不是DELETE+INSERT。見7.15 REPLACE句法。
• FLUSH flush_option語句。
• 在一個語句用:=設置變量的可能性：

  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。
• 可在一個函數名字之間與“(”有任何數量的空格。這也使所有的功能名字成為保留詞。
• "將是一個標識符引號字符(象MySQL `引號字符一樣)而不是一個字符串引號字符。
• REAL將是FLOAT一個同義詞，不是DOUBLE一個同義詞。

5.3 MySQL相比ANSI SQL92的差別

我們嘗試使得MySQL遵照ANSI SQL標准和ODBC SQL標准，但是在一些情況下，MySQL做一些不同的事情：

• --只是一個注釋，如果後面跟一個白空字符。見5.4.7 `--'作為一個注釋的開始。
• 對于VARCHAR列，當值被存儲時，拖後的空格被刪除。見E MySQL已知的錯誤和設計缺限。
• 在一些情況下，CHAR列偷偷地被改變為VARCHAR列。見7.7.1 平靜的列指定變化。
• 當你刪除一個表時，對表的權限不自動地廢除。你必須明確地發出一個REVOKE來廢除對一個表的權限。見7.26 GRANT和REVOKE句法。

5.4 MySQL缺乏的功能

下列功能在當前的MySQL版本是沒有的。對于一張優先級表指出何時新擴展可以加入MySQL, 你應該咨詢在線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

對于更複雜的子查詢，通常你可以創建臨時的表保存子查詢。然而在一些情況下，這種選擇將行不通。最經常遇到的情形是DELETE語句，對于它標准SQL不支持聯結(join)(除了在子選擇)。對于這種情況，有2個可用選擇，直到子選擇被MySQL支持。

第一個選擇是使用一種過程化的程序語言(例如Perl或PHP)來提交一個SELECT查詢獲得要被刪除記錄主鍵，並然後使用這些值構造DELETE語句(DELETE FROM ... WHERE ... IN (key1, key2, ...))。

第二個選擇是使用交互式SQL自動構造一套DELETE語句，使用MySQL擴展CONCAT()（代替標准||操作符)。例如：

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得到，然而，在其他環境下，你現在可以使用函數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 事務處理

不支持事務處理。MySQL將在短時間內支持原子(atomic)操作，它象沒有回卷的事務。用原子操作，你能執行一組INSERT/SELECT/whatever 命令並且保証沒有其他線程介入。在本文中，你通常不會需要回卷。目前，你可通過使用LOCK TABLES和UNLOCK TABLES命令阻止其他線程的幹擾。見7.24 LOCK TABLES/UNLOCK TABLES句法。

5.4.4 存儲過程和觸發器

一個存儲過程是能在服務器中編譯並存儲的一套SQL命令。一旦這樣做了，顧客不需要一直重新發出全部查詢，而可以參考存儲過程。因為查詢僅需一次詞法分析並且較少的信息需要在服務器和客戶之間傳送，因此這提供了更好的性能。你與可以通過擁有在服務器中的函數庫提升概念上的層次。

一個觸發器是當一個特別的事件發生時，被調用的一個存儲過程。例如，你可以安裝一個存儲過程，它在每次從一個交易表刪除一條記錄時觸發，並且當它所有交易被刪除時，自動地從一個客戶表中刪除相應的客戶。

計劃修改的語言將能處理存儲過程，但是沒有觸發器。觸發器通常使每件事情變慢，即使對他們不需要的查詢。

為了倆解什麼時候MySQL可能得到存儲過程，見F 我們想在未來加入到MySQL的事情列表(TODO)。

5.4.5 外鍵

注意，在SQL中外鍵不用于聯結表，而主要用于檢查參考完整性(RI)。如果你想要得到用一個SELECT語句從多個表得到結果， 你通過聯結表做！

SELECT * from table1,table2 where table1.id = table2.id; 

見7.13 JOIN句法。見8.3.5 使用外鍵。

在MySQL里存在FOREIGN KEY句法僅僅為了與其他SQL供應商的CREATE TABLE命令相兼容；它不做任何事情。沒有ON DELETE ...的FOREIGN KEY句法主要用于文檔目的。一些ODBC應用程序可以使用它自動生成WHERE子句，但是這通常很容易的覆蓋。 FOREIGN KEY有時用作一個約束檢查，但是如果行以正確的順序被插入表，該檢查實際上是不必要的。MySQL僅僅支持這些子句(不考慮是否他們工作!)，因為一些應用程序要求他們存在。

在MySQL中，你可以解決ON DELETE ...沒被實現的問題，，在你從一個用外鍵的表刪除記錄時，通過為一個應用程序增加適當的DELETE語句即可。實際上，這很快(在一些情況下更快)並且比使用外鍵更比便于移植。

在不久的將來我們將擴充FOREIGN KEY實現，以便至少信息將在表說明文件中保存並且可以由mysqldump和ODBC檢索。

5.4.5.1 不使用外鍵的理由

有很多與FOREIGN KEY有關的問題我們不知道從哪兒開始：

• 外鍵使生活更複雜，因為外鍵的定義必須存儲在一個數據庫中並且實現他們將破壞使用能被移動、拷貝和刪除文件的全部“好方法”。
• 速度影響對INSERT和UPDATE語句是可怕的，並且在這種情況下幾乎所有的FOREIGN KEY檢查都是無用的，因為不管怎樣你通常以正確的順序在正確的表中插入記錄。
• 當更新一張表時，也有在許多表上保存鎖的需求，因為副作用可以串聯通過全部數據庫。首先從一張表中刪除記錄並且隨後從其他表中刪除他們，這更快。
• 你再也不可以通過做一個全面的表刪除並隨後恢複所有的記錄的方法來恢複一張表(從新來源或從一個備份)。
• 如果你有外鍵，你不能傾倒和恢複表，除非你以一個非常特定的做這些。
• 很容易做一個“允許的”的循環定義使得不可能用一個單個create語句重建每一個表，就算定義可行又可用。

FOREIGN KEY唯一好的方面是它給ODBC和一些其他客戶程序檢查一張表如何被連接的能力，並且使用它們顯示出連接圖表並幫助構造應用。

MySQL不久將存儲FOREIGN KEY定義以便一個客戶能詢問並收到原來的連接如何進行的一個答案。當前的“.frm 文件格式沒有它應有的地位。

5.4.6 視圖

MySQL不支持視圖，但是它在TODO上。

5.4.7 '--'作為一個注釋的開始

有些其他SQL數據庫使用'--'開始注釋。MySQL有“#”作為開始數注釋的字符，即使mysql命令行工具刪除以'--'開始的所有行。你也可以在MySQL中使用C注釋風格/* this is a comment */。見7.29 注釋句法。

MySQL3.23.3和以上版本支持'--'注釋風格，只要注釋跟在一個空格之後。這是因為這種退化的注釋風格已經引起用像下列代碼那樣的自動生成的SQL查詢的許多問題，這里我們自動地為!payment!插入支付值：

UPDATE tbl_name SET credit=credit-!payment! 

你想出當payment的值是負的時將發生什麼嗎？

因為1--1在SQL中是合法的，我們認為'--'開始注釋是可怕的。

然而在MySQL 3.23中，你可使用：1-- This is a comment

如果你正在運行一個比3.23早的MySQL的版本，下列的討論才涉及你：

如果你在一個文本文件中有一個SQL程序，它包含'--'注釋，你應該使用：

shell> replace " --" " #" < text-file-with-funny-comments.sql \
         | mysql database

而不是通常： 

shell> mysql database < text-file-with-funny-comments.sql

你也可以“現場”編輯命令文件將'--'注釋改為'#'注釋：

shell> replace " --" " #" -- text-file-with-funny-comments.sql

用這個命令改回他們：

shell> replace " #" " --" -- text-file-with-funny-comments.sql

5.5 MySQL 遵循什麼標准？

Entry level SQL92。ODBC level 0-2。

5.6 怎樣處理沒有COMMIT/ROLLBACK

MySQL不支持COMMIT-ROLLBACK。問題是有效地處理COMMIT-ROLLBACK將需要完全不同于MySQL今天使用的表布局。MySQL也將需要額外的線程在表上做自動清除工作，而且磁盤用量將更高。這將使MySQL比現今慢上大約2-4倍。MySQL比幾乎所有其他SQL數據庫都快(一般至少快2-3倍)。原因之一就是缺少COMMIT-ROLLBACK。

目前，我們是更多地實現SQL服務器語言(象存儲過程)，有了它，你將確實很少需要COMMIT-ROLLBACK，這也將得到更好的性能。

通常需要事務的循環可以借助LOCK TABLES進行編碼，並且當你能即時地更新記錄時，你不需要光標(cursor)。

我們在TODO上有事務和光標，然而並非相當優先。如果我們實現這些，將作為CREATE TABLE的選項，那意味著COMMIT-ROLLBACK將僅工作在那些表上，以便速度損失僅僅強加在那些表上。

我們在TcX有一個更大的需求，一個比100%通用數據庫的真正快速的數據庫。無論何時我們發現一個方法來實現這些特征而沒有任何速度損失，我們將可能做它。暫時，有許多更重要的事情要做。檢查TODO，看我們此時如何將事情優先排列。（有的較高級別支持的客戶可以改變它，因此事情是可以重新優先化的。）

當前的問題實際上是ROLLBACK，沒有ROLLBACK，你能用LOCK TABLES做任何COMMIT動作。為了支持ROLLBACK，MySQL將必須被改變以存儲所有的舊記錄，如果發出ROLLBACK，它們被更新的並且將任何東西恢複到起點。對于簡單的情形，這不是難做的 (當前isamlog可以用于此目的)，但是為ALTER/DROP/CREATE TABLE實現ROLLBACK將是更困難的。

避免使用ROLLBACK，你可以使用下列策略：

1. 使用LOCK TABLES ...鎖住所有你想要存取的數據庫表。
2. 測試條件。
3. 如果一切無誤，更新。
4. 使用UNLOCK TABLES釋放你的鎖。

這通常比使用可能帶ROLLBACK的交易是一個更快的方法，盡管不總是這樣。這個解決方案不能處理的唯一狀況是當某人在更新當中殺死線程時。在這種情況下，所有的鎖將被釋放，但是一些更改不能被執行。

你也可使函數以單個操作更新記錄。你能通過使用下列技術得到一個很有效率的應用程序：

• 相對他們的當前的值修改字段
• 僅僅更新那些實際上更改的字段

例如，當我們正在更新一些客戶信息時，我們僅僅更新那些改變了的客戶數據並只測試沒有任何數據的改變，或數據取決于改變的數據，與原來的行相比變化了。對于改變了的數據的測試用WHERE子句在UPDATE語句中完成。如果記錄沒被更新，我們給客戶一條消息：“你改變了的一些數據已被其他用戶改變了”，然後我們在一個窗口中顯示新行對照舊行，因此用戶能決定他該使用哪個版本的客戶記錄。

這給了我們類似于“列鎖定”的東西，但是實際上甚至更好，因為我們僅僅更新某些列，使用相對于他們的當前值的值。這意味著典型的UPDATE語句看上去象這些一樣東西：

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';

正如你能看到的，這是很有效的並且就算其他客戶已經改變了pay_back或money_he_owes_us列的也能工作。

在許多情況下，為管理一些表格的唯一標識符目的，用戶已經想要ROLLBACK或LOCK TABLES。這可用一個AUTO_INCREMENT列和一個SQL函數LAST_INSERT_ID()或C API函數mysql_insert_id()更高效地處理。見20.4.29 mysql_insert_id()。

在TcX，我們從來沒有任何對行級鎖定的需求，因為我們總是能通過編碼解決它。一些情況下需要確實行鎖定，但是他們是很少見的。如果你想要行級鎖定，你可以在表中使用標志列並且這樣做：

UPDATE tbl_name SET row_flag=1 WHERE id=ID; 

如果行被找到發現並且row_flag在原來的行已經不是1，對受影響的行數MySQL返回1。

你可以想到它，因為MySQL把上面的查詢變為：

UPDATE tbl_name SET row_flag=1 WHERE id=ID and row_flag <> 1;

第一章, 前一章, 下一章, 最後一章，目錄.