7.21 SHOW句法 (得到表,列等的信息)
SHOW DATABASES [LIKE wild]
or SHOW TABLES [FROM db_name] [LIKE wild]
or SHOW COLUMNS FROM tbl_name [FROM db_name] [LIKE wild]
or SHOW INDEX FROM tbl_name [FROM db_name]
or SHOW STATUS
or SHOW VARIABLES [LIKE wild]
or SHOW [FULL] PROCESSLIST
or SHOW TABLE STATUS [FROM db_name] [LIKE wild]
or SHOW GRANTS FOR user
SHOW提供關于數據庫、桌子、列或服務器的信息。如果使用LIKE wild部分,wild字符串可以是一個使用SQL的“%”和“_”通配符的字符串。
你能使用db_name.tbl_name作為tbl_name FROM db_name句法的另一種選擇。這兩個語句是相等的:
mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;
SHOW DATABASES列出在MySQL服務器主機上的數據庫。你也可以用mysqlshow命令得到這張表。
SHOW TABLES列出在一個給定的數據庫中的表。你也可以用mysqlshow db_name命令得到這張表。
注意:如果一個用戶沒有一個表的任何權限,表將不在SHOW TABLES或mysqlshow db_name中的輸出中顯示。
SHOW COLUMNS列出在一個給定表中的列。如果列類型不同于你期望的是基于CREATE TABLE語句的那樣,注意,MySQL有時改變列類型。見7.7.1 隱含的列說明變化。
DESCRIBE語句提供了類似SHOW COLUMNS的信息。見7.23 DESCRIBE 句法 (得到列的信息)。
SHOW TABLE STATUS(在版本3.23引入)運行類似SHOW STATUS,但是提供每個表的更多信息。你也可以使用mysqlshow --status db_name命令得到這張表。下面的列被返回:
列 含義
Name 表名
Type 表的類型 (ISAM,MyISAM或HEAP)
Row_format 行存儲格式 (固定, 動態, 或壓縮)
Rows 行數量
Avg_row_length 平均行長度
Data_length 數據文件的長度
Max_data_length 數據文件的最大長度
Index_length 索引文件的長度
Data_free 已分配但未使用了字節數
Auto_increment 下一個 autoincrement(自動加1)值
Create_time 表被創造的時間
Update_time 數據文件最後更新的時間
Check_time 最後對表運行一個檢查的時間
Create_options 與CREATE TABLE一起使用的額外選項
Comment 當創造表時,使用的注釋 (或為什麼MySQL不能存取表信息的一些信息)。
SHOW FIELDS是SHOW COLUMNS一個同義詞,SHOW KEYS是SHOW INDEX一個同義詞。你也可以用mysqlshow db_name tbl_name或mysqlshow -k db_name tbl_name 列出一張表的列或索引。
SHOW INDEX以非常相似于ODBC的SQLStatistics調用的格式返回索引信息。下面的列被返回:
列 含義
Table 表名
Non_unique 0,如果索引不能包含重複。
Key_name 索引名
Seq_in_index 索引中的列順序號, 從 1 開始。
Column_name 列名。
Collation 列怎樣在索引中被排序。在MySQL中,這可以有值A(升序) 或NULL(不排序)。
Cardinality 索引中唯一值的數量。這可通過運行isamchk -a更改.
Sub_part 如果列只是部分被索引,索引字符的數量。NULL,如果整個鍵被索引。
SHOW STATUS提供服務器的狀態信息(象mysqladmin extended-status一樣)。輸出類似于下面的顯示,盡管格式和數字可以有點不同:
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Connections | 17 |
| Created_tmp_tables | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 2 |
| Handler_delete | 2 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_next | 0 |
| Handler_read_rnd | 35 |
| Handler_update | 0 |
| Handler_write | 2 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 1 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 1 |
| Open_files | 2 |
| Open_streams | 0 |
| Opened_tables | 11 |
| Questions | 14 |
| Slow_queries | 0 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 149111 |
+--------------------------+--------+
上面列出的狀態變量有下列含義:
Aborted_clients 由于客戶沒有正確關閉連接已經死掉,已經放棄的連接數量。
Aborted_connects 嘗試已經失敗的MySQL服務器的連接的次數。
Connections 試圖連接MySQL服務器的次數。
Created_tmp_tables 當執行語句時,已經被創造了的隱含臨時表的數量。
Delayed_insert_threads 正在使用的延遲插入處理器線程的數量。
Delayed_writes 用INSERT DELAYED寫入的行數。
Delayed_errors 用INSERT DELAYED寫入的發生某些錯誤(可能重複鍵值)的行數。
Flush_commands 執行FLUSH命令的次數。
Handler_delete 請求從一張表中刪除行的次數。
Handler_read_first 請求讀入表中第一行的次數。
Handler_read_key 請求數字基于鍵讀行。
Handler_read_next 請求讀入基于一個鍵的一行的次數。
Handler_read_rnd 請求讀入基于一個固定位置的一行的次數。
Handler_update 請求更新表中一行的次數。
Handler_write 請求向表中插入一行的次數。
Key_blocks_used 用于關鍵字緩存的塊的數量。
Key_read_requests 請求從緩存讀入一個鍵值的次數。
Key_reads 從磁盤物理讀入一個鍵值的次數。
Key_write_requests 請求將一個關鍵字塊寫入緩存次數。
Key_writes 將一個鍵值塊物理寫入磁盤的次數。
Max_used_connections 同時使用的連接的最大數目。
Not_flushed_key_blocks 在鍵緩存中已經改變但是還沒被清空到磁盤上的鍵塊。
Not_flushed_delayed_rows 在INSERT DELAY隊列中等待寫入的行的數量。
Open_tables 打開表的數量。
Open_files 打開文件的數量。
Open_streams 打開流的數量(主要用于日志記載)
Opened_tables 已經打開的表的數量。
Questions 發往服務器的查詢的數量。
Slow_queries 要花超過long_query_time時間的查詢數量。
Threads_connected 當前打開的連接的數量。
Threads_running 不在睡眠的線程數量。
Uptime 服務器工作了多少秒。
關于上面的一些注釋:
* 如果Opened_tables太大,那麼你的table_cache變量可能太小。
* 如果key_reads太大,那麼你的key_cache可能太小。緩存命中率可以用key_reads/key_read_requests計算。
* 如果Handler_read_rnd太大,那麼你很可能有大量的查詢需要MySQL掃描整個表或你有沒正確使用鍵值的聯結(join)。
SHOW VARIABLES顯示出一些MySQL系統變量的值,你也能使用mysqladmin variables命令得到這個信息。如果缺省值不合適,你能在mysqld啟動時使用命令行選項來設置這些變量的大多數。輸出類似于下面的顯示,盡管格式和數字可以有點不同:
+------------------------+--------------------------+
| Variable_name | Value |
+------------------------+--------------------------+
| back_log | 5 |
| connect_timeout | 5 |
| basedir | /my/monty/ |
| datadir | /my/monty/data/ |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| join_buffer_size | 131072 |
| flush_time | 0 |
| interactive_timeout | 28800 |
| key_buffer_size | 1048540 |
| language | /my/monty/share/english/ |
| log | OFF |
| log_update | OFF |
| long_query_time | 10 |
| low_priority_updates | OFF |
| max_allowed_packet | 1048576 |
| max_connections | 100 |
| max_connect_errors | 10 |
| max_delayed_threads | 20 |
| max_heap_table_size | 16777216 |
| max_join_size | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| net_buffer_length | 16384 |
| port | 3306 |
| protocol-version | 10 |
| record_buffer | 131072 |
| skip_locking | ON |
| socket | /tmp/mysql.sock |
| sort_buffer | 2097116 |
| table_cache | 64 |
| thread_stack | 131072 |
| tmp_table_size | 1048576 |
| tmpdir | /machine/tmp/ |
| version | 3.23.0-alpha-debug |
| wait_timeout | 28800 |
+------------------------+--------------------------+
見10.2.3 調節服務器參數。
SHOW PROCESSLIST顯示哪個線程正在運行,你也能使用mysqladmin processlist命令得到這個信息。如果你有process權限, 你能看見所有的線程,否則,你僅能看見你自己的線程。見7.20 KILL句法。如果你不使用FULL選項,那麼每個查詢只有頭100字符被顯示出來。
SHOW GRANTS FOR user列出對一個用戶必須發出以重複授權的授權命令。
mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root''localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
7.22 EXPLAIN句法(得到關于SELECT的信息)
EXPLAIN tbl_name
or EXPLAIN SELECT select_options
EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一個同義詞。
當你在一條SELECT語句前放上關鍵詞EXPLAIN,MySQL解釋它將如何處理SELECT,提供有關表如何聯結和以什麼次序聯結的信息。
借助于EXPLAIN,你可以知道你什麼時候必須為表加入索引以得到一個使用索引找到記錄的更快的SELECT。你也能知道優化器是否以一個最佳次序聯結表。為了強制優化器對一個SELECT語句使用一個特定聯結次序,增加一個STRAIGHT_JOIN子句。
對于非簡單的聯結,EXPLAIN為用于SELECT語句中的每個表返回一行信息。表以他們將被讀入的順序被列出。MySQL用一邊掃描多次聯結的方式解決所有聯結,這意味著MySQL從第一個表中讀一行,然後找到在第二個表中的一個匹配行,然後在第3個表中等等。當所有的表被處理完,它輸出選擇的列並且回溯表列表直到找到一個表有更多的匹配行,從該表讀入下一行並繼續處理下一個表。
從EXPLAIN的輸出包括下面列:
table
輸出的行所引用的表。
type
聯結類型。各種類型的信息在下面給出。
possible_keys
possible_keys列指出MySQL能使用哪個索引在該表中找到行。注意,該列完全獨立于表的次序。這意味著在possible_keys中的某些鍵實際上不能以生成的表次序使用。如果該列是空的,沒有相關的索引。在這種情況下,你也許能通過檢驗WHERE子句看是否它引用某些列或列不是適合索引來提高你的查詢性能。如果是這樣,創造一個適當的索引並且在用EXPLAIN檢查查詢。見7.8 ALTER TABLE句法。為了看清一張表有什麼索引,使用SHOW INDEX FROM tbl_name。
key
key列顯示MySQL實際決定使用的鍵。如果沒有索引被選擇,鍵是NULL。
key_len
key_len列顯示MySQL決定使用的鍵長度。如果鍵是NULL,長度是NULL。注意這告訴我們MySQL將實際使用一個多部鍵值的幾個部分。
ref
ref列顯示哪個列或常數與key一起用于從表中選擇行。
rows
rows列顯示MySQL相信它必須檢驗以執行查詢的行數。
Extra
如果Extra列包括文字Only index,這意味著信息只用索引樹中的信息檢索出的。通常,這比掃描整個表要快。如果Extra列包括文字where used,它意味著一個WHERE子句將被用來限制哪些行與下一個表匹配或發向客戶。
不同的聯結類型列在下面,以最好到最差類型的次序:
system
桌子僅有一行(=系統表)。這是const聯結類型的一個特例。
const
桌子有最多一個匹配行,它將在查詢開始時被讀取。因為僅有一行,在這行的列值可被剩下的優化器認為是常數。 const表很快,因為它們只讀取一次!
eq_ref
對于每個來自于先前的表的行組合,從該表中讀取一行。這可能是最好的聯結類型,除了const類型。它用在一個索引的所有部分被聯結使用並且索引是UNIQUE或PRIMARY KEY。
ref
對于每個來自于先前的表的行組合,所有有匹配索引值的行將從這張表中讀取。如果聯結只使用鍵的最左面前綴,或如果鍵不是UNIQUE或PRIMARY KEY(換句話說,如果聯結不能基于鍵值選擇單個行的話),使用ref。如果被使用的鍵僅僅匹配一些行,該聯結類型是不錯的。
range
只有在一個給定範圍的行將被檢索,使用一個索引選擇行。ref列顯示哪個索引被使用。
index
這與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引文件通常比數據文件小。
ALL
對于每個來自于先前的表的行組合,將要做一個完整的表掃描。如果表格是第一個沒標記const的表,這通常不好,並且通常在所有的其他情況下很差。你通常可以通過增加更多的索引來避免ALL,使得行能從早先的表中基于常數值或列值被檢索出。
通過相乘EXPLAIN輸出的rows行的所有值,你能得到一個關于一個聯結要多好的提示。這應該粗略地告訴你MySQL必須檢驗多少行以執行查詢。當你使用max_join_size變量限制查詢時,也用這個數字。見10.2.3 調節服務器參數。
下列例子顯示出一個JOIN如何能使用EXPLAIN提供的信息逐步被優化。
假定你有顯示在下面的SELECT語句,你使用EXPLAIN檢驗:
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
tt.ProjectReference, tt.EstimatedShipDate,
tt.ActualShipDate, tt.ClientID,
tt.ServiceCodes, tt.RepetitiveID,
tt.CurrentProcess, tt.CurrentDPPerson,
tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
et_1.COUNTRY, do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
對于這個例子,假定:
* 被比較的列被聲明如下:
表 列 列類型
tt ActualPC CHAR(10)
tt AssignedPC CHAR(10)
tt ClientID CHAR(10)
et EMPLOYID CHAR(15)
do CUSTNMBR CHAR(15)
* 表有顯示在下面的索引:
表 索引
tt ActualPC
tt AssignedPC
tt ClientID
et EMPLOYID(主鍵)
do CUSTNMBR(主鍵)
* tt.ActualPC值不是均勻分布的。
開始,在任何優化被施行前,EXPLAIN語句產生下列信息:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
do ALL PRIMARY NULL NULL NULL 2135
et_1 ALL PRIMARY NULL NULL NULL 74
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872
range checked for each record (key map: 35)
因為type對每張表是ALL,這個輸出顯示MySQL正在對所有表進行一個完整聯結!這將花相當長的時間,因為必須檢驗每張表的行數的乘積次數!對于一個實例,這是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大,你只能想象它將花多長時間……
如果列聲明不同,這里的一個問題是MySQL(還)不能高效地在列上使用索引。在本文中,VARCHAR和CHAR是相同的,除非他們聲明為不同的長度。因為tt.ActualPC被聲明為CHAR(10)並且et.EMPLOYID被聲明為CHAR(15),有一個長度失配。
為了修正在列長度上的不同,使用ALTER TABLE將ActualPC的長度從10個字符變為15個字符:
mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
現在tt.ActualPC和et.EMPLOYID都是VARCHAR(15),再執行EXPLAIN語句產生這個結果:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
do ALL PRIMARY NULL NULL NULL 2135
range checked for each record (key map: 1)
et_1 ALL PRIMARY NULL NULL NULL 74
range checked for each record (key map: 1)
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
這不是完美的,但是是好一些了(rows值的乘積少了一個74一個因子),這個版本在幾秒內執行。
第2種改變能消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比較的列的長度失配:
mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
MODIFY ClientID VARCHAR(15);
現在EXPLAIN產生的輸出顯示在下面:
table type possible_keys key key_len ref rows Extra
et ALL PRIMARY NULL NULL NULL 74
tt ref AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where used
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
這“幾乎”象它能得到的一樣好。
剩下的問題是,缺省地,MySQL假設在tt.ActualPC列的值是均勻分布的,並且對tt表不是這樣。幸好,很容易告訴MySQL關于這些:
shell> myisamchk --analyze PATH_TO_MYSQL_DATABASE/tt
shell> mysqladmin refresh
現在聯結是“完美”的了,而且EXPLAIN產生這個結果:
table type possible_keys key key_len ref rows Extra
tt ALL AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 where used
et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1
et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1
do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
注意在從EXPLAIN輸出的rows列是一個來自MySQL聯結優化器的“教育猜測”;為了優化查詢,你應該檢查數字是否接近事實。如果不是,你可以通過在你的SELECT語句里面使用STRAIGHT_JOIN並且試著在在FROM子句以不同的次序列出表,可能得到更好的性能。
7.23 DESCRIBE句法 (得到列的信息)
{DESCRIBE | DESC} tbl_name {col_name | wild}
DESCRIBE提供關于一張表的列的信息。col_name可以是一個列名字或包含SQL的“%”和“_”通配符的一個字符串。
如果列類型不同于你期望的是基于一個CREATE TABLE語句,注意MySQL有時改變列類型。見7.7.1 隱含的列說明變化。
這個語句為了與 Oracle 兼容而提供的。
SHOW語句提供類似的信息。見7.21 SHOW句法(得到表,列的信息)。
7.24 LOCK TABLES/UNLOCK TABLES句法
LOCK TABLES tbl_name [AS alias] {READ | [LOW_PRIORITY] WRITE}
[, tbl_name {READ | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES
LOCK TABLES為當前線程鎖定表。UNLOCK TABLES釋放被當前線程持有的任何鎖。當線程發出另外一個LOCK TABLES時,或當服務器的連接被關閉時,當前線程鎖定的所有表自動被解鎖。
如果一個線程獲得在一個表上的一個READ鎖,該線程(和所有其他線程)只能從表中讀。如果一個線程獲得一個表上的一個WRITE鎖,那麼只有持鎖的線程READ或WRITE表,其他線程被阻止。
每個線程等待(沒有超時)直到它獲得它請求的所有鎖。
WRITE鎖通常比READ鎖有更高的優先級,以確保更改盡快被處理。這意味著,如果一個線程獲得READ鎖,並且然後另外一個線程請求一個WRITE鎖, 隨後的READ鎖請求將等待直到WRITE線程得到了鎖並且釋放了它。當線程正在等待WRITE鎖時,你可以使用LOW_PRIORITY WRITE允許其他線程獲得READ鎖。如果你肯定終于有個時刻沒有線程將有一個READ鎖,你應該只使用LOW_PRIORITY WRITE。
當你使用LOCK TABLES時,你必須鎖定你將使用的所有表!如果你正在一個查詢中多次使用一張表(用別名),你必須對每個別名得到一把鎖!這條政策保証表鎖定不會死鎖。
注意你應該不鎖定任何你正在用INSERT DELAYED使用的表,這是因為在這種情況下,INSERT被一個不同的線程執行。
通常,你不必鎖定表,因為所有單個UPDATE語句是原語;沒有其他線程能防礙任何其它正在執行SQL語句的線程。當你想鎖定表,有一些情況:
* 如果你將在一堆表上運行許多操作,鎖定你將使用的表是較快的。當然缺點是,沒有其他線程能更新一個READ鎖定的表並且沒有其他線程能讀一個WRITE-鎖定的表。
* MySQL不支持事務環境,所以如果你想要保証在一個SELECT和一個UPDATE之間沒有其他線程到來,你必須使用LOCK TABLES。下面顯示的例子要求LOCK TABLES以便安全地執行:
mysql> LOCK TABLES trans READ, customer WRITE;
mysql> select sum(value) from trans where customer_id= some_id;
mysql> update customer set total_value=sum_from_previous_statement
where customer_id=some_id;
mysql> UNLOCK TABLES;
沒有LOCK TABLES,另外一個線程可能有一個機會在執行SELECT和UPDATE語句之間往trans表中插入一個新行。
通過使用漸增更改(UPDATE customer SET value=value+new_value)或LAST_INSERT_ID()函數,在很多情況下你能使用LOCK TABLES來避免。
你也可以使用用戶級鎖定函數GET_LOCK()和RELEASE_LOCK()解決一些情況,這些鎖保存在服務器的一張哈希表中並且用pthread_mutex_lock()和pthread_mutex_unlock()實現以獲得高速度。見7.4.12 其他函數。
有關鎖定政策的更多信息,見10.2.8 MySQL 怎樣鎖定表。
7.25 SET OPTION句法
SET [OPTION] SQL_VALUE_OPTION= value, ...
SET OPTION設置影響服務器或你的客戶操作的各種選項。你設置的任何選擇保持有效直到當前會話結束,或直到你設置選項為不同的值。
CHARACTER SET character_set_name | DEFAULT
這用給定的映射表從/到客戶映射所有字符串。對character_set_name當前唯一的選項是 cp1251_koi8,但是你能容易通過編輯在MySQL源代碼分發的“sql/convert.cc”文件增加新的映射。缺省映射能用character_set_name的DEFAULT值恢複。注意設置CHARACTER SET選項的語法不同于設置其他選項目的語法。
PASSWORD = PASSWORD('some password')
設置當前用戶的口令。任何非匿名的用戶能改變他自己的口令!
PASSWORD FOR user = PASSWORD('some password')
設置當前服務器主機上的一個特定用戶的口令。只有具備存取mysql數據庫的用戶可以這樣做。用戶應該以user@hostname格式給出,這里user和hostname完全與他們列在mysql.user表條目的User和Host列一樣。例如,如果你有一個條目其User和Host字段是'bob'和'%.loc.gov',你將寫成:
mysql> SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");
或
mysql> UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' and host="%.loc.gov";
SQL_AUTO_IS_NULL = 0 | 1
如果設置為1(缺省 ),那麼對于一個具有一個自動加1的行的表,用下列構件能找出最後插入的行:WHERE auto_increment_column IS NULL。這被一些 ODBC 程序入Access使用。
SQL_BIG_TABLES = 0 | 1
如果設置為1,所有臨時表存在在磁盤上而非內存中。這將更慢一些,但是對需要大的臨時表的大SELECT操作,你將不會得到The table tbl_name is full的錯誤。對于一個新連接的缺省值是0(即,使用內存中的臨時表)。
SQL_BIG_SELECTS = 0 | 1
如果設置為0,如果一個SELECT嘗試可能花很長的時間,MySQL將放棄。這在一個不妥當的WHERE語句發出時是有用的。一個大的查詢被定義為一個將可能必須檢驗多于max_join_size行的SELECT。對一個新連接的缺省值是1(它將允許所有SELECT語句)。
SQL_LOW_PRIORITY_UPDATES = 0 | 1
如果設置為1,所有INSERT、UPDATE、DELETE和LOCK TABLE WRITE語句等待,直到在受影響的表上沒有未解決的SELECT或LOCK TABLE READ。
SQL_SELECT_LIMIT = value | DEFAULT
從SELECT語句返回的記錄的最大數量。如果一個SELECT有一個LIMIT子句,LIMIT優先與SQL_SELECT_LIMIT值。對一個新連接的缺省值是“無限”的。如果你改變了限制,缺省值能用SQL_SELECT_LIMIT的一個DEFAULT值恢複。
SQL_LOG_OFF = 0 | 1
如果設置為1,如果客戶有process權限,對該客戶沒有日志記載到標准的日志文件中。這不影響更新日志記錄!
SQL_LOG_UPDATE = 0 | 1
如果設置為0, 如果客戶有process權限,對該客戶沒有日志記載到更新日志中。這不影響標准日志文件!
TIMESTAMP = timestamp_value | DEFAULT
為該客戶設置時間。如果你使用更新日志恢複行,這被用來得到原來的時間標記。
LAST_INSERT_ID = #
設置從LAST_INSERT_ID()返回的值。當你在更新一個表的命令中使用LAST_INSERT_ID()時,它存儲在更新日志中。
INSERT_ID = #
設置當插入一個AUTO_INCREMENT值時,由INSERT命令使用的值。這主要與更新日志一起使用。
7.26 GRANT和REVOKE句法
GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
TO user_name [IDENTIFIED BY 'password']
[, user_name [IDENTIFIED BY 'password'] ...]
[WITH GRANT OPTION]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
ON {tbl_name | * | *.* | db_name.*}
FROM user_name [, user_name ...]
GRANT在MySQL 3.22.11或以後版本中實現。對于更早MySQL版本,GRANT語句不做任何事情。
GRANT和REVOKE命令允許系統主管在4個權限級別上授權和撤回賦予MySQL用戶的權利:
全局級別
全局權限作用于一個給定服務器上的所有數據庫。這些權限存儲在mysql.user表中。
數據庫級別
數據庫權限作用于一個給定數據庫的所有表。這些權限存儲在mysql.db和mysql.host表中。
表級別
表權限作用于一個給定表的所有列。這些權限存儲在mysql.tables_priv表中。
列級別
列權限作用于在一個給定表的單個列。這些權限存儲在mysql.columns_priv表中。
對于GRANT如何工作的例子,見6.11 為MySQL增加新的用戶權限。
對于GRANT和REVOKE語句,priv_type可以指定下列的任何一個:
ALL PRIVILEGES FILE RELOAD
ALTER INDEX SELECT
CREATE INSERT SHUTDOWN
DELETE PROCESS UPDATE
DROP REFERENCES USAGE
ALL是ALL PRIVILEGES的一個同義詞,REFERENCES還沒被實現,USAGE當前是“沒有權限”的一個同義詞。它能用在你想要創建一個沒有權限用戶的時候。
為了從一個用戶撤回grant的權限,使用GRANT OPTION的一個priv_type值:
REVOKE GRANT OPTION ON ... FROM ...;
對于表,你能指定的唯一priv_type值是SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、GRANT、INDEX和ALTER。
對于列,你能指定的唯一priv_type值是(即,當你使用一個column_list子句時)是SELECT、INSERT和UPDATE。
你能通過使用ON *.*語法設置全局權限,你能通過使用ON db_name.*語法設置數據庫權限。如果你指定ON *並且你有一個當前數據庫,你將為該數據庫設置權限。(警告:如果你指定ON *而你沒有一個當前數據庫,你將影響全局權限!)
為了容納對任意主機的用戶授予的權利,MySQL支持以user@host格式指定user_name值。如果你想要指定一個特殊字符的一個user字符串(例如“-”),或一個包含特殊字符或通配符的host字符串(例如“%”),你可以用括號括起能用戶或主機名字 (例如,'test-user'@'test-hostname')。
你能在主機名中指定通配符。例如,user@"%.loc.gov"適用于在loc.gov域中任何主機的user,並且user@"144.155.166.%"適用于在144.155.166類 C 子網中任何主機的user。
簡單形式的user是user@"%"的一個同義詞。注意:如果你允許匿名用戶連接MySQL服務器(它是缺省的),你也應該增加所有本地用戶如user@localhost,因為否則,當用戶試圖從本地機器上登錄到MySQL服務器時,對于mysql.user表中的本地主機的匿名用戶條目將被使用!匿名用戶通過插入有User=''的條目到mysql.user表中來定義。通過執行這個查詢,你可以檢驗它是否作用于你:
mysql> SELECT Host,User FROM mysql.user WHERE User='';
目前,GRANT僅支持最長60個字符的主機、表、數據庫和列名。一個用戶名字能最多到16個字符。
對與一個表或列的權限是由4個權限級別的邏輯或形成的。例如,如果mysql.user表指定一個用戶有一個全局select權限,它不能被數據庫、表或列的一個條目否認。
對于一個列的權限能如下計算:
global privileges
OR (database privileges AND host privileges)
OR table privileges
OR column privileges
在大多數情況下,你只授予用戶一個權限級別上的權限,因此現實通常不象上面所說的那樣複雜。
權限檢查過程的細節在6 MySQL 存取權限系統中給出。
如果你為一個在mysql.user表中不存在的用戶/主機名組合授權,一個條目被增加並且保留直到用一個DELETE命令刪除。換句話說,GRANT可以創建user表的條目,但是REVOKE將不刪除;你必須明確地使用DELETE刪除.
在MySQL 3.22.12或以後,如果創建一個新用戶或如果你有全局授予權限,用戶的口令將被設置為由IDENTIFIED BY子句指定的口令,如果給出一個。如果用戶已經有了一個口令,它被一個新的代替。
警告:如果你創造一個新用戶但是不指定一個IDENTIFIED BY子句,用戶沒有口令。這是不安全的。
口令也能用SET PASSWORD命令設置。見7.25 SET OPTION句法。
如果你為一個數據庫授權,如果需要在mysql.db表中創建一個條目。當所有為數據庫的授權用REVOKE刪除時,這個條目被刪除。
如果一個用戶沒有在一個表上的任何權限,當用戶請求一系列表時,表不被顯示(例如,用一個SHOW TABLES語句)。
WITH GRANT OPTION子句給與用戶有授予其他用戶在指定的權限水平上的任何權限的能力。你應該謹慎對待你授予他grant權限的用戶,因為具有不同權限的兩個用戶也許能合並權限!
你不能授予其他用戶你自己不具備的權限; agree權限允許你放棄你僅僅擁有的那些權限。
要知道,當你將一個特定權限級別上的grant授予其他用戶,用戶已經擁有(或在未來被授予!)的在該級別上的任何權限也可由該用戶授權。假定你授權一個用戶在一個數據庫上的insert權限,那麼如果你授權在數據庫上select權限並且指定WITH GRANT OPTION,用戶能不僅放棄select權限,還有insert。如果你授權用戶在數據庫上的update權限,用戶能放棄insert、select和update。
你不應該將alter權限授予一個一般用戶。如果你這樣做,用戶可以通過重命名表試圖顛覆權限系統!
注意,如果你正在使用即使一個用戶的表或列的權限,服務器要檢查所有用戶的表和列權限並且這將使MySQL慢下來一點。
當mysqld啟動時,所有的權限被讀入存儲器。數據庫、表和列權限馬上生效,而用戶級權限在下一次用戶連接時生效。你用GRANT或REVOKE對受權表執行的更改立即被服務器知曉。如果你手工修改授權表(使用INSERT、UPDATE等等),你應該執行一個FLUSH PRIVILEGES語句或運行mysqladmin flush-privileges告訴服務器再次裝載授權表。見6.9 權限變化何時生效。
ANSI SQL版本的GRANT與MySQL版本之間的最大差別:
* ANSI SQL 沒有全局或數據庫級別權限,並且 ANSI SQL 不支持所有MySQL支持的權限。
* 當你在 ANSI SQL 拋棄一張表時,表的所有權限均被撤消。如果你在 ANSI SQL 撤銷權限,所有基于該權限的授權也被也被撤消。在MySQL中,權限只能用明確的REVOKE命令或操作MySQL授權表拋棄。
7.27 CREATE INDEX句法
CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length)],... )
CREATE INDEX語句在MySQL版本 3.22 以前不做任何事情。在 3.22 或以後版本中,CREATE INDEX被映射到一個ALTER TABLE語句來創建索引。見7.8 ALTER TABLE句法。
通常,你在用CREATE TABLE創建表本身時創建表的所有索引。見7.7 CREATE TABLE句法。CREATE INDEX允許你把索引加到現有表中。
一個(col1,col2,...)形式的列表創造一個多列索引。索引值有給定列的值串聯而成。
對于CHAR和VARCHAR列,索引可以只用一個列的部分來創建,使用col_name(length)句法。(在BLOB和TEXT列上需要長度)。下面顯示的語句使用name列的頭10個字符創建一個索引:
mysql> CREATE INDEX part_of_name ON customer (name(10));
因為大多數名字通常在頭10個字符不同,這個索引應該不比從整個name列的創建的索引慢多少。另外,在索引使用部分的列能使這個索引成為更小的文件大部分, 它能保存很多磁盤空格並且可能也加快INSERT操作!
注意,如果你正在使用MySQL版本 3.23.2 或更新並且正在使用MyISAM桌子類型,你只能在可以有NULL值的列或一個BLOB/TEXT列上增加一個索引,
關于MySQL如何使用索引的更多信息,見10.4 MySQL索引的使用。
7.28 DROP INDEX句法
DROP INDEX index_name ON tbl_name
DROP INDEX從tbl_name表拋棄名為index_name的索引。DROP INDEX在MySQL 3.22 以前的版本中不做任何事情。在 3.22 或以後,DROP INDEX被映射到一個ALTER TABLE語句來拋棄索引。見7.8 ALTER TABLE句法。
7.29 注釋句法
MySQL服務器支持# to end of line、-- to end of line和/* in-line or multiple-line */注釋風格:
mysql> select 1+1; # This comment continues to the end of line
mysql> select 1+1; -- This comment continues to the end of line
mysql> select 1 /* this is an in-line comment */ + 1;
mysql> select 1+
/*
this is a
multiple-line comment
*/
1;
注意--注釋風格要求你在--以後至少有一個空格!
盡管服務者理解剛才描述的注釋句法,mysql客戶分析/* ... */注釋的方式上有一些限制:
* 單引號和雙引號字符被用來標志一個括起來的字符串的開始,即使在一篇注釋內。如果引號在主室內沒被第2個引號匹配,分析器不知道注釋已經結束。如果你正在交互式運行mysql,你能告知有些混亂,因為提示符從mysql>變為'>或">。
* 一個分號被用來指出當前的SQL語句結束並且跟隨它的任何東西表示下一行的開始。
當你交互式運行mysql時和當你把命令放在一個文件中並用mysql < some-file告訴mysql從那個文件讀它的輸入時,這些限制都適用。
MySQL不支持‘--’的ANSI SQL注釋風格。見5.4.7 ‘--’作為一篇注釋的開始。
7.30 CREATE FUNCTION/DROP FUNCTION句法
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|REAL|INTEGER}
SONAME shared_library_name
DROP FUNCTION function_name
一個用戶可定義函數(UDF)是用一個像MySQL的原生(內置)函數如ABS()和CONCAT()的新函數來擴展MySQL的方法。
AGGREGATE是MySQL 3.23的一個新選項。一個AGGREGATE函數功能就像一個原生MySQL GROUP函數如SUM或COUNT()。
CREATE FUNCTION在mysql.func系統表中保存函數名、類型和共享庫名。你必須對mysql數據庫有insert和delete權限以創建和拋棄函數。
所有活躍的函數在每次服務器啟動時被重新裝載,除非你使用--skip-grant-tables選項啟動mysqld,在這種情況下,UDF初始化被跳過並且UDF是無法獲得的。(一個活躍函數是一個已經用CREATE FUNCTION裝載並且沒用DROP FUNCTION刪除的函數。)
關于編寫用戶可定義函數的指令,見14 為MySQL增加新函數。對于UDF的工作機制,函數必須用 C 或 C++ 編寫,你的操作系統必須支持動態裝載並且你必須動態編譯了mysqld(不是靜態)。
7.31 MySQL對保留詞很挑剔嗎?
一個常見的問題源自于試圖使用MySQL內置的數據類型或函數名同名的列來創建數據庫表,例如TIMESTAMP或GROUP。你允許這樣做(例如,ABS是一個允許的列名),但是當使用其名字也是列名的函數時,在一個函數名和“ ( ”之間不允許白空。
下列詞明確地在MySQL中被保留。他們的大多數被 ANSI SQL92 禁止作為列或表名(例如,group)。一些被保留因為MySQL需要他們並且正在(當前)使用一個yacc分析器:
action add aggregate all
alter after and as
asc avg avg_row_length auto_increment
between bigint bit binary
blob bool both by
cascade case char character
change check checksum column
columns comment constraint create
cross current_date current_time current_timestamp
data database databases date
datetime day day_hour day_minute
day_second dayofmonth dayofweek dayofyear
dec decimal default delayed
delay_key_write delete desc describe
distinct distinctrow double drop
end else escape escaped
enclosed enum explain exists
fields file first float
float4 float8 flush foreign
from for full function
global grant grants group
having heap high_priority hour
hour_minute hour_second hosts identified
ignore in index infile
inner insert insert_id int
integer interval int1 int2
int3 int4 int8 into
if is isam join
key keys kill last_insert_id
leading left length like
lines limit load local
lock logs long longblob
longtext low_priority max max_rows
match mediumblob mediumtext mediumint
middleint min_rows minute minute_second
modify month monthname myisam
natural numeric no not
null on optimize option
optionally or order outer
outfile pack_keys partial password
precision primary procedure process
processlist privileges read real
references reload regexp rename
replace restrict returns revoke
rlike row rows second
select set show shutdown
smallint soname sql_big_tables sql_big_selects
sql_low_priority_updates sql_log_off sql_log_update sql_select_limit
sql_small_result sql_big_result sql_warnings straight_join
starting status string table
tables temporary terminated text
then time timestamp tinyblob
tinytext tinyint trailing to
type use using unique
unlock unsigned update usage
values varchar variables varying
varbinary with write when
where year year_month zerofill
下列符號(來自上表)被ANSI SQL禁止但是被MySQL允許作為列/表名。這是因為這些名字的一些是很自然的名字並且很多人已經使用了他們。
* ACTION
* BIT
* DATE
* ENUM
* NO
* TEXT
* TIME
* TIMESTAMP