mybatis和mysql常用总结

日常中用到的数据库是mysql,框架是mybatis,常用零碎知识总结，以供参考：

mysql常见函数：

REPLACE()，字符替换函数:

CONCAT(sl，s2，...) 字符拼接

REPLACE (UUID(),'-','') //生成uuid可作为主键id;

SUBSTR(),字符串截取函数:

REPLACE (phone,SUBSTR(phone,4,4),'****')//可对手机号进行脱敏

DATE_FORMAT(),日期时间格式化函数：

SELECT DATE_FORMAT(createTime,'%Y-%m') as month,COUNT(*) from reports GROUP BY month;

DATEDIFF()函数：日期差函数；

SELECT DATEDIFF("1992-12-13","1990-01-03")

GREATEST(arg1, arg2, arg3, ...)：该函数返回参数列表最大值，参数有一个为null,就返回null;

对应返回最小值函数LEAST(arg1, arg2, arg3, ...)；

SELECT GREATEST(ifnull(date1,0), ifnull(date2,0)) as a from DUAL order by a;

使用 GROUP_CONCAT（）函数必须使用group by函数对源数据进行分组，否则所有数据会被合并成一行

SELECT *from a 
where cd = '1111'
and not EXISTS (SELECT b.id from b b where b.cd = a.cd )


SELECT *
FROM a
WHERE ocd = '2'
ORDER BY (CASE WHEN updated_time IS NOT NULL THEN updated_time ELSE time END) DESC
LIMIT 0,10

REPLACE(UUID(),'-',''), 
REPLACE(user_phone,SUBSTR(phone,4,4), "****")号码脱敏
upper(#{Spell})
CONCAT(name,'test')
CONCAT_WS(',',name,'test')
DATE_FORMAT(DATE_SUB(date,INTERVAL 1 DAY),'%Y-%m-%d') useDeadline
DATEDIFF("1993-03-10","1992-10-01") 日期差函数 时间大的放前面，小的放后面
TIMESTAMPDIFF(YEAR, "1991-02-03", CURDATE()) 时间差函数 时间小的放前面，大的放后面
LOCATE(#{code,jdbcType=VARCHAR},tree_path) >0
case
CASE #{param.Time,jdbcType=TIMESTAMP} WHEN NULL THEN NULL ELSE DATE_ADD(#{param.Time,jdbcType=TIMESTAMP},INTERVAL 1 DAY) END,
CASE WHEN #{param.Time,jdbcType=TIMESTAMP} IS NULL THEN NULL ELSE DATE_ADD(#{param.Time,jdbcType=TIMESTAMP},INTERVAL 1 DAY) END,
left(str，index),right(str,index),substring(str,index，len)字符截取函数
GROUP_CONCAT(name ORDER BY convert(name using gbk) asc SEPARATOR "，")

UPDATE tablea set name=TRIM(TRAILING FROM name)字符去除空白字符
ALTER TABLE tablea RENAME tableaa

show VARIABLES like "%CACHE%"查询数据库缓存相关信息（是否开启，大小等）
show STATUS like "%qcache%"查询缓存命中数

1.mybatis中做数据库查询返参类型如果是map类型，java代码中对应的类型通常是Map<String,Object>:

mapper.java文件中接口定义：
List<Map<String,Object>> selectProductCount();

mapper.xml：
<select id="selectProductCount" resultType="map">
	SELECT
	org_cd as orgCd,
	count(org_cd) as proCount
	from product
	GROUP BY org_cd
</select>

mysql不同数据类型比较：

Mysql在比较两种不同数据类型时，第一步是将他们转化为同一种类型，然后在比较。例如有个datatime字段，和一个日期字符串做比较，mysql会把日期字符串转换成datetime类型再比较

多表关联更新：

update aa inner join bb on aa.pid = bb.id set aa.name = bb.name where aa.flag = 1;

批量多条件查询：

<if test="addVolist != null and addVolist.size() > 0">
			AND (
			<foreach collection="addVolist" index="index" item="item" separator="OR" open="(" close=")">
			(
				b.acd=#{item.a,jdbcType=VARCHAR}
	     		AND b.bcd=#{item.b,jdbcType=VARCHAR}
	     		AND b.ccd=#{item.c,jdbcType=VARCHAR}
	     	)
			</foreach>
			)
		</if>

  <insert id="insertBatchSelective" parameterType="com.a.vo" >
    insert into stu
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>

    </trim>
    values
      <foreach collection="idList" open="(" close=")" item="item" separator="),(">
        <trim suffixOverrides="," >
      <if test="item != null" >
        #{item,jdbcType=INTEGER},
      </if>

        </trim>
      </foreach>
  </insert>


批量新增模板
<insert id="batchd">
    <foreach item="item" index="index" collection="List" open="" separator=";" close="">
	 	INSERT ignore INTO tablea(
	  	   	a,
			b,
			c,
			...)
		SELECT 
	  		a,
			b,
			c,
			...)
			FROM tablea 
	  	WHERE a = #{a,jdbcType=VARCHAR}

	</foreach>
</insert>	
批量删除模板
  <delete id="deletes">
  	DELETE
	FROM
		stu
	WHERE
		objectcd IN 
		<foreach collection="objCds" open="(" close=")" item="item" separator=",">
			#{item}
		</foreach>
  </delete>
  <delete id="deleteList">
	  <if test="voList!=null and voList.size()>0 ">
	    <foreach collection="voList" item="vo" open="" separator=";" close=";">
	     delete from str where a=#{vo.a} and b=#{vo.b} 
	  	</foreach>
	  </if>
  </delete>

java:

流操作：

List<String> cds = aos.stream().map(vo -> vo.getCode()).collect(Collectors.toList());
/分组
Map<String, List<AddVo>> collect =addVoList.stream().collect(Collectors.groupingBy(AddVo::getCd));
//去重
List<AddVo> list = paramList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> o.getCd()))),ArrayList::new));

oles.stream().map(item -> list.stream().filter(target -> item.getCd().equals(target.getCd())).findFirst().map(target -> {
						 item.seName(target.getName());
							return voList;
						}).orElse(null)).collect(Collectors.toList());
取集合差集
seVos = seVos.stream().filter(item -> !desVos.stream().map(e -> e.getCode()).collect(Collectors.toList()).contains(item.getCode())).collect(Collectors.toList());

vo = vos.stream().filter(item -> !set.contains(String.valueOf(item.getId()))).findFirst().orElse(null);

List<CompletableFuture<JSONObject>> completableFutureList = new ArrayList<>();
		if (!CollectionUtils.isEmpty(list)) {
			list.forEach(vo -> {
				CompletableFuture<JSONObject> completableFuture = CompletableFuture
						.supplyAsync(() -> runa(vo), executor);
			 completableFutureList.add(completableFuture);
				try {
				 Thread.sleep(200);
				} catch (InterruptedException e) {
				 logger.error("aaaaaa",e);
				}
			});
			List<JSONObject> result = completableFutureList.stream().map(CompletableFuture::join).collect(Collectors.toList());
 Collections.sort(bList, Comparator.comparingInt(o -> aList.indexOf(o.getCustomerTypeCd())));

敏感信息脱敏：

phone.replaceAll("(\\w{3})\\w*(\\w{4})", "$1****$2")
email.replaceAll("[^@]*(@.*$)", "****$1")

string.replaceAll(".","*")

linux环境下常用命令：

tailf -n 100 a.txt //显示文件末尾100行

grep ‘’good‘’ *.log -rnEi |grep "student\|teacher" //过滤字符串