## 模块 5: Online Schema Change

TiDB 数据库可通过 Online DDL 特性实现 Online Schema Change，允许用户在不停止应用程序的情况下修改表定义，例如添加或删除列、修改列属性、添加或删除索引以及重命名表。这些操作可以在线执行，实现零停机时间。有关该原理的更多详细信息，请参阅：[DDL 语句的执行原理及最佳实践](https://docs.pingcap.com/zh/tidb/stable/ddl-introduction/)。

在本模块中，参与者将在应用运行过程中为表 `SHOP`.`TRANSACTIONS` 增加一个叫 `Discount` 的列。

完成本模块后，还可以在[TiDB Cloud Starter 入门](https://labs.pingcap.com/lab?preview=demo_203)中的“模块 5：无阻塞在线 DDL 演进表结构”中复习 Online DDL 的基础知识。

### 步骤 1. 检查“交易列表”页面和数据库表

+ 访问 [交易列表](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/transactions"|urlencode}) 处的“全部订单”页面。本实验的目标是为该表添加一个“折扣”列。

![204_5_1_1.png 85%](https://lab-static.pingcap.com/images/2026/1/23/157184ca8ad8e975d65f8999b5b516fcad1087bf.png)

+ 在 VS Code 中，打开一个新的终端（我们称之为“VS Code 终端 2”）。

![204_5_1_2.png 85%](https://lab-static.pingcap.com/images/2026/1/22/d3c60da79b95bd149d9bde24611fe3996e42d374.png)

+ 在 VS Code 终端 2 中，执行 `php artisan model:show Transaction` 命令，查看与 Model（模型）`APP/Model/Transaction.php` 对应的表 `SHOP`.`TRANSACTIONS` 的定义。

  ```bash
  php artisan model:show Transaction
  ```

  ```output
  App\Models\Transaction .....................................................
  Database ............................................................. mysql
  Table ......................................................... transactions

  Attributes ..................................................... type / cast
  id unique, fillable .............................. bigint(20) unsigned / int
  user_id fillable ....................................... bigint(20) unsigned
  product_id fillable .................................... bigint(20) unsigned
  price fillable ............................................... decimal(10,2)
  buy_count fillable ................................................. int(11)
  total_price fillable ......................................... decimal(18,2)
  created_at nullable ................................... timestamp / datetime
  updated_at nullable ................................... timestamp / datetime
  pay_type fillable ............................................... bigint(20)

  Relations ..................................................................
  Product HasOne ...................................... App\Models\Product
  User HasOne ................................................ App\Models\User
  PayType HasOne .......................................... App\Models\PayType

  Events .....................................................................

  Observers ..................................................................
  ```

+ 或者，在 VS Code 终端 2 中，使用命令 `php artisan db:table transactions` 检查表 `SHOP`.`TRANSACTIONS` 的定义，并确认 `Discount` 列不存在。

  ```bash
  php artisan db:table transactions
  ```

  ```output
  shop.transactions ..........................................................
  Columns .................................................................. 9
  Size ............................................................... 2.02 KB
  Engine .............................................................. InnoDB
  Collation ............................................... utf8mb4_unicode_ci

  Column ................................................................ Type
  id bigint .............................................. bigint(20) unsigned
  user_id bigint ......................................... bigint(20) unsigned
  product_id bigint ...................................... bigint(20) unsigned
  price decimal ................................................ decimal(10,2)
  buy_count int ...................................................... int(11)
  total_price decimal .......................................... decimal(18,2)
  created_at timestamp, nullable ................................... timestamp
  updated_at timestamp, nullable ................................... timestamp
  pay_type bigint ............................................... 1 bigint(20)

  Index ......................................................................
  primary id .................................................. btree, primary
  user_transaction_idx user_id ......................................... btree
  product_transaction_idx product_id ................................... btree
  ```

### 步骤 2. Online Schema Change 的准备

+ 在 VS Code 终端中，通过 MySQL 客户端连接到 TiDB Cloud。

   ```bash
   mysql --comments -u ${LAB:SERVERLESS_CLUSTER_USERNAME} \
   -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \
   --ssl \
   --ssl-ca=/etc/ssl/certs/ca-certificates.crt \
   --ssl-verify-server-cert \
   -p${LAB:SERVERLESS_CLUSTER_PASSWORD} \
   ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}
   ```

   ```output
   Your MySQL connection id is 61317775
   Server version: 8.0.11-TiDB-v7.5.2-serverless TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

   Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   MySQL [shop]>
   ```

+ 在 VS Code 终端 2 中，运行命令检查系统变量 `tidb_enable_metadata_lock` 的值。其默认值为 `ON`。

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_enable_metadata_lock';
  ```

  ```output
  MySQL [shop]> SHOW GLOBAL VARIABLES LIKE 'tidb_enable_metadata_lock';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | tidb_enable_metadata_lock | ON    |
  +---------------------------+-------+
  1 row in set (0.011 sec)
  ```

+ 将系统变量 `tidb_enable_metadata_lock` 的值更改为 `OFF`。VS Code 会弹出窗口询问 “Are you sure you want to paste 2 lines of text into the terminal?”，请点击 “Paste”。

   ![204_5_2_1.png 85%](https://lab-static.pingcap.com/images/2026/2/6/f59a9fb8fb4b13e9f37efd228ef37c04b1da3a1f.png)

  ```sql
  SET GLOBAL tidb_enable_metadata_lock = OFF;
  SHOW GLOBAL VARIABLES LIKE 'tidb_enable_metadata_lock';
  ```

  ```output
  MySQL [shop]> SET GLOBAL tidb_enable_metadata_lock = OFF;
  Query OK, 0 rows affected (0.016 sec)

  MySQL [shop]> SHOW GLOBAL VARIABLES LIKE 'tidb_enable_metadata_lock';
  +---------------------------+-------+
  | Variable_name             | Value |
  +---------------------------+-------+
  | tidb_enable_metadata_lock | OFF   |
  +---------------------------+-------+
  1 row in set (0.011 sec)
  ```

+ 注意：当系统变量 `tidb_enable_metadata_lock` 为 `ON`（默认值）时，所有 DDL 命令都必须等待相关表上的 DML 语句执行完毕。在此等待期间，DDL 命令将被阻塞，这与 MySQL 的行为一致。
只有当系统变量 `tidb_enable_metadata_lock` 为 `OFF` 时，DDL 命令的执行才不会受到任何 DML 命令的影响。更多详情，请参阅[TiDB Cloud Starter 入门](https://labs.pingcap.com/lab?preview=demo_203)中的“模块 5：无阻塞在线 DDL 演进表结构”。

+ 在 VS Code 终端 2 中，退出 MySQL 客户端。

   ```sql
   EXIT
   ```

   ```output
   MySQL [shop]> EXIT
   Bye
   root@0c2c53eace8f:~/Mshop#
   ```

+ 修改控制器（Controller） `app/Http/Controllers/ProductsController.php` 中的函数 `public function buy(Request $request, Product $product)`。此函数实现了购买交易功能，在模式更改过程中可能会重复执行。将变量 `$OnlineSchemaChange` 的初始值从 `false` 改为 `true` 并保存文件。此更改立即生效。

  ```php
  /**
  * Process the products purchase.
  */
  public function buy(Request $request, Product $product)
  {
    try {
        //Validate
        $validated = $request->validate(rules: [
            'buy_count' => 'required|integer|min:1',
            'pay_with' => 'required|integer|min:1',
        ]);
    } catch(ValidationException $e) {

        return redirect('/products/' . $product->id)
            ->withErrors($validated)
            ->withInput();
    }

    /**
    * If $OnlineSchemaChange is true, execute function $this->executeTransactionWithRetry(...) to prevent from the
    * error 'Error 8028: Information schema is changed during the execution of the statement' by retry for purchase transaction.
    * If $OnlineSchemaChange is true, exectue function $this->executeTransaction(...) for purchase transaction without retry.
    */
    $OnlineSchemaChange = true;

    if($OnlineSchemaChange) {

        //Execute the logic of purchase transaction.
        return $this->executeTransactionWithRetry($request, $product, $validated);
    } else {
        //Use recursive functions to handle retry logic of purchase transaction.
        return $this->executeTransaction($request, $product, $validated);

    }
  }
  ```

+ 上述修改的原因在于，当 Online DDL 开始在 `SHOP`.`TRANSACTIONS` 表上执行时，在 DDL 命令执行之前已开始且当前正在 `SHOP`.`TRANSACTIONS` 表上运行的事务可能会因表结构变更而失败（ERROR 8028: Information schema is changed）。因此，应用程序中引入了另一个函数 `private function executeTransactionWithRetry($request, $product, $validated, $retryCount = 0)`，通过重试机制解决此问题，确保所有事务都能正常执行。更多详情，请参阅[TiDB Cloud Starter 入门](https://labs.pingcap.com/lab?preview=demo_203)中的“模块 5：无阻塞在线 DDL 演进表结构”。

### 步骤 3. 在应用程序中模拟持续修改数据

+ 登录到[图书管理](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode}) 页面，然后点击“开始自动变更”按钮。从此刻起，页面将自动随机执行交易和库存补充。为了便于观察，只有名为《Waterfalls》的书籍会被更新。

![204_5_3_2.png 85%](https://lab-static.pingcap.com/images/2026/2/10/a0e319fd5dca30240e316e2dec6315eb28c73797.png)

> [!TIP]
> 如果您已在浏览器中打开了[图书管理](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode})页面，请在点击「开始自动变更」按钮之前刷新该页面。

+ 保持这个页面打开，并开始下一步骤操作。

### 步骤 4. 执行 Online Schema Change

+ 在 VS Code 终端 2 中，使用 `php artisan make:migration…` 命令生成迁移文件，将 `Discount` 列添加到 `SHOP`.`TRANSACTIONS` 表中。

  ```bash
  php artisan make:migration add_discount_to_transactions_table --table=transactions
  ```

  ```output
  INFO  Migration [database/migrations/2026_01_22_171252_add_discount_to_transactions_table.php] created successfully.
  ```

+ 在 VSCode 中，找到新生成的代码文件 `2026_01_22_171252_add_discount_to_transactions_table.php`，该文件位于 `/database/migrations/` 目录下。请注意，您的文件名可能与手册中的示例不同；格式为：`xxxx_xx_xx_xxxxxx_add_discount_to_transactions_table.php`。

![205_5_3_1.png 85%](https://lab-static.pingcap.com/images/2026/1/22/c95ec6163c9498ad30ac3f16926650d4130b641d.png)

+ 更新文件 `/database/migrations/2026_01_22_171252_add_discount_to_transactions_table.php`，添加 `Discount` 列的定义和回滚操作，然后保存文件。请注意，您的文件名可能与手册示例不同；格式为：`xxxx_xx_xx_xxxxxx_add_discount_to_transactions_table.php`。

  ```php
  <?php

  use Illuminate\Database\Migrations\Migration;
  use Illuminate\Database\Schema\Blueprint;
  use Illuminate\Support\Facades\Schema;

  return new class extends Migration
  {
    /**
      * Run the migrations.
      */
    public function up(): void
    {
        Schema::table('transactions', function (Blueprint $table) {
            //
            $table->decimal('discount', 5,2)->default(0.0);
        });
    }

    /**
      * Reverse the migrations.
      */
    public function down(): void
    {
        Schema::table('transactions', function (Blueprint $table) {
            //
            $table->dropColumn('discount');
        });
    }
  };
  ```

+ 返回 VS Code 终端，使用 `php artisan migration` 命令对 `SHOP`.`TRANSACTIONS` 表执行 Online Schema Change。正在运行的事务不会阻塞表定义的更改。

  ```bash
  php artisan migrate
  ```

  ```output
  INFO  Running migrations.

  2026_01_22_171252_add_discount_to_transactions_table ................................ 845.16ms DONE
  ```

+ 观察 [图书管理](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode}) 页面，发现正在运行的事务并没有被表定义的变更所阻塞。

+ 在 VS Code 终端继续执行 `php artisan db:table transactions` 查看 `SHOP`.`TRANSACTIONS` 表定义。你会看到已新增 `Discount` 列，并且整个过程中没有事务因表结构变更而失败。

  ```bash
  php artisan db:table transactions
  ```

  ```output
  shop.transactions ..........................................................
  Columns ................................................................. 10
  Size ............................................................... 2.09 KB
  Engine .............................................................. InnoDB
  Collation ............................................... utf8mb4_unicode_ci

  Column ................................................................ Type
  id bigint .............................................. bigint(20) unsigned
  user_id bigint ......................................... bigint(20) unsigned
  product_id bigint ...................................... bigint(20) unsigned
  price decimal ................................................ decimal(10,2)
  buy_count int ...................................................... int(11)
  total_price decimal .......................................... decimal(18,2)
  created_at timestamp, nullable ................................... timestamp
  updated_at timestamp, nullable ................................... timestamp
  pay_type bigint ............................................... 1 bigint(20)
  discount decimal ................................................ 0 decimal(5,2)

  Index ......................................................................
  primary id .................................................. btree, primary
  user_transaction_idx user_id ......................................... btree
  product_transaction_idx product_id ................................... btree
  ```

### 步骤 5. 修改视图并检查新的交易列表页面

+ 在 VSCode 中，修改 Controller（控制器）文件 `APP/Http/Controllers/TransactionController.php` 中的 `public function index()` 函数。该函数用于实现“交易列表”功能。将变量 `$hasDiscount` 的初始值从 `false` 改为 `true`，然后保存文件。

  ```php
  /**
  * Display a listing of the resource.
  */
  public function index()
  {
  //
  // Rows per page.
  $row_per_page = 10;

  // Get the transactions of this page.
  $TransactionPaginate = Transaction::OrderBy('created_at', 'desc')
  ->OrderBy('user_id','desc')
  ->with('Product')
  ->with('User')
  ->with('PayType')
  ->paginate($row_per_page);

  // Set the title and data for view.
  $binding = [
      'title' => __('shop.transaction.Transactions-list'),
      'TransactionPaginate'=> $TransactionPaginate,
  ];

  /**
  * If $hasDiscount is true, it will use the view 'Transactions.listTransactionSave.blade.php' with 'Discount' column in the its table to list the transactions.
  * If $hasDiscount is false, it will use the view 'Transactions.listTransaction.blade.php' without 'Discount' column in the its table to list the transactions.
  */
  $hasDiscount = true;
  if($hasDiscount) {
      // Sent the Transactions data to view with the column 'Discount'.
      return view('transactions.listTransactionSave', $binding);
  }
  else {
      // Sent the Transactions data to view without the column 'Discount'.
      return view('transactions.listTransaction', $binding);
  }
  ```

+ 修改后，“全部订单”页面由 View（视图）`resource/views/Transactions.listTransactionSave.blade.php` 呈现。再次访问 [交易列表](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/transactions"|urlencode}) 的“全部订单”页面，会发现现在出现了“折扣”列。

![204_5_4_1.png 85%](https://lab-static.pingcap.com/images/2026/1/23/5f24f08e1df862456f944430c4c6923180d61421.png)

+ 本模块到此结束。