MYSQL函数

函数表示对输入参数值返回一个具有特定关系的值。列举了一部分常用的函数。

数学函数

• ABS(x): 返回x的绝对值
• pi(): 返回圆周率值
• SQRT(x): 返回x的二次平方根
• MOD(x,y): 返回x对y取余的结果
• CEIL(x) / CEILING(x): 返回不小于x的最小整数，返回值转化成一个BIGINT
• FLOOR(x): 返回一个不大于x的最大整数，返回值转化为一个BIGINT
• RAND() / RAND(x): RAND用来返回一个随机浮点数v，范围在 [0,1.0] 之间，指定的参数x用作随机数种子
• ROUND(x): 返回对x四舍五入的值
• ROUND(x,y): 返回对x四舍五入的值，保留y位小数
• TRUNCATE(x,y): 返回被舍去至小数点y为的x；若y为0，结果不带有小数点或者小数部分；若y为负数，截去小数点左起第y位开始后面所有低位的值；
• SIGN(x): 根据x是正负或者0，相应地返回-1/0/1；
• POW(x,y) / POWER(x,y): 返回x的y次方的结果；
• EXP(x): 计算以e为底的x次方；
• LOG(x): 返回x的自然对数，x相对于基数e的对数；
• LOG10(x): 返回x的基数为10的对数；
• RADIANS(x): 将参数x由角度转化为弧度；
• DEGREES(x): 将参数x由弧度转化为角度；

SELECT ABS(-2), pi(), SQRT(9), MOD(3,2), CEIL(-3.35), CEILING(4.5), FLOOR(4.5), FLOOR(-3.35), RAND(), ROUND(-1.14), ROUND(-1.69);

结果：

ABS(-2)	pi()	SQRT(9)	MOD(3,2)	CEIL(-3.35)	CEILING(4.5)	FLOOR(4.5)	FLOOR(-3.35)	RAND()	ROUND(-1.14)	ROUND(-1.69)
2	3.1415923	3	1	-3	5	4	-4	0.18981676287000698	-1	-2

除了上述的这些函数，还有一些其他的函数用来处理不同场景的数据；

字符串函数

• CHAR_LENGTH(str): 返回字符串str包含的字符个数；
• LENGTH(str): 返回字符串str的字节长度；

英文字符的个数和所占的字节长度相同

• CONCAT(s1,s2,...): 返回结果为连接参数产生的字符串，如果有任意一个参数为NULL，返回结果为NULL；如果所有参数都为非二进制字符串，返回结果为非二进制字符串；如果任一参数为二进制字符串，返回结果为二进制字符串；

SELECT concat('my',' ', 'name', ' ', 'is', ' ', '...');  -> my name is ...
SELECT concat('my',' ', 'name', ' ', NULL, ' ', '...');  -> NULL

• CONCAT_WS(x,s1,s2,..): 以x作为分隔符，返回以x连接的字符串结果，忽略被连接的NULL值；如果x为NULL，则返回结果为NULL；

SELECT CONCAT_WS('-', 'lst', '2nd', '3rd');   -> lst-2nd-3rd
SELECT CONCAT_WS('*', '2', '2', NULL, '4');   -> 2*2*4

• INSERT(s1,x,len,s2): 从s1的底x位开始的len个字符被替换为s2；
• LOWER(str) / LCASE(str): 将str替换为小写字符串；
• UPPER(str) / UCASE(str): 将str替换为大写字符串；
• LEFT(s,n): 获取字符串s从左边开始的n个字符；
• RIGHT(s,n): 获取字符串s从右边开始的n个字符；
• LPAD(s1,len,s2) / RPAD(s1,len,s2): 左边或者右边用字符串s2将字符串s1填充到len长度，如果s1本身长度大于len，则将字符串s1截断为len长度的字符串；

SELECT LPAD('hello', 4, '??'), LPAD('hello', 10, '??');

LPAD('hello', 4, '??')	LPAD('hello', 10, '??')
hell	?????hello

• LTRIM(s) / RTRIM(s) / TRIM(s): 删除字符串左侧/右侧/两侧空格并且返回删除空格后的字符串；
• TRIM(s1 FROM s): 删除字符串s两端所有的子串s1

SELECT TRIM('xy' FROM 'xyxboyyokxxyxy');

TRIM('xy' FROM 'xyxboyyokxxyxy')
xboyyokx

• REPEAT(s,n): 重复生成n遍字符串s；
• SPACE(n): 返回一个由n个空格组成的字符串；
• REPLACE(s,s1,s2): 使用s2代替字符串s中所有的字符串s1；
• STRCMP(s1,s2): 比较字符串大小的函数，如果两个字符串相同，则返回0；s1<s2返回TRUE
• SUBSTRING(s,n,len) / MID(s,n,len): 如果n为正数，从左边第n个开始取，取s的len个字符；如果n为负数，从右边第n个开始取，取s的len个字符；如果没有指定len，则从第n个开始取到最后；
• LOCATE(str1, str) / POSITION(str1 IN str) / INSTR(str, str1): 返回子字符串str1在字符串str中的开始位置；

SELECT LOCATE('ball', 'footballball'), POSITION('ball' IN 'footballball'), INSTR('footballball', 'ball');
-- 返回的结果都是：5

• REVERSE(str): 将字符串str反转
• ELT(N,s1,s2,s3): 返回指定位置的字符串，比如：n=3，则返回s3，n=4，则返回NULL；
• FIELD(s,s1,s2,s3,s4,...): 返回s作为字串在这些字符串中第一次出现的位置；
• FIND_IN_SET(s,sList): 返回s在sList中的位置；

SELECT FIND_IN_SET('Hi', 'hihi,Hey,Hi,bas');  -> 3

• MAKE_SET(x,s1,s2,...sn): 函数按x的二进制从s1,s2,..sn中选取字符串；

SELECT
 MAKE_SET(1,'a','b','c') as col_1,
 MAKE_SET(1 | 4, 'hello','hi','world') as col_2,
 MAKE_SET(1 | 4, 'nice', 'ok', NULL) as col_3;

col_1	col_2	col_3
a	hello,world	nice

日期和时间函数略

条件判断函数

• IF(expr, v1, v2): 如果满足表达式expr，则返回v1，否则返回v2；

SELECT
  IF(STRCMP('a', 'b'), 'yes', 'no');

• IFNULL(v1, v2): 假如v1不为NULL，则IFNULL()的返回值为v1；否则为v2；
• CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2]..[ELSE rn+1] END;: 如果expr是某个vn，则返回THEN后面的结果；如果与所有值都不相等，则返回ELSE后面的rn+1;

系统信息函数

• SELECT VERSION(): 查看MYSQL的版本；
• SELECT CONNECTION_ID(): 查看当前用户的连接数；
• SHOW PROCESSLIST; / SHOW FULL PROCESSLIST; 显示有哪些线程在运行，当前连接数、当前连接状态、帮忙识别出有问题的查询语句；
• SHOW DATABASE(); / SHOW SCHEMA(): 显示当前使用的数据库；

-- 获取用户名的函数
SELECT USER(), CURRENT_USER(), SYSTEM_USER();

获取字符串的字符集和排序方式的函数：

SELECT CHARSET('abc'),
  CHARSET(CONVERT('abc' USING latin1)),
  CHARSET(VERSION());

CHARSET('abc')	CHARSET(CONVERT('abc' USING latin1))	CHARSET(VERSION())
utf8mb4	latin1	utf8mb3

返回字符串排列方式：

SELECT COLLATION('abc'), COLLATION(CONVERT('abc' USING utf8));

可见使用不同字符集时字符串的排列方式也不同；

COLLATION('abc')	COLLATION(CONVERT('abc' USING utf8))
utf8mb4_0900_ai_ci	utf8mb3_general_ci

其他函数

• CONVERT(... USING ...): 改变字符串的默认字符集；
• CAST(x AS type) / CONVERT(x, type): 将一个类型的值转化成另一个类型的值；

SELECT CAST(100 AS char(2)), CONVERT('2023-11-11 10:10:10', TIME);

CAST(100 AS char(2))	CONVERT('2023-11-11 10:10:10', TIME)
10	10:10:10