mysql其他函數

1. 位元函數

對於位元運算，MySQL 使用 BIGINT (64位元) 演算法，因此這些運算子的最大範圍是 64 位元。

|

Bitwise OR:

mysql> SELECT 29 | 15;

-> 31

其結果為一個64位元無符號整數。

&

Bitwise AND:

mysql> SELECT 29 & 15;

-> 13

其結果為一個64位元無符號整數。

^

Bitwise XOR:

mysql> SELECT 1 ^ 1;

-> 0

mysql> SELECT 1 ^ 05

無參數    使用來自DES關鍵字檔案的第一個關鍵字。    

key_num    使用DES 關鍵字檔案給予的關鍵字數字(0-9)。    

key_str    使用給予的關鍵字字串為 str 加密。    

選擇--des-key-file伺服器指定關鍵字檔案。

回傳字串是一個二進位字串，其中第一個字元為 CHAR(128 | key_num)。

加上 128讓辨識加密關鍵字更加容易。若你使用字串關鍵字，則 key_num 為127。

結果的字串長度為  new_len = orig_len + (8-(orig_len % 8))+1。

DES關鍵字檔案中的每一行都有以下格式：

key_num des_key_str

每個key_num 必須是從0到0範圍內的數字。文件中行的排列順序是任意的。 des_key_str 是用來加密訊息的字串。在數字和關鍵字之間應該至少有一個空格。若你未指定任何到DES_ENCRYPT()的關鍵字參數，則第一個關鍵字為預設的使用關鍵字。

使用FLUSH DES_KEY_FILE語句，你可以讓 MySQL從關鍵字檔案讀取新的關鍵字值。這要求你享有 RELOAD特權。

擁有一套預設關鍵字的一個好處就是它向應用程式提供了一個檢驗加密列值的方式，而無須向最終用戶提供解密這些值的權力。

mysql> SELECT customer_address FROM customer_table

> WHERE crypted_credit_card = DES_ENCRYPT('credit_card_number');

ENCRYPT(strm[strry)使用加密 salt 參數應為一個至少包含2個字元的字串。若沒有給予 salt 參數，則使用任意值。

mysql> SELECT ENCRYPT('hello');

-> 'VxuFAJXVARROc'

至少在某些系統中，ENCRYPT()除了str的前八位元字元之外會忽略所有內容。這個行為由下劃線的crypt() 系統所呼叫的執行所決定。

假如crypt()在你的系統中不可用(正如在 Windows系統), ENCRYPT() 則會一直回傳NULL。鑑於這個原因，我們向你推薦使用 MD5() 或SHA1() 來代替,因為這兩個函數適合所有的平台。

MD5(str)

為字串算出一個 MD5 128位元檢查和。該值以32位元十六進位數字的二進位字串的形式傳回, 若參數為 NULL 則會傳回NULL。例如，傳回值可被用作雜湊關鍵字。

mysql> SELECT MD5('testing');

-> 'ae2b1fca515949e5d54fb22b8ed95575'

這是"RSA Data Security, Inc. MD5 Message-Digg ，請參閱「Cast函數和運算子」中BINARY運算元項中給出的二進位字串轉換。

OLD_PASSWORD(str)

當PASSWORD()的執行變成改善安全性時，OLD_PASSWORD()會被加入 MySQL。 OLD_PASSWORD()傳回從前的PASSWORD()執行值( 4.1之前)，同時允許你為任何4.1 之前的需要連接到你的5.1 版本MySQL伺服器前客戶端設定密碼，從而不至於將它們切斷。

PASSWORD(str)

從原始密碼str 計算並傳回密碼字串，當參數為 NULL 時傳回 NULL。這個函數用於使用者授權表的Password欄位中的加密MySQL密碼儲存

mysql> SELECT PASSWORD('badpwd');

-> '7f84554057dd964b'

PASSWORD())加密是單向的可逆加密是單向的可逆加密。

PASSWORD() 執行密碼加密與Unix 密碼加密的方式不同。請參見ENCRYPT()。

註解： PASSWORD()函數在MySQL伺服器中的鑑定係統使用；你不應將它用在你個人的應用程式中。為達到相同目的，可使用 MD5()或SHA1() 代替。更多關於在您的應用程式中處理密碼及安全鑑定的資訊請參閱RFC 2195

SHA1(str) SHA(str)

為字串算出一個 SHA1 160位元檢查和，如RFC 3174 (安全雜湊演算法)中所述。該值被作為40位十六進制數字傳回，而當參數為NULL 時則傳回 NULL。這個函數的一個可能的用處就在於其作為雜湊關鍵字。你也可以將其作為儲存密碼的密碼安全函數使用。

mysql> SELECT SHA1('abc');

-> 'a9993e364706816aba3e25717850c26c9cd0d89d'

SHA1()可以被視為一個密碼的 MD， SHA() 和SHA1()具有相同的意義。

3. 資訊函數

BENCHMARK(count,expr)

BENCHMARK() 函數重複count 次執行表達式 expr 。 它可以用來計算  MySQL 處理表達式的速度。結果值通常為 0。另一種用處來自 mysql客戶端內部,能夠報告問詢執行的次數：

mysql> SELECT BENCHMARK(1000000,ENCODE('hello','goodbye'));

+----------------------------------------------+
| BENCHMARK(1000000,ENCODE('hello','goodbye')) |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (4.74 sec)

此處報告的時間是客戶端上的共用時間，而不是伺服器端上的CPU時間。建議執行多遍BENCHMARK()，並解釋與伺服器機器負載程度有關的結果。

·         CHARSET(str)

傳回字串自變數的字元集。

mysql> SELECT CHARSET('abc');        -> 'latin1'
mysql> SELECT CHARSET(CONVERT('abc' USING utf8));        -> 'utf8'
mysql> SELECT CHARSET(USER());        -> 'utf8'

· COERCIBILITY(str)

傳回字串自變數的整序可壓縮性值。

mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);        -> 0
mysql> SELECT COERCIBILITY(USER());        -> 3
mysql> SELECT COERCIBILITY('abc');        -> 4

回傳值有下列意義：

可壓縮性   意義   舉例   

0    明確排序   2    明確排序   列值   

3    系統常數   USER()回傳值   

4    可壓縮   文字字串   

5    可忽略   NULL得來的NULL或一個表達式     

COLLATION(str)

傳回惠字串參數的排序方式。

mysql> SELECT COLLATION('abc');

-> 'latin1_swedish_ci'

mysql> SELECT COLLATION(_utf8'abc');對於傳回

-(連接的連接ID (線程ID)。每個連線都有各自的唯一 ID。

mysql> SELECT CONNECTION_ID();

-> 23786

CURRENT_USER, CURRENT_USER()

傳回目前話路被驗證的使用者名稱和主機名稱組合。這個值符合確定你的存取權限的MySQL 帳戶。在被指定SQL SECURITY DEFINER特徵的儲存程式內， CURRENT_USER() 回傳程式的創建者。

CURRENT_USER()的值可以和USER()的值有所不同。

mysql> SELECT USER();

-> 'davida@localhost'

mysql> SELECT * FROM mysql.user;

ERROR 1044: Access 對 for f5 '

mysql> SELECT CURRENT_USER();

-> '@localhost'

這個例子解釋了雖然客戶端指定了一個 davida用戶名伺服器 (正如USER()函數的值所指示的),使用一個匿名的使用者帳戶確認該客戶端 (見CURRENT_USER()值得空用戶名部分 )。這種情況發生的一個原因是 One 在向 davida的授權清單中沒有足夠的帳戶。

CURRENT_USER() 傳回的字串使用 utf8字元集。

DATABASE()

傳回使用 utf8 字元集的預設(目前)資料庫名稱。在儲存程式裡，預設資料庫是同該程式向關聯的資料庫，但不一定與呼叫情境的預設資料庫相同。

mysql> SELECT DATABASE();

-> 'test'

若沒有預設資料庫, DATABASE()回傳 NULL。

FOUND_ROWS()

A SELECT語句可能包含一個 LIMIT 子句，用來限制伺服器傳回客戶端的行數。在某些情況下，需要不用再次運行該語句而得知在沒有LIMIT 時到底該語句回傳了多少行。為了知道這個行數, 包含在SELECT 語句中選擇  SQL_CALC_FOUND_ROWS ，隨後呼叫 FOUND_ROWS() ：

mysql> SELECT SQL_CALC_FOUND_ROWS * SELECT FOUND_ROWS();

第二個 SELECT回傳一個數字，指示了在沒有LIMIT子句的情況下，第一個SELECT返回了多少行 (若上述的 SELECT語句不包括 SQL_CALC_FOUND_ROWS 選項,則使用LIMIT 和不使用時，FOUND_CALC_FOUND_ROWS 選項,則使用LIMIT 和不使用時，FOUND_ROWS()可能會傳回不同的結果）。

通過 FOUND_ROWS()的有效行數是瞬時的，且不用於越過SELECT SQL_CALC_FOUND_ROWS語句後面的語句。若您需要稍候參考這個值，那麼將其儲存：

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ;

mysql> SET @rows = FOUND_ROWS();

假如你正在使用 SQLCAL出在全部結果集合中有所少行。然而， 這比不用LIMIT而再次執行問詢要快，原因是結果集合不需要被送到客戶端。

SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 在当你希望限制一个问询返回的行数时很有用，同时还能不需要再次运行问询而确定全部结果集合中的行数。一个例子就是提供页式显示的Web脚本，该显示包含显示搜索结果其它部分的页的连接。使用FOUND_ROWS() 使你确定剩下的结果需要多少其它的页。

SQL_CALC_FOUND_ROWS 和 FOUND_ROWS() 的应用对于UNION 问询比对于简单SELECT 语句更为复杂，原因是在UNION 中，LIMIT 可能会出现在多个位置。它可能适用于UNION中的个人 SELECT语句，或是总体上 到UNION 结果的全程。

SQL_CALC_FOUND_ROWS对于 UNION的意向是它应该不需要全程LIMIT而返回应返回的行数。SQL_CALC_FOUND_ROWS 和UNION 一同使用的条件是：

SQL_CALC_FOUND_ROWS 关键词必须出现在UNION的第一个 SELECT中。

FOUND_ROWS()的值只有在使用 UNION ALL时才是精确的。若使用不带ALL的UNION，则会发生两次删除， 而 FOUND_ROWS() 的指只需近似的。

假若UNION 中没有出现 LIMIT ，则SQL_CALC_FOUND_ROWS 被忽略，返回临时表中的创建的用来处理UNION的行数。

LAST_INSERT_ID() LAST_INSERT_ID(expr)

自动返回最后一个INSERT或 UPDATE 问询为 AUTO_INCREMENT列设置的第一个 发生的值。

mysql> SELECT LAST_INSERT_ID();

-> 195

产生的ID 每次连接后保存在服务器中。这意味着函数向一个给定客户端返回的值是该客户端产生对影响AUTO_INCREMENT列的最新语句第一个 AUTO_INCREMENT值的。这个值不能被其它客户端影响，即使它们产生它们自己的 AUTO_INCREMENT值。这个行为保证了你能够找回自己的 ID 而不用担心其它客户端的活动，而且不需要加锁或处理。

假如你使用一个非“magic”值来更新某一行的AUTO_INCREMENT 列，则LAST_INSERT_ID() 的值不会变化(换言之, 一个不是 NULL也不是 0的值)。

重点: 假如你使用单INSERT语句插入多个行， LAST_INSERT_ID() 只返回插入的第一行产生的值。其原因是这使依靠其它服务器复制同样的 INSERT语句变得简单。

例如：

mysql> USE test;

Database changed
mysql> CREATE TABLE t (    ->   id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,    ->   name VARCHAR(10) NOT NULL    -> );Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t;+----+------+
| id | name |
+----+------+
|  1 | Bob  |
+----+------+
1 row in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO t VALUES    -> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
|  1 | Bob  |
|  2 | Mary |
|  3 | Jane |
|  4 | Lisa |
+----+------+
4 rows in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

虽然第二个问询将3 个新行插入 t, 对这些行的第一行产生的 ID 为 2, 这也是 LAST_INSERT_ID()返回的值。

假如你使用 INSERT IGNORE而记录被忽略，则AUTO_INCREMENT 计数器不会增量，而 LAST_INSERT_ID() 返回0,这反映出没有插入任何记录。

若给出作为到LAST_INSERT_ID()的参数expr ，则参数的值被函数返回，并作为被LAST_INSERT_ID()返回的下一个值而被记忆。这可用于模拟序列：

创建一个表，用来控制顺序计数器并使其初始化：

o                     mysql> CREATE TABLE sequence (id INT NOT NULL);

o                     mysql> INSERT INTO sequence VALUES (0);

使用该表产生这样的序列数 ：

o                     mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);

o                     mysql> SELECT LAST_INSERT_ID();

UPDATE 语句会增加顺序计数器并引发向LAST_INSERT_ID()  的下一次调用，用来返回升级后的值。 SELECT 语句会检索这个值。  mysql_insert_id() C API函数也可用于获取这个值。

你可以不用调用LAST_INSERT_ID()而产生序列，但这样使用这个函数的效用在于 ID值被保存在服务器中，作为自动产生的值。它适用于多个用户，原因是多个用户均可使用 UPDATE语句并用SELECT语句(或mysql_insert_id()),得到他们自己的序列值，而不会影响其它产生他们自己的序列值的客户端或被其它产生他们自己的序列值的客户端所影响。

注意， mysql_insert_id() 仅会在INSERT 和UPDATE语句后面被升级， 因此你不能在执行了其它诸如SELECT或 SET 这样的SQL语句后使用 C API 函数来找回 LAST_INSERT_ID(expr) 对应的值。

ROW_COUNT()

ROW_COUNT()傳回被前面語句升級的、插入的或刪除的行數。 這個行數和 mysql 客戶端顯示的行數及mysql_affected_rows() C API 函數回傳的值相同。

mysql> INSERT INTO t VALUES(1),(2),(3);

詢問完成, 表中有3 行 (0.00秒)

🜠重複: 0 (0.00秒)

記錄: 355: 0 mysql> SELECT ROW_COUNT();

| ROW_COUNT() |
+-------------+
|          +-------------+

表中有1行 (0.00 秒)

| ROW_COUNT() |

|           2 |
+-------------+

表中有1行 (0.00 秒)

-> 'davida@localhost'

這個值及你所連接的客戶主機。這個值可以和CURRENT_USER() 的值不同。

你可以這樣提取用戶名部分：

mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);

-> 'davida'

由於字符集中在一個的值，你  USER() 也應確保'@' 字串文字在該字元集中得到解釋：

mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);

-> 'davida'

VERSION()

mysql> SELECT FORMAT(12332.1,4); > SELECT FORMAT(12332.2,0);

-> '12,332'

GET_LOCK(str,timeout)

設法使用字串str 給定的名字得到一個鎖， 超時為timeout 秒。若成功得到鎖，則返回 1，若操作逾時則返回0  (例如,由於另一個客戶端已提前封鎖了這個名字 ),若發生錯誤則返回NULL (諸如缺乏記憶或線程mysqladmin kill 被斷開 )。假如你有一個用GET_LOCK()得到的鎖，當你執行RELEASE_LOCK()或你的連接斷開(正常或非正常)時，這個鎖就會解除。

這個函數可用來執行應用程式鎖定或模擬記錄鎖定。名稱被鎖定在伺服器範圍內。假如一個名字已經被一個客戶端封鎖，GET_LOCK() 會封鎖來自另一個客戶端申請封鎖同一個名字的任何請求。這使得對一個封鎖名達成協議的客戶端使用這個名字合作執行建議鎖。然而要知道它也允許不在一組合作客戶端中的一個客戶端封鎖名字，不論是服役的還是非故意的，這樣阻止任何合作中的客戶端封鎖這個名字。一個減少這種情況發生的辦法就是使用資料庫特定的或應用程式特定的封鎖名。例如，  使用db_name.str或 app_name.str 形式的封鎖名稱。

mysql> SELECT GET_LOCK('lock1',10);

-> 1

mysql> SELECT RELEASE_LOCK('lock2');

mysql> SELECT RELEASE_LOCK('lock1');

注意，第二個原因是鎖'lock1' 杯第二個GET_LOCK()呼叫解開。

給出一個作為字串的網路位址的點位址表示，傳回一個代表該位址數值的整數。位址可以是4或8位元位址。

mysql> SELECT INET_ATON('209.207.224.40');

-> 3520061480

產生的數字總是依照網路位元組順序。如同上面的例子，數字依照 209×2563 + 207×2562 + 224×256 + 40 計算。

INET_ATON() 也能理解短格式 IP 位址：

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
-> 213070643,07 INET_ATON()產生的值時，推薦你使用 INT UNSIGNED 列。假如你使用 (帶符號) INT欄位, 則對應的第一個八位元組大於127的IP 位址值會被截至  2147483647 (即, INET_ATON('127.255.255.255') 所傳回的值)。

INET_NTOA(expr)

給定一個數字網路位址 (4 或 8 比特),傳回作為字串的該位址的電位址表示。

mysql> SELECT INET_NTOA(3520061480);

IS_FREE_LOCK(str)

檢查名為鎖定的鎖是否可以使用之鎖言若鎖可以使用，則回傳  1  (沒有人在用這個鎖), 若這個鎖正在被使用，則回傳0 ，出現錯誤則回傳 NULL  (諸如不正確的參數 )。

IS_USED_LOCK(str)

檢查名為str的鎖是否正在被使用(換言之,被封鎖)。若被封鎖，則傳回使用該鎖的用戶端的連線識別碼。否則返回 NULL。

MASTER_POS_WAIT(log_name,log_pos[,timeout])

此函數對於控制主從同步很有用處。它會持續封鎖，直到從裝置閱讀和應用主機記錄中所有補充資料到指定的位置。傳回值是其為到達指定位置而必須等待的記錄事件的數目。若從裝置SQL執行緒沒有被啟動、從裝置主機資訊尚未初始化、參數不正確或出現任何錯誤，則函數傳回 NULL。若超時時間被超過，則回傳-1。若在MASTER_POS_WAIT() 等待期間，從裝置SQL執行緒中止，則函數傳回 NULL。若從設備由指定位置通過，函數會立即傳回結果。

假如已經指定了一個逾時時間值，當 超時時間 秒數經過後MASTER_POS_WAIT()會停止等待。超時時間 必須大於 0；一個為零或為負值的 逾時時間 表示沒有超市時間。

NAME_CONST(name,value)

傳回給定值。 當用來產生一個結果集合欄位時, NAME_CONST()促使該欄位使用給定名稱。

mysql> SELECT NAME_CONST('myname', 14);

+--------+

+--------+
|     14 |
+--- -----+

這個函數被加進 MySQL 5.0.12。它只做內部使用。你可能會在mysqlbinlog 的書櫥中看到這個函數。

RELEASE_LOCK(str)

解開被GET_LOCK()取得的，用字串str 所命名的鎖。若鎖被解開，則回傳  1，若改執行緒尚未建立鎖，則回傳0 (此時鎖沒有被解開 ), 若命名的鎖不存在，則回傳 NULL。若該鎖從未被對GET_LOCK()的呼叫獲取，或鎖已經提前解開，則該鎖不存在。

DO 語句和RELEASE_LOCK()同時使用很方便。

SLEEP(duration)

睡眠(暫停) 時間為duration 參數給定的秒數，然後返回 0。若 SLEEP() 被中斷,它會回到 1。 duration 或許或包括一個給定的以微秒為單位的分數部分。

UUID()

回傳一個通用唯一識別碼(UUID) ，其產生的根據是《DCE 1.1: 遠端過程呼叫》 (附錄A) CAE (公共應用軟體環境) 的說明，該作品於1997年10月由 The Open Group 出版 (文件編號 C706,http://www.opengroup.org/public/pubs/catalog/c706.htm).

UUID被設計成一個在時間和空間上都獨一無二的數字。 2個對UUID() 的呼叫應產生2個不同的值，即使這些呼叫的執行是在兩個互不相連的單獨電腦上進行。

UUID 是一個由5位十六進位數的字串表示的128位元數字 ，其格式為  aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee ：

前3個數字從一個時間戳產生。

第4 個數字保持暫時唯一性，以防時間戳值失去單一性 (例如, 由於經濟時)。

第5個數字是一個 IEEE 802 節點號，它提供空間唯一性。若後者不可用，則用一個隨機數字替換。 (例如, 由於主機沒有乙太網路卡，或我們不知道怎樣在你的作業系統上找到介面的機器位址 )。假若這樣，空間唯一性就不能保證。儘管如此，一個衝突的發生機率還是非常低的。

目前,一個介面的 MAC 位址盡被FreeBSD 和 Linux考慮。在其它作業系統中, MySQL使用隨機產生的 48位元數字。

mysql> SELECT UUID();

-> '6ccd780c-baba-1026-9564-0040f4311e29'

注意， UUID() 不支援複製功能。

VALUES(col_name)

在一個INSERT … ON DUPLICATE KEY UPDATE …語句中，你可以在UPDATE 子句中使用 VALUES(col_name)函數，用來存取來自該語句的INSERT 部分的列值。換言之，UPDATE 子句中的 VALUES(col_name) 存取需要插入的col_name 的值,並不會發生重複鍵衝突。這個函數在多行插入中特別有用。  VALUES()函數只在INSERT ... UPDATE 語句中有意義，而在其它情況下只會回傳 NULL。

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

-> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);