MySQL常用内置函数详细介绍

MySQL提供了丰富的内置函数，用于数据处理、计算、转换和分析，以下是常用的内置函数介绍。

一、字符串函数

字符串函数用于处理和操作文本数据，是数据库操作中最常用的函数类别之一。

CONCAT()

连接两个或多个字符串。

SELECTCONCAT('Hello',' ','World');-- 返回 'Hello World'SELECTCONCAT(first_name,' ',last_name)ASfull_nameFROMemployees;

注意：任一参数为NULL，结果为NULL。使用CONCAT_WS可避免此问题。

CONCAT_WS()

使用指定分隔符连接字符串，自动跳过NULL值。

SELECTCONCAT_WS(', ','John','Doe',NULL,'Engineer');-- 返回 'John, Doe, Engineer'SELECTCONCAT_WS('-',year,month,day)ASdate_formattedFROMevents;

SUBSTRING() / SUBSTR()

提取字符串的子串。

SELECTSUBSTRING('MySQL Database',1,5);-- 返回 'MySQL'SELECTSUBSTRING('2023-05-15',6,2)ASmonth;-- 返回 '05'

LENGTH() / CHAR_LENGTH()

• LENGTH(): 返回字符串的字节长度
• CHAR_LENGTH(): 返回字符串的字符数

SELECTLENGTH('你好'),CHAR_LENGTH('你好');-- 通常返回 6, 2 (UTF8编码)

REPLACE()

替换字符串中的子串。

SELECTREPLACE('My SQL',' ','');-- 返回 'MySQL'SELECTREPLACE(email,'@example.com','@newdomain.com')FROMusers;

TRIM() / LTRIM() / RTRIM()

移除字符串两端/左端/右端的空格或指定字符。

SELECTTRIM(' MySQL ');-- 返回 'MySQL'SELECTTRIM(BOTH'x'FROM'xxxMySQLxxx');-- 返回 'MySQL'

UPPER() / LOWER()

转换字符串为大写/小写。

SELECTUPPER('mysql'),LOWER('MYSQL');-- 返回 'MYSQL', 'mysql'

LPAD() / RPAD()

在字符串左侧/右侧填充指定字符至指定长度。

SELECTLPAD('123',5,'0');-- 返回 '00123'SELECTRPAD(name,20,'.')ASformatted_nameFROMproducts;

二、数值函数

数值函数用于执行数学计算和处理数字数据。

ROUND()

四舍五入到指定小数位。

SELECTROUND(123.4567,2);-- 返回 123.46SELECTROUND(123.4567,-1);-- 返回 120 (四舍五入到十位)

CEIL() / CEILING() / FLOOR()

• CEILING() / CEIL(): 向上取整
• FLOOR(): 向下取整

SELECTCEIL(12.3),FLOOR(12.9);-- 返回 13, 12

ABS()

返回绝对值。

SELECTABS(-15.3),ABS(15.3);-- 都返回 15.3

MOD()

返回除法余数。

SELECTMOD(10,3);-- 返回 1SELECT*FROMordersWHEREMOD(order_id,2)=0;-- 选择偶数ID订单

RAND()

生成0到1之间的随机浮点数。

SELECTRAND();-- 例如 0.7384925760134728SELECTFLOOR(1+RAND()*10)ASrandom_number;-- 1到10的随机整数

POWER() / POW()

计算幂。

SELECTPOWER(2,3);-- 返回 8 (2^3)SELECTPOWER(price,1.05)ASincreased_priceFROMproducts;

SQRT()

计算平方根。

SELECTSQRT(16);-- 返回 4

SIGN()

返回数字的符号：-1(负), 0(零), 1(正)。

SELECTSIGN(-5),SIGN(0),SIGN(5);-- 返回 -1, 0, 1

三、日期和时间函数

日期和时间函数用于处理日期时间数据，是业务应用中最常用的函数类别之一。

NOW()

返回当前日期和时间。

SELECTNOW();-- 例如 '2023-05-15 14:30:45'INSERTINTOlogs(event_time,message)VALUES(NOW(),'User login');

CURDATE() / CURTIME()

• CURDATE(): 返回当前日期
• CURTIME(): 返回当前时间

SELECTCURDATE(),CURTIME();-- 例如 '2023-05-15', '14:30:45'

DATE_ADD() / DATE_SUB()

增加或减少日期间隔。

SELECTDATE_ADD('2023-05-15',INTERVAL7DAY);-- 返回 '2023-05-22'SELECTDATE_SUB(NOW(),INTERVAL1MONTH)ASone_month_ago;

DATEDIFF()

计算两个日期之间的天数差。

SELECTDATEDIFF('2023-06-01','2023-05-15');-- 返回 17SELECTDATEDIFF(NOW(),hire_date)ASdays_employedFROMemployees;

EXTRACT()

从日期时间值中提取特定部分。

SELECTEXTRACT(YEARFROM'2023-05-15');-- 返回 2023SELECTEXTRACT(MONTHFROMNOW())AScurrent_month;

DATE_FORMAT()

格式化日期时间值为字符串。

SELECTDATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');-- '2023-05-15 14:30:45'SELECTDATE_FORMAT(birth_date,'%W, %M %e, %Y')ASformatted_dobFROMusers;

STR_TO_DATE()

将字符串转换为日期时间值。

SELECTSTR_TO_DATE('15,05,2023','%d,%m,%Y');-- 返回 '2023-05-15'

TIMESTAMPDIFF()

计算两个日期时间之间的差（指定单位）。

SELECTTIMESTAMPDIFF(HOUR,'2023-05-15 08:00:00','2023-05-15 14:30:00');-- 返回 6

四、聚合函数

聚合函数用于对一组值执行计算并返回单个值，通常与GROUP BY子句一起使用。

COUNT()

计算行数。

SELECTCOUNT(*)FROMcustomers;-- 计算所有行SELECTCOUNT(email)FROMcustomers;-- 只计算非NULL的emailSELECTdepartment,COUNT(*)ASemployee_countFROMemployeesGROUPBYdepartment;

SUM()

计算数值列的总和。

SELECTSUM(salary)AStotal_payrollFROMemployees;SELECTdepartment,SUM(salary)ASdept_totalFROMemployeesGROUPBYdepartment;

AVG()

计算平均值。

SELECTAVG(rating)ASaverage_ratingFROMproduct_reviews;SELECTdepartment,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment;

MIN() / MAX()

返回最小值和最大值。

SELECTMIN(price)AScheapest,MAX(price)ASmost_expensiveFROMproducts;SELECTcategory,MIN(release_date)ASfirst_releaseFROMmoviesGROUPBYcategory;

GROUP_CONCAT()

将组中的字符串连接为单个字符串。

SELECTdepartment,GROUP_CONCAT(last_name SEPARATOR', ')ASemployeesFROMemployeesGROUPBYdepartment;

五、控制流函数

控制流函数用于基于条件返回不同值，类似于编程语言中的条件语句。

IF()

简单的条件判断。

SELECTIF(score>=60,'Pass','Fail')ASresultFROMexams;SELECTname,IF(is_active,'Active','Inactive')ASstatusFROMusers;

IFNULL()

如果第一个参数为NULL，返回第二个参数；否则返回第一个参数。

SELECTIFNULL(phone,'No phone provided')AScontact_phoneFROMcustomers;SELECTproduct_name,IFNULL(discount,0)ASdiscount_percentFROMproducts;

NULLIF()

如果两个参数相等，返回NULL；否则返回第一个参数。

SELECTNULLIF(quantity,0)ASsafe_quantityFROMinventory;-- 将0转换为NULL

CASE

复杂的条件判断，有两种形式。

-- 简单CASESELECTname,CASEratingWHEN'A'THEN'Excellent'WHEN'B'THEN'Good'WHEN'C'THEN'Average'ELSE'Poor'ENDASperformanceFROMemployees;-- 搜索CASESELECTproduct_name,price,CASEWHENprice<10THEN'Budget'WHENpriceBETWEEN10AND50THEN'Mid-range'WHENprice>50THEN'Premium'ELSE'Unknown'ENDASprice_categoryFROMproducts;

六、JSON函数 (MySQL 5.7+)

随着JSON在应用中的普及，MySQL提供了强大的JSON处理功能。

JSON_OBJECT()

创建JSON对象。

SELECTJSON_OBJECT('name','John','age',30,'city','New York');-- 返回 {"name": "John", "age": 30, "city": "New York"}

JSON_ARRAY()

创建JSON数组。

SELECTJSON_ARRAY('apple','banana','orange');-- 返回 ["apple", "banana", "orange"]

JSON_EXTRACT() / -> (简写)

提取JSON文档中的数据。

SELECTJSON_EXTRACT('{"name": "John", "address": {"city": "New York"}}','$.name');-- 返回 "John"SELECT'{"name": "John", "address": {"city": "New York"}}'->'$.address.city';-- 返回 "New York"

JSON_SET()

插入或更新JSON文档中的数据。

SELECTJSON_SET('{"name": "John"}','$.age',30);-- 返回 {"name": "John", "age": 30}

JSON_UNQUOTE()

移除JSON字符串值的引号。

SELECTJSON_UNQUOTE('"Hello World"');-- 返回 Hello WorldSELECTJSON_UNQUOTE(JSON_EXTRACT('{"name": "John"}','$.name'));-- 返回 John-- 简写：column->>'$.path' 等同于 JSON_UNQUOTE(JSON_EXTRACT(column, '$.path'))

七、加密和哈希函数

这些函数用于数据加密、哈希和安全处理。

MD5()

计算MD5 128位校验和。

SELECTMD5('password');-- 返回 32位十六进制字符串INSERTINTOusers(username,password_hash)VALUES('john',MD5('secure123'));

注意：MD5已不推荐用于密码存储，应使用更安全的算法。

SHA1() / SHA2()

计算SHA1 (160位) 或SHA2 (224, 256, 384, 512位) 哈希值。

SELECTSHA1('password');-- 40位十六进制字符串SELECTSHA2('password',256);-- 64位十六进制字符串 (推荐)

AES_ENCRYPT() / AES_DECRYPT()

使用高级加密标准(AES)算法加密/解密数据。

SET@key_str='my_secret_key';SET@encrypted=AES_ENCRYPT('sensitive_data',@key_str);SELECTAES_DECRYPT(@encrypted,@key_str);-- 返回 'sensitive_data'

PASSWORD()

计算MySQL原生密码哈希。

SELECTPASSWORD('password');-- 仅用于MySQL内部用户验证

注意：此函数在MySQL 8.0中已弃用，推荐使用身份验证插件。

八、转换函数

转换函数用于在不同数据类型之间转换值。

CAST()

将值转换为指定类型。

SELECTCAST('2023-05-15'ASDATE);-- 字符串转日期SELECTCAST(123.45ASSIGNED);-- 浮点转整数SELECTCAST(NOW()ASCHAR)AScurrent_time_str;-- 日期转字符串

CONVERT()

类似于CAST()，但语法不同。

SELECTCONVERT('123',SIGNED);-- 字符串转整数SELECTCONVERT('text'USINGutf8mb4);-- 转换字符集

COALESCE()

返回参数列表中第一个非NULL值。

SELECTCOALESCE(NULL,NULL,'first non-null','second');-- 返回 'first non-null'SELECTname,COALESCE(phone,email,'No contact')AScontact_infoFROMcustomers;

九、窗口函数 (MySQL 8.0+)

窗口函数执行跨多行的相关计算，而不将它们分组为单个输出行。

ROW_NUMBER()

为分区中的每一行分配唯一的序号。

SELECTdepartment,employee_name,salary,ROW_NUMBER()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASdept_rankFROMemployees;

RANK() / DENSE_RANK()

• RANK(): 有并列时跳过后续名次
• DENSE_RANK(): 有并列时不跳过后续名次

SELECTproduct_name,category,price,RANK()OVER(PARTITIONBYcategoryORDERBYpriceDESC)ASprice_rank,DENSE_RANK()OVER(PARTITIONBYcategoryORDERBYpriceDESC)ASdense_price_rankFROMproducts;

SUM() OVER() / AVG() OVER()

聚合函数与OVER()子句结合使用，计算运行总计或移动平均。

SELECTorder_date,amount,SUM(amount)OVER(ORDERBYorder_date)ASrunning_total,AVG(amount)OVER(ORDERBYorder_dateROWSBETWEEN2PRECEDINGANDCURRENTROW)ASmoving_avgFROMorders;

LEAD() / LAG()

访问当前行之后(LEAD)或之前(LAG)的行的数据。

SELECTmonth,revenue,LAG(revenue,1)OVER(ORDERBYmonth)ASprev_month_revenue,LEAD(revenue,1)OVER(ORDERBYmonth)ASnext_month_revenue,revenue-LAG(revenue,1)OVER(ORDERBYmonth)ASrevenue_changeFROMmonthly_sales;

十、其他函数

1. 正则表达式函数 (MySQL 8.0+)

REGEXP_LIKE()

判断字符串是否匹配正则表达式。

SELECT*FROMusersWHEREREGEXP_LIKE(email,'^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,4}$');

REGEXP_REPLACE()

替换匹配正则表达式的子串。

SELECTREGEXP_REPLACE(phone_number,'[^0-9]','')ASclean_phoneFROMcontacts;

2. UUID()函数

UUID() 是 MySQL 提供的用于生成通用唯一标识符（Universally Unique Identifier）的函数，它返回一个符合 RFC 4122 标准的 128 位唯一值。

-- 返回一个 36 字符长的字符串: 'c3b4c9d7-7e8f-11ee-9439-0242ac120002'SELECTUUID();-- 为会话生成唯一IDINSERTINTOuser_sessions(session_id,user_id,created_at)VALUES(UUID(),123,NOW());