Laravel项目中MySQL DATEDIFF在SQLite测试环境的兼容问题

方案一：动态生成兼容多数据库的查询表达式 #

通过Laravel数据库门面判断当前驱动，动态生成对应数据库的日期差计算逻辑，同时使用参数绑定避免SQL注入：

use Illuminate\Support\Facades\DB;

// 获取当前数据库驱动类型
$driver = DB::getDriverName();

// 根据驱动生成日期差计算表达式
if ($driver === 'mysql') {
    $diffExpr = "floor(datediff(created_at, ?) / ?) * ? AS diff_days_range";
} else { // SQLite环境
    $diffExpr = "floor((julianday(created_at) - julianday(?)) / ?) * ? AS diff_days_range";
}

// 执行查询
$statisticsInRangeDate = $myModel->selectRaw(
    "{$diffExpr}, count(*) as total_clicks, min(created_at), max(created_at)",
    [
        $dateInfo['currentRangeDate'],
        $dateInfo['daysRange'],
        $dateInfo['daysRange']
    ]
)
->groupByRaw('diff_days_range')
->orderByRaw('diff_days_range DESC')
->get();

方案二：为SQLite自定义datediff函数（推荐） #

在测试环境初始化阶段，为SQLite注册与MySQL行为一致的datediff函数，业务代码无需任何修改即可兼容两种数据库：

方式1：在PHPUnit测试类的setUp方法中注册 #

protected function setUp(): void
{
    parent::setUp();

    if (DB::getDriverName() === 'sqlite') {
        DB::statement("CREATE FUNCTION datediff(date1, date2) RETURNS INTEGER BEGIN RETURN julianday(date1) - julianday(date2); END;");
    }
}

方式2：在测试环境服务提供者中注册 #

在app/Providers/AppServiceProvider.php的boot方法中添加判断：

public function boot()
{
    if (app()->environment('testing') && DB::getDriverName() === 'sqlite') {
        DB::statement("CREATE FUNCTION datediff(date1, date2) RETURNS INTEGER BEGIN RETURN julianday(date1) - julianday(date2); END;");
    }
}

注册完成后，原有的查询代码可以直接保留，无需改动即可在MySQL和SQLite内存库中正常运行。

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