MyBatis+MySQL数据库操作小技巧

1.批量修改

字段一表示要修改的字段

字段二、字段三表示根据什么来进行修改

 

UPDATE 表名
    SET 字段一 = CASE 字段二
        WHEN 1 THEN 10
        WHEN 2 THEN 35
        WHEN 3 THEN 35
    END
WHERE 字段三=2 AND 字段二 in (1,2,3);

2.批量修改多个字段

 

字段一、字段三表示要修改的字段

字段二表示根据什么来进行修改

 

UPDATE 表名
    SET 
	字段一 = CASE 字段二
        WHEN 1 THEN 10
        WHEN 2 THEN 35
        WHEN 3 THEN 35
    END,
	字段三 = CASE 字段二
        WHEN 1 THEN 2
        WHEN 2 THEN 2
        WHEN 3 THEN 2
    END

WHERE 字段二 in (1,2,3);

3.批量添加

 

 

parameterType="java.util.List"
insert into t_score_week_bed_item(bedscoreid,score,itemid) values
  <foreach collection="list" item="item" separator="," >  
  	(#{item.bedscoreid},#{item.score},#{item.itemid})
  </foreach>

4.单个添加语句返回ID

 

 

useGeneratedKeys="true" keyProperty="checkid"

5.MySQL设置某个字段为默认时间函数

 

 

CURRENT_TIMESTAMP

 

6.结果集中设置某个字段不为空时显示自己，为空时显示第二个

 

IFNULL(字段名,"为空值显示")

 

 

7.设置根据查询出来的状态显示的值

 

CASE a.`status`
WHEN '0' THEN '未打款'
WHEN '1' THEN '已打款未确认'
WHEN '2' THEN '已完成'
WHEN '3' THEN '未打款被投诉'
WHEN '4' THEN '未确认被投诉'
 END AS `status`
 
IF(num<0,0,num)
格式：IF(Condition,A,B)
意义：当Condition为TRUE时，返回A；当Condition为FALSE时，返回B

8.设置时间输出格式

 

 

 

参考：

http://www.w3school.com.cn/sql/sql_func_now.asp

http://www.w3school.com.cn/sql/func_date_format.asp

 

DATE_FORMAT(a.regtime,'%Y-%m-%d %T')

 

 

9.MyBaits报异常“元素内容必须由格式正确的字符数据或标记组成”，解决方式如下

 

<if test="beginDate!=null">
 <![CDATA[AND a.adddate >#{beginDate}]]>
</if>
<if test="endDate!=null">
 <![CDATA[AND a.adddate < #{endDate}]]>
</if>

10.生成随机固定时间

 

 

SELECT FLOOR(7 + (RAND() * 6));     取7到12的随机整数，包括7到12
SELECT FLOOR(12+(rand()*13));   取12到24的随机整数，包括12到24
LPAD(FLOOR(0 + (RAND() * 23)),2,0)  取0-24之间的随机数，不够2位的前补0
格式 ： 2008-12-11 17:43:11
举例：
给用户赋一个随机日期，年份为1990-2014
select CONCAT(FLOOR(1990 + (RAND() * 25)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0))
结果：1999-10-07
select CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0))
结果：15:39:18
日期转换为时间戳
SELECT UNIX_TIMESTAMP('2015-04-29')
字符串转化成日期时间
str_to_date('2017-05-27 12:08:19',,'%Y-%m-%d %T')
下面是一个示例：生成2017-04-19 早上9点下下午21点之间生成一个随机时间并转换成时间戳
select CONCAT('2017-04-19 ',LPAD(FLOOR(9 + (RAND() * 9)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0));
select UNIX_TIMESTAMP(str_to_date(CONCAT('2017-04-19 ',LPAD(FLOOR(9 + (RAND() * 9)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)),'%Y-%m-%d %T'));

11.根据某个字段进行分组然后查询另一个字段最大值的所在的记录

 

 

 

 

select * from (select * from 表名 order by 最大值字段 desc) as a group by a.分组字段

12.判断两个字符串是否一样

 

 

 

如果这两个字符串相等返回0，如果第一个参数是根据当前的排序小于第二个参数顺序返回-1，否则返回1

SELECT STRCMP('2017-06-02',DATE_FORMAT(NOW(),'%Y-%m-%d'));

13.需要某个时间的前面多久或者后面多久的时间

例如：今天是2016年08月01日。

 

date_sub('2016-08-01',interval 1 day) 表示 2016-07-31
date_sub('2016-08-01',interval 0 day) 表示 2016-08-01
date_sub('2016-08-01',interval -1 day) 表示 2016-08-02

date_sub(curdate(),interval 1 day) 表示 2016-07-31
date_sub(curdate(),interval -1 day) 表示 2016-08-02
date_sub(curdate(),interval 1 month) 表示 2016-07-01
date_sub(curdate(),interval -1 month) 表示 2016-09-01
date_sub(curdate(),interval 1 year) 表示 2015-08-01
date_sub(curdate(),interval -1 year) 表示 2017-08-01


备注：
SELECT NOW(),CURDATE(),CURTIME()

结果类似：
NOW()       2016-08-01 16:25:46
CURDATE()   2016-08-01
CURTIME()   16:25:46

 

14.获取一个时间的年、月、日、时、分、秒

 

 

set @dt = '2008-09-10 07:15:30.123456';
select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30

15.根据开始时间结束时间判断状态

CASE
    WHEN NOW() < ROOT.START_DATE THEN 0
    WHEN NOW() > ROOT.END_DATE THEN 2
ELSE 1 END  AS `STATUS`,