mysql 函数

发表于   |   分类于

前言

mysql语法支持函数的调用，例如：获取当前时间的内置函数now()；这样我们可以很方便的在sql语句中直接获取系统当前的时间，concat(str1, str2, str3)拼接字符串函数，如果mysql没有提供这些内置的函数，对于获取当前时间，字符串的拼接等功能，我们就只能在应用层做处理了，除此之外，mysql还支持自定义函数，用于完成某些特定的功能；但是，我们并不建议，自定义函数中处理的逻辑过于复杂，这样会影响mysql服务器处理请求的效率，毕竟对于一个系统来说，数据存储层往往都是整个系统的瓶颈所在，处理的逻辑越简单越好。

内置函数

  • 条件判断

    //语法一：CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result..n END  
    select (CASE WHEN type=1 THEN '收入' WHEN type=2 THEN '支出' ELSE '转账' END) AS '类型'  from t_table; 

    //语法二：IF(expr1,expr2,expr3) expr1为表达式；expr2,expr3为条件值。true返回expr2,否则返回expr3  
    select IF(type=1,'收入','支出') AS '类型' from t_table;  

    //语法三：IFNULL(expr1,expr2);expr1为变量值；expr2为条件值，如果该表达式为NULL，则返回expr2，否则返回expr1  
    select IFNULL(FNAME,'未知名称') as name from t_table;

  • concat 函数

    //字符串拼接函数(应用场景：模糊查询等)
    select * from t_table where FName like concat('%',#name#,'%')

  • concat_ws 函数

    //以|连接字符串: '1|2|3'
    select concat_ws('|',1,2,3) from t_table;

  • min 函数

    //查询当前列的最小值
    select min(column) as '全表最小值' from t_table;
    //分组后，求每组的最小值
    select min(column) as '每组最小值' from t_table group by fgroup;

  • max 函数

    //查询当前列的最大值
    select max(column) as '全表最大值' from t_table;
    //分组后，求每组的最小值
    select max(column) as '每组最大值' from t_table group by fgroup;

  • sum 函数

    //求表中该列的总和
    select sum(column) as '总和' from t_table;
    //分组后，求每组的总和
    select sum(column) as '每组总和' from t_table group by fgroup;

  • count 函数

    //求表总记录数
    select count(column) as '全表总记录数' from t_table;
    //分组后，求每组的总记录数
    select count(column) as '每组总记录数' from t_table group by fgroup;

  • length 函数

    //求列名对应值的字节数
    select length(column) as '字节数' from t_table;

  • char_length 函数

    //求列名对应值的字符数
    select char_length(column) as '字符数' from t_table;

  • upper 函数

    //将查询出来的值转换为大写
    select upper(column) from t_table;

  • lower 函数

    //将查询出来的值转换为小写
    select lower(column) from t_table;

  • trim 函数

    //去掉查询值两端的空格
    select trim(column) from t_table;

  • reverse 函数

    //将查询的值，逆序输出
    select reverse(column) from t_table;

  • substring 函数

    //截取从字符位置1开始，长度3的字符串
    select substring(column,1,3) as '截取字符串' from t_table;

  • now 函数

    //设置最后一次修改时间为当前时间
    update t_table set lastModifyTime = now() where id = 1;

  • month 函数

    //获取创建时间的月份
    select month(createTime) as '日期月份' from t_table;

  • year 函数

    //获取创建时间的年份
    select year(createTime) as '日期年份' from t_table;

  • date_format 函数

    //日期格式化
    select date_format(createTime,'%Y-%m-%d') as '格式化时间(yyyy-MM-dd)' from t_table;
    select date_format(createTime,'%Y-%m-%d %H:%i:%s') as '格式化时间(yyyy-MM-dd HH:mm:ss)' from t_table;

自定义函数

  • 示例

    自定义getParentIdPath函数；其作用：递归并以字符串形式输出指定记录的所有parentId。

    BEGIN
      //定义变量
      DECLARE info VARCHAR(255);
      DECLARE pId BIGINT;
      
      //将查出来的值赋给变量pId
      SELECT FParentId INTO pId FROM t_table WHERE FID=locationId;
      
      //给变量赋值
      SET info=locationId;
      
      // do-while循环
      WHILE pId IS NOT NULL DO
          SET info = CONCAT(pId, split, info);
          SELECT FParentId INTO pId FROM t_table WHERE FID=pId;
      END WHILE;
      
      //返回结果
      RETURN info;
    END
    参数：`locationId` bigint,`split` varchar(10)
    返回值：varchar(255)
    类型：FUNCTION

  • 使用

    select getParentIdPath(FlocationId, '->') as '父parentId路径' from t_table

总结

以上只是介绍了比较常用的内置函数使用，以及自定义函数的定义及使用；自定义函数字符类型的参数要指定长度，例如：’split’ varchar(10)；切记不要在自定义函数中做过于复杂的逻辑处理，会影响mysql服务器处理性能的。