对sql语句也要有敬畏之心

前言

在上个月，在帮同事解决bug的时候，我人生中第一次看到了对datetime类型的字段做like查询的sql语句…

案发现场分析

这是一条很简单的语句，查询某个月某个用户的签到记录

  SELECT 
        *
    FROM
       xxxx
    where 
        uid = #{uid}
        AND 
        sign_date like CONCAT('%',#{day},'%')

不得不说，当我看到这段sql的时候，我蒙蔽了，在我的认知里，我们一直以为只有文本才可以使用like的， 没想到datetime类型也可以使用？

我们来分析一下这一小段糟点满满的sql：

0.签到日期使用datetime类型，明显应该使用date。

• 业务要求只需要精确到天。
• date长度为3，datetime长度为8
• 当查询某一天的签到情况时，使用date可以使用等于匹配索引，而使用datetime只能使用范围匹配,无故增加处理难度以及效率。

1.使用了like模糊查询，首先明确一点，like确实是只有对文本类型的数据才可以使用的。那这里为什么datetime类型也可以使用呢,答案就是Mysql会任劳任怨的把每一条库里的记录由datetime类型转化为对应的字符串类型，然后再和模糊查询的条件去一一匹配，嗯，你必须承认这条Sql完成了需求。但是这个效率直接爆炸，Mysql要将每行datetime类型数据转化为字符串类型数据，然后再去匹配，效率非常低，索引直接失效。对于datetime类型，使用between或者>=，<=月初月尾日期来实现，都会走索引的。

2.在使用like模糊查询时，不管三七二十一直接就使用前后缀匹配，这也是不允许的。在使用like模糊查询中，如果是能使用前缀匹配的时候尽量使用前缀匹配，这样当该列有索引的时候，可以走索引。

总结

这是一条很简单的语句，但是不得不说写的非常的糟糕，完全没有考虑过性能，只是为了完成需求而已。

我们编写sql语句一定要考虑性能，要充分利用索引，尽可能的编写好的代码。

另外，我还发现，对时间类型的字段使用like，只有在读的时候才会生效，Mysql会默默把时间类型的字段转为字符串类型，然后进行模糊匹配。但是在写的时候呢，Mysql拒绝帮我们做转换，抛出异常，大家有兴趣的时候可以试一下。我Google了很久，试图找到只支持这样读而不支持这样写的原因，但是并没有找到具体的解释，只能说Mysql目前并没有提供这样的支持，但是也没太大所谓了，没人会这样用的，千万不要这样用。

  update 
        xxxx
    FROM
       xxxx
    where 
        uid = #{uid}
        AND 
        sign_date like CONCAT('%',#{day},'%')

抛出异常

Error: Incorrect datetime value