如何用MySQL或Windows批处理生成含指定YYYY、QQ标识的导出文件名？

Got it, let's tackle this problem step by step. You want to replace the YYYY and QQ placeholders in your output filename with specific values (like 2017 and Q4) instead of relying on the current date. Here are a couple of flexible solutions you can pick from:

方案1：直接用Windows批处理变量（最简单快捷） #

This approach lets you hardcode or easily adjust your target year and quarter right in a batch file, no database changes needed.

Create a .bat file with the following content:

@echo off
:: Define your target year and quarter here
set TARGET_YEAR=2017
set TARGET_QUARTER=Q4

:: Run the MySQL command with dynamically generated filename
mysql -h DATABASE -u yyyy -pxxxx < E:/Step_2.sql > "E:/OUTPUT_%TARGET_YEAR%_%TARGET_QUARTER%.csv"

• Just update the TARGET_YEAR and TARGET_QUARTER values whenever you need to switch to a different period.
• If your output path has spaces, wrap the filename in double quotes (like shown) to avoid parsing errors.

方案2：Store parameters in a MySQL table (persistent, scalable) #

If you want to manage these target values centrally in your database (great for teams or frequent value changes), you can create a small config table and pull values from it in your batch script.

Step 1: Create the config table in your MySQL database #

Run this SQL once to set up the table and insert your initial values:

CREATE TABLE IF NOT EXISTS export_config (
    param_key VARCHAR(50) PRIMARY KEY,
    param_value VARCHAR(50) NOT NULL
);

-- Insert your target year and quarter
REPLACE INTO export_config (param_key, param_value)
VALUES ('target_year', '2017'), ('target_quarter', 'Q4');

Later, you can update these values with a simple UPDATE statement whenever you need to switch periods.

Step 2: Batch script to pull values and run export #

Create a .bat file that fetches the config values first, then uses them to generate the output filename:

@echo off
:: Fetch target year from MySQL and assign to variable
for /f "tokens=*" %%a in ('mysql -h DATABASE -u yyyy -pxxxx -s -e "SELECT param_value FROM export_config WHERE param_key='target_year';"') do set TARGET_YEAR=%%a

:: Fetch target quarter from MySQL and assign to variable
for /f "tokens=*" %%a in ('mysql -h DATABASE -u yyyy -pxxxx -s -e "SELECT param_value FROM export_config WHERE param_key='target_quarter';"') do set TARGET_QUARTER=%%a

:: Execute the export with the dynamic filename
mysql -h DATABASE -u yyyy -pxxxx < E:/Step_2.sql > "E:/OUTPUT_%TARGET_YEAR%_%TARGET_QUARTER%.csv"

• The -s flag in the MySQL command removes extra formatting (like headers and borders) so the batch script can parse the value cleanly.
• This way, you never have to edit the batch file—just update the export_config table in MySQL when you need new values.

Quick note on password security #

If your MySQL password contains special characters (like !, $, or &), replace -pxxxx with --password=xxxx to avoid unexpected parsing issues in the batch script.

内容的提问来源于stack exchange，提问作者Jeff