MySQL Reference Manual for version 4.1.0-alpha.


6 MySQL 語言參考

MySQL 擁有一個復雜的但直觀易學的 SQL 接口。這個章節描述了各種不同的命令、類型和函數,為了高效地使用 MySQL 需要了解它們。這個章節也可以視為 MySQL 中包含的所有功能的參考。

6.1 語言結構

6.1.1 文字:怎麼寫字串與數位

這個章節描述了在 MySQL 中書寫字串與數位的各種不同方式。也包含有對各種不同差別和“gotchas”,當你在處理 MySQL 的基本類型時可能陷入的疑惑。

6.1.1.1 字串

字串是多個字元組成的一個字元序列,由單引號(“'”) 或雙引號 (“"”) 字元包圍。(但在 ANSI 樣式中運行時只能用單引號)。

例如:

'a string'
"another string"

在一個字串中,如果某個序列具有特殊的含義,每個序列以反斜線符號 (“\”)開頭,稱為轉義字元。 MySQL 辨識下列轉義字元:

\0
一個 ASCII 0 (NUL) 字元。
\'
一個 ASCII 39 單引號 (“'”) 字元。
\"
一個 ASCII 34 雙引號 (“"”) 字元。
\b
一個 ASCII 8 退格符。
\n
一個 ASCII 10 換行符。
\r
一個 ASCII 13 回車符。
\t
一個 ASCII 9 制表符(TAB)。
\z
ASCII(26) (Control-Z)。這個字元可以處理在 Windows 系統中 ASCII(26) 代表一個文件的結束的問題。(當使用 mysql database < filename 時 ASCII(26) 可能會引起問題產生。)
\\
一個 ASCII 92 反斜線 (“\”) 字元。
\%
一個 ASCII 37 “%” 字元。它用於在正文中搜尋“%”的文字案例,否則這裡“%”將解釋為一個通配符。查看章節 6.3.2.1 字串比較函數
\_
一個 ASCII 95 “_” 字元。它用於在正文中搜尋“_”的文字案例,否則這裡“_”將解釋為一個通配符。查看章節 6.3.2.1 字串比較函數.

注意如果在某些正文環境內使用 “\%”“\_”,將傳回字串 “\%”“\_” 而不是 “%”“_”

字串中包含引號的可以有下列幾種寫法:

下面顯示的 SELECT 演示引號和轉義是如何工作:

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "This\nIs\nFour\nlines";
+--------------------+
| This
Is
Four
lines |
+--------------------+

如果你想要把二進位資料插入到一個字元類型的欄位中(例如BLOB),下列字元必須由轉義序清單示:

NUL
ASCII 0,你應該用 “\0”(一個反斜線和一個ASCII “0”字元)表示它。
\
ASCII 92,反斜線。需要用 “\\” 表示。
'
ASCII 39,單引號。需要用 “\'” 表示。
"
ASCII 34,雙引號。需要用 “\"” 表示。

如果你寫 C 代碼,你可以使用 C API 函數mysql_real_escape_string() 來為 INSERT 語句轉義字元。查看章節 8.1.2 C API 函數概述。在 Perl 中,你可以使用 DBI 包中的 quote 方法來將這些特殊字元轉換成適當的轉義序列。查看章節 8.5.2 DBI 接口

你應該在任何可能包含上述特殊字元的字串中使用轉義函數!

另外,很多 MySQL API 提供了一些占位符處理能力,這允許你在查詢語句中插入特殊標記,然後在執行查詢時對它們綁定資料值。這樣,API 會自動為你從數值中轉換它們。

6.1.1.2 數位

整數被表示為一個數位序列。浮點數使用 “.” 作為一個十進制的分隔符。這兩種類型的數位可以前置 `-' 以表示一個負值。

有效整數的範例:

1221
0
-32

有效浮點數的範例:

294.42
-32032.6809e+10
148.00

一個整數可以在浮點語境中使用﹔它被解釋為等值的浮點數。

6.1.1.3 十六進制值

MySQL 支援十六進制數值。在數位的語境中,它們表現類似於一個整數(64位精度)。在字串的語境中,它們表現類似於一個二進位的字串,每一對十六進制數位被轉換為一個字元:

mysql> SELECT x'4D7953514C';
         -> MySQL
mysql> SELECT 0xa+0;
         -> 10
mysql> SELECT 0x5061756c;
         -> Paul

表達式 x'hexstring' (4.0 中新加入) 是基於 ANSI SQL 的,表達式 0x 是基於 ODBC 的。十六進制的字串通常被 ODBC 用於為 BLOB 類型欄位賦值。你可以通過 HEX() 將一個字串或一個數值轉化為十六進制格式。

6.1.1.4 NULL

NULL 值並不意味著“無資料”,並且是不同於例如數位類型的 0 或字串類型的空串。查看章節 A.5.3 NULL 值問題

當使用純文字文件匯入與匯出格式時 (LOAD DATA INFILESELECT ... INTO OUTFILE),NULL 可以用 \N 來描述。查看章節 6.4.9 LOAD DATA INFILE 句法.

6.1.2 資料庫、表、索引、列和別名

資料庫、表、索引、列和別名都需遵守 MySQL 同樣的規則。

注意,從 MySQL 3.23.6 開始,規則發生改變了,此時引進了用“`”參照標識符(資料庫、表和欄位名)。如果你以 ANSI 樣式運行,“"” 也可以用於參照標識符。查看章節 1.8.2 以 ANSI 樣式運行 MySQL

標識符 最大長度 允許的字元
資料庫 64 一個目錄名中允許的任何字元,除了“/”“\”“.”
64 一個文件名中允許的任何字元,除了 “/”“.”
64 所有的字元。
別名 255 所胡的字元。

注意,除了上面的,在一個標識符中還不能有 ASCII(0) 或 ASCII(255) 或參照字元。

注意,如果標識符是一個受限掉的詞或包含特殊的字元,當使用它時,必須以一個 ` (backtick) 來參照它。

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

查看章節 6.1.7 MySQL 對保留字挑剔嗎?

在 MySQL 3.23.6 以前的版本中,命名規則如下:

建議不要使用象 1e 這樣的名字,因為一個表達式 1e+1 是存在二義性的。它可以被解釋為表達式 1e + 1 或數位 1e+1

在 MySQL 中,你可以使用下清單格中的任一種方式參照一個列:

列參照 含義
col_name col_name 來自查詢所用的任何一個表中對應欄位
tbl_name.col_name col_name 來自目前資料庫中的表 tbl_name
db_name.tbl_name.col_name col_name 來自資料庫 db_name 中的表 tbl_name。這個形式從 MySQL 3.22 或以後版本開始可用。
`column_name` 該欄位是一個關鍵詞或包含特殊字元。

在一條語句中的列參照中,不需要明確指定一個 tbl_namedb_name.tbl_name 前綴,除非這個參照存在二義性。例如,假設表 t1t2 均包含一個欄位 c,當用一個使用了 t1t2SELECT 檢索 c 時。在這種情況下,c 存在二義性,因為它在這個語句所使用的表中不是唯一的,因而必須通過寫出 t1.ct2.c 來指明你所需的是哪個表。同樣的,如果從資料庫 db1 的表 t 和資料庫 db2 的表 t 中檢索,你必須用db1.t.col_namedb2.t.col_name 來指定參照哪個庫表的列。

句法 .tbl_name 意味著表 tbl_name 在目前資料庫中。這個句法是為了與 ODBC 兼容,因為一些 ODBC 程式以一個 “.” 字元作為表名的前綴。

6.1.3 名字的大小寫敏感性

在 MySQL 中,資料庫和表對就於那些目錄下的目錄和文件。因而,操作系統的敏感性決定資料庫和表命名的大小寫敏感。這就意味著資料庫和表名在 Windows 中是大小寫不敏感的,而在大多數類型的 Unix 系統中是大小寫敏感的。一個特例是 Mac OS X,當預設的 HFS+ 文件系統使用時。然而 Mac OS X 還支援 UFS 卷,那些在 Mac OS X 是大小寫敏感的就如他們在任一 Unix 上一樣。查看章節 1.8.3 MySQL 對 ANSI SQL92 的擴展

注意:盡管在 Windows 中資料庫與表名是忽略大小寫的,你不應該在同一個查詢中使用不同的大小寫來參照一個給定的資料庫和表。下面的查詢將不能工作,因為它以 my_tableMY_TABLE 參照一個表:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

列名與列的別名在所有的情況下均是忽略大小寫的。

表的別名是區分大小寫的。下面的查詢將不能工作,因為它用 aA 參照別名:

mysql> SELECT col_name FROM tbl_name AS a
    ->                 WHERE a.col_name = 1 OR A.col_name = 2;

如果記憶資料庫和表名的字母大小寫有困難,建議採用一個一致一約定,例如總是以小寫字母建立資料庫和表。

避免這個問題的另一個辦法就是以 -O lower_case_table_names=1 參數啟動 mysqld。預設地在 Windows 中這個選項為 1 ,在 Unix 中為 0。

如果 lower_case_table_names 為 1 ,MySQL 將在儲存與尋找時將所有的表名轉換為小寫字線。(從 MySQL 4.0.2 開始,這個選項同樣適用於資料庫名。) 注意,當你變更這個選項時,你必須在啟動 mysqld 前首先將老的表名轉換為小寫字母。

如果將 MyISAM 從 Windows 移動到一個 Unix 硬碟中,在某些情況下你可能需要使用 “mysql_fix_extensions” 工具來修正指定資料庫目錄下的文件副檔名(小寫字母 “.frm”,大寫字母 “.MYI”“.MYD”)。“mysql_fix_extensions” 存放在 “scripts” 幾目錄下。

6.1.4 使用者變數

MySQL 支援連線特定(connection-specific)的使用者變數,用 @variablename 句法表示。一個變數名可以由目前字元集中包含的文字與數位字元以及 “_”“$”“.” 組成。缺少的字元集為 ISO-8859-1 Latin1﹔這可以通過改變 mysqld--default-character-set 的選項來改變。查看章節 4.6.1 用於資料和排序的字元集

變數不必被初始化。預設地,他們的值為 NULL 並可以儲存一個整數、實數或字串值。當連線線程退出時,這個線程的所有變數將會自動地被釋放。

你可以通過 SET 句法來設定一個變數:

SET @variable= { integer expression | real expression | string expression }
[,@variable= ...].

在語句中除了 SET 之外還可以直接為一個變數賦值。然而在這各情況下,賦值運算子為 := 而不是 =,因為 = 在非 SET 語句中是用於比較的:

mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

使用者變數可以用於表達式所允許的任何地方。注意,這在數位必須明確指定的語境中並不適用,例如,在 SELECTLIMIT 幾句中或一個 LOAD DATA 語句的 IGNORE number LINES 的幾句中。

注意:在一個 SELECT 語句中,各個表達式只有在它被送到客戶端時才能被求值。這就意味著,在 HAVINGGROUP BYORDER BY 幾句中,你不能使用一個包含在 SELECT 部份所設定變數的表達式。例如:下面的語句將不會按預期的運作:

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM table_name HAVING b=5;

原因是因為 @aa 不會是目前行的值,而是前一個符合條件的行的 id 值。

規則就是在同一語句中決不賦值 使用同一個變數。

6.1.5 系統變數

從 MySQL 4.0.3 開始,我們提供了對大量的系統變數和連線變數的更好的存取方式。你可以不需要關閉伺服器就可以變更其中的大部變數值。

系統變數可分為兩種類型:線程特定(Thread-specific)或稱為連線特定(connection-specific)變數,它們是目前連線唯一的﹔全局變數,它們用於設定全局事件。全局變數也同樣被用於設定一個新連線的相應線程特定變數的初始值。

mysqld 啟動時,所有的全局變數以命令行參數和選項文件內容初始化。可以通過 SET GLOBAL 命令變更這些值。當一個新的連線線程被建立時,將以全局變數值初始化線程特定變數,直到你執行一個新的 SET GLOBAL 命令時,線程特定變數才會改變。

為了設定一個 全局(GLOBAL) 變數值,可以使用下面的任一句法:(在這裡,我們以 sort_buffer_size 變數作為一個範例)

SET GLOBAL sort_buffer_size=value;
SET @@global.sort_buffer_size=value;

為了設定一個 會話(SESSION) 變數的值,可以使用下面任一句法:

SET SESSION sort_buffer_size=value;
SET @@session.sort_buffer_size=value;
SET sort_buffer_size=value;

如果你沒有明確指定 GLOBALSESSION,那麼預設地將是設定 SESSION。查看章節 5.5.6 SET 句法

LOCALSESSION 的同義詞。

通過下面的任一命令可以檢索到一個 全局(GLOBAL) 變數值:

SELECT @@global.sort_buffer_size;
SHOW GLOBAL VARIABLES like 'sort_buffer_size';

通過下面的任一命令可以檢索到一個 會話(SESSION) 變數值:

SELECT @@session.sort_buffer_size;
SHOW SESSION VARIABLES like 'sort_buffer_size';

檢索 一個變數值時使用 @@variable_name 句法,或沒有指定 GLOBALSESSION 時,如果線程特定(thread-specific)的 (SESSION) 值存在,MySQL 將傳回它。如果不存在,那麼 MySQL 將傳回全局變數值。

在設定 全局(GLOBAL) 變數而不是在檢索他們的時候需要使用 GLOBAL ,是為了在之後參照一個同名的線程特定(thread-specific)變數或刪除同名的一個線程特定(thread-specific)變數時不至發生問題。在這種情況下,你可能無意間改變整個伺服器的狀態而不是你自己的連線。

下面的清單是你可以使用 GLOBALSESSION 對它們進行變更和檢索的所有變數。

變數名 變數值類型 變數類型
autocommit bool SESSION
big_tables bool SESSION
binlog_cache_size num GLOBAL
bulk_insert_buffer_size num GLOBAL | SESSION
concurrent_insert bool GLOBAL
connect_timeout num GLOBAL
convert_character_set string SESSION
delay_key_write OFF | ON | ALL GLOBAL
delayed_insert_limit num GLOBAL
delayed_insert_timeout num GLOBAL
delayed_queue_size num GLOBAL
error_count num LOCAL
flush bool GLOBAL
flush_time num GLOBAL
foreign_key_checks bool SESSION
identity num SESSION
insert_id bool SESSION
interactive_timeout num GLOBAL | SESSION
join_buffer_size num GLOBAL | SESSION
key_buffer_size num GLOBAL
last_insert_id bool SESSION
local_infile bool GLOBAL
log_warnings bool GLOBAL
long_query_time num GLOBAL | SESSION
low_priority_updates bool GLOBAL | SESSION
max_allowed_packet num GLOBAL | SESSION
max_binlog_cache_size num GLOBAL
max_binlog_size num GLOBAL
max_connect_errors num GLOBAL
max_connections num GLOBAL
max_error_count num GLOBAL | SESSION
max_delayed_threads num GLOBAL
max_heap_table_size num GLOBAL | SESSION
max_join_size num GLOBAL | SESSION
max_sort_length num GLOBAL | SESSION
max_tmp_tables num GLOBAL
max_user_connections num GLOBAL
max_write_lock_count num GLOBAL
myisam_max_extra_sort_file_size num GLOBAL | SESSION
myisam_max_sort_file_size num GLOBAL | SESSION
myisam_sort_buffer_size num GLOBAL | SESSION
net_buffer_length num GLOBAL | SESSION
net_read_timeout num GLOBAL | SESSION
net_retry_count num GLOBAL | SESSION
net_write_timeout num GLOBAL | SESSION
query_cache_limit num GLOBAL
query_cache_size num GLOBAL
query_cache_type enum GLOBAL
read_buffer_size num GLOBAL | SESSION
read_rnd_buffer_size num GLOBAL | SESSION
rpl_recovery_rank num GLOBAL
safe_show_database bool GLOBAL
server_id num GLOBAL
slave_compressed_protocol bool GLOBAL
slave_net_timeout num GLOBAL
slow_launch_time num GLOBAL
sort_buffer_size num GLOBAL | SESSION
sql_auto_is_null bool SESSION
sql_big_selects bool SESSION
sql_big_tables bool SESSION
sql_buffer_result bool SESSION
sql_log_binlog bool SESSION
sql_log_off bool SESSION
sql_log_update bool SESSION
sql_low_priority_updates bool GLOBAL | SESSION
sql_max_join_size num GLOBAL | SESSION
sql_quote_show_create bool SESSION
sql_safe_updates bool SESSION
sql_select_limit bool SESSION
sql_slave_skip_counter num GLOBAL
sql_warnings bool SESSION
table_cache num GLOBAL
table_type enum GLOBAL | SESSION
thread_cache_size num GLOBAL
timestamp bool SESSION
tmp_table_size enum GLOBAL | SESSION
tx_isolation enum GLOBAL | SESSION
version string GLOBAL
wait_timeout num GLOBAL | SESSION
warning_count num LOCAL
unique_checks bool SESSION

num 標記的變數可以設定一個數位值。以 bool 標記的變數可以設定 0、1、ONOFFenum 類型的變數通常是設定為該變數的某一個可用值,但也可以設定為相對應的數位。(enum 的第一個值為 0)。

下面是某些變數的描述:

變數 描述
identity last_insert_id 的別名 (Sybase 兼容)
sql_low_priority_updates low_priority_updates 的別名
sql_max_join_size max_join_size 的別名
delay_key_write_for_all_tables 如它與 delay_key_write 一起被設定,那麼所有新打開的 MyISAM 表將使用 delayed key writes.
version VERSION() 的別名 (Sybase (?) 兼容)

在啟動選項章節中可以找到其它的變數的描述,SHOW VARIABLES 的描述在 SET 部分。查看章節 4.1.1 mysqld 命令行選項。查看章節 4.5.6.4 SHOW VARIABLES。查看章節 5.5.6 SET 句法

6.1.6 註釋句法

MySQL 伺服器支援 # 到該行結束-- 到該行結束 以及 /* 行中間或多個行 */ 的註釋方格:

mysql> SELECT 1+1;     # 這個註釋直到該行結束
mysql> SELECT 1+1;     -- 這個註釋直到該行結束
mysql> SELECT 1 /* 這是一個在行中間的註釋 */ + 1;
mysql> SELECT 1+
/*
這是一個
多行註釋的形式
*/
1;

注意 -- (雙長劃) 註釋風格要求在兩個長劃後至少有一個空格!

盡管伺服器理解剛才描述的註釋句法,但 MySQL 客戶端的語法分析在 /* ... */ 註釋方式上還有所限止:

不論你是以交談式運行 mysql 還是將命令放在一個文件中,然後以 mysql < some-file 告訴 mysql 讀取它的輸入,這個限制均存在。

MySQL 支援 `--' ANSI SQL 註釋風格,但在兩個長劃後必須跟有一個空格。查看章節 1.8.4.7 `--' 作為一個註釋的開始

6.1.7 MySQL 對保留字挑剔嗎?

一個常見的問題來於試圖使用 MySQL 內建的資料類型或函數名作為表的欄位名來建立資料表,例如 TIMESTAMPGROUP。但是,允許你這樣做(例如,ABS 是一個允許的列名),當使用函數名也是列名的函數時,函數名與後面跟著的 “(” 之間不允許存在空格。

下面的詞在 MySQL 中是被明確保留的。它們大多數被 ANSI SQL92 禁止作為列或表名(例如, GROUP)。一些被保留是因為 MySQL 需要它們並且它使用的是一個 yacc 語法分析器:

單詞 單詞 單詞
ADD ALL ALTER
ANALYZE AND AS
ASC AUTO_INCREMENT BDB
BEFORE BERKELEYDB BETWEEN
BIGINT BINARY BLOB
BOTH BTREE BY
CASCADE CASE CHANGE
CHAR CHARACTER CHECK
COLLATE COLUMN COLUMNS
CONSTRAINT CREATE CROSS
CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP
DATABASE DATABASES DAY_HOUR
DAY_MINUTE DAY_SECOND DEC
DECIMAL DEFAULT DELAYED
DELETE DESC DESCRIBE
DISTINCT DISTINCTROW DIV
DOUBLE DROP ELSE
ENCLOSED ERRORS ESCAPED
EXISTS EXPLAIN FALSE
FIELDS FLOAT FOR
FORCE FOREIGN FROM
FULLTEXT FUNCTION GRANT
GROUP HASH HAVING
HIGH_PRIORITY HOUR_MINUTE HOUR_SECOND
IF IGNORE IN
INDEX INFILE INNER
INNODB INSERT INT
INTEGER INTERVAL INTO
IS JOIN KEY
KEYS KILL LEADING
LEFT LIKE LIMIT
LINES LOAD LOCALTIME
LOCALTIMESTAMP LOCK LONG
LONGBLOB LONGTEXT LOW_PRIORITY
MASTER_SERVER_ID MATCH MEDIUMBLOB
MEDIUMINT MEDIUMTEXT MIDDLEINT
MINUTE_SECOND MOD MRG_MYISAM
NATURAL NOT NULL
NUMERIC ON OPTIMIZE
OPTION OPTIONALLY OR
ORDER OUTER OUTFILE
PRECISION PRIMARY PRIVILEGES
PROCEDURE PURGE READ
REAL REFERENCES REGEXP
RENAME REPLACE REQUIRE
RESTRICT RETURNS REVOKE
RIGHT RLIKE RTREE
SELECT SET SHOW
SMALLINT SOME SONAME
SPATIAL SQL_BIG_RESULT SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT SSL STARTING
STRAIGHT_JOIN STRIPED TABLE
TABLES TERMINATED THEN
TINYBLOB TINYINT TINYTEXT
TO TRAILING TRUE
TYPES UNION UNIQUE
UNLOCK UNSIGNED UPDATE
USAGE USE USER_RESOURCES
USING VALUES VARBINARY
VARCHAR VARCHARACTER VARYING
WARNINGS WHEN WHERE
WITH WRITE XOR
YEAR_MONTH ZEROFILL

以下的符號(來自上表)是被 ANSI SQL 禁止的,但是可以被 MySQL 用於列/表名。這是由於用他們命名是很自然的並且有很多的人已經使用了它們。