【实用教程】MySQL内置函数

1 背景

在MySQL查询等操作过程中，我们需要根据实际情况，使用其提供的内置函数。今天我们就来一起来学习下这些函数，在之后的使用过程中更加得心应手。

2 MySQL函数

2.1 字符串函数

常用的函数如下：

concat(s1,s2,…sn)	字符串拼接
lower(str)	将字符串str全部转换为小写
upper(str)	将字符串str全部转换为大写
lpad(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substr(str,start,len)	截取从字符串str从start位置起的len个长度的字符串
concat(s1,s2,…sn)	字符串拼接
lower(str)	将字符串str全部转换为小写
## group_concat(…)	函数用于将查询结果集中的多行数据连接成一个字符串

简单使用实例：

select concat('hello','world');select lower('Hello');select upper('hello');select lpad('hello',10,'-');select rpad('hello',10,'-');select trim(' hello w ');  -- 从第一位开始截取，截取5位
select substr('hello world',1,5);`

使用场景：

由于业务需求变更，人员的id，统一为5位数，不足5位数的全部在前边补0，比如1的人员id需要修改为00001；

-- 使用lpad进行填充select lpad(id, 5, '0') as id from user;

注意 concat 和group_concat的使用：

CONCAT 函数：CONCAT 函数用于连接两个或多个字符串。它接受任意数量的参数，将这些参数按顺序连接起来，并返回一个包含所有参数连接结果的字符串。例如：

SELECT CONCAT('Hello', ' ', 'World'); -- 输出: 'Hello World'

GROUP_CONCAT 函数：GROUP_CONCAT 函数用于将查询结果集中的多行数据连接成一个字符串。它通常与 GROUP BY 语句一起使用，用于将分组后的多行数据连接成一个字符串，每个分组的数据用指定的分隔符隔开。如：

SELECT GROUP_CONCAT(column_name SEPARATOR ', ') FROM table_name GROUP BY group_column;

在这个例子中，column_name 是需要连接的列，group_column 是分组的列，SEPARATOR 是用于分隔连接结果的字符串。

2.2 数值函数

常用的数值函数如下（在实际工作中使用较少）：

ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的模
rand()	返回0~1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

2.3 日期函数

常用的日期函数如下：

curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date1和结束时间date2之间的天数
date_format(date, format)	将日期格式化为指定格式

简单使用实例：

select curdate();
select curtime();  
select now();  
select year('2023-07-21 12:31:45');  
select month('2023-07-21 12:31:45');  
select day('2023-07-21 12:31:45');  -- 获取当前时间往后推10年的时间  
select date_add(now(), interval 10 year);  select datediff('2023-07-21 12:31:45', '2020-07-21 0:0:45');

date_format中一些常用的日期格式化符号：

• %Y：四位年份（例如：2023）

• %m：两位月份（01 到 12）

• %d：两位日期（01 到 31）

• %H：24小时制的小时（00 到 23）

• %i：两位分钟（00 到 59）

• %s：两位秒数（00 到 59）

如果你想将日期字段 order_date 格式化为 'YYYY-MM-DD' 的形式，你可以这样使用 DATE_FORMAT 函数

SELECT DATE_FORMAT(order_date, ‘%Y-%m-%d’) AS formatted_date FROM orders;

在这个查询中，order_date 会被格式化成 ‘YYYY-MM-DD’ 的形式，并且结果会以 formatted_date 的别名返回。

还可以使用 DATE_FORMAT 函数来处理日期时间字段，例如将日期时间字段格式化为 'YYYY-MM-DD HH:MM:SS' 的形式：

SELECT DATE_FORMAT(order_datetime, ‘%Y-%m-%d %H:%i:%s’) AS formatted_datetime FROM orders;

2.4 流程控制函数

常用的流程控制函数如下：

if(value, t, f)	如果value为true，返回t，否则返回f
ifnull(value1, value2)	如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] … else [default] end	如果val1为true，返回res1，否则返回default默认值
case [expr] when [val1] then [res1] … else [default] end	如果expr的值等于val1，返回res1，否则返回default默认值

select *,  if(city = '北京','bj','other')  
as city_name  
from user;  select *,  
case when city = '北京' then 'bj'  else 'other'  end  
as city_name2  
from user;

3 其他注意

使用MySQL内置函数时，确实有一些需要注意的地方，特别是在处理大量数据时，可以影响查询的效率：

3.1 索引的使用：

• 如果在查询条件中使用了函数，可能会导致索引无法使用。例如，WHERE YEAR(date_column) = 2023 中的YEAR()函数可能导致无法使用date_column上的索引。在可能的情况下，尽量避免在查询条件中使用数。

3.2 函数的嵌套：

• 函数的嵌套使用可能会增加查询的复杂度。例如，嵌套了多层函数的查询可能会导致性能下降。在编写复杂查询时，确保函数的嵌套使用合理，不要过度复杂化查询。

3.3 数据类型转换：

• 函数可能引发数据类型转换，这可能导致不必要的性能开销。例如，在字符串和数字之间进行转换可能会消耗一定的性能。在使用函数时，注意函数返回值的数据类型，尽量避免不必要的数据类型转换。

3.4 使用合适的函数：

• 使用合适的函数能够提高查询的效率。例如，在字符串拼接时，使用CONCAT()函数通常比使用+运算符更高效。了解函数的具体功能和适用场景，选择合适的函数可以提高查询性能。

3.5 聚合函数的合理使用：

• 当使用聚合函数（如SUM()、COUNT()等）时，注意是否需要在查询中使用GROUP BY语句。不合理的聚合函数使用可能导致结果不符合预期，也可能导致性能下降。

3.6 使用EXPLAIN语句分析查询计划：

• 使用EXPLAIN语句可以分析查询的执行计划，了解MySQL是如何执行查询的。通过分析查询计划，可以发现是否有不合理的函数使用或索引未使用等问题，从而优化查询性能。

总之，合理使用MySQL内置函数是可以提高查询效率的，但在使用时需要根据具体情况选择合适的函数，同时，通过使用EXPLAIN语句分析查询计划，可以帮助你发现潜在的性能问题并进行优化。

关注我，我们一起学习。