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-******** |
+---------------------------------+
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-******** |
+--------------------------------------+
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,)
//返回第⼀个与字符串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,);
⽰例：
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,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 获取⽇期和时间各部分值：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。