将执行结果导出到服务器的制定目录下

select * from `QRTZ_CRON_TRIGGERS` INTO OUTFILE '/var/lib/mysql-files/select-values.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'   LINES TERMINATED BY '\n';

如果遇到错误

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

执行SHOW VARIABLES LIKE "secure_file_priv";查看安全目录路径

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |

secure_file_priv值的意思：

NULL，表示禁止。
如果value值有文件夹目录，则表示只允许该目录下文件(子目录不行,仅当前目录)
如果为空，则表示不限制目录。

在my.cnf文件中修改. 不设置表示null，如果等于空表示无限制

直接在客户端执行并导出本机

mysql -u root -h 主机ip -p -D 数据库名 -e "select * from QRTZ_CRON_TRIGGERS" > /tmp/result.txt

输入密码即可

Reference

mysql官方文档

select @cdate:=DATE_ADD(@cdate,INTERVAL -1 day) as DAY_TIME
from(select @cdate:=DATE_ADD(STR_TO_DATE('2021-10-31','%Y-%m-%d'),INTERVAL 1 day)
from b2b_order limit 31) aa 
where @cdate>'2021-10-01' and @cdate<='2021-11-01'

-- mysql5.8 with

with customer  as( select date('2021-05-21') as birthday from DUAL)
SELECT date(concat(DATE_FORMAT(birthday,'%Y-%m'),'-01')),
date_add(date(CONCAT(DATE_FORMAT(DATE_ADD(birthday, INTERVAL 1 MONTH),'%Y-%m'),'-01')), interval -1 day)
from customer

最后修改于 2021-10-09