1. 使用 Mysql Workbench 导入数据,提示 ERROR 1148 (42000): The used command is not allowed with this MySQL version 的解决办法。

    在数据库连接配置的 advanced - others 框中, 添加 OPT_LOCAL_INFILE=1

  2. test 表的 id 字段的后六位,并将该字符串倒数一二和倒数三四换位置。

    1
    select concat(substring(id, -6,2),substring(id,-2,2),substring(, -4,2)) from test;
  3. 定时任务

    查看定时任务是否开启

    1
    SHOW VARIABLES LIKE '%event_scheduler%';

    开启定时任务

    1
    SET GLOBAL event_scheduler=1;

    查看已有定时任务

    1
    select * from  mysql.event;

    创建定时任务

    • 每天定时清除 80note 表数据
    1
    2
    3
    4
    5
    6
    DROP event IF EXISTS 80note_log;
    CREATE EVENT 80note_log
    ON SCHEDULE
    EVERY 1 DAY STARTS '2020-01-01 03:00:00'
    DO
    DELETE FROM 80note;
    • 同时处理多个 sql 语句。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    DELIMITER // 
    DROP event IF EXISTS 80note_err;
    CREATE EVENT 80note_err
    ON SCHEDULE
    EVERY 1 DAY STARTS '2020-01-01 03:00:00'
    DO
    BEGIN
    DELETE FROM 80note_err_log;
    update from 80note_user info ='123456';
    END //
    DELIMITER ;