mysql 常用的3类函数

一、字符串类。

注：mysql在处理字符串时，字符下标从1开始。

1、concat(string1, string2, ......); //连接字符串

mysql> select concat('leng', 'xue', 'gang') as name;
+-------------+
name
+-------------+
lengxuegang
+-------------+
1 row in set (0.00 sec)

2、instr(string, substring); //返回substring首次在string中出现的位置，不存在返回0

mysql> select instr('lengxuegang', 'xue');
+-----------------------------+
instr('lengxuegang', 'xue')
+-----------------------------+
5
+-----------------------------+
1 row in set (0.00 sec)

mysql> select instr('lengxuegang', 'none');
+------------------------------+
instr('lengxuegang', 'none')
+------------------------------+
0
+------------------------------+
1 row in set (0.00 sec)

3、lcase(string); //转换为小写

mysql> select lcase('LengxueGang');
+----------------------+
lcase('LengxueGang')
+----------------------+
lengxuegang
+----------------------+
1 row in set (0.00 sec)

4、left(string, length); //从string左边起取length个字符

mysql> select left('lengxuegang', 4);
+------------------------+
left('lengxuegang', 4)
+------------------------+
leng
+------------------------+
1 row in set (0.01 sec)

5、length(string); //返回string的长度

mysql> select length('lengxuegang');
+-----------------------+
length('lengxuegang')
+-----------------------+
11
+-----------------------+
1 row in set (0.25 sec)

6、locate(substring, string, [start_position]); //从start_position出开始查找，返回substring在string中首次出现的位置。其功能与instr类似，不过注意string与substring的位置是不一样的。

mysql> select locate('leng', 'lengxueganglengxuegang', 4);
+---------------------------------------------+
locate('leng', 'lengxueganglengxuegang', 4)
+---------------------------------------------+
12
+---------------------------------------------+
1 row in set (0.00 sec)

7、ltrim(string); //去除左边的空格

mysql> select ltrim(' leng');
+------------------+
ltrim(' leng')
+------------------+
leng
+------------------+
1 row in set (0.00 sec)

8、repeat(string, count); //重复string count次

mysql> select repeat('leng', 4);
+-------------------+
repeat('leng', 4)
+-------------------+
lenglenglengleng
+-------------------+
1 row in set (0.00 sec)

9、replace(string, search_str, replace_str); //在string中将search_str替换为replace_str

mysql> select replace('lengxueganglengxuegang', 'leng', 'cheng');
+----------------------------------------------------+
replace('lengxueganglengxuegang', 'leng', 'cheng')
+----------------------------------------------------+
chengxuegangchengxuegang
+----------------------------------------------------+
1 row in set (0.05 sec)

10、rtrim(string); //去除右端空格

mysql> select rtrim('leng ');
+--------------------+
rtrim('leng ')
+--------------------+
leng
+--------------------+
1 row in set (0.00 sec)

11、strcmp(string1， string2); //比较两个字符串大小，按大小关系分别返回1、0、-1

mysql> select strcmp('leng', 'cheng');
+-------------------------+
strcmp('leng', 'cheng')
+-------------------------+
1
+-------------------------+
1 row in set (0.04 sec)

mysql> select strcmp('cheng', 'leng');
+-------------------------+
strcmp('cheng', 'leng')
+-------------------------+
-1
+-------------------------+
1 row in set (0.00 sec)

mysql> select strcmp('leng', 'leng');
+------------------------+
strcmp('leng', 'leng')
+------------------------+
0
+------------------------+
1 row in set (0.00 sec)

12、substring(string, start_pos, length); //从string的start_pos开始，取length个字符

mysql> select substring('lengxuegang', 5, 3);
+--------------------------------+
substring('lengxuegang', 5, 3)
+--------------------------------+
xue
+--------------------------------+
1 row in set (0.00 sec)

13、trim(); //去除字符串两端空格

mysql> select trim(' leng ');
+-------------------+
trim(' leng ')
+-------------------+
leng
+-------------------+
1 row in set (0.00 sec)

14、ucase(string); //转换为大写

mysql> select ucase('lengxuegang');
+----------------------+
ucase('lengxuegang')
+----------------------+
LENGXUEGANG
+----------------------+
1 row in set (0.00 sec)

15、right(string, length); //取string右边length个字符

mysql> select right('lengxuegang', 4);
+-------------------------+
right('lengxuegang', 4)
+-------------------------+
gang
+-------------------------+
1 row in set (0.00 sec)

16、space(count); //生成count个空格

mysql> select space(5);
+----------+
space(5)
+----------+

+----------+
1 row in set (0.00 sec)

17、lpad(string, length, pad); //在string的左端填充pad，直到其长度达到length

mysql> select lpad('leng', 10, 'dacb');
+--------------------------+
lpad('leng', 10, 'dacb')
+--------------------------+
dacbdaleng
+--------------------------+
1 row in set (0.00 sec)

18、rpad(); //在string的右端填充pad，直到其长度达到length

mysql> select rpad('leng', 10, 'dacb');
+--------------------------+
rpad('leng', 10, 'dacb')
+--------------------------+
lengdacbda
+--------------------------+
1 row in set (0.00 sec)

19、coalesce(value1, value2, ...) 返回第一个非null值，如果全为null，则返回null

mysql> select coalesce(null, 1, 2);
+----------------------+
coalesce(null, 1, 2)
+----------------------+
1
+----------------------+
1 row in set (0.03 sec)

二、数学类

1、abs(num); //返回绝对值

mysql> select abs(-3.5);
+-----------+
abs(-3.5)
+-----------+
3.5
+-----------+
1 row in set (0.03 sec)

2、bin(decimal_num); //十进制转二进制

mysql> select bin(12);
+---------+
bin(12)
+---------+
1100
+---------+
1 row in set (0.05 sec)

3、ceiling(num); //向上取整

mysql> select ceiling(3.4);
+--------------+
ceiling(3.4)
+--------------+
4
+--------------+
1 row in set (0.00 sec)

mysql> select ceiling(-3.4);
+---------------+
ceiling(-3.4)
+---------------+
-3
+---------------+
1 row in set (0.00 sec)

4、conv(num, from_base, to_base); //进制转换

mysql> select conv(10, 10, 2);
+-----------------+
conv(10, 10, 2)
+-----------------+
1010
+-----------------+
1 row in set (0.00 sec)

5、floor(num); //向下取整

mysql> select floor(3.6);

+------------+
floor(3.6)
+------------+
3
+------------+
1 row in set (0.00 sec)

mysql> select floor(-3.6);
+-------------+
floor(-3.6)
+-------------+
-4
+-------------+

1 row in set (0.00 sec)

6、least(num1, num2, num3, ......); //取最小值

mysql> select least(10, 4, -4, 0);
+---------------------+
least(10, 4, -4, 0)
+---------------------+
-4
+---------------------+
1 row in set (0.10 sec)

7、mod(); //取余

mysql> select mod(10, 3);
+------------+
mod(10, 3)
+------------+
1
+------------+
1 row in set (0.00 sec)

8、power(num, power); //幂运算

mysql> select power(3, 3);
+-------------+
power(3, 3)
+-------------+
27
+-------------+
1 row in set (0.08 sec)

9、rand([seed]); //随机数

mysql> select rand();
+------------------+
rand()
+------------------+
0.10342728263086
+------------------+
1 row in set (0.00 sec)

mysql> select rand();
+------------------+
rand()
+------------------+
0.98467650821868
+------------------+
1 row in set (0.00 sec)

10、round(number, [decimals]); //四舍五入，decimals为小数位数

mysql> select round(1.2345);
+---------------+
round(1.2345)
+---------------+
1
+---------------+
1 row in set (0.00 sec)

mysql> select round(1.2345, 3);
+------------------+
round(1.2345, 3)
+------------------+
1.235
+------------------+
1 row in set (0.00 sec)

11、sign(number); //返回符号，正负或0

mysql> select sign(0);
+---------+
sign(0)
+---------+
0
+---------+
1 row in set (0.00 sec)

mysql> select sign(2);
+---------+
sign(2)
+---------+
1
+---------+
1 row in set (0.00 sec)

mysql> select sign(-2);
+----------+
sign(-2)
+----------+
-1
+----------+
1 row in set (0.00 sec)

12、sqrt(num); //开平方

mysql> select sqrt(3);
+-----------------+
sqrt(3)
+-----------------+
1.7320508075689
+-----------------+
1 row in set (0.00 sec)

13、greatest(value1, value2, ...); //取最大值

mysql> select greatest(2, 3, 10);
+--------------------+
greatest(2, 3, 10)
+--------------------+
10
+--------------------+
1 row in set (0.00 sec)

三、日期时间类

1、current_date(); //返回当前日期

mysql> select current_date();
+----------------+
current_date()
+----------------+
2012-07-01
+----------------+
1 row in set (0.04 sec)

2、current_time(); //返回当前时间

mysql> select current_time();
+----------------+
current_time()
+----------------+
02:05:41
+----------------+
1 row in set (0.00 sec)

3、current_timestamp(); //返回当前时间戳

mysql> select current_timestamp();
+---------------------+
current_timestamp()
+---------------------+
2012-07-01 02:06:12
+---------------------+
1 row in set (0.04 sec)

4、now(); //返回当前时间

mysql> select now();
+---------------------+
now()
+---------------------+
2012-07-01 02:06:57
+---------------------+
1 row in set (0.00 sec)

相关推荐：

推荐MySQL常用函数+福利

PHP中的MYSQL常用函数php下操作数据库必备

PHP中的MYSQL常用函数(php下操作数据库必备)_PHP教程

以上就是mysql 常用的三类函数的详细内容，更多请关注php中文网其它相关文章！