Laravel连接SQLSRV时Union查询CASE语句报错，MySQL正常求解决

问题原因 #

SQL Server与MySQL对字符串常量的解析规则存在差异：

• MySQL允许使用双引号包裹字符串常量
• SQL Server会将双引号包裹的内容识别为列名，因此你的CASE语句中"Program"、"Course"等内容被当成了不存在的列，触发Invalid column name错误。

修复后的代码 #

将CASE语句中所有双引号替换为单引号即可：

$courses = DB::table('courses')
            ->where('courses.name', 'like', '%'.$search_text.'%')
            ->selectRaw(
                'id, courses.name as name, courses.created_at as created_at,
                (CASE
                    WHEN courses.is_program = 1 THEN \'Program\'
                    WHEN courses.is_program = 0 THEN \'Course\'
                    ELSE \'Unknown\'
                END) AS type'
            );

$surveys = DB::table('surveys')
            ->where('surveys.name', 'like', '%'.$search_text.'%')
            ->selectRaw(
                'id, surveys.name as name, surveys.created_at as created_at,
                 (CASE
                    WHEN surveys.id != 0 THEN \'Survey\'
                    ELSE \'Unknown\'
                END) AS type'
            );

$exhibitions = DB::table('exhibitions')
            ->where('exhibitions.name', 'like', '%'.$search_text.'%')
            ->selectRaw(
                'id, exhibitions.name as name, exhibitions.created_at as created_at,
                 (CASE
                    WHEN exhibitions.id != 0 THEN \'Exhibition\'
                    ELSE \'Unknown\'
                END) AS type'
            );

$results = $courses->unionAll($surveys)->unionAll($exhibitions);
if ($perPage) {
    $results = $results->paginate($perPage);
} else {
    $results = $results->get();
}

额外优化建议 #

避免直接拼接$search_text到SQL语句中，防止SQL注入风险，改用Laravel查询构建器的自动参数绑定：

// 替换原where条件为更安全的写法
->where('courses.name', 'like', "%{$search_text}%")

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