mysql的一些小技巧（遇到过的坑）

在实际开发过程中，各种问题千奇百怪，这里记录一些遇到的问题，和一些数据库的方法，不定期更新，给自己也给他人方便。

1、mysql获取前一个月的日期和前一年的日期

set @dt = now();
select extract(year_month from date_add(@dt, interval -1 month));
select extract(year_month from date_add(@dt, interval -1 year));

前一个月的日期：

前一年的日期：

数据库中@代表是局部变量声明，如何没有@符会查找对应列名，没有会报错。同样的@符还可以解决下一个问题：

2、在数据库查询的结果集给每一列排序（排名）

SELECT
	s.*,
	( @i := @i + 1 ) num 
FROM
	`student` s,
	( SELECT @i := 0 ) a 
WHERE
	age > 17

排名在数据库中执行没有问题，但是放到XML文件中，在程序中运行就不行了。

报错：net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: “:” “:”

这是因为框架用了 Mybatis-Plus 。
框架中，有用到多租户的功能，Mybatis-plus 会 进行数据权限的过滤。要对该条sql，进行租户放行。

如果版本是3.1.1以上的 直接 添加注解 即可

参考Mybatis-plus官网，在Mapper的SQL上 加入注解 （亲测有效）

 @SqlParser(filter=true) 

3.11以下版本的要在配置文件加入（未测）

mybatis-plus:
  global-config:
    sql-parser-cache: true

3、将字符串字段转为数字排序或比大小

一次在查询后进行排序，发现排序的数字是varchar类型，排序完全错误。

解决方案：最简单的办法就是在字段后面加上+0

方法一：ORDER BY ‘123’+0;（首推）
方法二：ORDER BY CAST(‘123’ AS SIGNED);
方法三：ORDER BY CONVERT(‘123’,SIGNED);

错误版本：

正确方式：
方法一：

方法二：

方法三：

4、如何在mysql的查询结果中增加一个自定义字段进去

这是原本数据库查询字段，假如需要加一个"favorites"字段一并返回，看图二

SELECT id,name,age,address,datatime,num,'favorites' FROM `student`

5、mybatis动态sql中的trim标签的使用

1、trim标记是一个格式化的标记，可以完成set或者是where标记的功能，如下代码：

　select * from user 

　　<trim prefix="WHERE" prefixoverride="AND |OR ">

　　　　<if test="name != null and name.length()>0"> AND name=#{name}</if>

　　　　<if test="gender != null and gender.length()>0"> AND gender=#{gender}</if>

　　</trim>

假如说name和gender的值都不为null的话打印的SQL为：select * from user where name = ‘xx’ and gender = ‘xx’

在 where和name中间是不存在第一个and的，上面两个属性的意思如下：

prefix：前缀

prefixoverride：属性会忽略通过管道分隔的文本序列，去掉第一个and或者是or（注意此例中的空格也是必要的）
　　
2、

update user

　　<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">

　　　　<if test="name != null and name.length()>0"> name=#{name} , </if>

　　　　<if test="gender != null and gender.length()>0"> gender=#{gender} ,  </if>

　　</trim>

假如说name和gender的值都不为null的话打印的SQL为：update user set name=‘xx’ , gender=‘xx’ where id=‘x’

在’xx’ 和where中间不存在逗号，而且自动加了一个set前缀和where后缀，上面三个属性的意义如下，其中prefix意义如上：

suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

suffix：后缀

6、mybatis if-else(写法)

mybaits 中没有else要用chose when otherwise 代替

<choose>
    <when test="">
        //...
    </when>
    <otherwise>
        //...
    </otherwise>
</choose>

其中choose为一个整体
when是if
otherwise是else

范例

<!--批量插入用户-->
<insert id="insertBusinessUserList" parameterType="java.util.List">
    insert into `business_user` (`id` , `user_type` , `user_login` )
    values
    <foreach collection="list" index="index" item="item" separator=",">
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <choose>
                <when test="item.id != null and item.id !=''">
                    #{item.id,jdbcType=CHAR},
                </when>
                <otherwise>
                    '',
                </otherwise>
            </choose>
            <choose>
                <when test="item.userType != null and item.userType !=''">
                    #{item.userType,jdbcType=VARCHAR},
                </when>
                <otherwise>
                    '',
                </otherwise>
            </choose>
        </trim>
    </foreach>
</insert>

7、mybatis 中where标签

where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。

如果 where 元素没有按正常套路出牌，我们还是可以通过自定义 trim 元素来定制我们想要的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ... 
</trim>

<select id="findActiveBlogLike"  resultType="Blog">
  SELECT * FROM BLOG 
  <where> 
    <if test="state != null">
         state = #{state}
    </if> 
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

8、mybatis 中foreach标签

foreach 元素的功能是非常强大的，它允许你指定一个集合，声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串以及在迭代中间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

注意：你可以将任何可迭代对象（如列表、集合等）和任何的字典或者数组对象传递给foreach作为集合参数。当使用可迭代对象或者数组时，index是当前迭代的次数，item的值是本次迭代获取的元素。当使用字典（或者Map.Entry对象的集合）时，index是键，item是值。

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

9、mybatis 中selectKey标签

selectKey返回最近一次插入的id

<insert id="insertAuthor">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  </selectKey>
  insert into Author
    (id, username, password, email,bio, favourite_section)
  values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})
</insert>

在上面的示例中，selectKey 元素将会首先运行，Author 的 id 会被设置，然后插入语句会被调用。这给你了一个和数据库中来处理自动生成的主键类似的行为，避免了使 Java 代码变得复杂。

如果设置order=“AFTER”，那么先执行插入语句，然后是 selectKey 元素

selectKey 元素描述如下：

<selectKey
  keyProperty="id"
  resultType="int"
  order="BEFORE"
  statementType="PREPARED">

属性	描述
keyProperty	selectKey 语句结果应该被设置的目标属性。如果希望得到多个生成的列，也可以是逗号分隔的属性名称列表。
keyColumn	匹配属性的返回结果集中的列名称。如果希望得到多个生成的列，也可以是逗号分隔的属性名称列表。
resultType	结果的类型。MyBatis 通常可以推算出来，但是为了更加确定写上也不会有什么问题。MyBatis 允许任何简单类型用作主键的类型，包括字符串。如果希望作用于多个生成的列，则可以使用一个包含期望属性的 Object 或一个 Map。
order	这可以被设置为 BEFORE 或 AFTER。如果设置为 BEFORE，那么它会首先选择主键，设置 keyProperty 然后执行插入语句。如果设置为 AFTER，那么先执行插入语句，然后是 selectKey 元素 - 这和像 Oracle 的数据库相似，在插入语句内部可能有嵌入索引调用。
statementType	与前面相同，MyBatis 支持 STATEMENT，PREPARED 和 CALLABLE 语句的映射类型，分别代表 PreparedStatement 和 CallableStatement 类型。

10、mybatis 根据id批量删除

<delete id="deleteByLogic"  parameterType = "java.util.List">
     delete from user where 1>2
         or id in
      <foreach collection="list"  item="item" open="(" separator="," close=")"  >
           #{item}
      </foreach>
</delete>

11、mybatis 批量新增和修改

//批量新增
int batchInsertGoods(List<Goods> list);
 
//批量修改
int batchUpdateGoodsByIds(List<Map<String, Object>> list);

<!-- 批量新增-->
<insert id="batchInsertGoods" parameterType="java.util.List">
    INSERT INTO goods(i_id, goods_name)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.id,jdbcType=VARCHAR},#{item.goodsName,jdbcType=VARCHAR})
    </foreach>
</insert>
 
<!-- 批量修改方式一-->
<update id="batchupdateGoodsByIds" parameterType="java.util.List">
      UPDATE goods
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="goods_name = case" suffix="end">
                <foreach collection="list" item="item" index="index" >
                    <if test="item.goodsName != null and item.goodsName != ''">
                        when i_id = #{item.id, jdbcType=VARCHAR} then #{item.goodsName, jdbcType=VARCHAR}
                    </if>
                    <if test="item.goodsName == null or item.goodsName == ''">
                        when i_id = #{item.id, jdbcType=VARCHAR} then goods.goods_name
                    </if>
                </foreach>
            </trim>
            <trim prefix="iorder = case" suffix="end">
                <foreach collection="list" item="item" index="index" >
                    <if test="item.iorder != null and item.iorder != ''">
                        when i_id = #{item.id, jdbcType=VARCHAR} then #{item.iorder, jdbcType=VARCHAR}
                    </if>
                    <if test="item.iorder == null or item.iorder == ''">
                        when i_id = #{item.id, jdbcType=VARCHAR} then goods.iorder
                    </if>
                </foreach>
            </trim>
        </trim>
        WHERE i_id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id, jdbcType=VARCHAR}
        </foreach>
</update>
 
<!--批量修改方式二 -->
<update id="batchUpdateOption" parameterType="java.util.List" >
    update goods set
        c_content =
      <foreach collection="list" item="item" index="index" separator=" " open="case c_id" close="end">
        when #{item.cId, jdbcType=VARCHAR} then #{item.content, jdbcType=VARCHAR}
      </foreach>
       ,dt_editdate = NOW()
    where c_id in
    <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
      #{item.cId, jdbcType=INTEGER}
    </foreach>
  </update>
 
<!-- 根据ids批量获取Goods列表-->
<select id="getGoodsList" parameterType="java.util.List" resultType="java.util.Map" >
    SELECT id, goods_name WHERE id in
       <foreach collection="list" item="item" index="index" open="(" separator="," close=")">
           #{item}
       </foreach>
</select>

-- 批量新增
INSERT INTO goods(id, goods_name)
VALUES
(?, ?),
(?, ?);
 
-- 批量修改，不为空则修改，为空则不修改（goods.goods_name，goods.iorder代表不修改）
UPDATE goods set goods_name = case when id = ? then ? when id = ? then goods.goods_name end, 
iorder = case when id = ? then goods.iorder when id = ? then ? end 
WHERE id in ( ? , ? );

12、mysql中case when then end的含义和用法

case when then else end 语句

一是：用于查询满足多种条件的情况，类似java中的if…else；
二是：就是用于进行行转列的查询，这个是放在select 子句后面的，充当的是字段的作用。

一：具体用法分为两种，一种是简单的函数形式，另一种就是表达式的形式。

简单的函数形式：case 字段 when 值 then 结果 else 其他情况 end；

表达式的形式：case when 字段=值（这里写表达式，例如 score=80） then 结果 else 其他情况 end;

简单的函数形式
select (case score when 'a' then '优秀' else '良好' end) as score from student;

表达式形式
select (case when score between 85 and 90  then '优秀' else '良好' end) as score from student;

(case when then end)就是select后面需要查询出来的一个字段,在使用时可以用小括号括起来可读性更高，对其可以使用聚合函数，别名，去重，等操作。

二：行列转换

insert into `t_score`(name,course,score) values
    ('王海', '语文', 86),
    ('王海', '数学', 83),
    ('王海', '英语', 93),
    ('陶俊', '语文', 88),
    ('陶俊', '数学', 84),
    ('陶俊', '英语', 94),
    ('刘可', '语文', 80),
    ('刘可', '数学', 86),
    ('刘可', '英语', 88),
    ('李春', '语文', 89),
    ('李春', '数学', 80),
    ('李春', '英语', 87);

用case when then end 函数进行查询

SELECT name,
max(CASE course WHEN '语文' THEN score ELSE 0 END ) Chinese,
max(CASE course WHEN '数学' THEN score ELSE 0 END ) math,
max(CASE course WHEN '英语' THEN score ELSE 0 END ) English,
sum(score) score
FROM t_score 
GROUP BY name
union
select 'TOTAL',
sum(CASE course WHEN '语文' THEN score ELSE 0 END ),
sum(CASE course WHEN '数学' THEN score ELSE 0 END ),
sum(CASE course WHEN '英语' THEN score ELSE 0 END ),
sum(score) from t_score;

效果图：

另一种方法获取所需结果

利用with rollup 给结果集新添一行，再使用ifnull过程函数判断name的值是否为空

select ifnull(name,'TOTAL') name,
sum(CASE course WHEN '语文' THEN score ELSE 0 END ) Chinese,
sum(CASE course WHEN '数学' THEN score ELSE 0 END ) math,
sum(CASE course WHEN '英语' THEN score ELSE 0 END ) English,
sum(score) score
FROM t_score 
GROUP BY name with rollup;

效果图一样
最后用到了with rollup

13、Mysql中的WITH ROLLUP用法

WITH ROLLUP：在group分组字段的基础上再进行统计数据。

14、mybatis中if标签的坑

今天工作中遇到的，记录下

mybatis 做 if 判断

注意：下面这种写法只适用于 id 类型为字符串.

<if test="id != null and id != '' ">
　　    id = #{id}
</if>

如果id类型为int 当id=0时 这个判断不会进入
可以这样写<if test="id != null and id != '' or id==0">或者<if test="id != null>

15、根据表名获取字段注释

-- 获取表内所有字段注释
select column_name, column_comment from INFORMATION_SCHEMA.Columns where table_name='student';
-- 获取表内指定字段注释
select column_name, column_comment from INFORMATION_SCHEMA.Columns where table_name='student' and column_name = 'address';

16、SQL中的替换函数replace()使用

将id=6的name字段值改为wokou
replace into test_tb VALUES(6,‘wokou’,‘新九州岛’,‘日本’)

总结：向表中“替换插入”一条数据，如果原表中没有id=6这条数据就作为新数据插入(相当于insert into作用)；如果原表中有id=6这条数据就做替换(相当于update作用)。对于没有指定的字段以默认值插入。

引用原文
https://www.cnblogs.com/martinzhang/p/3301224.html

17、mysql如何按照自己想要的顺序返回

在实际中如果没有order字段进行排序或者order的顺序不是自己想要的，如何按照自己想要的逻辑排序
order by field(XXX,2,5,4,13,6)