批量写入数据.insertAll

Testing Is Documentation

tests/Database/Create/InsertAllTest.php批量写入数据.insertAll - 图1

Uses

  1. <?php
  3. use Leevel\Database\Condition;
  4. use Tests\Database\DatabaseTestCase as TestCase;

insertAll 基本用法

写入成功后，返回 lastInsertId。

  1. public function testBaseUse(): void
  2. {
  3.     $connect = $this->createDatabaseConnectMock();
  5.     $sql = <<<'eot'
  6.         [
  7.             "INSERT INTO `test_query` (`test_query`.`name`,`test_query`.`value`) VALUES (:pdonamedparameter_name,:pdonamedparameter_value),(:pdonamedparameter_name_1,:pdonamedparameter_value_1),(:pdonamedparameter_name_2,:pdonamedparameter_value_2),(:pdonamedparameter_name_3,:pdonamedparameter_value_3)",
  8.             {
  9.                 "pdonamedparameter_name": [
  10.                     "小鸭子1"
  11.                 ],
  12.                 "pdonamedparameter_value": [
  13.                     "呱呱呱1"
  14.                 ],
  15.                 "pdonamedparameter_name_1": [
  16.                     "小鸭子2"
  17.                 ],
  18.                 "pdonamedparameter_value_1": [
  19.                     "呱呱呱2"
  20.                 ],
  21.                 "pdonamedparameter_name_2": [
  22.                     "小鸭子3"
  23.                 ],
  24.                 "pdonamedparameter_value_2": [
  25.                     "呱呱呱3"
  26.                 ],
  27.                 "pdonamedparameter_name_3": [
  28.                     "小鸭子4"
  29.                 ],
  30.                 "pdonamedparameter_value_3": [
  31.                     "呱呱呱4"
  32.                 ]
  33.             },
  34.             false
  35.         ]
  36.         eot;
  38.     $data = [
  39.         ['name' => '小鸭子1', 'value' => '呱呱呱1'],
  40.         ['name' => '小鸭子2', 'value' => '呱呱呱2'],
  41.         ['name' => '小鸭子3', 'value' => '呱呱呱3'],
  42.         ['name' => '小鸭子4', 'value' => '呱呱呱4'],
  43.     ];
  45.     $this->assertSame(
  46.         $sql,
  47.         $this->varJson(
  48.             $connect
  49.                 ->sql()
  50.                 ->table('test_query')
  51.                 ->insertAll($data)
  52.         )
  53.     );
  54. }

insertAll 绑定参数

  1. public function testBind(): void
  2. {
  3.     $connect = $this->createDatabaseConnectMock();
  5.     $sql = <<<'eot'
  6.         [
  7.             "INSERT INTO `test_query` (`test_query`.`name`,`test_query`.`value`) VALUES (:pdonamedparameter_name,:pdonamedparameter_value),(:pdonamedparameter_name_1,:pdopositional2namedparameter_0_1),(:pdonamedparameter_name_2,:pdonamedparameter_value_2),(:pdonamedparameter_name_3,:pdopositional2namedparameter_1_3)",
  8.             {
  9.                 "pdonamedparameter_name": [
  10.                     "小鸭子1"
  11.                 ],
  12.                 "pdonamedparameter_value": [
  13.                     "呱呱呱1"
  14.                 ],
  15.                 "pdonamedparameter_name_1": [
  16.                     "小鸭子2"
  17.                 ],
  18.                 "pdopositional2namedparameter_0_1": [
  19.                     "吃肉1"
  20.                 ],
  21.                 "pdonamedparameter_name_2": [
  22.                     "小鸭子3"
  23.                 ],
  24.                 "pdonamedparameter_value_2": [
  25.                     "呱呱呱3"
  26.                 ],
  27.                 "pdonamedparameter_name_3": [
  28.                     "小鸭子4"
  29.                 ],
  30.                 "pdopositional2namedparameter_1_3": [
  31.                     "吃肉2"
  32.                 ]
  33.             },
  34.             false
  35.         ]
  36.         eot;
  38.     $data = [
  39.         ['name' => '小鸭子1', 'value' => '呱呱呱1'],
  40.         ['name' => '小鸭子2', 'value' => Condition::raw('?')],
  41.         ['name' => '小鸭子3', 'value' => '呱呱呱3'],
  42.         ['name' => '小鸭子4', 'value' => Condition::raw('?')],
  43.     ];
  45.     $this->assertSame(
  46.         $sql,
  47.         $this->varJson(
  48.             $connect
  49.                 ->sql()
  50.                 ->table('test_query')
  51.                 ->insertAll($data, ['吃肉1', '吃肉2'])
  52.         )
  53.     );
  55.     $sql = <<<'eot'
  56.         [
  57.             "INSERT INTO `test_query` (`test_query`.`name`,`test_query`.`value`) VALUES (:pdonamedparameter_name,:pdonamedparameter_value),(:pdonamedparameter_name_1,:hello),(:pdonamedparameter_name_2,:pdonamedparameter_value_2),(:pdonamedparameter_name_3,:world)",
  58.             {
  59.                 "pdonamedparameter_name": [
  60.                     "小鸭子1"
  61.                 ],
  62.                 "pdonamedparameter_value": [
  63.                     "呱呱呱1"
  64.                 ],
  65.                 "pdonamedparameter_name_1": [
  66.                     "小鸭子2"
  67.                 ],
  68.                 "pdonamedparameter_name_2": [
  69.                     "小鸭子3"
  70.                 ],
  71.                 "pdonamedparameter_value_2": [
  72.                     "呱呱呱3"
  73.                 ],
  74.                 "pdonamedparameter_name_3": [
  75.                     "小鸭子4"
  76.                 ],
  77.                 "hello": "hello 吃肉",
  78.                 "world": "world 喝汤"
  79.             },
  80.             false
  81.         ]
  82.         eot;
  84.     $data = [
  85.         ['name' => '小鸭子1', 'value' => '呱呱呱1'],
  86.         ['name' => '小鸭子2', 'value' => Condition::raw(':hello')],
  87.         ['name' => '小鸭子3', 'value' => '呱呱呱3'],
  88.         ['name' => '小鸭子4', 'value' => Condition::raw(':world')],
  89.     ];
  91.     $this->assertSame(
  92.         $sql,
  93.         $this->varJson(
  94.             $connect
  95.                 ->sql()
  96.                 ->table('test_query')
  97.                 ->insertAll($data, ['hello' => 'hello 吃肉', 'world' => 'world 喝汤']),
  98.             1
  99.         )
  100.     );
  101. }

bind.insertAll 绑定参数批量写入数据

  1. public function testWithBindFunction(): void
  2. {
  3.     $connect = $this->createDatabaseConnectMock();
  5.     $sql = <<<'eot'
  6.         [
  7.             "INSERT INTO `test_query` (`test_query`.`name`,`test_query`.`value`) VALUES (:pdonamedparameter_name,:pdonamedparameter_value),(:pdonamedparameter_name_1,:pdopositional2namedparameter_0_1),(:pdonamedparameter_name_2,:pdonamedparameter_value_2),(:pdonamedparameter_name_3,:pdopositional2namedparameter_1_3)",
  8.             {
  9.                 "pdonamedparameter_name": [
  10.                     "小鸭子1"
  11.                 ],
  12.                 "pdonamedparameter_value": [
  13.                     "呱呱呱1"
  14.                 ],
  15.                 "pdonamedparameter_name_1": [
  16.                     "小鸭子2"
  17.                 ],
  18.                 "pdopositional2namedparameter_0_1": [
  19.                     "吃鱼"
  20.                 ],
  21.                 "pdonamedparameter_name_2": [
  22.                     "小鸭子3"
  23.                 ],
  24.                 "pdonamedparameter_value_2": [
  25.                     "呱呱呱3"
  26.                 ],
  27.                 "pdonamedparameter_name_3": [
  28.                     "小鸭子4"
  29.                 ],
  30.                 "pdopositional2namedparameter_1_3": [
  31.                     "吃肉"
  32.                 ]
  33.             },
  34.             false
  35.         ]
  36.         eot;
  38.     $data = [
  39.         ['name' => '小鸭子1', 'value' => '呱呱呱1'],
  40.         ['name' => '小鸭子2', 'value' => Condition::raw('?')],
  41.         ['name' => '小鸭子3', 'value' => '呱呱呱3'],
  42.         ['name' => '小鸭子4', 'value' => Condition::raw('?')],
  43.     ];
  45.     $this->assertSame(
  46.         $sql,
  47.         $this->varJson(
  48.             $connect
  49.                 ->sql()
  50.                 ->table('test_query')
  51.                 ->bind(['吃鱼', '吃肉'])
  52.                 ->insertAll($data)
  53.         )
  54.     );
  55. }

insertAll 支持 replace 用法

  1. public function testReplace(): void
  2. {
  3.     $connect = $this->createDatabaseConnectMock();
  5.     $sql = <<<'eot'
  6.         [
  7.             "REPLACE INTO `test_query` (`test_query`.`name`,`test_query`.`value`) VALUES (:pdonamedparameter_name,:pdonamedparameter_value),(:pdonamedparameter_name_1,:pdopositional2namedparameter_0_1),(:pdonamedparameter_name_2,:pdonamedparameter_value_2),(:pdonamedparameter_name_3,:pdopositional2namedparameter_1_3)",
  8.             {
  9.                 "pdonamedparameter_name": [
  10.                     "小鸭子1"
  11.                 ],
  12.                 "pdonamedparameter_value": [
  13.                     "呱呱呱1"
  14.                 ],
  15.                 "pdonamedparameter_name_1": [
  16.                     "小鸭子2"
  17.                 ],
  18.                 "pdopositional2namedparameter_0_1": [
  19.                     "吃鱼"
  20.                 ],
  21.                 "pdonamedparameter_name_2": [
  22.                     "小鸭子3"
  23.                 ],
  24.                 "pdonamedparameter_value_2": [
  25.                     "呱呱呱3"
  26.                 ],
  27.                 "pdonamedparameter_name_3": [
  28.                     "小鸭子4"
  29.                 ],
  30.                 "pdopositional2namedparameter_1_3": [
  31.                     "吃肉"
  32.                 ]
  33.             },
  34.             false
  35.         ]
  36.         eot;
  38.     $data = [
  39.         ['name' => '小鸭子1', 'value' => '呱呱呱1'],
  40.         ['name' => '小鸭子2', 'value' => Condition::raw('?')],
  41.         ['name' => '小鸭子3', 'value' => '呱呱呱3'],
  42.         ['name' => '小鸭子4', 'value' => Condition::raw('?')],
  43.     ];
  45.     $this->assertSame(
  46.         $sql,
  47.         $this->varJson(
  48.             $connect
  49.                 ->sql()
  50.                 ->table('test_query')
  51.                 ->bind(['吃鱼', '吃肉'])
  52.                 ->insertAll($data, [], true)
  53.         )
  54.     );
  55. }

insertAll 空数据批量写入示例

  1. public function testInsertWithEmptyData(): void
  2. {
  3.     $connect = $this->createDatabaseConnectMock();
  5.     $sql = <<<'eot'
  6.         [
  7.             "INSERT INTO `test_query` () VALUES (),(),(),()",
  8.             [],
  9.             false
  10.         ]
  11.         eot;
  13.     $data = [
  14.         [],
  15.         [],
  16.         [],
  17.         [],
  18.     ];
  20.     $this->assertSame(
  21.         $sql,
  22.         $this->varJson(
  23.             $connect
  24.                 ->sql()
  25.                 ->table('test_query')
  26.                 ->insertAll($data)
  27.         )
  28.     );
  29. }

insertAll.replace 空数据写入示例

  1. public function testReplaceWithEmptyData(): void
  2. {
  3.     $connect = $this->createDatabaseConnectMock();
  5.     $sql = <<<'eot'
  6.         [
  7.             "REPLACE INTO `test_query` () VALUES (),(),(),()",
  8.             [],
  9.             false
  10.         ]
  11.         eot;
  13.     $data = [
  14.         [],
  15.         [],
  16.         [],
  17.         [],
  18.     ];
  20.     $this->assertSame(
  21.         $sql,
  22.         $this->varJson(
  23.             $connect
  24.                 ->sql()
  25.                 ->table('test_query')
  26.                 ->insertAll($data, [], true)
  27.         )
  28.     );
  29. }