Laravel 查詢大資料和匯出問題 (上)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255)  NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

1

Route::get('download', [ExampleController::class, 'download']);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

public function download()
{
    $headers = [
        'Content-Encoding: UTF-8',
        'Content-Type' => 'text/csv',
    ]; // 可有可無

    return response()->streamDownload(function () {
        echo "\xEF\xBB\xBF"; // UTF8 BOM

        // Header
        echo "欄位1,欄位2,欄位3\n";

        // Data
        echo "11,22,33\n";
        echo "11,22,33\n";
    }, 'example.csv', $headers);
}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

public function download()
{
    return response()->streamDownload(function () {
        // Data
        $this->getData();
    }, 'example.csv');
}

protected function getData()
{
    // 方法一
    User::query()
    // ->select('id', 'name', 'email')
    ->chunk(100000, function ($users) {
        foreach ($users as $user) {
            echo implode(',', $user->toArray()) . "\n";
        }
    });

    // 方法二
    DB::table('users')
    // ->select('id', 'name', 'email')
    ->orderBy('id', 'desc')
    ->chunk(100000, function ($rows) {
        foreach ($rows as $row) {
            echo implode(',', (array) $row) . "\n";
        }
    });
}