MySQL Reference Manual for version 4.1.0-alpha.


6.4 資料操縱:SELECT, INSERT, UPDATE, DELETE

6.4.1 SELECT 句法

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] rows | rows OFFSET offset]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT 用於檢索從一個或多個表中選取出的行。select_expression 表示你希望檢索的列。 SELECT 也可以被用於檢索沒有參照任何表的計算列。例如:

mysql> SELECT 1 + 1;
         -> 2

所有使用的關鍵詞必須嚴格以上面所顯示的次序被給出。舉例來說,一個 HAVING 幾句必須出現在 GROUP BY 幾句後,在 ORDER BY 字句之前。

6.4.1.1 JOIN 句法

MySQL 支援在 SELECT 中使用下面所示的 JOIN 句法:

table_reference, table_reference
table_reference [CROSS] JOIN table_reference
table_reference INNER JOIN table_reference join_condition
table_reference STRAIGHT_JOIN table_reference
table_reference LEFT [OUTER] JOIN table_reference join_condition
table_reference LEFT [OUTER] JOIN table_reference
table_reference NATURAL [LEFT [OUTER]] JOIN table_reference
{ OJ table_reference LEFT OUTER JOIN table_reference ON conditional_expr }
table_reference RIGHT [OUTER] JOIN table_reference join_condition
table_reference RIGHT [OUTER] JOIN table_reference
table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference 定義如下:

table_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE INDEX (key_list)]]

join_condition 定義如下:

ON conditional_expr |
USING (column_list)

通常不應該在 ON 存在任何條件式,它是用於限制在結果集中有哪個行的(對於這個規則也有例外)。如果你希望哪個記錄行應該在結果中,你必須在 WHERE 幾句中限制它。

注意,在早於 3.23.17 的版本中,INNER JOIN 不接受一個 join_condition

上面所顯示的最後一個 LEFT OUTER JOIN 句法僅僅是為了與 ODBC 兼容而存在的:

一些範例:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    ->          LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    ->          WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    ->          WHERE key1=1 AND key2=2 AND key3=3;

查看章節 5.2.6 MySQL 如何最佳化 LEFT JOINRIGHT JOIN

6.4.1.2 UNION 句法

SELECT ...
UNION [ALL]
SELECT ...
  [UNION
   SELECT ...]

UNION 在 MySQL 4.0.0 中被實現。

UNION 用於將多個 SELECT 語句的結果聯合到一個結果集中。

SELECT 中的 select_expression 部分列出的列必須具有同樣的類型。第一個 SELECT 查詢中使用的列名將作為結果集的列名傳回。

SELECT 命令是一個普通的選擇命令,但是有下列的限制:

如果你不為 UNION 使用關鍵詞 ALL,所有傳回的記錄行將是唯一的,就好像你為整個傳回集使用了一個 DISTINCT。如果你指定了 ALL,那麼你將得到從所有使用的 SELECT 語句中傳回的所有相符記錄行。

如果你希望對整個 UNION 結果使用一個 ORDER BY,你應該使用圓括號:

(SELECT a FROM table_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM table_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;

6.4.2 HANDLER 句法

HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name CLOSE

HANDLER 語句提供了直接存取 MyISAM 表儲存引擎的接口。

HANDLER 語句的第一個形式打開一個表,通過後來的 HANDLER ... READ 語句使它可讀取。這個表物件將不能被其它線程共享,也不會被關閉,除非線程調用 HANDLER tbl_name CLOSE 或線程關閉。

第二個形式讀取指定的索引遵從那個條件並且適合 WHERE 條件的一行(或更多的,由 LIMIT 幾句指定)。 如果索引由幾個部分組成(範圍有幾個列),值以逗號分隔的清單指定﹔如果只提供的一部分值,那麼第一個列是必需的。

第三個形式從表中以索引的順序讀取相符 WHERE 條件的一行(或更多的,由 LIMIT 幾句指定)。

第四個形式(沒有索引清單)從表中以自然的列順序(在資料文件中儲存的次序)讀取相符 WHERE 條件的一行(或更多的,由 LIMIT 幾句指定)。如果期望做一個全表掃描,它將比 HANDLER tbl_name READ index_name 更快。

HANDLER ... CLOSE 關閉一個以 HANDLER ... OPEN 打開的表。

HANDLER 是一個稍微低級的語句。舉例來說,它不提供一致性約束。更確切地說,HANDLER ... OPEN 接受一個表的快照,並且 鎖定表。這就意味著在一個 HANDLER ... OPEN 被執行後,表資料仍會被 (這個或其它的線程) 修改,這些修改可能在 HANDLER ... NEXTHANDLER ... PREV 掃描中才會部分地出現。

使用這個接口代替普通 SQL 的原因是:

6.4.3 INSERT 句法

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name
        SET col_name=(expression | DEFAULT), ...
        [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

INSERT 將新行插入到一個已存在的表中。INSERT ... VALUES 形式的語句基於明確的值插入記錄行。INSERT ... SELECT 形式的語句從另一個或多個表中選取出值,並將其插入。有多重值清單的 INSERT ... VALUES 形式的語句在 MySQL 3.22.5 或更新的版本中被支援。col_name=expression 句法在 MySQL 3.22.10 或更新的版本中得到支援。

tbl_name 是記錄將要被插入的表。列名清單或 SET 幾句指出語句指定的值賦給哪個列:

如果你使用 INSERT ... SELECT 或一個 INSERT ... VALUES 語句插入多值列,你可以使用 C API 函數 mysql_info() 得到查詢的資訊。資訊字串的格式如下:

Records: 100 Duplicates: 0 Warnings: 0

Duplicates 指出因與某些現有的唯一索引值重復而不能被插入的記錄行數目。Warnings 指出在嘗試插入的列值中在某些方面可能有問題的數目。在下列任何一個條件下,警告都會發生:

6.4.3.1 INSERT ... SELECT 句法

INSERT [LOW_PRIORITY] [IGNORE] [INTO] tbl_name [(column list)] SELECT ...

使用 INSERT ... SELECT 語句,你可以從一個或多個表中讀取多個記錄行,並將其快速地插入到一個表中。

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

一個 INSERT ... SELECT 語句有下列條件的限止:

你當然也可以使用 REPLACE 代替 INSERT 來蓋寫老的記錄行。

6.4.4 INSERT DELAYED 句法

INSERT DELAYED ...

INSERT 語句的 DELAYED 選項是一個 MySQL 特有的選項,如果你的客戶端不能等待 INSERT 的完成,這將會是很有用的。This is a common problem when you use MySQL for logging and 當你打開日誌記錄使用 MySQL 並且你周期性的需花費很長時間才完成的 SELECTUPDATE 語句時,這將是一個很普遍的問題。DELAYED 在 MySQL 3.22.15 中被引入。它是 MySQL 對 ANSI SQL92 的一個擴展。

INSERT DELAYED 僅僅工作與 ISAMMyISAM 表。注意,因為 MyISAM 表支援並發的 SELECTINSERT,如果在資料文件中沒有閒置的塊,那你將很少需要對 MyISAM 表使用 INSERT DELAYED。查看章節 7.1 MyISAM

當你使用 INSERT DELAYED 時,客戶端將立即得到一個 OK,當表不被任何其它線程使用時,該行將被插入。

使用 INSERT DELAYED 的另一個主要的好處就是,從很多客戶端來的插入請求會被打包在一起並寫入一個塊中。這比做許多單獨的插入要快的多。

注意,目前的記錄行佇列是被儲存在記憶體中的,一直到他們被插入到表中。這就意味著,如果你使用強制的方法(kill -9) 殺死 mysqld,或者如果意外地死掉,任何沒有寫到硬碟中的記錄行佇列都將會丟失!

下面詳細地描述當你為 INSERTREPLACE 使用 DELAYED 選項時會發生什麼。在這個描述中,“線程”是遇到一個 INSERT DELAYED 命令的線程,“處理器”是處理所有對於一個特定表的 INSERT DELAYED 語句的線程。

注意,如果表沒有在使用中,INSERT DELAYED 將比一個正常的 INSERT 慢。讓伺服器為你使用 INSERT DELAYED 的每張表處理一個單獨的線程,也是有額外的開銷的。這就意味著,你應該在確定你的確需要它時才使用 INSERT DELAYED

6.4.5 UPDATE 句法

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT rows]

or

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

UPDATE 以新的值更新現存表中行的列。SET 幾句指出要修改哪個列和他們應該給定的值。WHERE 幾句如果被給出,指定哪個記錄行應該被更新。否則,所有的記錄行被更新。如果 ORDER BY 幾句被指定,記錄行將被以指定的次序更新。

如果你指定關鍵詞 LOW_PRIORITYUPDATE 的執行將被延遲,直到沒有其它的客戶端正在讀取表。

如果你指定關鍵詞 IGNORE,該更新語句將不會異常中止,即使在更新過程中出現重復鍵錯誤。導致衝突的記錄行將不會被更新。

如果在一個表達式中從 tbl_name 中存取一個列,UPDATE 使用列的目前值。舉例來說,下面的語句設定 age 列值為它的目前值加 1 :

mysql> UPDATE persondata SET age=age+1;

UPDATE 賦值是從左到右計算的。舉例來說,下列語句將 age 列設定為它的兩倍,然後再加 1 :

mysql> UPDATE persondata SET age=age*2, age=age+1;

如果你設定列為其目前的值,MySQL 注意到這點,並不更新它。

UPDATE 傳回實際被改變的記錄行數目。在 MySQL 3.22 或更新的版本中,C API 函數 mysql_info() 傳回被相符並更新的記錄行數目,以及在 UPDATE 期間發生的警告的數目。

在 MySQL 3.23 中,你可以使用 LIMIT # 來確保只有給定的記錄行數目被變更。

如果一個 ORDER BY 幾句被使用(從 MySQL 4.0.0 開始支援),記錄行將以指定的次序被更新。這實際上只有連同 LIMIT 一起才有用。

從 MySQL 4.0.4 開始,你也可以執行一個包含多個表的 UPDATE 的操作:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

注意:多表 UPDATE 不可以使用 ORDER BYLIMIT

6.4.6 DELETE 句法

DELETE [LOW_PRIORITY] [QUICK] FROM table_name
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT rows]

or

DELETE [LOW_PRIORITY] [QUICK] table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]

or

DELETE [LOW_PRIORITY] [QUICK]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]

DELETEtable_name 中刪除 where_definition 中給定條件的記錄行,並傳回刪除的記錄數目。

如果你發出一個沒有 WHERE 幾句的 DELETE,所有的記錄行將被刪除。如果你以 AUTOCOMMIT 樣式執行它,那麼它類似於 TRUNCATE。查看章節 6.4.7 TRUNCATE 句法。在 MySQL 3.23 中,沒有一個 WHERE 幾句的 DELETE 將傳回零作為受影響的記錄數目。

當你刪除所有記錄行時,如果你真的希望知道多少條記錄被刪除,你可以使用一個這種形式的 DELETE 語句:

mysql> DELETE FROM table_name WHERE 1>0;

注意,這將比一個沒有 WHERE 幾句的 DELETE FROM table_name 語句慢,因為它一次只刪除一行。

如果你指定關鍵詞 LOW_PRIORITYDELETE 的執行將被延遲,直到沒有其它的客戶端正在讀取表。

如果你指定關鍵詞 QUICK,那麼在刪除過程中儲存引擎將不會歸並索引葉,這可能會加速某些類型的刪除操作。

MyISAM 表中,刪除了的記錄被放在一個連結表中維護,以後的 INSERT 操作將重新使用刪除後的記錄位置。為了回收閑置的空間,並減小文件尺寸,使用 OPTIMIZE TABLE 語句或 myisamchk 實用程式重新組織表。OPTIMIZE TABLE 使用比較容易,但是 myisamchk 更快點。查看章節 4.5.1 OPTIMIZE TABLE 句法 和章節 4.4.6.10 表最佳化

第一個多表刪除格式從 MySQL 4.0.0 開始被支援。第二個多表刪除格式從 MySQL 4.0.2 開始被支援。

僅僅在 FROMUSING 幾句 之前 列出的表中的相符記錄行被刪除。效果就是,你要以從多個表中同時刪除記錄行,並且同樣可以有其它的表用於檢索。

在表名後的 .* 僅僅是為了兼容 Access

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

在上面的情況下,我們僅僅從 t1t2 表中刪除相符的記錄行。

如果一個 ORDER BY 幾句被使用(從 MySQL 4.0.0 開始支援), 記錄行將以指定的次序刪除。這實際上只有連同 LIMIT 一起才有用。範例如下:

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

這將刪除相符 WHERE 幾句的,並且最早被插入(通過 timestamp 來確定)的記錄行。

DELETE 語句的LIMIT rows 選項是 MySQL 特有的,它告訴伺服器在控制權被傳回到客戶端之前可被刪除的最大記錄行數目。這可以用來確保一個特定的 DELETE 命令不會占用太長的時間。你可以簡單地重復使用 DELETE 命令,直到被影響的記錄行數目小於 LIMIT 值。

從 MySQL 4.0 開始,在 DELETE 語句中可以指定多個表,用以從一個表中刪除依賴於多表中的特殊情況的記錄行。然而,在一個多表刪除中,不能使用 ORDER BYLIMIT

6.4.7 TRUNCATE 句法

TRUNCATE TABLE table_name

在 3.23 中,TRUNCATE TABLE 被映射為 COMMIT ; DELETE FROM table_name。查看章節 6.4.6 DELETE 句法

在下面的方式中,TRUNCATE TABLE 不同於 DELETE FROM ...

TRUNCATE 是一個 Oracle SQL 的擴展。

6.4.8 REPLACE句法

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...

REPLACE 功能與 INSERT 完全一樣,除了如果在表中存在一個老的記錄與新記錄在一個 UNIQUEPRIMARY KEY 上有相同的值,那麼在新記錄被插入之前,老的記錄將被刪除。查看章節 6.4.3 INSERT 句法

換句話說,你不可以從一個 REPLACE 中存取老的記錄行的值。某些老的 MySQL 版本中,你或許可以這樣做,但是這是一個 Bug,現在已被修正了。

為了能夠使用 REPLACE,你必須有對該表的 INSERTDELETE 權限。

當你使用一個 REPLACE 時,如果新的記錄行代替了老的記錄行,mysql_affected_rows() 將傳回 2。這是因為在新行被插入之前,重復記錄行被先刪除了。

這個事實使得判斷 REPLACE 是否是加入一條記錄還是取代一條記錄很容易:檢查受影響記錄行的值是 1 (加入)還是 2(取代)。

注意,除非你使用一個 UNIQUE 索引或 PRIMARY KEY ,使用 REPLACE 命令是沒有感覺的,因為它會僅僅執行一個 INSERT

6.4.9 LOAD DATA INFILE 句法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE number LINES]
    [(col_name,...)]

LOAD DATA INFILE 語句以非常高的速度從一個純文字文件中讀取記錄行並插入到一個表中。如果 LOCAL 關鍵詞被指定,文件從客戶端主機讀取。如果 LOCAL 沒有被指定,文件必須位於伺服器上。(LOCAL 在 MySQL 3.22.6 或更新的版本中被支援。)

由於安全性的原因,當讀取位於伺服器端的純文字文件時,文件必須處於資料庫目錄或可被所有人讀取的地方。同時,為了對伺服器端的文件使用 LOAD DATA INFILE,你必須在伺服器主機上有 FILE 權限。查看章節 4.2.7 由 MySQL 提供的權限

在 MySQL 3.23.49 和 MySQL 4.0.2 中,只有當你沒有以 --local-infile=0 選項啟動 mysqld,或你沒有禁止你的客戶端程式支援 LOCAL的情況下,LOCAL 才會工作。查看章節 4.2.4 LOAD DATA LOCAL 的安全性問題.

如果你指定關鍵詞 LOW_PRIORITYLOAD DATA 語句的執行將會被延遲,直到沒有其它的客戶端正在讀取表。

如果你對一個 MyISAM 表指定關鍵詞 CONCURRENT,那麼當 LOAD DATA正在執行時,其它的線程仍可以從表中檢索資料。使用這個選項時,如果同時也有其它的線程正在使用表,這當然會有一點影響 LOAD DATA 的執行性能。

使用 LOCAL 將比讓伺服器直接存取文件要慢一些,因為文件的內容必須從客戶端主機傳送到伺服器主機。而在另一方面,你不再需要有 FILE 權限用於裝載本地文件。

如果你使用先於 MySQL 3.23.24 的版本,你不能夠以 LOAD DATA INFILE 讀取一個 FIFO 。如果你需要從一個 FIFO (例如,gunzip 的輸出文件) 中讀取,可以使用 LOAD DATA LOCAL INFILE 代替。

你也可以使用 mysqlimport 實用程式裝載資料文件﹔它通過發送一個 LOAD DATA INFILE 命令到伺服器來動作。--local 選項使得 mysqlimport 從客戶端主機讀取資料文件。如果客戶端與伺服器支援壓縮協定,你可以指定 --compress 選項,以在較慢的網路中獲得更好的性能。

當從伺服器主機定位文件時,伺服器使用下列規則:

注意,這些規則意味著,一個以 `./myfile.txt' 給出的文件是從伺服器的資料目錄中讀取的,然而,以 `myfile.txt' 給出的一個文件是從目前資料庫的資料目錄下讀取的。舉例來說,下面的 LOAD DATA 語句從 db1 資料庫目錄下讀取文件 `data.txt',因為 db1 是目前資料庫,即使該語句明確地指定讀取的文件被放入到 db2 資料庫中的一個表中:

mysql> USE db1;
mysql> LOAD DATA INFILE "data.txt" INTO TABLE db2.my_table;

REPLACEIGNORE 關鍵詞控制對與現有的記錄在唯一鍵值上重復的記錄的處理。如果你指定 REPLACE,新的記錄行將取代有相同唯一鍵值的現有記錄行。如果你指定 IGNORE,將跳過與現有的記錄行在唯一鍵值上重復的輸入記錄行。如果你沒有指定任何一個選項,當重復鍵值出現時,將會發生一個錯誤,純文字文件的剩餘部分也將被忽略。

如果你使用 LOCAL 關鍵詞從一個本地文件中讀取資料,在此操作過程中,伺服器沒有辦法停止文件的傳送,因此預設的處理方式就好像是 IGNORE 被指定一樣。

如果你在一個空的 MyISAM 表上使用 LOAD DATA INFILE,所有非唯一索引會以一個分批方式被建立(就像 REPAIR)。當有許多索引時,這通常可以使 LOAD DATA INFILE 更快一些。

LOAD DATA INFILESELECT ... INTO OUTFILE 的逆操作。查看章節 6.4.1 SELECT 句法。 使用 SELECT ... INTO OUTFILE 將資料從一個資料庫寫到一個文件中。使用 LOAD DATA INFILE 讀取文件到資料庫中。兩個命令的 FIELDSLINES 幾句的句法是一樣的。兩個幾句都是可選的,但是如果兩個同時被指定,FIELDS 幾句必須出現在 LINES 幾句之前。

如果你指定一個 FIELDS 幾句,它的幾句 (TERMINATED BY[OPTIONALLY] ENCLOSED BYESCAPED BY) 也是可選的,不過,你必須至少指定它們中的一個。

如果你沒有指定一個 FIELDS 幾句,預設的相同於如果你這樣寫:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

如果你沒有指定一個 LINES 幾句,預設的相同於如果你這樣寫:

LINES TERMINATED BY '\n'

換句話說,當讀取輸入時,預設值導致 LOAD DATA INFILE 表現如下:

相反的,當寫入輸出時,預設值導致 SELECT ... INTO OUTFILE 表現如下:

注意,為了寫 FIELDS ESCAPED BY '\\',你必須指定兩個反斜線,該值會作為一個反斜線被讀入。

IGNORE number LINES 選項可被用於忽略文件開頭處的一個列名的頭:

mysql> LOAD DATA INFILE "/tmp/file_name" INTO TABLE test IGNORE 1 LINES;

當你一前一後地使用 SELECT ... INTO OUTFILELOAD DATA INFILE 將資料從一個資料庫寫到一個文件中,然後再從文件中將它讀入資料庫中時,兩個命令的欄位和行處理選項必須相符。否則,LOAD DATA INFILE 將不能正確地解釋文件內容。假設你使用 SELECT ... INTO OUTFILE 以逗號分隔欄位的方式將資料寫入到一個文件中:

mysql> SELECT * INTO OUTFILE 'data.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM ...;

為了將由逗號分隔的文件讀回時,正確的語句應該是:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY ',';

如果你試圖用下面所示的語句讀取文件,它將不會工作,因為命令 LOAD DATA INFILE 以定位符區分欄位值:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY '\t';

可能的結果是每個輸入行將被解釋為一個單獨的欄位。

LOAD DATA INFILE 也可以被用來讀取從外部來源獲得的文件。例如,dBASE 格式的文件,欄位以逗號分隔並以雙引號包圍著。如果文件中的行以一個換行符終止,那麼下面所示的可以說明你將用來裝載文件的欄位和行處理選項:

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

任何欄位和行處理選項都可以指定一個空字串('')。如果不是空的,FIELDS [OPTIONALLY] ENCLOSED BYFIELDS ESCAPED BY 值必須是一個單個字元。FIELDS TERMINATED BYLINES TERMINATED BY 值可以超過一個字元。例如,為了寫入由回車換行符終止的行,或讀取包含這樣的行的文件,應該指定一個 LINES TERMINATED BY '\r\n' 幾句。

舉例來說,為了讀取一個文件到一個 SQL 表中,文件以一行 %% 分隔(開玩笑的),你可以這樣做:

CREATE TABLE jokes (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, joke TEXT
NOT NULL);
LOAD DATA INFILE "/tmp/jokes.txt" INTO TABLE jokes FIELDS TERMINATED BY ""
LINES TERMINATED BY "\n%%\n" (joke);

FIELDS [OPTIONALLY] ENCLOSED BY 控制欄位的包圍字元。對於輸出 (SELECT ... INTO OUTFILE),如果你省略單詞 OPTIONALLY,所有的欄位被 ENCLOSED BY 字元包圍。這樣的一個輸出文件(以一個逗號作為欄位分界符)範例如下:

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

如果你指定 OPTIONALLYENCLOSED BY 字元僅被作用於包圍 CHARVARCHAR 欄位:

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

注意,在一個欄位值中出現的 ENCLOSED BY 字元,通過用 ESCAPED BY 字元作為其前綴對其轉義。同時也要注意,如果你指定一個空的 ESCAPED BY 值,可能會產生不能被 LOAD DATA INFILE 正確讀出的輸出文件。例如,如果轉義字元為空,上面顯示的輸出將變成如下顯示的輸出。請注意第四行的第二個欄位,它包含一個逗號跟在一個引號後的兩個字元,這(錯誤的)看起來像是一個欄位的終止:

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

對於輸入,ENCLOSED BY 字元如果存在,它將從欄位值的尾部被剝離。(不管 OPTIONALLY 是否被指定,都是這樣﹔對於輸入解釋,OPTIONALLY 不會影響它。) 由ESCAPED BY 字元領先於 ENCLOSED BY 字元的出現,將被解釋為目前欄位值的一部分。另外,在欄位中出現的重復的 ENCLOSED BY 字元被解釋為單個 ENCLOSED BY ,只要欄位本身也是以該字元開始的。例如,如果 ENCLOSED BY '"' 被指定,引號將做如下處理:

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY 控制如何寫入或讀出特殊字元。如果 FIELDS ESCAPED BY 字元不是空的,它將被用於做為下列輸出字元的前綴:

如果 FIELDS ESCAPED BY 字元為空,沒有字元被轉義。指定一個空的轉義字元可能不是一個好的主意,特別是如果你的資料欄位值中包含剛才清單中的任何字元時。

對於輸入,如果 FIELDS ESCAPED BY 字元不為空,該字元的出現將會被剝離,後續的字元在字面上做為欄位值的一部分。除了一個轉義的 “0”“N” (即,\0\N,如果轉義字元為 `\')。這些序列被解釋為 ASCII 0 (一個零值字節) 和 NULL。查看下面的有關 NULL 處理的規則。

關於更多的 “\” 轉義句法資訊,查看章節 6.1.1 文字:怎麼寫字串與數位

在某些情況下,欄位與行處理相互作用:

NULL 值的處理有很多,取決於你所使用的 FIELDSLINES 選項:

一些不能被 LOAD DATA INFILE 支援的情況:

下面的範例將裝載 persondata 表的所有列:

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

沒有欄位列被指定,因而 LOAD DATA INFILE 認為輸入行包含表列中所有的欄位。使用預設的 FIELDSLINES 值。

如果你希望裝載表中的某些列,那指定一個欄位清單:

mysql> LOAD DATA INFILE 'persondata.txt'
    ->           INTO TABLE persondata (col1,col2,...);

如果輸入文件的欄位次序不同於表中列的順序,你也必須指定一個欄位清單。否則 MySQL 不知道如何將輸入欄位與表中的列相符。

如果一個行有很少的欄位,沒有輸入欄位的列將被設定為預設值。預設值賦值在章節 6.5.3 CREATE TABLE 句法 中被描述。

一個空的欄位值不同於欄位值丟失的解釋:

注意,如果在一個 INSERTUPDATE 語句中明確地將一個空字串賦給一個字串、數位或日期或時間類型,你會得到與上面相同的結果。

如果對 TIMESTAMP 列指定一個 NULL 值,或者當欄位清單被指定時, TIMESTAMP 在欄位清單中被遺漏(僅僅第一個 TIMESTAMP 列被影響),TIMESTAMP 列會被設定為目前的日期和時間。

如果輸入的記錄行有太多的欄位,多餘的欄位將被忽略,並增加警告的數目。

LOAD DATA INFILE 認為所有的輸入均是字串,因而,對於 ENUMSET 列,你不能以 INSERT 語句的形式為其設定數位值。所有的 ENUMSET 必須以字串指定!

如果你正在使用 C API,當 LOAD DATA INFILE 查詢結束時,你可以調用 API 函數 mysql_info() 獲得有關查詢的資訊。資訊串的格式如下:

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

警告會在某些情況下發生,這些情況與值通過 INSERT 語句插入時發生警告的情況一樣 (查看章節 6.4.3 INSERT 句法),但是 LOAD DATA INFILE 有一點與它不一樣,當在輸入行中有太多或過少的欄位,它也會產生警告。警告不會被儲存在任何地主﹔警告的數目僅能表示一切是否順利。如果得到警告,並希望確切地知道為什麼會得到它們,一個方法就是使用 SELECT ... INTO OUTFILE,將它保存到另外一個文件中,並與原先的輸入文件進行比較。

如果你需要 LOAD DATA 從一個管道中讀取,你可以使用下面的技巧:

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

如果你使用的版本早於 MySQL 3.23.25,你只能通過 LOAD DATA LOCAL INFILE 來執行上面。

有關 INSERT 相對 LOAD DATA INFILE 的效率和加快 LOAD DATA INFILE 的更多資訊,請查看章節 5.2.9 INSERT 查詢的速度

6.4.10 DO 句法

DO expression, [expression, ...]

執行表達式,但不傳回任何結果。這是 SELECT expression, expression 的一個縮寫,但是當你並不關心結果時,它稍有點優勢,因為它稍稍快一點。

這主要有益於有副作用的函數,比如 RELEASE_LOCK