Mysql内置函数

前言

今天在启动服务的时候，发现select sum的时候返回了一个null，之前一直以为没有的话会返回0。所以查了一下，发现有几个函数可以判断空值，这样自己手动处理null为0就正常了。
所以顺便整理一下mysql的内置函数。

处理null值

这里的expr表达式可以是常量或者某些字段

• COALESCE(expression_1, expression_2, ...,expression_n)
  在所有的表达式中返回值为第一个不是null的值
• IFNULL(expr1,expr2)
  如果第一个表达式为NULL，返回第二个表达式，否则返回第一个
• NULLIF(expr1,expr2)
  如果两个表达式相同，则返回NULL，否则返回expr1的值
• ISNULL(expr1)
  如果expr1的值为null，返回1，否则返回0

字符串函数

• CANCAT(expr1，expr2)/CONCAT_WS(x, s1, s2, ...)
  用于将所有参数表达式的值连接，如CONCAT(id, '->', name)，会返回"1->User"类似的信息。concat_ws会使用第一个参数来连接其他字符。
• LTRIM(str)/RTRIM(str)/TRIM(str)
  用于去除str中的空格，分别对应去除左、右、两端空格。
  其中trim函数有更加强大的用法

  TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
  

  可以指定删除字符的位置以及删除的字符，默认为BOTH和' '(空格)字符。
• LEFT(str,n)/RIGHT(str,n)
  返回str字符串最左边/最右边n个长度的串
• SPACE(N)
  返回N个字符的空格字符
• REPEAT(str,count)
  返回str重复count次得到的新字符串
• ELT(N,str1,str2,str3,...)
  根据N的值来确定返回哪个参数，比如N为1，则返回str1。如果N的值超过参数的个数，则返回NULL
• FIELD(str,str1,str2,str3,...)
  返回列表中和str相等的第一个值的索引，如果没有返回0。比如

l> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
+---------------------------------------------------------+
| FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo')          |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

• FIND_IN_SET(needle,haystack)
  haystack为一个由','分隔的字符串，needle是一个不包含','字符的字符串。函数返回haystack所有','间隔的子串中和needle相等的子串的位置，如果没有则返回0，如果两个参数任何一个为null，返回null

mysql> SELECT FIND_IN_SET('y','x,y,z');
+--------------------------+
| FIND_IN_SET('y','x,y,z') |
+--------------------------+
|                        2 |
+--------------------------+
1 row in set

• MAKE_SET(bits,str1,str2,..)
  返回一个用','间隔的字符串，中间的键值由函数的参数组成，具体的组成方式由bits决定，没一个比特位代表一个参数，如5换成2进制为101，表示取第一个和第三个参数。如果选取的参数中有null，则跳过。

时间函数

• 日期函数
  • CURDATE(), CURRENT_DATE, CURRENT_DATE()
    用于获取当前日期。格式为'YYYY-MM-DD'，若+0则返回YYYYMMDD
  • UTC_DATE, UTC_DATE()
    返回当前世界标准时间; 若+0则返回YYYYMMDD
  • UNIX_TIMESTAMP([date])
    返回一个时间戳。
• 时间函数
  • CURTIME(), CURRENT_TIME, CURRENT_TIME()
    用于获取当前时间， 格式为'HH:MM:SS' 若+0则返回 HHMMSS
  • UTC_TIME, UTC_TIME()
    用于获取当前世界标准时间， 格式为同上
• day相关函数
  • LAST_DAY(date)
    返回该月的最后一天对应的值。 如果参数是无效的，则返回NULL。
  • DAY(date)，DAYOFMONTH(date)
    返回值1-31或者参数为0时返回0，可以与LASTDAY结合返回一个月的天数
  • DAYOFWEEK(date)
    返回值1-7 对应 星期天-星期六
  • DAYOFYEAR(date)
    返回值1-366
• 时间处理函数
  • DATEDIFF(date_expr1,date_expr2)
    返回相差的天数，有正负值的区别。如果需要换算成相差的周或者月，可以除以对应的换算进制再取整。
  • TIMEDIFF(expr1,expr2)
    返回两个TIME或DATETIME值之间的差值，但是两个参数的类型必须一致，否则会返回null，其中一个值为null，返回值也会是null
  • TIMESTAMPDIFF(unit,begin,end)
    返回end-begin的值，单位为unit，unit可选的值有（MICROSECOND，SECOND，MINUTE，HOUR，DAY，WEEK，MONTH，QUARTER，YEAR）
  • EXTRACT(unit FROM date)
    用来截取时间中的某些单位之间的间隔，unit可取的值有（DAY，DAY_HOUR，DAY_MICROSECOND，DAY_MINUTE，DAY_SECOND，HOUR，HOUR_MICROSECOND，HOUR_MINUTE，HOUR_SECOND，MICROSECOND，MINUTE，MINUTE_MICROSECOND，MINUTE_SECOND，MONTH，QUARTER，SECOND，SECOND_MICROSECOND，WEEK，YEAR，YEAR_MONTH）

格式转换函数

• CONV(N,from_base,to_base)
  函数用于转换进制，将N从from_bace进制转换为to_back进制，进制的范围为2-36。
• CAST(expr AS type)
  函数可以将任何类型的值转换为具有指定类型的值。目标类型可以为（BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED）