mysql解决问题及小技巧记录

最新推荐文章于 2022-07-19 15:56:29 发布

God_Ming

最新推荐文章于 2022-07-19 15:56:29 发布

阅读量602

点赞数

分类专栏：数据库文章标签： mysql timestamp

本文链接：https://blog.csdn.net/jinzhencs/article/details/60140203

版权

数据库专栏收录该内容

16 篇文章 0 订阅

订阅专栏

1.得到两个日期相差多少秒

created是个datetime.
本例场景是得到该条记录创建了多少秒,从而与过期时间比对判断是否过期.筛选出已经过期的记录拿出来操作并进行更新.

SELECT unix_timestamp()- unix_timestamp(created) as expiredTime from t_test where id = 1

上千万条记录性能存不存在问题目前没有结论。如果不行后续想有没有更好的解决方法

2.分批次取数据<转>

很多场景都是需要我们分批取数据,避免block住库表.
一般做法是

select * from datatable limit offset,amount

这里就会有一个问题，随着offset值的越来越大，这条sql要扫描的表数据就会越来越多，因为要定位到offset这一行就需要扫描比offset小的所有行。显然在一张大数据量的表中，去这样做，性能就会出问题。为了避免这样的情形出现，我们自然会想到使用索引来解决，比如使用自增序列id进行分批取：

select * from datatable where id >= start and id<start+batchCount

这样我们对id建索引，然后分批去取，显然效果会高很多，但是如果自增序列由于删除等操作变得不是连续，就会出现空执行和多执行的情况出现。要解决这个方法，我们就需要结合使用索引和分页的优势来处理：

select * from datatable where id >= start limit batchCount

然后每次取回来我们再计算出起始id值，再去取下一批数据。这样就可以既避免了第一种不走索引，查询性能低下的问题，又解决了第二种id不连续，导致取回来的数据量不稳定导致浪费的问题了。

例子:
这个查询可能会返回10W条数据.需要分批次查询

<!-- 筛选出在未来一天内即将过期的记录 -->
SELECT `id`, `mobile`
FROM t_user
WHERE id >= #{startIndex}  and
`status` = 1 AND
(
    `expire_sec` - 
    (
        unix_timestamp() - unix_timestamp(`created`)
    )
)   < 86400
limit 1000

3.替换某字段的部分字符

很多情况下我们升级版本会需要更新数据，大多情况下都是同样的改变，
如果是固定的字符串更换，则使用mysql的replace即可

1.简单示例

UPDATE `user` SET `email` = replace(email,'@qq.com','@163.com') WHERE `email` like '@qq.com';

2.替换某字段的部分字符，但是依赖于其他表
例如:update xxx set x = (select * from x )

不能用update select，只能用join

UPDATE `policy` p inner join `list_resource_monitor_settings` l
on p.resource_uuid = l.resource_uuid
SET p.`definition` = replace(definition,'sun.management.', CONCAT(l.resource_short_name,'/')) 
WHERE p.`definition` like '%sun.management.MemoryImpl.%';