[TOC]
MySQL 提供了很多功能強大、使用友善的運算符和函數。通過使用這些運算符完成各種各樣的運算操作。函數可以幫助開發人員簡單、快速的編寫 SQL 語句。
MySQL函數簡介
MySQL 函數是 MySQL 資料庫提供的内部函數，這些内部函數可以幫助使用者更加友善地處理表中的資料。函數就像預定的公式一樣存放在資料庫裡，每個使用者都可以調用已經存在的函數來完成某些功能。
提示：
函數就是輸入值然後得到相應的輸出結果，輸入值稱為參數（parameter），輸出值稱為傳回值。函數可以很友善的地實作業務邏輯的重用，并且 MySQL 資料庫允許使用者自己建立函數，以适應實際的業務操作。正确使用函數會讓讀者在編寫 SQL 語句時起到事半功倍的效果。
MySQL 函數用來對資料表中的資料進行相應的處理，以便得到使用者希望得到的資料，使 MySQL 資料庫的功能更加強大。
MySQL 函數包括數學函數、字元串函數、日期和時間函數、條件判斷函數、系統資訊函數和加密函數等。這些函數不僅能幫助使用者做很多事情，比如字元串的處理、數值的運算、日期的運算等，還可以幫助開發人員編寫出簡單快捷的 SQL 語句。
SELECT、INSERT、UPDATE 和 DELETE 語句及其子句（例如 WHERE、ORDER BY、HAVING 等）中都可以使用 MySQL 函數。例如，資料表中的某個資料是負數，現在需要将這個資料顯示為整數，這時就可以在 SELECT 語句中使用絕對值函數。
下面介紹這幾類函數的使用範圍。
- 數學函數主要用于處理數字 。這類函數包括絕對值函數、正弦函數、餘弦函數和獲得随機數的函數等。
- 字元串函數主要用于處理字元串 。其中包括字元串連接配接函數、字元串比較函數、将字元串的字母都變成小寫或大寫字母的函數和擷取子串的函數等。
- 日期和時間函數主要用于處理日期和時間 。其中包括擷取目前時間的函數、擷取目前日期的函數、傳回年份的函數和傳回日期的函數等。
- 條件判斷函數主要用于在 SQL 語句中控制條件選擇 。其中包括 IF 語句、CASE 語句和 WHERE 語句等。
- 系統資訊函數主要用于擷取 MySQL 資料庫的系統資訊 。其中包括擷取資料庫名的函數、擷取目前使用者的函數和擷取資料庫版本的函數等。
- 加密函數主要用于對字元串進行加密解密。 其中包括字元串加密函數和字元串解密函數等。
- 其他函數主要包括格式化函數和鎖函數等。
以上這些都是 MySQL 資料庫中具有代表性的函數，大家并不需要一次全部記住，隻需要知道有這樣的函數就可以了，實際應用時可以查閱：
- MySQL 常用函數彙總
- MySQL 官方參考文檔
MySQL常用運算符概述
在 MySQL 中，可以通過運算符來擷取表結構以外的另一種資料。- 例如，學生表中存在一個 birth 字段，這個字段表示學生的出生年份。如果想得到這個學生的實際年齡，可以使用 MySQL 中的算術運算符用目前的年份減學生出生的年份，求出的結果就是這個學生的實際年齡了。
MySQL 所提供的運算符可以直接對表中資料或字段進行運算，進而實作使用者的新需求，增強了 MySQL 的功能。
每種資料庫都支援 SQL 語句，但是它們也都有各自支援的運算符。我們除了需要學會使用 SQL 語句外，還需要掌握各種運算符。
MySQL 支援 4 種運算符，分别是：
算術運算符
算術運算符是 SQL 中最基本的運算符，MySQL 支援的運算符包括加、減、乘、除和取餘運算，它們是最常用、最簡單的一類運算符。
下表列出了這些運算符的作用和使用方法。
比較運算符
當使用 SELECT 語句進行查詢時，MYSQL 允許使用者對表達式的左邊操作數和右邊操作數進行比較，比較結果為真，則傳回 1，為假則傳回 0，比較結果不确定則傳回 NULL。MySQL 支援的比較運算符如下表所示。
等于運算（=）
= 運算符用來比較兩邊的操作數是否相等，相等的話傳回 1，不相等的話傳回 0。具體的文法規則如下：
- 若有一個或兩個操作數為 NULL，則比較運算的結果為 NULL。
- 若兩個操作數都是字元串，則按照字元串進行比較。
- 若兩個操作數均為整數，則按照整數進行比較。
- 若一個操作數為字元串，另一個操作數為數字，則 MySQL 可以自動将字元串轉換為數字。
安全等于運算符（<=>）
<=> 操作符和 = 操作符類似，不過 <=> 可以用來判斷 NULL 值，具體文法規則為：
- 當兩個操作數均為 NULL 時，其傳回值為 1 而不為 NULL；
- 而當一個操作數為 NULL 時，其傳回值為 0 而不為 NULL。
不等于運算符（<> 或者 !=）
與 = 的作用相反，<> 和 != 用于判斷數字、字元串、表達式是否不相等。對于 <> 和 !=，如果兩側操作數不相等，傳回值為 1，否則傳回值為 0；如果兩側操作數有一個是 NULL，那麼傳回值也是 NULL。
兩個不等于運算符作用相同，都可以進行數字、字元串、表達式的比較判斷。
小于等于運算符（<=）
<= 是小于等于運算符，用來判斷左邊的操作數是否小于或者等于右邊的操作數；如果小于或者等于，傳回值為 1，否則傳回值為 0；如果兩側操作數有一個是 NULL，那麼傳回值也是 NULL。
- 左邊操作數小于或者等于右邊時，傳回值為 1，例如 4<=4；
- 當左邊操作數大于右邊時，傳回值為 0，例如“good”第 3 個位置的“o”字元在字母表中的順序大于“god”中的第 3 個位置的“d”字元，是以傳回值為0；
- 同樣，比較 NULL 值時傳回 NULL。
小于運算符（<）
< 是小于運算符，用來判斷左邊的操作數是否小于右邊的操作數；如果小于，傳回值為 1，否則傳回值為 0；如果兩側操作數有一個是 NULL，那麼傳回值也是 NULL。
- 當左邊操作數小于右邊時，傳回值為 1，例如 1<2；
- 當左邊操作數大于右邊時，傳回值為 0，例如“good”第 3 個位置的“o”字元在字母表中的順序大于“god”中的第 3 個位置的“d”字元，是以傳回值為 0；
- 同樣，比較 NULL 值時傳回 NULL。
大于等于運算符（>=）
>= 是大于等于運算符，用來判斷左邊的操作數是否大于或者等于右邊的操作數；如果大于或者等于，傳回值為 1，否則傳回值為 0；如果兩側操作數有一個是 NULL，那麼傳回值也是 NULL。
- 左邊操作數大于或者等于右邊時，傳回值為 1，例如 4>=4；
- 當左邊操作數小于右邊時，傳回值為 0，例如 1>=2；
- 同樣，比較 NULL 值時傳回 NULL。
大于運算符（>）
> 是大于運算符，用來判斷左邊的操作數是否大于右邊的操作數；如果大于，傳回值為 1，否則傳回值為 0；如果兩側操作數有一個是 NULL，那麼傳回值也是 NULL。
由結果可以看到：
- 左邊操作數大于右邊時，傳回值為 1，例如 5.5>5；
- 當左邊操作數小于右邊時，傳回 0，例如 1>2；
- 同樣，比較 NULL 值時傳回 NULL。
IS NULL(ISNULL) 和 IS NOT NULL 運算符
IS NULL 或 ISNULL 運算符用來檢測一個值是否為 NULL，如果為 NULL，傳回值為 1，否則傳回值為 0。ISNULL 可以認為是 IS NULL 的簡寫，去掉了一個空格而已，兩者的作用和用法都是完全相同的。
IS NOT NULL 運算符用來檢測一個值是否為非 NULL，如果是非 NULL，傳回值為 1，否則傳回值為 0。
IS NULL 和 ISNULL 的作用相同，隻是寫法略有不同。ISNULL 和 IS NOT NULL 的傳回值正好相反。
BETWEEN AND 運算符
BETWEEN AND 運算符用來判斷表達式的值是否位于兩個數之間，或者說是否位于某個範圍内，它的文法格式如下：
expr BETWEEN min AND max
expr 表示要判斷的表達式，min 表示最小值，max 表示最大值。如果 expr 大于等于 min 并且小于等于 max，那麼傳回值為 1，否則傳回值為 0。
總結
使用比較運算符時需要注意空值 NULL，大部分比較運算符遇到 NULL 時也會傳回 NULL。
邏輯運算符
邏輯運算符又稱為布爾運算符，用來确定表達式的真和假。包括與、或、非和異或、等邏輯運算符。其傳回值為布爾型，真值（1 或 true）和假值（0 或 false）。MySQL中支援的邏輯運算符如下表所示。
邏輯非運算（NOT 或者 !）
NOT
和
!
都是邏輯非運算符，傳回和操作數相反的結果，具體文法規則為：
- 當操作數為 0（假）時，傳回值為 1；
- 當操作數為非零值時，傳回值為 0；
- 當操作數為 NULL 時，傳回值為 NULL。
NOT 1+1
和
! 1+1
的傳回值不同，這是因為 NOT 與 ! 的優先級不同：
- NOT 的優先級低于 +，是以
NOT 1+1
NOT(1+1)
1+1
NOT 1+1
- 相反，! 的優先級别要高于 +，是以
! 1+1
(!1)+1
!1
注意：
在使用運算符運算時，一定要注意運算符的優先級，如果不能确定計算順序，最好使用括号，以保證運算結果的正确。邏輯與運算符（AND 或者 &&）
AND 和 && 都是邏輯與運算符，具體文法規則為：
- 當所有操作數都為非零值并且不為 NULL 時，傳回值為 1；
- 當一個或多個操作數為 0 時，傳回值為 0；
- 操作數中有任何一個為 NULL 時，傳回值為 NULL。
AND 和 && 的作用相同。
1 AND-1
中沒有 0 或者 NULL，是以傳回值為 1；
1 AND 0
中有操作數 0，是以傳回值為 0；
1 AND NULL
雖然有 NULL，是以傳回值為 NULL。
注意：
AND 運算符可以有多個操作數，但要注意多個操作數運算時，AND 兩邊一定要使用空格隔開，不然會影響結果的正确性。邏輯或運算符（OR 或者 ||）
OR 和 || 都是邏輯或運算符，具體文法規則為：
- 當兩個操作數都為非 NULL 值時，如果有任意一個操作數為非零值，則傳回值為 1，否則結果為 0；
- 當有一個操作數為 NULL 時，如果另一個操作數為非零值，則傳回值為 1，否則結果為NULL；
- 假如兩個操作數均為 NULL 時，則傳回值為 NULL。
OR 和 || 的作用相同。下面是對各個結果的解析：
-
1 OR -1 OR 0
-
1 OR 2
-
1 OR NULL
-
0 OR NULL
-
NULL OR NULL
異或運算（XOR 運算符）
XOR 表示邏輯異或，具體文法規則為：
- 當任意一個操作數為 NULL 時，傳回值為 NULL；
- 對于非 NULL 的操作數，如果兩個操作數都是非 0 值或者都是 0 值，則傳回值為 0；
- 如果一個為0值，另一個為非 0 值，傳回值為 1。
1 XOR 1
和
0 XOR 0
中運算符兩邊的操作數都為非零值，或者都是零值，是以傳回 0；
1 XOR 0
中兩邊的操作數，一個為 0 值，另一個為非 0 值，是以傳回值為 1；
1 XOR NULL
中有一個操作數為 NULL，是以傳回值為 NULL；
1 XOR 1 XOR 1
中有多個操作數，運算符相同，是以運算順序從左到右依次計算，
1 XOR 1
的結果為 0，再與 1 進行異或運算，是以傳回值為 1。
提示：
a XOR b 的計算等同于 (a AND (NOT b))或者 ((NOT a) AND b)。位運算符
所謂位運算，就是按照記憶體中的比特位（Bit）進行操作，這是計算機能夠支援的最小機關的運算。程式中所有的資料在記憶體中都是以二進制形式存儲的，位運算就是對這些二進制資料進行操作。
位運算一般用于操作整數，對整數進行位運算才有實際的意義。整數在記憶體中是以補碼形式存儲的，正數的補碼形式和原碼形式相同，而負數的補碼形式和它的原碼形式是不一樣的，這一點大家要特别注意；這意味着，對負數進行位運算時，操作的是它的補碼，而不是它的原碼。
MySQL 中的整數字面量（常量整數，也就是直接書寫出來的整數）預設以 8 個位元組（Byte）來表示，也就是 64 位（Bit）。
- 例如，5 的二進制形式為：0000 0000 ... 0000 0101
省略号部分都是 0，101 前面總共有 61 個 0。
MySQL 支援 6 種位運算符，包括按位與 & 、按位或 |、按位取反 ~、按位異或 ^、按位左移 << 和按位右移 >> 等位運算符。位運算必須先将資料轉換為補碼，然後在根據資料的補碼進行操作。運算完成後，将得到的值轉換為原來的類型（十進制數），傳回給使用者。
注意：位運算中的
&
、
|
、
~
和邏輯運算中的
&&
、
||
、
!
非常相似。
位或運算符 |
參與
|
運算的兩個二進制位有一個為 1 時，結果就為 1，兩個都為 0 時結果才為 0。例如
1|1
結果為 1，
0|0
結果為0，
1|0
結果為1，這和邏輯運算中的
||
非常類似。
例子：使用位或運算符進行正數運算，SQL 語句如下：
mysql> SELECT 10|15,9|4|2;
+-------+-------+
| 10|15 | 9|4|2 |
+-------+-------+
| 15 | 15 |
+-------+-------+
1 row in set (0.00 sec)
10 的補碼為 1010，15 的補碼為 1111，按位或運算之後，結果為 1111，即整數 15；9 的補碼為 1001，4 的補碼為 0100，2 的補碼為 0010，按位或運算之後，結果為 111，即整數 15。
例子：使用位或運算符進行負數運算，SQL 語句如下：
mysql> SELECT -7|-1;
+----------------------+
| -7|-1 |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)
-7 的補碼為 60 個‘1’加 1001，-1 的補碼為 64 個‘1’，按位或運算之後，結果為 64 個‘1’，即整數 18446744073709551615。
可以發現，任何數和 -1 進行位或運算時，最終結果都是 -1 的十進制數。
位與運算符 &
參與
&
運算的兩個二進制位都為 1 時，結果就為 1，否則為 0。例如
1|1
結果為 1，
0|0
結果為 0，
1|0
結果為 0，這和邏輯運算中的
&&
非常類似。
例子：使用位與運算符進行正數運算，SQL 語句如下：
mysql> SELECT 10 & 15,9 & 4 & 2;
+---------+-----------+
| 10 & 15 | 9 & 4 & 2 |
+---------+-----------+
| 10 | 0 |
+---------+-----------+
1 row in set (0.00 sec)
10 的補碼為 1010，15 的補碼為 1111，按位與運算之後，結果為 1010，即整數 10；9 的補碼為 1001，4 的補碼為 0100，2 的補碼為 0010，按位與運算之後，結果為 0000，即整數 0。
例子：使用位與運算符進行負數運算，SQL 語句如下：
mysql> SELECT -7&-1;
+----------------------+
| -7&-1 |
+----------------------+
| 18446744073709551609 |
+----------------------+
1 row in set (0.01 sec)
-7 的補碼為 60 個‘1’加 1001，-1 的補碼為 64 個‘1’，按位與運算之後，結果為 60 個‘1’加 1001，即整數 18446744073709551609。
可以發現，任何數和 -1 進行位與運算時，最終結果都為任何數本身的十進制數。
位異或運算符 ^
參與
^
運算的兩個二進制位不同時，結果為 1，相同時，結果為 0。例如
1|1
結果為 0，
0|0
結果為 0，
1|0
結果為1。
例子：使用位異或運算符進行正數運算，SQL 語句如下：
mysql> SELECT 10^15,1^0,1^1;
+-------+-----+-----+
| 10^15 | 1^0 | 1^1 |
+-------+-----+-----+
| 5 | 1 | 0 |
+-------+-----+-----+
1 row in set (0.00 sec)
10 的補碼為 1010，15 的補碼為 111，按位異或運算之後，結果為 0101，即整數 5；1 的補碼為 0001，0 的補碼為 0000，按位異或運算之後，結果為 0001；1 和 1 本身二進制位完全相同，是以結果為 0。
例子：使用位異或運算符進行負數運算，SQL 語句如下：
mysql> SELECT -7^-1;
+-------+
| -7^-1 |
+-------+
| 6 |
+-------+
1 row in set (0.00 sec)
-7 的補碼為 60 個‘1’加 1001，-1 的補碼為 64 個‘1’，按位異或運算之後，結果為 110，即整數 6。
位左移運算符 <<
位左移是按指定值的補碼形式進行左移，左移指定位數之後，左邊高位的數值被移出并丢棄，右邊低位空出的位置用 0 補齊。
位左移的文法格式為：
expr << n
其中，n 指定值 expr 要移位的位數，n 必須為非負數。
例子：使用位左移運算符進行正數計算，SQL 語句如下：
mysql> SELECT 1<<2,4<<2;
+------+------+
| 1<<2 | 4<<2 |
+------+------+
| 4 | 16 |
+------+------+
1 row in set (0.00 sec)
1 的補碼為 0000 0001，左移兩位之後變成 0000 0100，即整數 4；4 的補碼為 0000 0100，左移兩位之後變成 0001 0000，即整數 16。
例子：使用位左移運算符進行負數計算，SQL 語句如下：
mysql> SELECT -7<<2;
+----------------------+
| -7<<2 |
+----------------------+
| 18446744073709551588 |
+----------------------+
1 row in set (0.00 sec)
-7 的補碼為 60 個‘1’加 1001，左移兩位之後變成 56 個‘1’加 1110 0100，即整數 18446744073709551588。
位右移運算符 >>
位右移是按指定值的補碼形式進行右移，右移指定位數之後，右邊低位的數值被移出并丢棄，左邊高位空出的位置用 0 補齊。
位右移文法格式為：
expr >> n
其中，n 指定值 expr 要移位的位數，n 必須為非負數。
例子：使用位右移運算符進行正數運算，SQL 語句如下：
mysql> SELECT 1>>1,16>>2;
+------+-------+
| 1>>1 | 16>>2 |
+------+-------+
| 0 | 4 |
+------+-------+
1 row in set (0.00 sec)
1 的補碼為 0000 0001，右移 1 位之後變成 0000 0000，即整數 0；16 的補碼為 0001 0000，右移兩位之後變成 0000 0100，即整數 4。
例子：使用位右移運算符進行負數運算，SQL 語句如下：
mysql> SELECT -7>>2;
+---------------------+
| -7>>2 |
+---------------------+
| 4611686018427387902 |
+---------------------+
1 row in set (0.00 sec)
-7 的補碼為 60 個‘1’加 1001，右移兩位之後變成 0011 加 56 個‘1’加 1110，即整數 4611686018427387902。
位取反運算符 ~
位取反是将參與運算的資料按對應的補碼進行反轉，也就是做 NOT 操作，即 1 取反後變 0，0 取反後變為 1。
例子：下面看一個經典的取反例子，對 1 進行位取反運算，具體如下所示：
mysql> SELECT ~1,~18446744073709551614;
+----------------------+-----------------------+
| ~1 | ~18446744073709551614 |
+----------------------+-----------------------+
| 18446744073709551614 | 1 |
+----------------------+-----------------------+
1 row in set (0.00 sec)
常量 1 的補碼為 63 個‘0‘加 1 個’1‘，位取反後就是 63 個’1‘加一個’0‘，轉換為二進制後就是 18446744073709551614。
可以使用 BIN() 函數檢視 1 取反之後的結果，BIN() 函數的作用是将一個十進制數轉換為二進制數，SQL 語句如下:
mysql> SELECT BIN(~1);
+------------------------------------------------------------------+
| BIN(~1) |
+------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111111110 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)
1 的補碼表示為最右邊位為 1，其他位均為 0，取反操作之後，除了最低位，其他位均變為 1。
例子：使用位取反運算符進行運算，SQL 語句如下：
mysql> SELECT 5 & ~1;
+--------+
| 5 & ~1 |
+--------+
| 4 |
+--------+
1 row in set (0.00 sec)
邏輯運算
5&~1
中，由于位取反運算符‘~’的級别高于位與運算符‘&’，是以先對 1 進行取反操作，結果為 63 個‘1’加一個‘0’，然後再與整數 5 進行與運算，結果為 0100，即整數 4。
MySQL運算符的優先級
運算符的優先級決定了不同的運算符在表達式中計算的先後順序，下表列出了 MySQL 中的各類運算符及其優先級。
- =(指派運算）、:=
- II、OR
- XOR
- &&、AND
- NOT
- BETWEEN、CASE、WHEN、THEN、ELSE
- =(比較運算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
- |
- &
- <<、>>
- -(減号）、+
- *、/、%
- ^
- -(負号）、〜（位反轉）
- ！
可以看出，不同運算符的優先級是不同的。一般情況下，級别高的運算符優先進行計算，如果級别相同，MySQL 按表達式的順序從左到右依次計算。
另外，在無法确定優先級的情況下，可以使用圓括号“()”來改變優先級，并且這樣會使計算過程更加清晰。
MySQL IN和NOT IN用法詳解
MySQL 中的 IN 運算符用來判斷表達式的值是否位于給出的清單中；
- 如果是，傳回值為 1，
- 否則傳回值為 0。
；
- 如果不是，傳回值為 1，
- 否則傳回值為 0。
IN 和 NOT IN 的文法格式如下：
expr IN ( value1, value2, value3 ... valueN )
expr NOT IN ( value1, value2, value3 ... valueN )
- expr 表示要判斷的表達式，
- value1, value2, value3 ... valueN 表示清單中的值。
MySQL 會将 expr 的值和清單中的值逐一對比。
對空值 NULL 的處理
當 IN 運算符的兩側有一個為空值 NULL 時，如果找不到比對項，則傳回值為 NULL；如果找到了比對項，則傳回值為 1。
請看下面的 SQL 語句如下：
mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');
+------------------------+-------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,3,NULL,'thks') |
+------------------------+-------------------------+
| NULL | NULL |
+------------------------+-------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,10,NULL,'thks');
+------------------------+--------------------------+
| NULL IN (1,3,5,'thks') | 10 IN (1,10,NULL,'thks') |
+------------------------+--------------------------+
| NULL | 1 |
+------------------------+--------------------------+
1 row in set (0.00 sec)
NOT IN 恰好相反，當 NOT IN 運算符的兩側有一個為空值 NULL 時，如果找不到比對項，則傳回值為 NULL；如果找到了比對項，則傳回值為 0。
請看下面的 SQL 語句如下：
mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,0,NULL,'thks');
+----------------------------+-----------------------------+
| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,0,NULL,'thks') |
+----------------------------+-----------------------------+
| NULL | NULL |
+----------------------------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT NULL NOT IN (1,3,5,'thks'),10 NOT IN (1,10,NULL,'thks');
+----------------------------+------------------------------+
| NULL NOT IN (1,3,5,'thks') | 10 NOT IN (1,10,NULL,'thks') |
+----------------------------+------------------------------+
| NULL | 0 |
+----------------------------+------------------------------+
1 row in set (0.00 sec)