MySQL中的常用函數

在MySQL中，函數不僅可以出現在select語句及其子句中，而且還可以出現在update、delete語句中。

常用的函數有：

1. 字符串函數；主要用於處理字符串。

2. 數值函數；主要用於處理數字。

3. 日期和時間函數；主要用於處理日期和事件。

4. 系統信息函數；獲取系統信息。

1. 使用字符串函數：

雖然每種數據庫都支持SQL，但是每種數據庫擁有各自所支持的函數。

1.1 合並字符串函數concat() 和 concat_ws()：

在MySQL中可以通過函數concat()和concat_ws()將傳入的參數連接成為一個字符串。

語法定義為：

concat(s1, s2,...sn)
//該函數會將傳入的參數連接起來返回合並的字符串類型的數據。如果其中一個參數為null，則返回值為null.

示例：

mysql> select concat('my','s','ql');
+-----------------------+
| concat('my','s','ql') |
+-----------------------+
| mysql |
+-----------------------+
1 row in set (0.00 sec)
mysql> select concat('my','s','ql',null);
+----------------------------+
| concat('my','s','ql',null) |
+----------------------------+
| NULL |
+----------------------------+
1 row in set (0.00 sec)
mysql> select concat(curdate(), 12.2);
+-------------------------+
| concat(curdate(), 12.2) |
+-------------------------+
| 2016-08-2512.2 |
+-------------------------+
1 row in set (0.00 sec)
//說明：將當前時間和數值12.2合並。即concat()函數不僅可以接受字符串參數，而且還可以接受其他類型參數。

concat_ws()的定義：

concat_ws(sep,s1,s2,...sn)
//該函數與concat()相比，多了一個表示分隔符的seq參數，不僅將傳入的其他參數連接起來，而且還會通過分隔符將各個字符串分割開來。
//分隔符可以是一個字符串，也可以是其他參數。如果分割符為null，則返回結果為null。函數會忽略任何分割符後的參數null.

示例：

mysql> select concat_ws('-','020','87658907');
+---------------------------------+
| concat_ws('-','020','87658907') |
+---------------------------------+
| 020-87658907 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select concat_ws(null,'020','87658907');
+----------------------------------+
| concat_ws(null,'020','87658907') |
+----------------------------------+
| NULL |
+----------------------------------+
1 row in set (0.00 sec)
//當分隔符為null時，則返回結果為null
mysql> select concat_ws('-','020',null,'87658907');
+--------------------------------------+
| concat_ws('-','020',null,'87658907') |
+--------------------------------------+
| 020-87658907 |
+--------------------------------------+
1 row in set (0.00 sec)
//不是第一個參數的null將被忽略

1.2 比較字符串大小函數strcmp()：

strcmp()定義為：

strcmp(str1,str2);
//如果參數str1大於str2，返回1；如果str1小於str2,則返回-1；如果str1等於str2，則返回0；

示例：

mysql> select strcmp('abc','abd'),strcmp('abc','abc'),strcmp('abc','abb');
+---------------------+---------------------+---------------------+
| strcmp('abc','abd') | strcmp('abc','abc') | strcmp('abc','abb') |
+---------------------+---------------------+---------------------+
| -1 | 0 | 1 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

1.3 獲取字符串長度函數length()和字符數函數char_length()：

length()的定義如下：

length(str)

char_length(str)的定義如下：

char_length(str)

示例：

mysql> select length('mysql'),length('漢字'),char_length('mysql'),char_length('漢字');
+-----------------+----------------+----------------------+---------------------+
| length('mysql') | length('漢字') | char_length('mysql') | char_length('漢字') |
+-----------------+----------------+----------------------+---------------------+
| 5 | 4 | 5 | 4 |
+-----------------+----------------+----------------------+---------------------+
1 row in set, 2 warnings (0.00 sec)
//字符串‘MySQL'共有5個字符，但是占6個字節空間。這是因為每個字符串都是以\0結束。兩個函數都是獲取字符串的字符數而不是所占空間大小。

1.4 字母的大小寫轉換upper()和lower()：

字母大小轉換函數：upper(s); ucase(s);
字母小寫轉換函數：lower(s); lcase(s);

示例：

mysql> select upper('mysql'),ucase('mYsql'),lower('MYSQL'),lcase('MYsql');
+----------------+----------------+----------------+----------------+
| upper('mysql') | ucase('mYsql') | lower('MYSQL') | lcase('MYsql') |
+----------------+----------------+----------------+----------------+
| MYSQL | MYSQL | mysql | mysql |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)

1.5 查找字符串：

mysql中提供了豐富的函數去查找字符串的位置。分別有find_in_set()函數、field()函數、locate()函數、position()函數和instr()函數。同時還提供了查找指定位置的字符串的函數elt()。

1.5.1 返回字符串位置的find_in_set()函數：

函數定義為：

find_in_set(str1,str2) 
//會返回在字符串str2中與str1相匹配的字符串的位置，參數str2字符串中將包含若干個用逗號隔開的字符串。

示例：

mysql> select find_in_set('mysql','oracle,mysql,db2');
+-----------------------------------------+
| find_in_set('mysql','oracle,mysql,db2') |
+-----------------------------------------+
| 2 |
+-----------------------------------------+
1 row in set (0.00 sec)

1.5.2 返回指定字符串位置的field()函數：

函數定義為：

filed(str,str1,str2...)
//返回第一個與字符串str匹配的字符串的位置。

示例：

mysql> select field('mysql','oracle','db2','redis','mysql');
+-----------------------------------------------+
| field('mysql','oracle','db2','redis','mysql') |
+-----------------------------------------------+
| 4 |
+-----------------------------------------------+
1 row in set (0.00 sec)

1.5.3 返回子字符串相匹配的開始位置：

mysql中有三個函數可以獲取子字符串相匹配的開始位置，分別是locate()、position()、instr()函數。

locate(str1,str) //返回參數str中字符串str1的開始位置

position(str1 in str) 和 instr(str,str1)

示例：

mysql> select locate('sql','mysql'),position('sql' in 'mysql'),instr('mysql','sql');
+-----------------------+----------------------------+----------------------+
| locate('sql','mysql') | position('sql' in 'mysql') | instr('mysql','sql') |
+-----------------------+----------------------------+----------------------+
| 3 | 3 | 3 |
+-----------------------+----------------------------+----------------------+
1 row in set (0.00 sec)

1.5.4 返回指定位置的字符串的elt()函數：

函數語法為：

elt(n,str1,str2...);

示例：

mysql> select elt(1,'mysql','db2','oracle');
+-------------------------------+
| elt(1,'mysql','db2','oracle') |
+-------------------------------+
| mysql |
+-------------------------------+
1 row in set (0.00 sec)

1.5.5 選擇字符串的make_set()函數：

函數定義為：

make_set(num,str1,str2...strn)

示例：

mysql> select bin(5),make_set(5,'mysql','db2','oracle','redus');
+--------+--------------------------------------------+
| bin(5) | make_set(5,'mysql','db2','oracle','redus') |
+--------+--------------------------------------------+
| 101 | mysql,oracle |
+--------+--------------------------------------------+
1 row in set (0.00 sec)
//make_set()首先會將數值num轉換成二進制數，然後按照二進制從參數str1,str2,...,strn中選取相應的字符串。再通過二進制從右到左的順序讀取該值，如果值為1選擇該字符串，否則將不選擇該字符串。

1.6 從現有字符串中截取子字符串：

截取子字符串的函數有：left(),right(),substring(),mid();

1.6.1 從左邊或右邊截取子字符串：

函數定義為：

left(str,num)
//返回字符串str中包含前num個字母（從左邊數）的字符串。
right(str,num)
//返回字符串str中包含後num個字母（從右邊數）的字符串。

示例：

mysql> select left('mysql',2),right('mysql',3);
+-----------------+------------------+
| left('mysql',2) | right('mysql',3) |
+-----------------+------------------+
| my | sql |
+-----------------+------------------+
1 row in set (0.00 sec)

1.6.2 截取指定位置和長度的字符串：

可以通過substring()和mid()函數截取指定位置和長度的字符串。

函數語法為：

substring(str,num,len) //返回字符串str中的第num個位置開始長度為len的子字符串。
mid(str,num,len)

示例：

mysql> select substring('zhaojd',2,3),mid('zhaojd',2,4);
+-------------------------+-------------------+
| substring('zhaojd',2,3) | mid('zhaojd',2,4) |
+-------------------------+-------------------+
| hao | haoj |
+-------------------------+-------------------+
1 row in set (0.00 sec)

1.7 去除字符串的首尾空格：

去除字符串首尾空格的函數有：ltrim()、rtrim()、trim()

1.7.1 去除字符串開始處的空格：

函數定義如下：

ltrim(str) //返回去掉開始處空格的字符串

示例：

mysql> select length(concat('-',' mysql ','-')),length(concat('-',ltrim(' mysql '),'-'));
+-----------------------------------+------------------------------------------+
| length(concat('-',' mysql ','-')) | length(concat('-',ltrim(' mysql '),'-')) |
+-----------------------------------+------------------------------------------+
| 9 | 8 |
+-----------------------------------+------------------------------------------+
1 row in set (0.00 sec)

1.7.2 去除字符串結束處的空格：

rtrim(str) //返回去掉結束處空格的字符串。

示例：

mysql> select length(concat('-',' mysql ','-')) ,length(concat('-',rtrim(' mysql '),'-'));
+-----------------------------------+------------------------------------------+
| length(concat('-',' mysql ','-')) | length(concat('-',rtrim(' mysql '),'-')) |
+-----------------------------------+------------------------------------------+
| 9 | 8 |
+-----------------------------------+------------------------------------------+
1 row in set (0.00 sec)

1.7.3 去除字符串首尾空格：

trim(str) //返回去掉首尾空格的字符串

示例：

mysql> select concat(' mysql ') origi,length(concat(' mysql ')) orilen, concat(trim(' mysql ')) after, length(concat(trim(' mysql '))) afterlen;
+---------+--------+-------+----------+
| origi | orilen | after | afterlen |
+---------+--------+-------+----------+
| mysql | 7 | mysql | 5 |
+---------+--------+-------+----------+
1 row in set (0.00 sec)

1.8 替換字符串：

實現替換字符串的功能，分別為insert()和replace()

1.8.1 使用insert()函數：

函數定義為：

insert(str,pos,len,newstr)
//insert()函數會將字符串str中的pos位置開始長度為len的字符串用字符串newstr來替換。
//如果參數pos的值超過字符串長度，則返回值為原始字符串str。
//如果len的長度大於原來str中所剩字符串的長度，則從位置pos開始進行全部替換。若任何一個參數為null,則返回值為null.

示例：

mysql> select insert('這是mysql數據庫系統',3,5,'oracle') bieming;
+----------------------+
| bieming |
+----------------------+
| 這oracleql數據庫系統 |
+----------------------+
1 row in set, 1 warning (0.00 sec)

1.8.1 使用replace()函數：

函數的定義為：

replace(str,substr,newstr) //將字符串str中的子字符串substr用字符串newstr來替換。

示例：

mysql> select replace('這是mysql數據庫','mysql','db2') bieming;
+---------------+
| bieming |
+---------------+
| 這是db2數據庫 |
+---------------+
1 row in set, 1 warning (0.00 sec)

2. 使用數值函數：

2.1 獲取隨機數：

通過rand()和rand(x)函數來獲取隨機數。這兩個函數都會返回0-1之間的隨機數，其中rand()函數返回的數是完全隨機的，而rand(x)函數返回的隨機數值是完全相同的。

示例：

mysql> select rand(),rand(),rand(3),rand(3);
+--------------------+--------------------+--------------------+--------------------+
| rand() | rand() | rand(3) | rand(3) |
+--------------------+--------------------+--------------------+--------------------+
| 0.9600886758045188 | 0.7006410161970565 | 0.9057697559760601 | 0.9057697559760601 |
+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

2.2 獲取整數的函數：

在具體應用中，如果想要獲取整數，可以通過ceil()和floor()函數來實現。

ceil()函數的定義為：

ceil(x) //函數返回大於或等於數值x的最小整數。
floor() //函數返回小於或等於數值x的最大整數。

示例：

mysql> select ceil(4.3),ceil(-2.5),floor(4.3),floor(-2.5);
+-----------+------------+------------+-------------+
| ceil(4.3) | ceil(-2.5) | floor(4.3) | floor(-2.5) |
+-----------+------------+------------+-------------+
| 5 | -2 | 4 | -3 |
+-----------+------------+------------+-------------+
1 row in set (0.00 sec)

2.3 截取數值函數：

可以通過truncate()對數值的小數位進行截取：

函數定義為：

truncate(x,y) //返回數值x,保留小數點後y位

示例：

mysql> select truncate(903.343434,2),truncate(903.343,-1);
+------------------------+----------------------+
| truncate(903.343434,2) | truncate(903.343,-1) |
+------------------------+----------------------+
| 903.34 | 900 |
+------------------------+----------------------+
1 row in set (0.00 sec)

2.4 四捨五入函數：

對數值進行四捨五入可以通過round()函數實現:
round(x)
//函數返回值x經過四捨五入操作後的數值。
round(x,y)
//返回數值x保留到小數點後y位的值。在具體截取數據時需要進行四捨五入的操作。

示例：

mysql> select round(903.53567),round(-903.53567),round(903.53567,2),round(903.53567,-1);
+------------------+-------------------+--------------------+---------------------+
| round(903.53567) | round(-903.53567) | round(903.53567,2) | round(903.53567,-1) |
+------------------+-------------------+--------------------+---------------------+
| 904 | -904 | 903.54 | 900 |
+------------------+-------------------+--------------------+---------------------+
1 row in set (0.00 sec)

3. 使用日期和時間函數：

3.1 獲取當前日期和時間的函數：

3.1.1 獲取當前日期和時間（日期 + 時間）：

MySQL中可以通過四個函數獲取當前日期和時間，分別是now(),current_timestamp(),localtime(),sysdate()，這四個函數不僅可以獲取當前日期和時間，而且顯示的格式也一樣。推薦使用now()

示例：

mysql> select now(),current_timestamp(),localtime(),sysdate();
+---------------------+---------------------+---------------------+---------------------+
| now() | current_timestamp() | localtime() | sysdate() |
+---------------------+---------------------+---------------------+---------------------+
| 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 | 2016-08-25 16:09:20 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

3.1.2 獲取當前日期：

獲取當前日期的函數curdate()和current_date()函數。

示例：

mysql> select curdate(),current_date();
+------------+----------------+
| curdate() | current_date() |
+------------+----------------+
| 2016-08-25 | 2016-08-25 |
+------------+----------------+
1 row in set (0.00 sec)

3.1.3 獲取當前時間：

獲取當前時間的函數，curtime()或者current_time();推薦使用curtime();

示例：

mysql> select curtime(),current_time();
+-----------+----------------+
| curtime() | current_time() |
+-----------+----------------+
| 16:15:04 | 16:15:04 |
+-----------+----------------+
1 row in set (0.00 sec)

3.2 獲取日期和時間各部分值：

在MySQL中，可以通過各種函數來獲取當前日期和時間的各部分值，其中year()函數返回日期中的年份，quarter()函數返回日期屬於第幾個季度，month()函數返回日期屬於第幾個月，week()函數返回日期屬於第幾個星期，dayofmonth()函數返回日期屬於當前月的第幾天,hour()函數返回時間的小時，minute()函數返回時間的分鐘，second()函數返回時間的秒。

示例：

mysql> select now(),year(now()),quarter(now()),month(now()),week(now()),dayofmonth(now()),hour(now()),minute(now()),second(now());
+---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+
| now() | year(now()) | quarter(now()) | month(now()) | week(now()) | dayofmonth(now()) | hour(now()) | minute(now()) | second(now()) |
+---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+
| 2016-08-25 16:27:37 | 2016 | 3 | 8 | 34 | 25 | 16 | 27 | 37 |
+---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+
1 row in set (0.00 sec)

3.2.1 關於月的函數：

示例：

mysql> select now(),month(now()),monthname(now());
+---------------------+--------------+------------------+
| now() | month(now()) | monthname(now()) |
+---------------------+--------------+------------------+
| 2016-08-25 16:29:37 | 8 | August |
+---------------------+--------------+------------------+
1 row in set (0.00 sec)
//month()函數返回數字表示的月份，monthname()函數返回了英文表示的月份。

3.2.2 關於星期的函數：

示例：

mysql> select now(),week(now()),weekofyear(now()),dayname(now()),dayofweek(now()),weekday(now());
+---------------------+-------------+-------------------+----------------+------------------+----------------+
| now() | week(now()) | weekofyear(now()) | dayname(now()) | dayofweek(now()) | weekday(now()) |
+---------------------+-------------+-------------------+----------------+------------------+----------------+
| 2016-08-25 16:34:35 | 34 | 34 | Thursday | 5 | 3 |
+---------------------+-------------+-------------------+----------------+------------------+----------------+
1 row in set (0.00 sec)

3.2.3 關於天的函數：

示例：

mysql> select now(),dayofyear(now()),dayofmonth(now());
+---------------------+------------------+-------------------+
| now() | dayofyear(now()) | dayofmonth(now()) |
+---------------------+------------------+-------------------+
| 2016-08-25 16:37:12 | 238 | 25 |
+---------------------+------------------+-------------------+
1 row in set (0.00 sec)

3.2.4 獲取指定值的extract()：

函數定義為：

extract(type from date)
//上述函數會從日期和時間參數date中獲取指定類型參數type的值。type的取值可以是：year，month，day，hour，minute和second

示例：

mysql> select now(),extract(year from now()) year,extract(month from now()) month,extract(day from now()) day,extract(hour from now()) hour,extract(mi
nute from now()) minute,extract(second from now()) second;
+---------------------+------+-------+------+------+--------+--------+
| now() | year | month | day | hour | minute | second |
+---------------------+------+-------+------+------+--------+--------+
| 2016-08-25 16:43:45 | 2016 | 8 | 25 | 16 | 43 | 45 |
+---------------------+------+-------+------+------+--------+--------+
1 row in set (0.00 sec)

3.3 計算日期和時間的函數：

3.3.1 與默認日期和時間操作：

兩個函數來實現與默認日期和時間的操作，分別為to_days()和from_days()

to_days(date):該函數計算日期參數date與默認日期和時間（0000年1月1日）之間的想個天數。

from_days(number)：該函數計算從默認日期和時間（0000年1月1日）開始經歷number天後的日期和時間。

示例：

mysql> select now(),to_days(now()),from_days(to_days(now()));
+---------------------+----------------+---------------------------+
| now() | to_days(now()) | from_days(to_days(now())) |
+---------------------+----------------+---------------------------+
| 2016-08-25 16:50:30 | 736566 | 2016-08-25 |
+---------------------+----------------+---------------------------+
1 row in set (0.00 sec)
//指定兩個日期之間相隔的天數；
mysql> select now(),datediff(now(),'2000-12-01');
+---------------------+------------------------------+
| now() | datediff(now(),'2000-12-01') |
+---------------------+------------------------------+
| 2016-08-25 16:52:16 | 5746 |
+---------------------+------------------------------+
1 row in set (0.00 sec)

3.3.2 與指定日期和時間操作：

adddate(date,n)函數：該函數計算日期參數date加上n天後的日期。

subdate(date,n)函數：該函數計算日期參數date減去n天後的日期。

adddate(d,interval expr type)：返回日期參數d加上一段時間後的日期，表達式參數expr決定了時間的長度，參數type決定了所操作的對象。

subdate(d,interval expr type)：返回日期參數d減去一段時間後的日期，表達式expr決定了時間的長度。參數type決定了所操作的對象。

addtime(time,n):計算時間參數time加上n秒後的時間。

subtime(time,n):計算時間參數time減去n秒後的時間。

示例一：

mysql> select curdate(),adddate(curdate(),5),subdate(curdate(),5);
+------------+----------------------+----------------------+
| curdate() | adddate(curdate(),5) | subdate(curdate(),5) |
+------------+----------------------+----------------------+
| 2016-08-25 | 2016-08-30 | 2016-08-20 |
+------------+----------------------+----------------------+
1 row in set (0.00 sec) 

示例二：

mysql> select curdate(),adddate(curdate(),interval '2,3' year_month),subdate(curdate(),interval '2,3' year_month);
+------------+----------------------------------------------+----------------------------------------------+
| curdate() | adddate(curdate(),interval '2,3' year_month) | subdate(curdate(),interval '2,3' year_month) |
+------------+----------------------------------------------+----------------------------------------------+
| 2016-08-25 | 2018-11-25 | 2014-05-25 |
+------------+----------------------------------------------+----------------------------------------------+
1 row in set (0.00 sec)

示例三：

mysql> select curtime(),addtime(curtime(),5),subtime(curtime(),5);
+-----------+----------------------+----------------------+
| curtime() | addtime(curtime(),5) | subtime(curtime(),5) |
+-----------+----------------------+----------------------+
| 17:12:21 | 17:12:26 | 17:12:16 |
+-----------+----------------------+----------------------+
1 row in set (0.00 sec)

4. 使用系統信息函數：

select version(),database(),user();

示例：

mysql> select version(),database(),user();
+------------+------------+----------------+
| version() | database() | user() |
+------------+------------+----------------+
| 5.5.51-log | NULL | root@localhost |
+------------+------------+----------------+
1 row in set (0.00 sec)
//獲取 auto_increment約束的最後ID
select last_insert_id();

以上所述是小編給大家介紹的MySQL中的常用函數，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對網站的支持！