MySQL数据库日常开发常用函数整理

背景说明

• MySQL数据库日常开发写SQL时经常用一些函数，这里对日常用到的函数及用法专门整理，以便查看。

日期类函数

• DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')
• DATE_SUB(NOW(),INTERVAL 90 day)
• DATE_FORMAT(arise_time,'%Y-%m-%d') %Y 年，4 位 %m 月，数值(00-12) %d 月的天，数值(00-31)
• <![CDATA[ TO_DAYS( NOW() ) - TO_DAYS( arise_time) < 30 ]]>
• DATE_FORMAT(a.arise_time,'%Y-%m-%d') = '2020-10-21'
• DATE_FORMAT(#{startTime},'%Y-%m-%d %H:%i:%s')
• select UNIX_TIMESTAMP('2020-01-11 09:53:32'); 1578707612
• select FROM_UNIXTIME(1578707612); 2020-01-11 09:53:32
• 日期格式：
  • '%Y-%m-%d %H:%i:%S'
  • '%Y-%m-%d'，%Y 年，4 位 %m 月，数值(00-12) %d 月的天，数值(00-31)
• 注：函数 描述
  NOW() 返回当前的日期和时间
  CURDATE() 返回当前的日期
  CURTIME() 返回当前的时间
  DATE() 提取日期或日期/时间表达式的日期部分
  EXTRACT() 返回日期/时间按的单独部分
  DATE_ADD() 给日期添加指定的时间间隔
  DATE_SUB() 从日期减去指定的时间间隔
  DATEDIFF() 返回两个日期之间的天数
  DATE_FORMAT() 用不同的格式显示日期/时间

字符串类函数

• CONCAT('',#{变量名},'%')

mysql 保留字段值两位小数

• 1、round(x,d) :用于数据的四舍五入,round(x) ,其实就是round(x,0),也就是默认d为0；d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；

SELECT ROUND(100.3465,2),ROUND(100,2),ROUND(0.6,2),ROUND(114.6,-1);

结果分别：100.35,100，0.6,110

• 2、TRUNCATE(x,d)：函数返回被舍去至小数点后d位的数字x。若d的值为0，则结果不带有小数点或不带有小数部分。若d设为负数，则截去（归零）x小数点左起第d位开始后面所有低位的值。

SELECT TRUNCATE(100.3465,2),TRUNCATE(100,2),TRUNCATE(0.6,2),TRUNCATE(114.6,-1);

结果分别：100.34,100，0.6,110

• 3、FORMAT（X,D）：强制保留D位小数，整数部分超过三位的时候以逗号分割，并且返回的结果是string类型的

SELECT FORMAT(100.3465,2),FORMAT(100,2),FORMAT(,100.6,2);

结果分别：100.35,100.00，100.60

Mysql中的字符串转换成数字类型

• 1）直接用加法

字符串+0

例如：

select * from orders order by (mark+0)  desc

• 2）使用函数

CAST(value as type);

CONVERT(value, type);

• 注：这里的type可以为：
  　　　浮点数 : DECIMAL
  　　　整数 : SIGNED
  　　　无符号整数 : UNSIGNED

IF函数的使用

IF(expr1,expr2,expr3)

• 如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。
• 例如1：

SELECT if(sage < 20,'未成年','已经成年') result
from student
WHERE sname = '刘一'

• 例2：

SELECT if(sname = '刘一','他的名字叫六一','他不叫六一') result
from student

动态SQL

• 小于等于的写法
  • <![CDATA[ <= ]]>
    在今天使用Mybatis的xml文件中写sql语句时写入了一些特殊字符 如 < > & 等,但解析xml文件的时候会被转义，事实上并不希望它被转义,可以使用<![CDATA[ sql ]]>. 这是XML语法,在CDATA内部的所有内容都会被解析器忽略. 但是因此会导致<if test=""></if> <where></where> <choose></choose> <trim></trim>等标签本身功能失效,所以使用时把带有特殊字符的语句或字符本身放在<![CDATA[ ]]>内即可,即尽量精确其作用范围.
• MyBatis动态SQL中字符串比较判断

1. 改为：test="param eq 'A'.toString()"
2. 原因是OGNL语法的问题：
   这里 'A' 将被认为是 char 类型，但是 'AA' 或者 "A" 将被作为 String类型。
   所以我们可以用转义：<if test="name == &quot;A&quot;">
3. 或者将 <if test="index == 'A'"> 改为 <if test='index == "A"'>

未完待补充。。。

欢迎关注我的微信公众号ItBeeCoder