在MYSQL和MyBatis中踩过的坑和小技巧

原创
2016/11/23 17:21
阅读数 8.9K

1. sum()和count()使用场景不对导致出错:

     count(*)、count(1)、count(0)就是指绝对的行数,哪怕某行所有字段全部为null也会计算在内。count(1)和count(*)相比,innodb来说count(*)效率低。

     如果count(列名)查询出来的结果就是查出列名中不为null的行数;

     sum(列名)对指定列名进行求和

2. 比较好用的一些函数

    2.1 CONCAT_WS和CONCAT比较举例:

函数 NULL 空字符串'' 小结
CONCAT SELECT CONCAT('First name',NULL,'Last Name') AS TEST; SELECT CONCAT('First name','','Last Name') AS TEST2; 默认拼接的时候没有分隔符,直接拼接起来
CONCAT_WS SELECT CONCAT_WS(',','First name',NULL,'Last Name') AS TEST; SELECT CONCAT_WS(',','First name','','Last Name') AS TEST2; 拼接的内容之间使用第一个参数提供的分隔符分隔。如果想要直接拼接,但是需要忽略Null的话,可以使用空字符串做分隔符。效果就和CONCAT()一样了

    2.2 GREATEST()和LEAST()

3. MyBatis把int类型的0处理成空串’’和mysql处理空串’’为0的问题,在Mybatis的Mapper中整数类型条件该如何判断?

    当数据库字段类型是整数,如果参数变量为空字符串或者NULL,Mybatis会自动将参数赋值0,所以如果要判断整数参数的多种状态在传递数值到Mapper之前就要判断是否为空字符串和NULL并将相应的状态数值赋值给该参数,否则参数值等于空字符串、NULL和0得到的结果是一样的。

    一般情况下,涉及到int类型的操作的时候,在Service中会统一把数字类型先变成字符串类型,然后再传递到Mapper中操作。

4. 时间戳的使用

在创建新记录的时候把这个字段设置为当前时间,但以后修改时,不再刷新它(可以给createtime使用这个):TIMESTAMP DEFAULT CURRENT_TIMESTAMP

在创建新记录和修改现有记录的时候都对这个数据列刷新(可以给update使用这个):

 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

5. Mysql中字符的定义

    1. char和varchar限制的是字符,而不是字节。对不同的字符集来说,字符和字节的关系是不一定的。

    2. 并且也不能按照换算关系换算比如一个汉字在utf8中用三个字节表示,那么换算成英语应该是对应三个英文字母。这种换算是错误的。如果声明了varchar(2)那么放中文汉字是两个汉字,放英文字母同样是两个

6. 在使用resultMap的时候,要把ID写在第一行,否则的话,就会报错。

<resultMap id="getPhysicalItemMsgList" type="map" autoMapping="false">

    <id property="TEST_ID" column="TEST_ID" /> <!-- 这里不能和下面的一行调换位置-->

    <result property="TEST_1" column="TEST_1" />

    <collection property="TEST_LIST" javaType="ArrayList" ofType="map">

        <result property="TEST_LIST1" column="TEST_LIST1"/>

    </collection>

</resultMap>

原因可以通过查看resultMap的定义发现resultMap规定了排序,需要按照定义来排序才能解析通过,然后执行

<!ELEMENT resultMap (constructor?,id*,result*,association*,collection*, discriminator?)>
<!ATTLIST resultMap
id CDATA #REQUIRED
type CDATA #REQUIRED
extends CDATA #IMPLIED
autoMapping (true|false) #IMPLIED
>

7. 一般情况在Mapper中判断参数是否为空的时候是字符串居多,今天遇上需要判断list类型的的参数是否为空,依然习惯性的使用了下面的写法:

<if test="TEST_LIST != '' and TEST_LIST != null">
    CT.TEST IN
    <foreach collection="TEST_LIST" index="index" item="item" separator="," open="(" close=")">
       #{item.test}
    </foreach>    
</if>

结果就报错了下面的错误:

Caused by: org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.lang.IllegalArgumentException: invalid comparison: java.util.ArrayList and java.lang.String
### Cause: java.lang.IllegalArgumentException: invalid comparison: java.util.ArrayList and java.lang.String

最后,改成以下写法:

<if test="TEST_LIST != '' and TEST_LIST.size()>0">
    CT.TEST IN
    <foreach collection="TEST_LIST" index="index" item="item" separator="," open="(" close=")">
       #{item.test}
    </foreach>    
</if>

搞定。

8. 关于时间列表排序或者比较大小的问题

      要想对一系列的时间进行排序,在排序之前要统一用date_format()把时间的格式转一下。否则的话,排序出来的顺序可能会出现错误。

9. 在MySQL中使用union的时候,如果紧挨着union的内层进行排序的话,是没有作用的

10. 更新一张表,限制条件也是从要更新的表里面抽取的。这是如果按照常规的sql来写。就会出现语法错误,执行不下去,需要变成下面的这种写法

-- 更新同一张表的写法
UPDATE BLOOD_PRESSURE_RECORD A1,
    -- 从表中查出条件
    (SELECT 
        MIN(BLOOD_PRESSURE_RECORD_KEY_ID) AS BLOOD_PRESSURE_RECORD_KEY_ID
    FROM
        BLOOD_PRESSURE_RECORD
    WHERE
        HYPERTENSION_RECORD_KEY_ID = 14) A2 
SET 
    A1.SYSTOLIC_PRESSURE = 222,
    A1.DIASTOLIC_PRESSURE = 321,
    A1.MEASURE_DATE = '2016-10-12',
    A1.MEASURE_DOCTOR = '123',
    A1.EDITOR_ID = 34,
    A1.EDITOR_NAME = '医生一号',
    A1.UPDATE_TIME = NOW()
WHERE
    A1.IS_ENABLE = 1
        AND A1.BLOOD_PRESSURE_RECORD_KEY_ID = A2.BLOOD_PRESSURE_RECORD_KEY_ID


    

 

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部