工作中 Mybatis和mysql灵活运用的提升(持续更新)

看大佬们的灵活运用真羡慕....菜鸡只能打好笔记好好学习

1.记一个根据一定的条件按照两种排序规则进行排序的sql 

 select p.training_id,
        p.main_title,
        p.training_type,
        p.lecturer,
        from p_training_info p
        where p.status = '1' 
        order by p.training_type, 
case when p.training_type = 1 then p.train_start_dttm
 else modify_dttm end

2.活动replace和concat


  • replace(a,str1,str2)指定字段a中的str1被替换为str2
    • 
CONCAT(str1,str2,…) 将括号中的字符串拼接起来

  • CONCAT可用于模糊查询,如 a.name like concat("%",#{param},"%")
    • 

    UPDATE b_rep a SET a.url = 
REPLACE(a.chart_url, 'https', 'http') WHERE a.chart_url LIKE 'https%';
UPDATE b_rep a SET a.url = 
CONCAT('http:', a.chart_url) WHERE a.public_url LIKE '//%';

    3.当integer类型的数字为0时候,我们动态sql中的if test判断不出来,他会认为该为0数字为空
    

    4.mybatis中关于大于小于的识别问题
    

    原符号    <       <=      >       >=       &        '        "
替换符号 &lt;   &lt;=    &gt;    &gt;=   &amp;    &apos;    &quot;

    union --m一下
    

    https://www.w3school.com.cn/sql/sql_union.asp
    

    6.关于插入一个表(p_email_user_external)中的数据到另外一个表(p_email_user)
    

    在做这个操作时候有两个问题
    

  • 首先主表中email 不能为空,但是从表中中是空的,这里
    
ALTER TABLE p_email_user MODIFY email VARCHAR (128) DEFAULT NULL COMMENT '邮箱';将其置为可空,如果第
    • 

  • 将从表中的signature_str作为第一张表user_id列传入
    • 

  • 第从表中无datasource列,这里将'2'作为默认值传入
    • 

    ###插入外部邮箱数据到内部
INSERT INTO p_email_user (
    email,
    user_id,
    datasource
SELECT
    email,
    signature_str AS user_id,
    '2' as datasource
    p_email_user_external;

    7 根据返回的字段的值进行判断返回
    

    select isnull(a,0) as a ,b,c from test
    
只有当a的值为null的时候,替代为0.
    

    如果是空白也需要这样的话,isnull是没有效果的.
    
select case when len(a)=0 then 0 else isnull(a,0) end as a ,b,c from test
    
--这样,可以判断空白和null
    

    8 mybatis中 test if中判断字符串形式数字是否相等
    

    不能使用：
    

    <if test="sex=='Y'">
　　and 1=1

    可以使用的方式有两种方式：
    

    <if test = 'sex== "Y"'>
<if test="sex=='Y'.toString()">

    9 查询varchar类型数字的最大值
    

    如下sql需要使用cast(col as type)将varchar类型用户id字段转为int类型
    

    SELECT
    max(cast(userid AS SIGNED))

    type的类型有
    
value

    10   在delete 和 update 后面加 limit 1 绝对是个好习惯
    

    在业务场景要求高的数据库中，对于单条删除和更新操作，在 delete 和 update 后面加 limit 1 绝对是个好习惯。比如，在删除执行中，第一条就命中了删除行，如果 SQL 中有 limit 1；这时就 return 了，否则还会执行完全表扫描才 return。效率不言而喻
    

    如果是清空表数据建议直接用 truncate，效率上 truncate 远高于 delete，因为 truncate 不走事务，不会锁表，也不会生产大量日志写入日志文件；truncate table table_name 后立刻释放磁盘空间，并重置 auto_increment 的值。delete 删除不释放磁盘空间，但后续 insert 会覆盖在之前删除的数据上。
    

    对于以下例子
    

    delete from t where sex = 1;
    

  • 降低写错 SQL 的代价，就算删错了，比如 limit 500, 那也就丢了 500 条数据，并不致命，通过 binlog 也可以很快恢复数据。
    • 

  • 避免了长事务，delete 执行时 MySQL 会将所有涉及的行加写锁和 Gap 锁（间隙锁），所有 DML 语句执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。
    • 

  • delete 数据量大时，不加 limit 容易把 cpu 打满，导致越删越慢。
    
针对上述第二点，前提是 sex 上加了索引，大家都知道，加锁都是基于索引的，如果 sex 字段没索引，就会扫描到主键索引上，那么就算 sex = 1 的只有一条记录，也会锁表。
    
当然，如果 sex 是唯一索引的话，是不必要加上 limit 1 了。因为 limit 的存在主要就是为了防止全表扫描，从而提高性能。如果一个语句本身可以预知不用全表扫描，有没有 limit  性能的差别并不大

    再看一个例子
    

    如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：
    

  • 第一种，直接执行 delete from T limit 10000;
    • 

  • 第二种，在一个连接中循环执行 20 次 delete from T limit 500;
    • 

  • 第三种，在 20 个连接中同时执行 delete from T limit 500。
    • 
怎么删除表的前 10000 行。比较多的朋友都选择了第二种方式，即：在一个连接中循环执行 20 次 delete from T limit 500。确实是这样的，第二种方式是相对较好的。
    

  • 第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。
    • 

  • 第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。
    • 

  • 这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。所以，在 delete 后加 limit 是个值得养成的好习惯。
    • 

    推荐第二种:第二种避免了长事务，delete 执行时 MySQL 会将所有涉及的行加写锁和 Gap 锁（间隙锁），所有 DML 语句执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。而本种方法分成多次占用锁，串行执行，不占有锁的间隙其他客户端可以工作，类似于现在多任务操作系统的时间分片调度，大家分片使用资源，不直接影响使用。
    

    第三种效率虽高，但容易锁住同一条记录，发生死锁的可能性比较高
    

    11. 应尽量避免在 where 子句中使用 or 来连接条件
    

    select * from user where userid=1 or age=18;

    -- 使用 union all 
select * from user where userid=1
union all
select * from user where age=18;
-- 或者分开两条 SQL写
select * from user where userid=1;
select * from user where age=18;