定时删除某个表的数据

shell实现

crontab -e 添加

* * * * * bash /path/clean.sh

clean.sh

#!/bin/bash

echo "开始执行clean"

# 获取3天前的时间戳（13位）
timestamp=$(date -d "-3 days" +%s%3N)
echo $timestamp

# 执行docker命令删除数据
docker exec mysql mysql -uroot -p'password' -e "delete from table1 where CREATE_TIME < $timestamp"
echo "结束执行clean"

Python实现

"""
定时删除表 table1
"""
import pymysql
import datetime
import logging
import os

ip = "192.168.1.1"
retain_days = 3
log_path = '/clean.log'
# 日志保留大小 kb
log_size_reserve = 1

# 日志设置
logging.basicConfig(filename=log_path,
                    level=logging.INFO, format='%(asctime)s %(levelname)s: %(message)s', datefmt='%Y-%m-%d %H:%M:%S')


# 限制日志大小
def delete_file_head(file_path, file_size_limit):
    """
    如果日志文件超过了指定大小，则删除文件前面部分，直到文件大小不超过指定大小。
    :param file_path: 日志文件路径。
    :param file_size_limit: 日志文件大小限制，单位为 Kb。
    """
    # 获取日志文件的大小，单位为字节（Byte）
    file_size = os.path.getsize(file_path)

    logging.info('日志文件大小为 ' + str(file_size))

    # 如果文件大小不超过限制，直接返回
    if file_size <= file_size_limit * 1024:
        logging.info('文件大小不超过限制，直接返回')
        return

    # 计算需要删除的字节数
    byte_to_delete = file_size - file_size_limit * 1024

    new_content = ''
    # 读取文件内容，并删除前面的部分
    with open(file_path, 'r') as file:
        file_content = file.read()
        new_content = file_content[byte_to_delete:]
    with open(file_path, 'w') as file:
        file.write(new_content)

    logging.info('已将日志文件 "{}" 的大小限制为 {} Kb。'.format(file_path, file_size_limit))


# 执行删除操作
def del():
    now = get_time_stamp()
    # Connect to the database
    connection = pymysql.connect(host=ip,
                                 user='root',
                                 password='password',
                                 database='db1',
                                 cursorclass=pymysql.cursors.DictCursor)
    logging.info('get connection!')
    with connection:
        sql = 'delete from table1 where CREATE_TIME < ' + str(now)
        logging.info('本次执行的sql是 ' + sql)

        with connection.cursor() as cursor:
            cursor.execute(sql)
            affected_rows = cursor.rowcount
            logging.info('受影响的行数是 ' + str(affected_rows))

# 获取n天之前的timestamp
def get_time_stamp():
    tod = datetime.datetime.now()
    d = datetime.timedelta(days=retain_days)
    a = tod - d
    return int(a.timestamp()*1000)



if __name__ == '__main__':
    logging.info('')
    logging.info('start job')
    delete_file_head(log_path, log_size_reserve)
    del()
    logging.info('finish job')