运行PowerShell脚本从MySQL导出数据到CSV时遇错误求助

问题场景 #

编写了一段PowerShell脚本，用于从MySQL多表查询考勤数据并按日期筛选。去掉WHERE条件里的日期筛选时，能正常生成带数据的CSV；但加上日期筛选后就报错，尝试格式化日期也无法解决。

原脚本代码 #

###PROMPT USER FOR DETAILS
Param(
[Parameter(
Mandatory = $true,
ParameterSetName = '',
ValueFromPipeline = $true)]
[string]$Query
)

$MySQLAdminUserName = "你的MySQL用户名"
$MySQLAdminPassword = "你的MySQL密码"
$MySQLDatabase = "目标数据库名"
$MySQLHost = 'localhost'

$ConnectionString = "Server=$MySQLHost;Uid=$MySQLAdminUserName;Pwd=$MySQLAdminPassword;database=$MySQLDatabase;Allow User Variables=True;"
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") 
$mysql = New-Object MySql.Data.MySqlClient.MySqlConnection($ConnectionString)
$mysql.Open()

$sqlquery1 = "SELECT
DISTINCT upper(personnel_employee.emp_code) as Employee_ID,
upper(personnel_employee.first_name)as First_Name,
upper(personnel_employee.last_name) as Last_Name,
date_format(att_payloadbase.att_date,'%Y/%m/%d') as Att_Date ,
dayname(att_payloadbase.att_date) as Week_Day,
if(weekday(att_payloadbase.att_date)IN(5,6) ,'Weekend','') as Exception,
IF(att_holiday.alias = '', IFNULL(att_holiday.alias,0), IFNULL(att_holiday.alias,0))as Holiday,
att_timeinterval.alias as TimeTable,
round((att_payloadbase.duration)/60,2) as Duration,
date_format(att_payloadbase.check_in,'%Y/%m/%d %T') as Check_In,
date_format(att_payloadbase.check_out,'%Y/%m/%d %T')as Check_Out,
round((att_payloadbase.duty_duration)/60,2) as Duty_Duration,
att_payloadbase.work_day,
date_format(att_payloadbase.clock_in,'%Y/%m/%d %T') as Clock_In,
date_format(att_payloadbase.clock_out,'%Y/%m/%d %T') as Clock_Out,
round((att_payloadbase.total_time)/60,2) as Total_Time,
round((att_payloadbase.actual_worked)/60,2) as Actual_WT,
IF(att_holiday.alias!='' ,480, 0+ round((att_payloadbase.total_worked)/60,2)) as Total_WT,
round((att_payloadovertime.normal_ot)/60,2) as Normal_OT,
round((att_payloadovertime.weekend_ot)/60,2) as Weekend_OT,
round((att_payloadovertime.holiday_ot)/60,2) as Holiday_OT,
round((att_payloadovertime.total_ot)/60,2) as Total_OT
FROM att_payloadbase inner JOIN personnel_employee ON att_payloadbase.emp_id = personnel_employee.id
left JOIN att_timeinterval ON att_payloadbase.timetable_id = att_timeinterval.id
left JOIN att_holiday ON att_payloadbase.att_date = att_holiday.start_date
left JOIN att_payloadovertime ON att_payloadbase.overtime_id = att_payloadovertime.uuid
WHERE date_format(att_payloadbase.att_date,'%Y%m%d') ='$Query' AND personnel_employee.status <100 LIMIT 10"

$req = New-Object Mysql.Data.MysqlClient.MySqlCommand($sqlquery1,$mysql)
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($req)
$dataSet = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet, "Query1") | Out-Null

# Get the date
$DateStamp = (Get-Date -Format "yyyyMMddHHmmss");

#File Location
$filename = "C:/DataExchange/Test/Time_Card_$DateStamp.csv"

$dataSet.Tables["Query1"] | Export-Csv -path $filename  -NoTypeInformation;

报错信息 #

调用带有2个参数的"Fill"时出现异常："命令执行期间遇到致命错误。"
At C:\Scripts\Attendance_Report_By_Date.ps1:57 char:1
+ $dataAdapter.Fill($dataSet, "Query1") | Out-Null
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : MySqlException

Export-Csv : 无法将参数绑定到参数'InputObject'，因为该参数为null。
At C:\Scripts\Attendance_Report_By_Date.ps1:64 char:29
+ ... et.Tables["Query1"] | Export-Csv -path $filename  -NoTypeInformation;
+                           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCommand

解决方法 #

1. 改用参数化查询（核心方案） #

直接将用户输入的$Query拼进SQL语句会导致格式转义问题，还存在SQL注入风险。改用MySQL参数化查询能彻底解决：

修改脚本中的SQL语句和参数部分：

# 修改SQL语句，把'$Query'替换为参数占位符@DateParam
$sqlquery1 = "SELECT
DISTINCT upper(personnel_employee.emp_code) as Employee_ID,
upper(personnel_employee.first_name)as First_Name,
upper(personnel_employee.last_name) as Last_Name,
date_format(att_payloadbase.att_date,'%Y/%m/%d') as Att_Date ,
dayname(att_payloadbase.att_date) as Week_Day,
if(weekday(att_payloadbase.att_date)IN(5,6) ,'Weekend','') as Exception,
IF(att_holiday.alias = '', IFNULL(att_holiday.alias,0), IFNULL(att_holiday.alias,0))as Holiday,
att_timeinterval.alias as TimeTable,
round((att_payloadbase.duration)/60,2) as Duration,
date_format(att_payloadbase.check_in,'%Y/%m/%d %T') as Check_In,
date_format(att_payloadbase.check_out,'%Y/%m/%d %T')as Check_Out,
round((att_payloadbase.duty_duration)/60,2) as Duty_Duration,
att_payloadbase.work_day,
date_format(att_payloadbase.clock_in,'%Y/%m/%d %T') as Clock_In,
date_format(att_payloadbase.clock_out,'%Y/%m/%d %T') as Clock_Out,
round((att_payloadbase.total_time)/60,2) as Total_Time,
round((att_payloadbase.actual_worked)/60,2) as Actual_WT,
IF(att_holiday.alias!='' ,480, 0+ round((att_payloadbase.total_worked)/60,2)) as Total_WT,
round((att_payloadovertime.normal_ot)/60,2) as Normal_OT,
round((att_payloadovertime.weekend_ot)/60,2) as Weekend_OT,
round((att_payloadovertime.holiday_ot)/60,2) as Holiday_OT,
round((att_payloadovertime.total_ot)/60,2) as Total_OT
FROM att_payloadbase inner JOIN personnel_employee ON att_payloadbase.emp_id = personnel_employee.id
left JOIN att_timeinterval ON att_payloadbase.timetable_id = att_timeinterval.id
left JOIN att_holiday ON att_payloadbase.att_date = att_holiday.start_date
left JOIN att_payloadovertime ON att_payloadbase.overtime_id = att_payloadovertime.uuid
WHERE date_format(att_payloadbase.att_date,'%Y%m%d') = @DateParam AND personnel_employee.status <100 LIMIT 10"

$req = New-Object Mysql.Data.MysqlClient.MySqlCommand($sqlquery1,$mysql)
# 添加参数并赋值
$req.Parameters.Add("@DateParam", [MySql.Data.MySqlClient.MySqlDbType]::String).Value = $Query

2. 验证输入日期格式 #

确保用户输入的$Query参数严格符合%Y%m%d格式（比如20240520），和SQL中date_format(att_payloadbase.att_date,'%Y%m%d')的输出格式完全一致，避免格式不匹配导致查询失败。

3. 添加错误捕获（便于排查） #

在数据库操作部分添加try/catch/finally块，能捕获更详细的MySQL错误信息，方便定位问题：

try {
    $mysql.Open()
    $req = New-Object Mysql.Data.MysqlClient.MySqlCommand($sqlquery1,$mysql)
    $req.Parameters.Add("@DateParam", [MySql.Data.MySqlClient.MySqlDbType]::String).Value = $Query
    $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($req)
    $dataSet = New-Object System.Data.DataSet
    $dataAdapter.Fill($dataSet, "Query1") | Out-Null

    # 生成CSV部分
    $DateStamp = (Get-Date -Format "yyyyMMddHHmmss");
    $filename = "C:/DataExchange/Test/Time_Card_$DateStamp.csv"
    $dataSet.Tables["Query1"] | Export-Csv -path $filename -NoTypeInformation;
} catch {
    Write-Error "执行出错: $_"
} finally {
    # 确保数据库连接关闭
    if ($mysql.State -eq 'Open') {
        $mysql.Close()
    }
}

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