工作中 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 end2.活动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中关于大于小于的识别问题
原符号 < <= > >= & ' "
替换符号 < <= > >= & ' "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
FROM
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>可以使用的方式有两种方式：
<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))
FROM
usertype的类型有
value | 描述 |
|---|---|
DATE | 日期，格式为 'YYYY-MM-DD'. |
DATETIME | 日期加具体的时间，格式为 'YYYY-MM-DD HH:MM:SS'. |
TIME | 时间，格式为 'HH:MM:SS'. |
CHAR | 字符型 |
SIGNED | int |
UNSIGNED | 无符号int |
BINARY | 二进制型 |
DECIMAL | float型 |
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;理由：使用or可能会使索引失效，从而全表扫描。
说明：
对于 or+没有索引的 age 这种情况。假设它走了 userId 的索引，但是走到 age 查询条件时，它还得全表扫描。也就是需要三步过程：全表扫描+索引扫描+合并。如果一开始就走全表扫描，直接一遍扫描就完事。
MySQL 是有优化器的，处于效率与成本考虑，遇到 or 条件，索引可能失效。看起来也合情合理。