（四）MySQL函数

tech2024-12-28 12

（四）MySQL函数function

一、数学函数

1.绝对值函数：ABS(x)

ABS(x)用于返回x的绝对值

mysql> select abs(10), abs(-10), abs(0), abs(10.1); +---------+----------+--------+-----------+ | abs(10) | abs(-10) | abs(0) | abs(10.1) | +---------+----------+--------+-----------+ | 10 | 10 | 0 | 10.1 | +---------+----------+--------+-----------+ 1 row in set (0.00 sec) mysql>

2.返回圆周率的函数:PI()

PI() 返回圆周率值

mysql> select pi(); +----------+ | pi() | +----------+ | 3.141593 | +----------+ 1 row in set (0.00 sec) mysql>

3.平方根函数：SQRT(x)

SQRT(x) 返回非负数 x 的二次方根

4.求余函数：MOD(x,y)

MOD(x,y) 返回 x 被 y 除后的余数，MOD(x,y) 对于带有小数部分的数值也起作用，它

返回除法运算后的精确余数

mysql> help mod; Name: 'MOD' Description: Syntax: MOD(N,M), N % M, N MOD M Modulo operation. Returns the remainder of N divided by M. URL: https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html Examples: mysql> SELECT MOD(234, 10); -> 4 mysql> SELECT 253 % 7; -> 1 mysql> SELECT MOD(29,9); -> 2 mysql> SELECT 29 MOD 9; -> 2 mysql> select mod(9,3), mod(10.1234,3), mod(234,10); +----------+----------------+-------------+ | mod(9,3) | mod(10.1234,3) | mod(234,10) | +----------+----------------+-------------+ | 0 | 1.1234 | 4 | +----------+----------------+-------------+ 1 row in set (0.00 sec) mysql> select mod(10, 3), mod(10,3), 10 mod 3, 10%3; +------------+-----------+----------+------+ | mod(10, 3) | mod(10,3) | 10 mod 3 | 10%3 | +------------+-----------+----------+------+ | 1 | 1 | 1 | 1 | +------------+-----------+----------+------+ 1 row in set (0.00 sec) mysql>

5.获取整数的函数：CEIL(x) 、CEILING(x) 、FLOOR(x)

CEIL(x) 用于返回不小于 x 的最小整数值

mysql> select ceil(10.9999), ceil(10.0001), ceil(-3.35), ceil(3.35); +---------------+---------------+-------------+------------+ | ceil(10.9999) | ceil(10.0001) | ceil(-3.35) | ceil(3.35) | +---------------+---------------+-------------+------------+ | 11 | 11 | -3 | 4 | +---------------+---------------+-------------+------------+ 1 row in set (0.00 sec) mysql>

CEILING(x) 用于返回不小于 x 的最小整数

mysql> select ceiling(10.9999), ceiling(10.0001), ceiling(-3.35), ceiling(3.35); +------------------+------------------+----------------+---------------+ | ceiling(10.9999) | ceiling(10.0001) | ceiling(-3.35) | ceiling(3.35) | +------------------+------------------+----------------+---------------+ | 11 | 11 | -3 | 4 | +------------------+------------------+----------------+---------------+ 1 row in set (0.00 sec) mysql>

FLOOR(x) 返回不大于 x 的最大整数值

mysql> select floor(10.9999), floor(10.0001), floor(-3.35), floor(3.35); +----------------+----------------+--------------+-------------+ | floor(10.9999) | floor(10.0001) | floor(-3.35) | floor(3.35) | +----------------+----------------+--------------+-------------+ | 10 | 10 | -4 | 3 | +----------------+----------------+--------------+-------------+ 1 row in set (0.00 sec) mysql>

6.获取随机数的函数：RAND() 、RAND(x)

RAND() 用于返回一个随机浮点值，范围在 0 ~ 1 之间

mysql> select rand(), rand(), rand(); +--------------------+----------------------+---------------------+ | rand() | rand() | rand() | +--------------------+----------------------+---------------------+ | 0.9110636021113615 | 0.028126648653416568 | 0.40744246766664316 | +--------------------+----------------------+---------------------+ 1 row in set (0.01 sec) mysql>

RAND(x) 用于返回一个随机浮点值，范围在 0~1 之间，x 被用作种子值，用来产生重复序列

mysql> select rand(5), rand(5), rand(8), rand(8),rand(8); +---------------------+---------------------+---------------------+---------------------+---------------------+ | rand(5) | rand(5) | rand(8) | rand(8) | rand(8) | +---------------------+---------------------+---------------------+---------------------+---------------------+ | 0.40613597483014313 | 0.40613597483014313 | 0.15668530311126755 | 0.15668530311126755 | 0.15668530311126755 | +---------------------+---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql>

7. 四舍五入的函数：ROUND(x) 、ROUND(x,y)

ROUND(x) 用于对 x 进行四舍五入

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

ROUND(x,y) 用于对 x 进行四舍五入，并保留小数点后 y 位

mysql> select round(1.6345,1), round(1.6345,2), round(1.6345,0), round(123.456,-1), round(123.542,-2); +-----------------+-----------------+-----------------+-------------------+-------------------+ | round(1.6345,1) | round(1.6345,2) | round(1.6345,0) | round(123.456,-1) | round(123.542,-2) | +-----------------+-----------------+-----------------+-------------------+-------------------+ | 1.6 | 1.63 | 2 | 120 | 100 | +-----------------+-----------------+-----------------+-------------------+-------------------+ 1 row in set (0.00 sec) mysql>

8.截取数值的函数：TRUNCATE(x,y)

TRUNCATE(x,y) 用于对 x 进行截取，结果保留小数点后 y 位

mysql> select truncate(123.456,1), truncate(123.456789,2), truncate(1.99,0); +---------------------+------------------------+------------------+ | truncate(123.456,1) | truncate(123.456789,2) | truncate(1.99,0) | +---------------------+------------------------+------------------+ | 123.4 | 123.45 | 1 | +---------------------+------------------------+------------------+ 1 row in set (0.00 sec) mysql>

9.符号函数：SIGN(x)

SIGN(x) 用于返回参数 x 的符号，当 x 为负数时返回 -1 ，当 x 为正数时返回 1 ，当 x 为零时返回 0

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

10.幂运算函数：POW(x,y) 、POWER(x,y) 、EXP(x)

POW(x,y) 、POWER(x,y) 用于返回 x 的 y 次方的结果

mysql> select pow(2,2), pow(2,0), pow(2,-2); +----------+----------+-----------+ | pow(2,2) | pow(2,0) | pow(2,-2) | +----------+----------+-----------+ | 4 | 1 | 0.25 | +----------+----------+-----------+ 1 row in set (0.02 sec) mysql> select power(2,2), power(2,0), power(2,-2); +------------+------------+-------------+ | power(2,2) | power(2,0) | power(2,-2) | +------------+------------+-------------+ | 4 | 1 | 0.25 | +------------+------------+-------------+ 1 row in set (0.00 sec) mysql>

返回e(自然对数的底)的X次方的值

mysql> select exp(2), exp(0), exp(-2); +------------------+--------+--------------------+ | exp(2) | exp(0) | exp(-2) | +------------------+--------+--------------------+ | 7.38905609893065 | 1 | 0.1353352832366127 | +------------------+--------+--------------------+ 1 row in set (0.00 sec) mysql>

11.对数运算函数：LOG(x) 、LOG10(x)

LOG(x) 返回 x 的自然对数

mysql> select log(3), log(0), log(-3); +--------------------+--------+---------+ | log(3) | log(0) | log(-3) | +--------------------+--------+---------+ | 1.0986122886681098 | NULL | NULL | +--------------------+--------+---------+ 1 row in set, 2 warnings (0.01 sec) mysql>

使用 LOG10(x) 计算以 10 为基数的对数

mysql> select log10(2), log10(100), log10(-100), log10(0); +--------------------+------------+-------------+----------+ | log10(2) | log10(100) | log10(-100) | log10(0) | +--------------------+------------+-------------+----------+ | 0.3010299956639812 | 2 | NULL | NULL | +--------------------+------------+-------------+----------+ 1 row in set, 2 warnings (0.00 sec) mysql>

12.角度与弧度相互转换的函数：RADIANS(x) 、DEGREES(x)

RADIANS(x) 用于将参数 x 由角度转化为弧度

mysql> select radians(90), radians(180); +--------------------+-------------------+ | radians(90) | radians(180) | +--------------------+-------------------+ | 1.5707963267948966 | 3.141592653589793 | +--------------------+-------------------+ 1 row in set (0.01 sec) mysql>

DEGREES(x) 用于将参数 x 由弧度转化为角度

mysql> select degrees(pi()), degrees(pi()/2); +---------------+-----------------+ | degrees(pi()) | degrees(pi()/2) | +---------------+-----------------+ | 180 | 90 | +---------------+-----------------+ 1 row in set (0.00 sec) mysql>

13.正弦函数和反正弦函数：SIN(x) 、ASIN(x)

SIN(x) 用于返回 x 的正弦值，其中 x 为弧度值

mysql> select sin(1), round(sin(pi())); +--------------------+------------------+ | sin(1) | round(sin(pi())) | +--------------------+------------------+ | 0.8414709848078965 | 0 | +--------------------+------------------+ 1 row in set (0.55 sec) mysql>

ASIN(x) 用于返回 x 的反正弦，即正弦为 x 的值

14.余弦函数和反余弦函数：COS(x) 、ACOS(x)

COS(x) 用于返回 x 的余弦，其中 x 为弧度值

mysql> select cos(0), cos(pi()), cos(1); +--------+-----------+--------------------+ | cos(0) | cos(pi()) | cos(1) | +--------+-----------+--------------------+ | 1 | -1 | 0.5403023058681398 | +--------+-----------+--------------------+ 1 row in set (0.00 sec) mysql>

ACOS(x) 用于返回 x 的反余弦，即余弦是 x 的值

mysql> select acos(1), acos(0), round(acos(0.5403023058681397)); +---------+--------------------+---------------------------------+ | acos(1) | acos(0) | round(acos(0.5403023058681397)) | +---------+--------------------+---------------------------------+ | 0 | 1.5707963267948966 | 1 | +---------+--------------------+---------------------------------+ 1 row in set (0.00 sec) mysql>

15.正切函数、反正切函数、余切函数：TAN(x) 、ATAN(x) 、COT(x)

TAN(x) 返回 x 的正切，其中 x 为给定的弧度值

mysql> select tan(0.3), round(tan(pi()/4)); +---------------------+--------------------+ | tan(0.3) | round(tan(pi()/4)) | +---------------------+--------------------+ | 0.30933624960962325 | 1 | +---------------------+--------------------+ 1 row in set (0.00 sec) mysql>

ATAN(x) 用于返回 x 的反正切，即正切为 x 的值

mysql> select atan(0.30933624960962325), atan(1); +---------------------------+--------------------+ | atan(0.30933624960962325) | atan(1) | +---------------------------+--------------------+ | 0.3 | 0.7853981633974483 | +---------------------------+--------------------+ 1 row in set (0.00 sec) mysql>

COT(x) 用于返回 x 的余切

mysql> select cot(0.3), 1/tan(0.3), cot(pi()/4); +--------------------+--------------------+--------------------+ | cot(0.3) | 1/tan(0.3) | cot(pi()/4) | +--------------------+--------------------+--------------------+ | 3.2327281437658275 | 3.2327281437658275 | 1.0000000000000002 | +--------------------+--------------------+--------------------+ 1 row in set (0.00 sec) mysql>

二、字符串函数

1.计算字符串长度的函数：CHAR_LENGTH(str) 、LENGTH(str)

CHAR_LENGTH(str) 用于统计str 的字符个数

mysql> select char_length('date'), char_length('中国'); +---------------------+-----------------------+ | char_length('date') | char_length('中国') | +---------------------+-----------------------+ | 4 | 2 | +---------------------+-----------------------+ 1 row in set (0.00 sec) mysql>

LENGTH(str) 用于统计 str 的字节长度，使用 utf8 编码字符集时，一个汉字是3个字节，一个数字或字母是1个字节

mysql> select length('date'), length('中国'); +----------------+------------------+ | length('date') | length('中国') | +----------------+------------------+ | 4 | 6 | +----------------+------------------+ 1 row in set (0.00 sec) mysql>

2.合并字符串的函数：CONCAT(s1,s2,…) 、CONCAT_WS(x,s1,s2,…)

CONCAT(s1,s2,…) 用于合并字符串 s1, s2, …，如果有一个参数为 NULL，则结果为 NULL

mysql> help concat; Name: 'CONCAT' Description: Syntax: CONCAT(str1,str2,...) Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form. CONCAT() returns NULL if any argument is NULL. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL' mysql> SELECT CONCAT('My', NULL, 'QL'); -> NULL mysql> SELECT CONCAT(14.3); -> '14.3' mysql> select concat('My','SQL','5.7'), concat('My',null,'SQL'); +--------------------------+-------------------------+ | concat('My','SQL','5.7') | concat('My',null,'SQL') | +--------------------------+-------------------------+ | MySQL5.7 | NULL | +--------------------------+-------------------------+ 1 row in set (0.00 sec) mysql>

CONCAT_WS(x,s1,s2,…) 以 x 作为分隔符，将 s1, s2, … 合并，如果分隔符为 NULL ，则结果为 NULL

mysql> help concat_ws; Name: 'CONCAT_WS' Description: Syntax: CONCAT_WS(separator,str1,str2,...) CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name'); -> 'First name,Second name,Last Name' mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name'); -> 'First name,Last Name' mysql> select concat_ws('-','1st','2nd','3rd'), concat_ws(',','1st','2nd','3rd'); +----------------------------------+----------------------------------+ | concat_ws('-','1st','2nd','3rd') | concat_ws(',','1st','2nd','3rd') | +----------------------------------+----------------------------------+ | 1st-2nd-3rd | 1st,2nd,3rd | +----------------------------------+----------------------------------+ 1 row in set (0.01 sec) mysql> select concat_ws('/',null,'test1','test2'), concat_ws('/','test1',null,'test2'), concat_ws('/','test1','test2',null); +-------------------------------------+-------------------------------------+-------------------------------------+ | concat_ws('/',null,'test1','test2') | concat_ws('/','test1',null,'test2') | concat_ws('/','test1','test2',null) | +-------------------------------------+-------------------------------------+-------------------------------------+ | test1/test2 | test1/test2 | test1/test2 | +-------------------------------------+-------------------------------------+-------------------------------------+ 1 row in set (0.00 sec) mysql> select concat_ws(null,'test1','test2'); +---------------------------------+ | concat_ws(null,'test1','test2') | +---------------------------------+ | NULL | +---------------------------------+ 1 row in set (0.00 sec) mysql>

3.替换字符串的函数：INSERT(s1,x,len,s2)

(1) INSERT(s1,x,len,s2) 用于返回字符串 s1，其子字符串起始于 x 位置和被字符串 s2 取代的 len 字符

(2) 如果 x 超过字符串长度，则返回值为原始字符串，如果 len 的长度大于其他字符串的长度则从位置 x 开始替换

(3) 如果任何一个参数为 NULL ，则返回值为 NULL

mysql> select insert("abcdefg",2,4,"123"); #从第2个字符开始替换，替换4个字符 +-----------------------------+ | insert("abcdefg",2,4,"123") | +-----------------------------+ | a123fg | +-----------------------------+ 1 row in set (0.00 sec) mysql> select insert("abcdefg",5,6,"123"); #len6超出了，就将后面的都替换成123 +-----------------------------+ | insert("abcdefg",5,6,"123") | +-----------------------------+ | abcd123 | +-----------------------------+ 1 row in set (0.00 sec) mysql> select insert("abcdefg",5,6,null), insert(null,5,6,"123"); +----------------------------+------------------------+ | insert("abcdefg",5,6,null) | insert(null,5,6,"123") | +----------------------------+------------------------+ | NULL | NULL | +----------------------------+------------------------+ 1 row in set (0.00 sec) mysql> select insert('quest',2,4,'what') as col1, -> insert('quest',-1,4,'what') as col2, #x-1超出长度范围，返回原始字符串 -> insert('quest',3,100,'wh') as col3; +-------+-------+------+ | col1 | col2 | col3 | +-------+-------+------+ | qwhat | quest | quwh | +-------+-------+------+ 1 row in set (0.00 sec) mysql>

4.转换大小写的函数：LOWER(str) 、LCASE(str) 、UPPER(str) 、UCASE(str)

LOWER(str) 用于将字符串 str 中的字母字符全部转换成小写字母

mysql> select lower('BEAUTIFUL'), lower('BEAutiFUL'), lower('BEAUTIful'); +--------------------+--------------------+--------------------+ | lower('BEAUTIFUL') | lower('BEAutiFUL') | lower('BEAUTIful') | +--------------------+--------------------+--------------------+ | beautiful | beautiful | beautiful | +--------------------+--------------------+--------------------+ 1 row in set (0.00 sec) mysql>

LCASE(str) 用于将字符串 str 中的字母字符全部转换成小写字母

mysql> select lcase('BEAUTIFUL'), lcase('BEAutiFUL'), lcase('BEAUTIful'); +--------------------+--------------------+--------------------+ | lcase('BEAUTIFUL') | lcase('BEAutiFUL') | lcase('BEAUTIful') | +--------------------+--------------------+--------------------+ | beautiful | beautiful | beautiful | +--------------------+--------------------+--------------------+ 1 row in set (0.00 sec) mysql>

UPPER(str) 用于将 str 中的小写字母转换成大写字母

UCASE(str) 用于将 str 中的小写字母转换成大写字母

5.获取指定长度的字符串的函数：LEFT(s,n) 、RIGHT(s,n)

LEFT(s,n) 用于返回字符串 s 开始的最左边 n 个字符

mysql> help left; Name: 'LEFT' Description: Syntax: LEFT(str,len) Returns the leftmost len characters from the string str, or NULL if any argument is NULL. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT LEFT('foobarbar', 5); -> 'fooba' mysql> select left('football',5); +--------------------+ | left('football',5) | +--------------------+ | footb | +--------------------+ 1 row in set (0.00 sec) mysql>

RIGHT(s,n) 用于返回字符串 s 中右边 n 个字符

mysql> help right; Name: 'RIGHT' Description: Syntax: RIGHT(str,len) Returns the rightmost len characters from the string str, or NULL if any argument is NULL. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT RIGHT('foobarbar', 4); -> 'rbar' mysql> select right('football',5); +---------------------+ | right('football',5) | +---------------------+ | tball | +---------------------+ 1 row in set (0.00 sec) mysql>

6.填充字符串的函数：LPAD(s1,len,s2) 、RPAD(s1,len,s2)

LPAD(s1,len,s2) 返回字符串 s1 ，其左边由字符串 s2填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值被缩短至 len 长度

mysql> select lpad('hello',4,'??'), lpad('hello',10,'??'); +----------------------+-----------------------+ | lpad('hello',4,'??') | lpad('hello',10,'??') | +----------------------+-----------------------+ | hell | ?????hello | +----------------------+-----------------------+ 1 row in set (0.00 sec) # 字符串'hello'长度大于4,不需要填充，只被缩短 # 字符串'hello'长度小于10,因此被填充 mysql>

RPAD(s1,len,s2) 返回字符串 s1 ，其右边由字符串 s2 填充到 len 字符长度，如果 s1 的长度大于 len ，则返回值被缩短至 len 长度

mysql> select rpad('hello',4,'??'), rpad('hello',10,'??'); +----------------------+-----------------------+ | rpad('hello',4,'??') | rpad('hello',10,'??') | +----------------------+-----------------------+ | hell | hello????? | +----------------------+-----------------------+ 1 row in set (0.00 sec) # 字符串'hello'长度大于4,不需要被填充，因此被缩短至4 # 字符串'hello'长度小于10,因此被填充 mysql>

7.删除空格的函数：LTRIM(s) 、RTRIM(s) 、TRIM(s)

LTRIM(s) 用于删除字符串 s 左侧的空格

mysql> help ltrim; Name: 'LTRIM' Description: Syntax: LTRIM(str) Returns the string str with leading space characters removed. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT LTRIM(' barbar'); -> 'barbar' mysql> select ltrim(' book '); +---------------------+ | ltrim(' book ') | +---------------------+ | book | +---------------------+ 1 row in set (0.54 sec) mysql> select trim(' book '); +-------------------------+ | trim(' book ') | +-------------------------+ | book | +-------------------------+ 1 row in set (0.00 sec) mysql>

RTRIM(s) 用于删除字符串 s 右侧的空格

mysql> help rtrim; Name: 'RTRIM' Description: Syntax: RTRIM(str) Returns the string str with trailing space characters removed. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT RTRIM('barbar '); -> 'barbar' mysql> select rtrim(' book '); +---------------------+ | rtrim(' book ') | +---------------------+ | book | +---------------------+ 1 row in set (0.00 sec) mysql>

TRIM(s) 用于删除字符串 s 两侧的空格

mysql> help trim; Name: 'TRIM' Description: Syntax: TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str) Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' mysql> select trim(' book '); +--------------------+ | trim(' book ') | +--------------------+ | book | +--------------------+ 1 row in set (0.00 sec) mysql> select trim(' book '); +-------------------------+ | trim(' book ') | +-------------------------+ | book | +-------------------------+ 1 row in set (0.00 sec) mysql>

8.删除指定字符串的函数：TRIM(s1 FROM s)

TRIM(s1 FROM s) 用于删除字符串 s 中两端所有的子字符串 s1 ，如果没有指定 s1 ，则默认删除字符串 s 两侧的空格

mysql> select trim('xy' from 'xyxywatexyrmelonxyxy'); +----------------------------------------+ | trim('xy' from 'xyxywatexyrmelonxyxy') | +----------------------------------------+ | watexyrmelon | +----------------------------------------+ 1 row in set (0.00 sec) mysql>

9.重复生成字符串的函数：REPEAT(s,n)

REPEAT(s,n) 用于重复字符串 s ，n 表示重复多少次

mysql> help repeat function; Name: 'REPEAT FUNCTION' Description: Syntax: REPEAT(str,count) Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT REPEAT('MySQL', 3); -> 'MySQLMySQLMySQL' mysql> select repeat('Bee',3); +-----------------+ | repeat('Bee',3) | +-----------------+ | BeeBeeBee | +-----------------+ 1 row in set (0.00 sec) mysql>

10.空格函数：SPACE(n)

SPACE(n) 用于返回 n 个空格

mysql> help space; Name: 'SPACE' Description: Syntax: SPACE(N) Returns a string consisting of N space characters. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT SPACE(6); -> ' ' mysql> select space(10); +------------+ | space(10) | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> select space(20); +----------------------+ | space(20) | +----------------------+ | | +----------------------+ 1 row in set (0.00 sec) mysql>

11.替换函数：REPLACE(s,s1,s2)

REPLACE(s,s1,s2) 表示使用字符串 s2 替换字符串 s 中所有的字符串 s1

mysql> select replace('www.baidu.www','w','t'), replace('www.baidu.www','ww','t'); +----------------------------------+-----------------------------------+ | replace('www.baidu.www','w','t') | replace('www.baidu.www','ww','t') | +----------------------------------+-----------------------------------+ | ttt.baidu.ttt | tw.baidu.tw | +----------------------------------+-----------------------------------+ 1 row in set (0.00 sec) mysql>

12.比较字符串大小的函数：STRCMP(s1,s2)

STRCMP(s1,s2) 用于比较字符串 s1 和 s2 的大小，若所有字符串相同则返回 0 ，若第一个字符串大于第二个字符串则返回 1 ，若第一个字符串小于第二个字符串则返回 -1

mysql> help strcmp; Name: 'STRCMP' Description: Syntax: STRCMP(expr1,expr2) STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise. URL: https://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html Examples: mysql> SELECT STRCMP('text', 'text2'); -> -1 mysql> SELECT STRCMP('text2', 'text'); -> 1 mysql> SELECT STRCMP('text', 'text'); -> 0 mysql> select strcmp('txt','txt'), strcmp('1txt','txt'), strcmp('txt','2txt'); +---------------------+----------------------+----------------------+ | strcmp('txt','txt') | strcmp('1txt','txt') | strcmp('txt','2txt') | +---------------------+----------------------+----------------------+ | 0 | -1 | 1 | +---------------------+----------------------+----------------------+ 1 row in set (0.00 sec) mysql>

13.获取子字符串的函数：SUBSTRING(s,n,len) 、MID(s,n,len)

SUBSTRING(s,n,len) 用于获取指定位置的子字符串

mysql> help substring; Name: 'SUBSTRING' Description: Syntax: SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len) The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string. For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT SUBSTRING('Quadratically',5); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki' mysql> select substring('1234567890',5) as col1, # 从第5个字符串开始获取 -> substring('1234567890' from 5) as col2, # 从第5个字符串开始获取 -> substring('1234567890',5,3) as col3, # 从第5个字符串开始，获取3个 -> substring('1234567890',-3) as col4, # (倒向)从第3个字符串开始获取 -> substring('1234567890',-5,3) as col5， # (倒向)从第5个字符串开始获取，获取3个 -> substring('1234567890' from -4 for 2) as col6; # (倒向)从第4个字符串开始获取，获取2个 +--------+--------+------+------+------+------+ | col1 | col2 | col3 | col4 | col5 | col6 | +--------+--------+------+------+------+------+ | 567890 | 567890 | 567 | 890 | 678 | 78 | +--------+--------+------+------+------+------+ 1 row in set (0.00 sec) mysql>

MID(s,n,len) 用于获取指定位置的子字符串

mysql> help mid; Name: 'MID' Description: Syntax: MID(str,pos,len) MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len). URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html mysql> select mid('1234567890',5) as col1, # 从第5个字符串开始获取 -> mid('1234567890' from 5) as col2, # 从第5个字符串开始获取 -> mid('1234567890',5,3) as col3, # 从第5个字符串开始，获取3个 -> mid('1234567890',-3) as col4, # (倒向)从第3个字符串开始获取 -> mid('1234567890',-5,3) as col5; # (倒向)从第5个字符串开始获取，获取3个 +--------+--------+------+------+------+ | col1 | col2 | col3 | col4 | col5 | +--------+--------+------+------+------+ | 567890 | 567890 | 567 | 890 | 678 | +--------+--------+------+------+------+ 1 row in set (0.00 sec) mysql>

14.匹配子字符串开始位置的函数：LOCATE(str1,str) 、POSITION(str1 IN str) 、INSTR(str, str1)

LOCATE(str1,str) 用于返回字符串 str1 在字符串 str 中的开始位置

mysql> help locate; Name: 'LOCATE' Description: Syntax: LOCATE(substr,str), LOCATE(substr,str,pos) The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if substr or str is NULL. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT LOCATE('bar', 'foobarbar'); -> 4 mysql> SELECT LOCATE('xbar', 'foobar'); -> 0 mysql> SELECT LOCATE('bar', 'foobarbar', 5); -> 7 mysql> select locate('45','123456789'), locate('045','123456789'), locate('789','123456789',4); +--------------------------+---------------------------+-----------------------------+ | locate('45','123456789') | locate('045','123456789') | locate('789','123456789',4) | +--------------------------+---------------------------+-----------------------------+ | 4 | 0 | 7 | +--------------------------+---------------------------+-----------------------------+ 1 row in set (0.00 sec) mysql>

POSITION(str1 IN str) 用于返回字符串 str1 在字符串 str 中的开始位置

mysql> help position; Name: 'POSITION' Description: Syntax: POSITION(substr IN str) POSITION(substr IN str) is a synonym for LOCATE(substr,str). URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html mysql> select position('45' in '123456789'), position('045' in '123456789'); +-------------------------------+--------------------------------+ | position('45' in '123456789') | position('045' in '123456789') | +-------------------------------+--------------------------------+ | 4 | 0 | +-------------------------------+--------------------------------+ 1 row in set (0.00 sec) mysql>

INSTR(str, str1) 用于返回子字符串 str1 在字符串 str 中的开始位置

mysql> help instr; Name: 'INSTR' Description: Syntax: INSTR(str,substr) Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT INSTR('foobarbar', 'bar'); -> 4 mysql> SELECT INSTR('xbar', 'foobar'); -> 0 mysql> select instr('123456789','45'); +-------------------------+ | instr('123456789','45') | +-------------------------+ | 4 | +-------------------------+ 1 row in set (0.00 sec) mysql>

15.反转字符串的函数：REVERSE(s)

REVERSE(s) 用于将字符串 s 反转

mysql> help reverse; Name: 'REVERSE' Description: Syntax: REVERSE(str) Returns the string str with the order of the characters reversed. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT REVERSE('abc'); -> 'cba' mysql> select reverse('123456789'); +----------------------+ | reverse('123456789') | +----------------------+ | 987654321 | +----------------------+ 1 row in set (0.00 sec) mysql>

16.返回指定位置的字符串的函数：ELT(n, s1, s2, s3, …)

ELT(n, s1, s2, s3, …) 用于返回第 n 个字符串，如果 n超出范围则返回 NULL

mysql> help elt; Name: 'ELT' Description: Syntax: ELT(N,str1,str2,str3,...) ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD(). URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd'); -> 'Aa' mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd'); -> 'Dd' mysql> select elt(3,'a','b','c','d'), elt(6,'a','b','c','d'); +------------------------+------------------------+ | elt(3,'a','b','c','d') | elt(6,'a','b','c','d') | +------------------------+------------------------+ | c | NULL | +------------------------+------------------------+ 1 row in set (0.00 sec) mysql>

17.返回指定字符串位置的函数：FIELD(s, s1, s2, …)

FIELD(s, s1, s2, …) 用于返回字符串 s 在列表 s1, s2, … 中的位置，如果不存在字符串 s 则返回 0 ，如果字符串 s 是 NULL 也返回 0

mysql> help field; Name: 'FIELD' Description: Syntax: FIELD(str,str1,str2,str3,...) Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found. If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double. If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT(). URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff'); -> 2 mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff'); -> 0 mysql> select field('b','a','b','c','d'), field('z','a','b','c','d'), field(null,'a','b','c','d'); +----------------------------+----------------------------+-----------------------------+ | field('b','a','b','c','d') | field('z','a','b','c','d') | field(null,'a','b','c','d') | +----------------------------+----------------------------+-----------------------------+ | 2 | 0 | 0 | +----------------------------+----------------------------+-----------------------------+ 1 row in set (0.00 sec) mysql>

18.返回子字符串位置的函数：FIND_IN_SET(s1, s2)

FIND_IN_SET(s1, s2) 用于返回字符串 s1 在字符串列表 s2中的位置

注意s2是一个列表

mysql> help find_in_set; Name: 'FIND_IN_SET' Description: Syntax: FIND_IN_SET(str,strlist) Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character. URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html Examples: mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2 mysql> select find_in_set('bye','hi,hello,bye,goodbye'); +-------------------------------------------+ | find_in_set('bye','hi,hello,bye,goodbye') | +-------------------------------------------+ | 3 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql>

三、日期和时间函数

1.获取当前日期的函数：CURDATE() 、CURRENT_DATE()

CURDATE() 用于获取系统当前日期

mysql> help curdate; Name: 'CURDATE' Description: Syntax: CURDATE() Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in string or numeric context. URL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html Examples: mysql> SELECT CURDATE(); -> '2008-06-13' mysql> SELECT CURDATE() + 0; -> 20080613 mysql> select curdate(), curdate() +0; +------------+--------------+ | curdate() | curdate() +0 | +------------+--------------+ | 2020-08-25 | 20200825 | +------------+--------------+ 1 row in set (0.00 sec) mysql>

CURRENT_DATE() 用于系统获取当前日期

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

2.获取当前时间的函数：CURTIME() 、CURRENT_TIME()

CURTIME() 用于获取系统当前时间

mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 05:48:48 | +-----------+ 1 row in set (0.00 sec) mysql>

CURRENT_TIME() 用于获取系统当前时间

mysql> select current_time(); +----------------+ | current_time() | +----------------+ | 05:48:54 | +----------------+ 1 row in set (0.00 sec) mysql>

3.获取当前日期和时间的函数：CURRENT_TIMESTAMP() 、LOCALTIME() 、NOW() 、SYSDATE()

CURRENT_TIMESTAMP() 用于获取系统当前日期和时间

mysql> select current_timestamp(); +---------------------+ | current_timestamp() | +---------------------+ | 2020-08-25 05:49:18 | +---------------------+ 1 row in set (0.00 sec) mysql>

LOCALTIME() 用于获取系统当前日期和时间

mysql> select localtime(); +---------------------+ | localtime() | +---------------------+ | 2020-08-25 05:49:57 | +---------------------+ 1 row in set (0.00 sec) mysql>

NOW() 用于获取系统当前日期和时间

mysql> select now(); +---------------------+ | now() | +---------------------+ | 2020-08-25 05:51:07 | +---------------------+ 1 row in set (0.00 sec) mysql>

SYSDATE() 用于获取系统当前日期和时间

mysql> select sysdate(); +---------------------+ | sysdate() | +---------------------+ | 2020-08-25 05:51:44 | +---------------------+ 1 row in set (0.00 sec) mysql>

4.获取时间戳的函数：UNIX_TIMESTAMP()

UNIX_TIMESTAMP() 用于获取 UNIX 格式的时间戳

mysql> select unix_timestamp(); +------------------+ | unix_timestamp() | +------------------+ | 1598306071 | +------------------+ 1 row in set (0.00 sec) mysql>

5.转换时间戳的函数：FROM_UNIXTIME()

FROM_UNIXTIME() 用于将 UNIX 格式的时间戳转换为普通格式的时间

mysql> select from_unixtime('1598306071'); +-----------------------------+ | from_unixtime('1598306071') | +-----------------------------+ | 2020-08-25 05:54:31.000000 | +-----------------------------+ 1 row in set (0.00 sec) mysql>

6.获取UTC日期的函数：UTC_DATE()

UTC_DATE() 用于获取当前 UTC (世界标准时间) 日期值

mysql> select utc_date(); +------------+ | utc_date() | +------------+ | 2020-08-25 | +------------+ 1 row in set (0.00 sec) mysql>

7.获取 UTC 时间的函数：UTC_TIME()

UTC_TIME() 用于获取当前 UTC (世界标准时间) 时间值

mysql> select utc_time(); +------------+ | utc_time() | +------------+ | 21:56:40 | +------------+ 1 row in set (0.00 sec) mysql>

8.获取月份的函数：MONTH(date) 、MONTHNAME(date)

MONTH(date) 用于返回 date 对应的月份

mysql> select month(now()), month('2020-08-25'); +--------------+---------------------+ | month(now()) | month('2020-08-25') | +--------------+---------------------+ | 8 | 8 | +--------------+---------------------+ 1 row in set (0.00 sec) mysql>

MONTHNAME(date) 用于返回 date 对应月份的英文全名

9.获取星期的函数：DAYNAME(date) 、DAYOFWEEK(date) 、WEEKDAY(date) 、WEEK(date) 、WEEKOFYEAR(date)

DAYNAME(date) 用于返回 date 对应的工作日的英文名称

mysql> select dayname(now()); +----------------+ | dayname(now()) | +----------------+ | Tuesday | +----------------+ 1 row in set (0.00 sec) mysql>

DAYOFWEEK(date) 用于返回 date 对应的一周中的索引，1 表示周日，2 表示周一，… ，7 表示周六

mysql> select dayofweek(now()); +------------------+ | dayofweek(now()) | +------------------+ | 3 | +------------------+ 1 row in set (0.00 sec) mysql>

WEEKDAY(date) 用于返回日期对应的工作日索引，0 表示周一，1 表示周二，… ，6 表示周日

mysql> select weekday(now()); +----------------+ | weekday(now()) | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql>

WEEK(date) 用于计算 date 是一年中的第几周，一年有 53 周

mysql> select week(now()); +-------------+ | week(now()) | +-------------+ | 34 | #2020-01-01是星期三，故从01-06日星期一开始算起 +-------------+ 1 row in set (0.00 sec) mysql>

WEEKOFYEAR(date) 用于计算日期 date 是一年中的第几周，一年有 53 周

mysql> select weekofyear(now()); +-------------------+ | weekofyear(now()) | +-------------------+ | 35 | #2020-01-01虽然是星期三，但也从01-01开始算起 +-------------------+ 1 row in set (0.00 sec) mysql>

10.获取天数的函数：DAYOFYEAR(date) 、DAYOFMONTH(date)

DAYOFYEAR(date) 用于返回 date 是一年中的第几天，一年有 365 天

mysql> select dayofyear(now()); +------------------+ | dayofyear(now()) | +------------------+ | 238 | +------------------+ 1 row in set (0.00 sec) mysql>

DAYOFMONTH(date) 用于计算 date 是一个月中的第几天

mysql> select dayofmonth(now()); +-------------------+ | dayofmonth(now()) | +-------------------+ | 25 | +-------------------+ 1 row in set (0.00 sec) mysql>

11.获取年份的函数：YEAR(date)

YEAR(date) 返回 date 对应的年份

mysql> select year(now()); +-------------+ | year(now()) | +-------------+ | 2020 | +-------------+ 1 row in set (0.00 sec) mysql>

12.获取季度的函数：QUARTER(date)

QUARTER(date) 返回 date 对应的一年中的季度值

mysql> select quarter(now()); +----------------+ | quarter(now()) | +----------------+ | 3 | +----------------+ 1 row in set (0.00 sec) mysql>

13.获取分钟的函数：MINUTE(time)

MINUTE(time) 返回 time 对应的分钟值

mysql> select minute(now()); +---------------+ | minute(now()) | +---------------+ | 18 | +---------------+ 1 row in set (0.00 sec) mysql>

14.获取秒钟的函数：SECOND(time)

SECOND(time) 返回 time 对应的秒数

mysql> select second(now()); +---------------+ | second(now()) | +---------------+ | 49 | +---------------+ 1 row in set (0.00 sec) mysq

15.获取日期的指定值的函数：EXTRACT(type FROM date)

EXTRACT(type FROM date) 用于获取指定的日期值

mysql> help extract; Name: 'EXTRACT' Description: Syntax: EXTRACT(unit FROM date) The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic. For information on the unit argument, see https://dev.mysql.com/doc/refman/5.7/en/expressions.html#temporal-inter vals. URL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html Examples: mysql> SELECT EXTRACT(YEAR FROM '2019-07-02'); # 当type为YEAR时，只返回年值 -> 2019 mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03'); # 当type为YEAR_MONTH时，返回年与月 -> 201907 mysql> SELECT EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03'); # 当type为DAY_MINUTE时，返回日、小时、分钟 -> 20102 mysql> SELECT EXTRACT(MICROSECOND -> FROM '2003-01-02 10:30:00.000123'); -> 123 mysql> select extract(year from now()), extract(month from now()), extract(day from now()), extract(hour from now()); +--------------------------+---------------------------+-------------------------+--------------------------+ | extract(year from now()) | extract(month from now()) | extract(day from now()) | extract(hour from now()) | +--------------------------+---------------------------+-------------------------+--------------------------+ | 2020 | 8 | 25 | 6 | +--------------------------+---------------------------+-------------------------+--------------------------+ 1 row in set (0.00 sec) mysql> select extract(year_month from now()), extract(day_minute from now()); +--------------------------------+--------------------------------+ | extract(year_month from now()) | extract(day_minute from now()) | +--------------------------------+--------------------------------+ | 202008 | 626 | # 当前时间是2020-08-25 06：26 +--------------------------------+--------------------------------+ 1 row in set (0.00 sec) mysql> select extract(microsecond from '2020-08-25 06:28:00.000321'); +--------------------------------------------------------+ | extract(microsecond from '2020-08-25 06:28:00.000321') | +--------------------------------------------------------+ | 321 | +--------------------------------------------------------+ 1 row in set (0.00 sec) mysql>

16.时间和秒钟转换的函数：TIME_TO_SEC(time) 、SEC_TO_TIME(time)

TIME_TO_SEC(time) 用于将 time 转换为秒钟，公式为 " 小时3600 + 分钟60 + 秒 "

mysql> select time_to_sec(now()); +--------------------+ | time_to_sec(now()) | +--------------------+ | 23474 | +--------------------+ 1 row in set (0.00 sec) mysql>

SEC_TO_TIME(time) 用于将秒值转换为时间格式

mysql> select sec_to_time('23474'); +----------------------+ | sec_to_time('23474') | +----------------------+ | 06:31:14.000000 | +----------------------+ 1 row in set (0.00 sec) mysql>

17.计算日期和时间的函数：DATE_ADD() 、ADDDATE() 、DATE_SUB() 、SUBDATE() 、ADDTIME() 、SUBTIME() 、DATE_DIFF()

DATE_ADD() 用于对日期进行加运算，格式为 DATE_ADD(date, INTERVAL expr type)，expr 与 type 的关系

mysql> help date_add(); Nothing found Please try to run 'help contents' for a list of all accessible topics mysql> help date_add; Name: 'DATE_ADD' Description: Syntax: DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit) These functions perform date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is evaluated as a string; it may start with a - for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. URL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html Examples: mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY); -> '2018-05-02' mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR); -> '2017-05-01' mysql> SELECT DATE_ADD('2020-12-31 23:59:59',INTERVAL 1 SECOND); -> '2021-01-01 00:00:00' mysql> SELECT DATE_ADD('2018-12-31 23:59:59',INTERVAL 1 DAY); -> '2019-01-01 23:59:59' mysql> SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND); -> '2101-01-01 00:01:00' mysql> SELECT DATE_SUB('2025-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND); -> '2024-12-30 22:58:59' mysql> SELECT DATE_ADD('1900-01-01 00:00:00',INTERVAL '-1 10' DAY_HOUR); -> '1899-12-30 14:00:00' mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); -> '1997-12-02' mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND); -> '1993-01-01 00:00:01.000001' mysql> select date_add(now(),interval 10 day); +---------------------------------+ | date_add(now(),interval 10 day) | +---------------------------------+ | 2020-09-4 06:52:50 | +---------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2020-12-31 23:59:59',interval 1 second); # 对指定的日期增加1秒 +---------------------------------------------------+ | date_add('2020-12-31 23:59:59',interval 1 second) | +---------------------------------------------------+ | 2021-01-01 00:00:00 | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add('2020-12-31 23:59:59',interval "1:1" minute_second); # 对指定的日期增加1分1秒，注意后面的写法 +--------------------------------------------------------------+ | date_add('2020-12-31 23:59:59',interval "1:1" minute_second) | +--------------------------------------------------------------+ | 2021-01-01 00:01:00 | +--------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>

ADDDATE()就是DATE_ADD() 的同义词

mysql> help adddate; Name: 'ADDDATE' Description: Syntax: ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days) When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see https://dev.mysql.com/doc/refman/5.7/en/expressions.html#temporal-inter vals. mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY); -> '2008-02-02' mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY); -> '2008-02-02' When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be added to expr. URL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html Examples: mysql> SELECT ADDDATE('2008-01-02', 31); -> '2008-02-02' mysql> select adddate(now(),interval 10 day); +--------------------------------+ | adddate(now(),interval 10 day) | +--------------------------------+ | 2020-09-4 06:57:44 | +--------------------------------+ 1 row in set (0.00 sec) mysql>

DATE_SUB() 用于对日期进行减运算，格式为 DATE_SUB(date, INTERVAL expr type) ，expr 与 type 的关系

mysql> select date_sub(now(),interval 60 day); # 给指定的日期减去60天 +---------------------------------+ | date_sub(now(),interval 60 day) | +---------------------------------+ | 2020-06-26 06:58:49 | +---------------------------------+ 1 row in set (0.00 sec) mysql>

SUBDATE() 用于对日期进行减运算，格式为 SUBDATE(date, INTERVAL expr type) ，expr 与 type 的关系

mysql> select subdate(now(),interval 60 day); # 对指定的日期减去60天 +--------------------------------+ | subdate(now(),interval 60 day) | +--------------------------------+ | 2020-06-26 07:00:08 | +--------------------------------+ 1 row in set (0.00 sec) mysql>

ADDTIME() 用于对日期进行加运算，格式为 ADDTIME(date, expr)

# 给指定的日期增加1时1分1秒 mysql> select now(), addtime(now(),"1:1:1"), addtime("2020-08-31 07:03:43","1:1:1"); +---------------------+------------------------+----------------------------------------+ | now() | addtime(now(),"1:1:1") | addtime("2020-08-31 07:03:43","1:1:1") | +---------------------+------------------------+----------------------------------------+ | 2020-08-25 07:02:08 | 2020-08-25 08:03:09 | 2020-08-25 08:04:44 | +---------------------+------------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql>

SUBTIME() 用于对日期进行减运算，格式为 SUBTIME(date, expr)

# 给指定的日期减去1时1分1秒 mysql> select now(), subtime(now(),"1:1:1"), subtime("2020-08-31 07:03:43","1:1:1"); +---------------------+------------------------+----------------------------------------+ | now() | subtime(now(),"1:1:1") | subtime("2020-08-31 07:03:43","1:1:1") | +---------------------+------------------------+----------------------------------------+ | 2020-08-25 07:09:03 | 2020-08-25 06:08:02 | 2020-08-25 06:02:42 | +---------------------+------------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql>

DATE_DIFF() 用于计算两个日期之间的间隔天数

mysql> select datediff('2020-8-25','2020-1-1'); +----------------------------------+ | datediff('2020-8-25','2020-1-1') | +----------------------------------+ | 237 | +----------------------------------+ 1 row in set (0.00 sec) mysql>

18.将日期和时间格式化的函数：DATE_FORMAT(date, format) 、TIME_FORMAT(time, format) 、GET_FORMAT(val_type, format_type)

DATE_FORMAT(date, format) 用于格式化日期，即根据 format 指定的格式显示 date 值，format 格式

mysql> help date_format; Name: 'DATE_FORMAT' Description: Syntax: DATE_FORMAT(date,format) Formats the date value according to the format string. URL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html Examples: mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V'); -> '1998 52' mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00' mysql> select date_format(now(),'%d'), date_format(now(),'%e'), date_format(now(),'%H'); +-------------------------+-------------------------+-------------------------+ | date_format(now(),'%d') | date_format(now(),'%e') | date_format(now(),'%H') | +-------------------------+-------------------------+-------------------------+ | 25 | 25 | 07 | +-------------------------+-------------------------+-------------------------+ 1 row in set (0.00 sec) mysql> select date_format(now(),'%p'), date_format(now(),'%hh'), date_format(now(),'%W'); +-------------------------+--------------------------+-------------------------+ | date_format(now(),'%p') | date_format(now(),'%hh') | date_format(now(),'%W') | +-------------------------+--------------------------+-------------------------+ | AM | 07h | Tuesday | +-------------------------+--------------------------+-------------------------+ 1 row in set (0.00 sec) mysql>

TIME_FORMAT(time, format) 用于格式化时间，即根据 format 指定的格式显示 time 值，format 格式

mysql> help time_format; Name: 'TIME_FORMAT' Description: Syntax: TIME_FORMAT(time,format) This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, seconds, and microseconds. Other specifiers produce a NULL value or 0. URL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html Examples: mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l'); -> '100 100 04 04 4' mysql> select time_format(now(),'%H'), time_format('07:23:30','%H %k %I'); +-------------------------+------------------------------------+ | time_format(now(),'%H') | time_format('07:23:30','%H %k %I') | +-------------------------+------------------------------------+ | 07 | 07 7 07 | +-------------------------+------------------------------------+ 1 row in set (0.00 sec) mysql>

GET_FORMAT() ，我们指定值类型和格式化类型，然后会显示成格式字符串

mysql> help get_format; Name: 'GET_FORMAT' Description: Syntax: GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'}) Returns a format string. This function is useful in combination with the DATE_FORMAT() and the STR_TO_DATE() functions. URL: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html Examples: mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR')); -> '03.10.2003' mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA')); -> '2003-10-31' mysql> select date_format(now(),get_format(date,'USA')); +-------------------------------------------+ | date_format(now(),get_format(date,'USA')) | +-------------------------------------------+ | 08.31.2020 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql>

%d该月日期，数字形式（00…31）

%e该月日期，数字形式（0…31）

%f微秒（000000…999999）

%H以2位数表示24小时（00…23）

%h,%I 以2位数表示12小时（01…12）

%i分钟，数字形式（00-59）

%j一年中的天数（001-366）

%k以24小时（0-23）

%l以12小时（0…12）

%M月份名称（january…December）

%m月份数字形式（00…12）

%p上午（AM）或下午（PM）

%r时间，12小时制（小时hh：分钟mm：秒钟ss后面加AM或PM）

%S,%s以2位数形式表示秒（00…59）

%T时间，24小时制（小时hh：分钟mm：秒数ss）

%U周（00…53），其中周日为每周的第一天

%u周（00…53），其中周一为每周的第一天

%V周（01…53），其中周日为每周的第一天，和%X一起使用

%v周（01…53），其中周一为每周的第一天，和%x一起使用

%W工作日名称（周日…周六）

%w一周中的每日（0=周日…6=周六）

%X该周的年份，其中周日为每周的第一天；数字形式4位数，和%V同时使用

%x该周的年份，其中周一为每周的第一天；数字形式4位数，和%v同时使用

%Y4位数形式表示年份

%y2位数形式表示年份

%% “%”文字字符

四、条件判断函数

1.IF()

IF(expr, v1, v2) 如果表达式 expr 为 TRUE ，则返回值为 v1 ，否则返回 v2

mysql> help if; Many help items for your request exist. To make a more specific request, please type 'help <item>', where <item> is one of the following topics: ALTER USER CREATE DATABASE CREATE EVENT CREATE SCHEMA CREATE USER DROP DATABASE DROP EVENT DROP SCHEMA DROP SERVER DROP TABLE DROP USER DROP VIEW IF FUNCTION IF STATEMENT mysql> help if function; Name: 'IF FUNCTION' Description: Syntax: IF(expr1,expr2,expr3) If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3. *Note*: There is also an IF statement, which differs from the IF() function described here. See [HELP IF statement]. If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression. The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows: o If expr2 or expr3 produce a string, the result is a string. If expr2 and expr3 are both strings, the result is case-sensitive if either string is case-sensitive. o If expr2 or expr3 produce a floating-point value, the result is a floating-point value. o If expr2 or expr3 produce an integer, the result is an integer. URL: https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html Examples: mysql> SELECT IF(1>2,2,3); -> 3 mysql> SELECT IF(1<2,'yes','no'); -> 'yes' mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no' mysql> select if(true,5,7), if(false,5,7), if(2>3,5*2,9/3); +--------------+---------------+-----------------+ | if(true,5,7) | if(false,5,7) | if(2>3,5*2,9/3) | +--------------+---------------+-----------------+ | 5 | 7 | 3.0000 | +--------------+---------------+-----------------+ 1 row in set (0.01 sec) mysql>

2.IFNULL()

IFNULL(v1, v2) ，如果 v1 不为 NULL ，则返回值为 v1 ；如果 v1 为 NULL ，则返回值为 v2

mysql> help ifnull; Name: 'IFNULL' Description: Syntax: IFNULL(expr1,expr2) If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. URL: https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html Examples: mysql> SELECT IFNULL(1,0); -> 1 mysql> SELECT IFNULL(NULL,10); -> 10 mysql> SELECT IFNULL(1/0,10); -> 10 mysql> SELECT IFNULL(1/0,'yes'); -> 'yes' mysql> select ifnull(1,2), ifnull(null,2), ifnull(1,null); +-------------+----------------+----------------+ | ifnull(1,2) | ifnull(null,2) | ifnull(1,null) | +-------------+----------------+----------------+ | 1 | 2 | 1 | +-------------+----------------+----------------+ 1 row in set (0.00 sec) mysql>

3.CASE

语法：CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2] [ELSE rn]

END 含义：如果 expr 等于某个 vn，则返回对应位置 THEN 后面的结果，如果与所有值都不相等，则返回 ELSE 后面的 rn

mysql> help case; Many help items for your request exist. To make a more specific request, please type 'help <item>', where <item> is one of the following topics: CASE OPERATOR CASE STATEMENT mysql> help case operator; Name: 'CASE OPERATOR' Description: Syntax: CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END The first CASE syntax returns the result for the first value=compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part. *Note*: The syntax of the CASE expr described here differs slightly from that of the SQL CASE statement described in [HELP CASE statement], for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END. The return type of a CASE expression result is the aggregated type of all result values. URL: https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html Examples: mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END; -> 'one' mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END; -> 'true' mysql> SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END; -> NULL mysql> select case 1 -> when 1 then "one" -> when 2 then "two" -> when 3 then "three" -> else "other" -> end; +---------------------------------------------------------------------------------+ | case 1 when 1 then "one" when 2 then "two" when 3 then "three" else "other" end | +---------------------------------------------------------------------------------+ | one | +---------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select case 5 when 1 then "one" when 2 then "two" when 3 then "three" else "other" end; +---------------------------------------------------------------------------------+ | case 5 when 1 then "one" when 2 then "two" when 3 then "three" else "other" end | +---------------------------------------------------------------------------------+ | other | +---------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql>

五、系统信息函数

1.获取 MySQL 版本号的函数：VERSION()

VERSION() 用于获取 MySQL 版本号

mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.31 | +-----------+ 1 row in set (0.02 sec) mysql>

2.查看当前用户的连接数的ID函数：CONNECTION_ID()

CONNECTION_ID() 用于查看当前用户的连接数

3.查看当前使用的数据库的函数：DATABASE() 、SCHEMA()

DATABASE() 用于查看当前使用的数据库

mysql> use test2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select database(); +------------+ | database() | +------------+ | test2 | +------------+ 1 row in set (0.01 sec) mysql>

SCHEMA() 用于查看当前使用的数据库

mysql> select schema(); +----------+ | schema() | +----------+ | test2 | +----------+ 1 row in set (0.00 sec) mysql>

4.查看当前登录的用户名的函数：USER() 、CURRENT_USER() 、SYSTEM_USER()

USER() 返回当前登录的用户及主机名

mysql> select user(); #第一个用户root连接 +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select user(); #第二个用户test1连接 +-----------------+ | user() | +-----------------+ | test1@localhost | +-----------------+ 1 row in set (0.00 sec) mysql> grant all on *.* to root@'192.168.1.%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> quit Bye [root@MySQL ~]# # 建立第三个用户root连接 [root@MySQL ~]# mysql -uroot -p123 -h 192.168.1.19 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 5.7.31 Source distribution Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select user(); +-------------------+ | user() | +-------------------+ | root@192.168.1.19 | +-------------------+ 1 row in set (0.00 sec) mysql>

CURRENT_USER() 用于返回当前登录的用户及主机名

SYSTEM_USER() 用于返回当前登录的用户及主机名

5.查看指定字符串的字符集的函数：CHARSET(str) ，字符集

CHARSET(str) 用于查看字符串 str 的字符集

mysql> select charset("ant"); +----------------+ | charset("ant") | +----------------+ | utf8 | +----------------+ 1 row in set (0.00 sec) mysql>

6.查看指定字符串的排列方式的函数：COLLATION(str) ，校验集

COLLATION(str) 用于查看字符串 str 的字符排列方式

mysql> select collation("ant"); +------------------+ | collation("ant") | +------------------+ | utf8_general_ci | +------------------+ 1 row in set (0.00 sec) mysql>

7.获取最后一个自动生成的 ID 值得函数：LAST_INSERT_ID()

LAST_INSERT_ID() 用于获取最后一个自动生成的ID 值

mysql> use test2; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed # 先创建一个表，其id字段带有AUTO_INCREMENT约束 mysql> create table mytest1(id int auto_increment primary key, name varchar(30)); Query OK, 0 rows affected (0.25 sec) mysql> desc mytest1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) mysql> insert into mytest1(name) values('zhang'),('li'),('wang'),('zhao'); #由于id是自动增长的，故省略 Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from mytest1; # 查看表的信息 +----+-------+ | id | name | +----+-------+ | 1 | zhang | | 2 | li | | 3 | wang | | 4 | zhao | +----+-------+ 4 rows in set (0.00 sec) mysql> select last_insert_id(); #由于是一次插入多个，故停留在“zhang” +------------------+ | last_insert_id() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec) # 这是因为LAST_INSERT_ID()只返回插入的第一行数据时产生值 mysql> insert into mytest1(name) values('sun'); #只插入一个数据，这时id没有指定，则自动生成，id为5 Query OK, 1 row affected (0.49 sec) mysql> select * from mytest1; +----+-------+ | id | name | +----+-------+ | 1 | zhang | | 2 | li | | 3 | wang | | 4 | zhao | | 5 | sun | +----+-------+ 5 rows in set (0.00 sec) mysql> select last_insert_id(); # 查看最后一个自动生成的id值 +------------------+ | last_insert_id() | +------------------+ | 5 | +------------------+ 1 row in set (0.00 sec) mysql>

六、加/解密函数

1.加密函数：PASSWORD(str) 、MD5(str) 、ENCODE(str, pswd_str)

PASSWORD(str) 从明文密码 str 计算并返回加密后的密码字符串，当参数为 NULL 时，返回 NULL

mysql> select password("password"); +-------------------------------------------+ | password("password") | +-------------------------------------------+ | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | +-------------------------------------------+ 1 row in set, 1 warning (0.01 sec) mysql> select password("abcdefghijklmnopqrstuvwxyz"); #无论要加密的字符串有多长，加密后的长度都是固定的 +-------------------------------------------+ | password("abcdefghijklmnopqrstuvwxyz") | +-------------------------------------------+ | *B58F635E5BD8189BB98470FF69AB6D637642C53B | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>

查看用户密码

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | mytest | | performance_schema | | sys | | test2 | +--------------------+ 6 rows in set (0.00 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.01 sec) mysql> select * from user\G; *************************** 1. row *************************** Host: localhost User: root Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 password_expired: N password_last_changed: 2020-08-13 22:42:43 password_lifetime: NULL account_locked: N *************************** 2. row *************************** Host: localhost User: mysql.session Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: Y Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE password_expired: N password_last_changed: 2020-08-13 20:16:56 password_lifetime: NULL account_locked: Y *************************** 3. row *************************** Host: localhost User: mysql.sys Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE password_expired: N password_last_changed: 2020-08-13 20:16:56 password_lifetime: NULL account_locked: Y *************************** 4. row *************************** Host: localhost User: test1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2020-08-31 12:54:07 password_lifetime: NULL account_locked: N *************************** 5. row *************************** Host: 192.168.1.% User: root Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 #第三个用户root连接密码 password_expired: N password_last_changed: 2020-08-31 13:00:40 password_lifetime: NULL account_locked: N 5 rows in set (0.00 sec) ERROR: No query specified mysql> select password("123"); #验证123加密后的密码是否与存储密码相同 +-------------------------------------------+ | password("123") | +-------------------------------------------+ | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>

MD5(str) 为字符串 str 算出一个 MD5 128 比特校验和

mysql> select md5("apple"); +----------------------------------+ | md5("apple") | +----------------------------------+ | 1f3870be274f6c49b3e31a0c6728957f | +----------------------------------+ 1 row in set (0.00 sec) mysql> select md5("abcdefghijklmnopqrstuvwxyz"); #无论要加密的字符串有多长，加密后的长度都是固定的 +-----------------------------------+ | md5("abcdefghijklmnopqrstuvwxyz") | +-----------------------------------+ | c3fcd3d76192e4007dfb496cca67e13b | +-----------------------------------+ 1 row in set (0.00 sec) mysql>

ENCODE(str, pswd_str) 使用 pswd_str 作为密码，加密 str

mysql> select encode('bee','password'), encode('apple','cake'); +--------------------------+------------------------+ | encode('bee','password') | encode('apple','cake') | +--------------------------+------------------------+ | 뤃 | ĵƝ[ | +--------------------------+------------------------+ 1 row in set, 2 warnings (0.01 sec) mysql>

2.解密函数：DECODE(crypt_str, pswd_str)

DECODE(crypt_str, pswd_str) 使用 pswd_str 作为密码，解密加密字符串 crypt_str

mysql> select decode(encode('apple','cake'),'cake'); +---------------------------------------+ | decode(encode('apple','cake'),'cake') | +---------------------------------------+ | apple | +---------------------------------------+ 1 row in set, 2 warnings (0.00 sec) mysql>

七、其他函数

1.格式化函数：FORMAT(x, n)

FORMAT(x, n) 将数字 x 格式化，并以四舍五入的方式保留小数点后 n 位，结果以字符串的形式返回

mysql> select format(123.456789,3), format(123.123456,3), format(123.4,4), format(123.4,0); +----------------------+----------------------+-----------------+-----------------+ | format(123.456789,3) | format(123.123456,3) | format(123.4,4) | format(123.4,0) | +----------------------+----------------------+-----------------+-----------------+ | 123.457 | 123.123 | 123.4000 | 123 | +----------------------+----------------------+-----------------+-----------------+ 1 row in set (0.00 sec) mysql>

2.不同进制的数字进行转换的函数：CONV()

CONV() 用于不同进制数之间的转换

mysql> select conv('a',16,2), conv('a',16,10), # 将16进制的a分别转换为2进制，10进制 -> conv(15,10,2), conv(15,10,8), conv(15,10,16); # 将10进制的15分别转换为2进制，8进制，16进制 +----------------+-----------------+---------------+---------------+----------------+ | conv('a',16,2) | conv('a',16,10) | conv(15,10,2) | conv(15,10,8) | conv(15,10,16) | +----------------+-----------------+---------------+---------------+----------------+ | 1010 | 10 | 1111 | 17 | F | +----------------+-----------------+---------------+---------------+----------------+ 1 row in set (0.00 sec) mysql>

3.IP 地址与数字互相转换的函数：INET_ATON(expr) 、INET_NTOA(expr)

INET_ATON(expr) 用于将网络地址转换为一个代表该地址数值的整数

mysql> select inet_aton('192.168.1.19'); +---------------------------+ | inet_aton('192.168.1.19') | +---------------------------+ | 3232235795 | +---------------------------+ 1 row in set (0.00 sec) mysql>

inet_ntoa()则是把inet_aton()生成的数字重新还原为ip地址的过程

mysql> select inet_ntoa('3232235795'); +-------------------------+ | inet_ntoa('3232235795') | +-------------------------+ | 192.168.1.19 | +-------------------------+ 1 row in set (0.01 sec) mysql>

4.加锁函数和解锁函数：GET_LOCK(str, timeout) 、RELEASE_LOCAK(str) 、 IS_FREE_LOCK(str) 、IS_USED_LOCK(str)

GET_LOCK(str, timeout) 使用字符串 str 来得到一个锁，持续时间 timeout 秒

若成功得到锁，则返回 1若操作超时，则返回 0若发生错误，则返回 NULL mysql> select get_lock('lock1', 10); +-----------------------+ | get_lock('lock1', 10) | +-----------------------+ | 1 | # 返回结果为1，说明成功得到了一个名称为'lock1'的锁，持续时间为10秒 +-----------------------+ 1 row in set (0.00 sec) mysql>

RELEASE_LOCAK(str) 用于解开被 GET_LOCK() 获取的，用字符串 str 所命名的锁

若锁被解开，则返回 1若该线程尚未创建锁，则返回 0若命名的锁不存在，则返回 NULL若该锁从未被 GET_LOCK() 的调用获取，或锁已经被提前解开，则该锁不存在 mysql> select release_lock('lock1'); +-----------------------+ | release_lock('lock1') | +-----------------------+ | 1 | # 返回值为1说明解锁成功 +-----------------------+ 1 row in set (0.00 sec) mysql>

IS_FREE_LOCK(str) 检查名为 str 的锁是否可以使用

若锁可以使用，则返回 1若锁正在被使用，则返回 0若出现错误，则返回 NULL mysql> select is_free_lock('lock1'); +-----------------------+ | is_free_lock('lock1') | +-----------------------+ | 0 | # 返回值为0说明锁正在被使用 +-----------------------+ 1 row in set (0.00 sec) mysql>

IS_USED_LOCK(str) 用于检查名为 str 的锁是否正在被使用，若被封锁，则返回使用该锁的客户端的连接标识符，否则返回 NULL

mysql> select is_used_lock('lock1'); +-----------------------+ | is_used_lock('lock1') | +-----------------------+ | 20 | # 返回结果为当前连接ID，表示名称为'lock1'的锁正在被使用 +-----------------------+ 1 row in set (0.00 sec) mysql>

5.重复执行指定操作的函数：BENCHMARK(count, expr)

BENCHMARK(count, expr) 用于重复 count 次执行表达式 expr

可以用于计算 MySQL 处理表达式的速度

可以在 MySQL 客户端内部报告语句执行的时间

mysql> help benchmark; Name: 'BENCHMARK' Description: Syntax: BENCHMARK(count,expr) The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how quickly MySQL processes the expression. The result value is 0, or NULL for inappropriate arguments such as a NULL or negative repeat count. The intended use is from within the mysql client, which reports query execution times: URL: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html Examples: mysql> SELECT BENCHMARK(1000000,AES_ENCRYPT('hello','goodbye')); +---------------------------------------------------+ | BENCHMARK(1000000,AES_ENCRYPT('hello','goodbye')) | +---------------------------------------------------+ | 0 | +---------------------------------------------------+ 1 row in set (4.74 sec) mysql> select password('123'); +-------------------------------------------+ | password('123') | +-------------------------------------------+ | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) # 执行1次加密操作花费了0.00秒 mysql> select benchmark(10000000,password('123')); +-------------------------------------+ | benchmark(10000000,password('123')) | +-------------------------------------+ | 0 | +-------------------------------------+ 1 row in set, 1 warning (1.46 sec) # 执行10000000次加密操作花费了1.46秒 mysql>

6.改变字符集的函数：CONVERT(… USING …)

CONVERT(… USING …) 用于改变字符串的默认字符集，但是这是一个临时转换

mysql> select charset('abc'); +----------------+ | charset('abc') | +----------------+ | utf8 | # 默认是utf8字符集 +----------------+ 1 row in set (0.00 sec) mysql> select charset(convert('abc' using latin1)); +--------------------------------------+ | charset(convert('abc' using latin1)) | +--------------------------------------+ | latin1 | # 转换成latin1字符集 +--------------------------------------+ 1 row in set (0.00 sec) mysql>

7.改变数据类型的函数：CAST(x, AS type) 、CONVERT(x, type)

CAST(x, AS type) 用于将一个数据类型的值转换为另一个数据类型的值

# 将整数类型100转换为带有3个显示宽度的字符串类型，结果为100 # 将整数类型100转换为带有2个显示宽度的字符串类型，结果为10 # 将整数类型100转换为带有1个显示宽度的字符串类型，结果为1 mysql> select cast(100 as char(3)), cast(100 as char(2)), cast(100 as char(1)); +----------------------+----------------------+----------------------+ | cast(100 as char(3)) | cast(100 as char(2)) | cast(100 as char(1)) | +----------------------+----------------------+----------------------+ | 100 | 10 | 1 | +----------------------+----------------------+----------------------+ 1 row in set, 2 warnings (0.00 sec) mysql>

CONVERT(x, type) 用于将一个数据类型的值转换为另一个数据类型的值

# 将整数类型100转换为带有3个显示宽度的字符串类型，结果为100 # 将整数类型100转换为带有2个显示宽度的字符串类型，结果为10 # 将整数类型100转换为带有1个显示宽度的字符串类型，结果为1 mysql> select convert(100,char(3)), convert(100,char(2)), convert(100,char(1)); +----------------------+----------------------+----------------------+ | convert(100,char(3)) | convert(100,char(2)) | convert(100,char(1)) | +----------------------+----------------------+----------------------+ | 100 | 10 | 1 | +----------------------+----------------------+----------------------+ 1 row in set, 2 warnings (0.00 sec) mysql>

两个效果一样这只是我的一些浅薄的见解，望多指教！

最新回复(0)