批量插入

<insert id="addGoods" parameterType="map">
        insert into odr_order_goods(version, deleted, createdBy, creator, createTime)
        values
        <foreach collection="goodsList" item="item" index="index" separator=",">
            (
            0,0,#{
          
   item.createdBy},#{
          
   item.creator},#{
          
   item.createTime}
            )
        </foreach>
    </insert>

批量查询

where a.XX in 
<foreach collection="id" index="index" item="item" open="(" separator="," close=")">
          #{
          
   item}
</foreach>

插入数据后返回记录id

<insert id="insertMerchant"  parameterType="Merchant" useGeneratedKeys="true" keyProperty="id">
...
</insert>

//insert语句之前使用;
//然后可以在传入的参数中直接获取id
<selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER">
            SELECT LAST_INSERT_ID() AS id
</selectKey>

根据时间筛选记录

//%Y-%m-%d %H:%i到分  %Y-%m-%d %H:%i:%S 到秒
<if test="startTime != null and startTime != ">
   AND DATE_FORMAT(a.createTime,%Y-%m-%d %H:%i) >= #{
          
   startTime}
</if>
<if test="endTime != null and endTime != ">
    AND DATE_FORMAT(a.createTime,%Y-%m-%d %H:%i) <![CDATA[ <= ]]> #{
          
   endTime}
 </if>

格式化金额到2位数

ROUND(a.XXX，2) //精确数字两位小数 **后续可进行计算**
FORMAT(a.XXX, 2) //精确数字两位小数  **后续不可进行计算**

case when 语法

case a.deleted 
when 0 THEN 已完成 
when 1 THEN 已撤单 
end 
as deletedName //别名

if语法

if(a.deleted=1,已删除,未删除) as XXX //如果deleted等于1，则取‘已删除’，否则取‘未删除’
IFNULL(a.orderSumAmount,0) //如果orderSumAmount为null,则值为0

使用union合并两张表结果，注意

两张表结果查询出的结果，字段名字必须完全相同，并一一对应

MySql 获取自增序号

select **(@i:=@i+1)sn**,a.* from biz_spend_order_goods a,**(select @i:=0)t** WHERE  goodsId=5 and type=2  ORDER BY a.id asc

查询数据库是否被锁定，或者造成死锁

select * from information_schema.innodb_trx;

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

show full processlist

kill 142975   //杀死被锁线程

从一张表批量更新(复制)数据到另一张表

//从m表中查询数据并复制更新到r表中
UPDATE biz_spend_order r
INNER JOIN (SELECT id,proviceCode,cityCode,areaCode,provinceName,cityName,areaName FROM biz_member) m ON m.id=r.memberId
SET r.proviceCode=m.proviceCode,r.cityCode=m.cityCode,r.areaCode=m.areaCode,r.provinceName=m.provinceName,r.cityName=m.cityName,r.areaName=m.areaName;

将以逗号分隔值拆分成多个单个值

//a.parentIds 值举例：5/6/9/
SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(a.parentIds,/,b.help_topic_id+1),/,-1) AS num
FROM
wo_category a,mysql.help_topic b
WHERE
b.help_topic_id <![CDATA[ < ]]> LENGTH(a.parentIds)-LENGTH(REPLACE(a.parentIds,/,))+1 and a.id = #{
          
   id}

持续更新…