MySQL函数大全，MySQL常用函数汇总!

骑猪看日落 2024-03-23 23:23 52阅读 0赞

MySQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。

MySQL 包含了大量并且丰富的函数，剩下的可以到「[MySQL 官网][MySQL]」查询。

可以对 MySQL 常用函数进行简单的分类，大概包括数值型函数、字符串型函数、日期时间函数、聚合函数等。

<table>  MySQL 数值型函数  
 <tbody> 
  <tr> 
   <th>函数名称</th> 
   <th>作&nbsp;用</th> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/abc.html" rel="nofollow" title="ABS">ABS</a></td> 
   <td>求绝对值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/sqrt.html" rel="nofollow" title="SQRT">SQRT</a></td> 
   <td>求二次方根</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/mod.html" rel="nofollow" title="MOD">MOD</a></td> 
   <td>求余数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/ceil_celing.html" rel="nofollow" title="CEIL 和&nbsp;CEILING">CEIL 和&nbsp;CEILING</a></td> 
   <td>两个函数功能相同，都是返回不小于参数的最小整数，即向上取整</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/floor.html" rel="nofollow" title="FLOOR">FLOOR</a></td> 
   <td>向下取整，返回值转化为一个BIGINT</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/rand.html" rel="nofollow" title="RAND">RAND</a></td> 
   <td>生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/round.html" rel="nofollow" title="ROUND">ROUND</a></td> 
   <td>对所传参数进行四舍五入</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/sign.html" rel="nofollow" title="SIGN">SIGN</a></td> 
   <td>返回参数的符号</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/pow_power.html" rel="nofollow" title="POW 和&nbsp;POWER">POW 和&nbsp;POWER</a></td> 
   <td>两个函数的功能相同，都是所传参数的次方的结果值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/sin.html" rel="nofollow" title="SIN">SIN</a></td> 
   <td>求正弦值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/asin.html" rel="nofollow" title="ASIN">ASIN</a></td> 
   <td>求反正弦值，与函数 SIN 互为反函数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/cos.html" rel="nofollow" title="COS">COS</a></td> 
   <td>求余弦值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/acos.html" rel="nofollow" title="ACOS">ACOS</a></td> 
   <td>求反余弦值，与函数 COS 互为反函数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/tan.html" rel="nofollow" title="TAN">TAN</a></td> 
   <td>求正切值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/atan.html" rel="nofollow" title="ATAN">ATAN</a></td> 
   <td>求反正切值，与函数 TAN 互为反函数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/cot.html" rel="nofollow" title="COT">COT</a></td> 
   <td>求余切值</td> 
  </tr> 
 </tbody> 
</table>

<table>  MySQL 字符串函数  
 <tbody> 
  <tr> 
   <th>函数名称</th> 
   <th>作&nbsp;用</th> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/length.html" rel="nofollow" title="LENGTH">LENGTH</a></td> 
   <td>计算字符串长度函数，返回字符串的字节长度</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/concat.html" rel="nofollow" title="CONCAT">CONCAT</a></td> 
   <td>合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/insert.html" rel="nofollow" title="INSERT">INSERT</a></td> 
   <td>替换字符串函数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/lower.html" rel="nofollow" title="LOWER">LOWER</a></td> 
   <td>将字符串中的字母转换为小写</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/upper.html" rel="nofollow" title="UPPER">UPPER</a></td> 
   <td>将字符串中的字母转换为大写</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/left.html" rel="nofollow" title="LEFT">LEFT</a></td> 
   <td>从左侧字截取符串，返回字符串左边的若干个字符</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/right.html" rel="nofollow" title="RIGHT">RIGHT</a></td> 
   <td>从右侧字截取符串，返回字符串右边的若干个字符</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/trim.html" rel="nofollow" title="TRIM">TRIM</a></td> 
   <td>删除字符串左右两侧的空格</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/replace.html" rel="nofollow" title="REPLACE">REPLACE</a></td> 
   <td>字符串替换函数，返回替换后的新字符串</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/substring.html" rel="nofollow" title="SUBSTRING">SUBSTRING</a></td> 
   <td>截取字符串，返回从指定位置开始的指定长度的字符换</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/reverse.html" rel="nofollow" title="REVERSE">REVERSE</a></td> 
   <td>字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串</td> 
  </tr> 
 </tbody> 
</table>

<table>  MySQL 日期和时间函数  
 <tbody> 
  <tr> 
   <th>函数名称</th> 
   <th>作 用</th> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/curdate_current_date.html" rel="nofollow" title="CURDATE&nbsp;和 CURRENT_DATE">CURDATE&nbsp;和 CURRENT_DATE</a></td> 
   <td>两个函数作用相同，返回当前系统的日期值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/curtime_current_time.html" rel="nofollow" title="CURTIME 和 CURRENT_TIME">CURTIME 和 CURRENT_TIME</a></td> 
   <td>两个函数作用相同，返回当前系统的时间值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/now_sysdate.html" rel="nofollow" title="NOW&nbsp;和&nbsp; SYSDATE">NOW&nbsp;和&nbsp; SYSDATE</a></td> 
   <td>两个函数作用相同，返回当前系统的日期和时间值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/unix_timestamp.html" rel="nofollow" title="UNIX_TIMESTAMP">UNIX_TIMESTAMP</a></td> 
   <td>获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/from_unixtime.html" rel="nofollow" title="FROM_UNIXTIME">FROM_UNIXTIME</a></td> 
   <td>将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/month.html" rel="nofollow" title="MONTH">MONTH</a></td> 
   <td>获取指定日期中的月份</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/monthname.html" rel="nofollow" title="MONTHNAME">MONTHNAME</a></td> 
   <td>获取指定日期中的月份英文名称</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/dayname.html" rel="nofollow" title="DAYNAME">DAYNAME</a></td> 
   <td>获取指定曰期对应的星期几的英文名称</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/dayofweek.html" rel="nofollow" title="DAYOFWEEK">DAYOFWEEK</a></td> 
   <td>获取指定日期对应的一周的索引位置值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/week.html" rel="nofollow" title="WEEK">WEEK</a></td> 
   <td>获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/dayofyear.html" rel="nofollow" title="DAYOFYEAR">DAYOFYEAR</a></td> 
   <td>获取指定曰期是一年中的第几天，返回值范围是1~366</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/dayofmonth.html" rel="nofollow" title="DAYOFMONTH">DAYOFMONTH</a></td> 
   <td>获取指定日期是一个月中是第几天，返回值范围是1~31</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/year.html" rel="nofollow" title="YEAR">YEAR</a></td> 
   <td>获取年份，返回值范围是 1970〜2069</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/time_to_sec.html" rel="nofollow" title="TIME_TO_SEC">TIME_TO_SEC</a></td> 
   <td>将时间参数转换为秒数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/sec_to_time.html" rel="nofollow" title="SEC_TO_TIME">SEC_TO_TIME</a></td> 
   <td>将秒数转换为时间，与TIME_TO_SEC 互为反函数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/date_add_adddate.html" rel="nofollow" title="DATE_ADD 和 ADDDATE">DATE_ADD 和 ADDDATE</a></td> 
   <td>两个函数功能相同，都是向日期添加指定的时间间隔</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/date_sub_subdate.html" rel="nofollow" title="DATE_SUB 和 SUBDATE">DATE_SUB 和 SUBDATE</a></td> 
   <td>两个函数功能相同，都是向日期减去指定的时间间隔</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/addtime.html" rel="nofollow" title="ADDTIME">ADDTIME</a></td> 
   <td>时间加法运算，在原始时间上添加指定的时间</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/subtime.html" rel="nofollow" title="SUBTIME">SUBTIME</a></td> 
   <td>时间减法运算，在原始时间上减去指定的时间</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/datediff.html" rel="nofollow" title="DATEDIFF">DATEDIFF</a></td> 
   <td>获取两个日期之间间隔，返回参数 1 减去参数 2 的值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/date_format.html" rel="nofollow" title="DATE_FORMAT">DATE_FORMAT</a></td> 
   <td>格式化指定的日期，根据参数返回指定格式的值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/weekday.html" rel="nofollow" title="WEEKDAY">WEEKDAY</a></td> 
   <td>获取指定日期在一周内的对应的工作日索引</td> 
  </tr> 
 </tbody> 
</table>

<table>  MySQL 聚合函数  
 <tbody> 
  <tr> 
   <th>函数名称</th> 
   <th>作用</th> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/max.html" rel="nofollow" title="MAX">MAX</a></td> 
   <td>查询指定列的最大值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/min.html" rel="nofollow" title="MIN">MIN</a></td> 
   <td>查询指定列的最小值</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/count.html" rel="nofollow" title="COUNT">COUNT</a></td> 
   <td>统计查询结果的行数</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/sum.html" rel="nofollow" title="SUM">SUM</a></td> 
   <td>求和，返回指定列的总和</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/avg.html" rel="nofollow" title="AVG">AVG</a></td> 
   <td>求平均值，返回指定列数据的平均值</td> 
  </tr> 
 </tbody> 
</table>

<table>  MySQL 流程控制函数  
 <tbody> 
  <tr> 
   <th>函数名称</th> 
   <th>作用</th> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/if.html" rel="nofollow" title="IF">IF</a></td> 
   <td>判断，流程控制</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/ifnull.html" rel="nofollow" title="IFNULL">IFNULL</a></td> 
   <td>判断是否为空</td> 
  </tr> 
  <tr> 
   <td><a href="http://c.biancheng.net/mysql/case.html" rel="nofollow" title="CASE">CASE</a></td> 
   <td>搜索语句</td> 
  </tr> 
 </tbody> 
</table>

[MySQL]: https://www.mysql.com/