MySQL函数大合集
一，数值函数
(1) 绝对值函数
mysql> select abs(-1); +---------+ | abs(-1) | +---------+ | 1 | +---------+ 1 row in set (0.00 sec)
(2) 求余函数
mysql> select mod(10,3); +-----------+ | mod(10,3) | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)
(3) 获取整数的函数
mysql> select ceil(123.123); #向上取整 +---------------+ | ceil(123.123) | +---------------+ | 124 | +---------------+ 1 row in set (0.00 sec) mysql> select floor(123.123); #向下取整 +----------------+ | floor(123.123) | +----------------+ | 123 | +----------------+ 1 row in set (0.00 sec)
(4) 获取随机数的函数
mysql> select rand(); #0~1随机数 +-------------------+ | rand() | +-------------------+ | 0.509076374125738 | +-------------------+ 1 row in set (0.00 sec)
(5) 四舍五入的函数
mysql> select round(rand()); +---------------+ | round(rand()) | +---------------+ | 0 | +---------------+ 1 row in set (0.00 sec)
(6) 截取数值的函数
mysql> select truncate(123.123,2); #第二个数值，代表截取长度 +---------------------+ | truncate(123.123,2) | +---------------------+ | 123.12 | +---------------------+ 1 row in set (0.00 sec)
(7) 符号函数
mysql> select sign(-1); #如果是负数对应的-1 整数对应的是1 0对应的是0 +----------+ | sign(-1) | +----------+ | -1 | +----------+ 1 row in set (0.00 sec)
(8) 幂运算函数
mysql> select pow(3,3); +----------+ | pow(3,3) | +----------+ | 27 | +----------+ 1 row in set (0.00 sec)
二，字符串函数
(9) 计算字符串长度的函数
mysql> select length('123'); #查看字节数 +---------------+ | length('123') | +---------------+ | 3 | +---------------+ 1 row in set (0.00 sec) mysql> select char_length('123'); #查看字符串的长度 +--------------------+ | char_length('123') | +--------------------+ | 3 | +--------------------+ 1 row in set (0.00 sec)
(10) 合并字符串的函数
mysql> select concat('hello','world'); +-------------------------+ | concat('hello','world') | +-------------------------+ | helloworld | +-------------------------+ 1 row in set (0.00 sec) mysql> select concat_ws('-','hello','world'); #可以指定分割符 +--------------------------------+ | concat_ws('-','hello','world') | +--------------------------------+ | hello-world | +--------------------------------+ 1 row in set (0.00 sec)
(11) 替换字符串的函数
mysql> select insert('hello',1,1,'H'); #第二个字段指定开始位置，第三个字段解锁位置 第四个字段，替换的值 +-------------------------+ | insert('hello',1,1,'H') | +-------------------------+ | Hello | +-------------------------+ 1 row in set (0.00 sec)
(12) 转换大小写的函数：
mysql> select lower('Hello'); #转为小写 +----------------+ | lower('Hello') | +----------------+ | hello | +----------------+ 1 row in set (0.00 sec) mysql> select upper('Hello'); #转为大写 +----------------+ | upper('Hello') | +----------------+ | HELLO | +----------------+ 1 row in set (0.00 sec)
(13) 获取指定长度的字符串的函数：
mysql> select left('hello',3); #输出从左的长度 +-----------------+ | left('hello',3) | +-----------------+ | hel | +-----------------+ 1 row in set (0.00 sec) mysql> select right('hello',3); #输出从右的长度 +------------------+ | right('hello',3) | +------------------+ | llo | +------------------+ 1 row in set (0.00 sec)
(14) 填充字符串的函数：
mysql> select lpad('hello',10,'@'); #如果不满足指定长度填充 +----------------------+ | lpad('hello',10,'@') | +----------------------+ | @@@@@hello | +----------------------+ 1 row in set (0.00 sec) mysql> select rpad('hello',10,'@'); +----------------------+ | rpad('hello',10,'@') | +----------------------+ | hello@@@@@ | +----------------------+ 1 row in set (0.00 sec)
(15) 删除空格的函数：
mysql> select ltrim(' hello '); #从左删除空格 +-------------------------+ | ltrim(' hello ') | +-------------------------+ | hello | +-------------------------+ 1 row in set (0.00 sec) mysql> select rtrim(' hello '); #从右删除空格 +-------------------------+ | rtrim(' hello ') | +-------------------------+ | hello | +-------------------------+ 1 row in set (0.00 sec) mysql> select trim(' hello '); #从两侧删除空格 +------------------------+ | trim(' hello ') | +------------------------+ | hello | +------------------------+ 1 row in set (0.00 sec)
(16) 删除指定字符串的函数：
mysql> select trim('h' from 'hello'); #指定字段进行删除 +------------------------+ | trim('h' from 'hello') | +------------------------+ | ello | +------------------------+ 1 row in set (0.00 sec)
(17) 重复生成字符串的函数：
mysql> select repeat('233',2); #将 +-----------------+ | repeat('233',2) | +-----------------+ | 233233 | +-----------------+ 1 row in set (0.00 sec)
(18) 空格函数：
mysql> select space(5); +-------+ | l | +-------+ | | +-------+ 1 row in set (0.00 sec)
(19) 替换函数：
mysql> select replace('hello','h','H'); +--------------------------+ | replace('hello','h','H') | +--------------------------+ | Hello | +--------------------------+ 1 row in set (0.00 sec)
(20) 比较字符串大小的函数：
mysql> select strcmp('a','C'); +-----------------+ | strcmp('a','C') | +-----------------+ | -1 | +-----------------+ 1 row in set (0.00 sec)
(21) 获取子字符串的函数：
mysql> select mid('hello',1,3); +------------------+ | mid('hello',1,3) | +------------------+ | hel | +------------------+ 1 row in set (0.00 sec)
(22) 匹配子字符串开始位置的函数：
mysql> select locate('e','hello'); +---------------------+ | locate('e','hello') | +---------------------+ | 2 | +---------------------+ 1 row in set (0.00 sec)
(23) 反转字符串的函数：
mysql> select reverse('12345'); +------------------+ | reverse('12345') | +------------------+ | 54321 | +------------------+ 1 row in set (0.00 sec)
