## 模块 5：在线表结构变更

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

在本模块中，你将在应用程序运行期间向 `SHOP`.`TRANSACTIONS` 表添加 `Discount` 列。

你也可以在完成本模块后，在 [Introduction to TiDB Cloud Starter](https://labs.tidb.io/labs/demo_203) 的 "Module 5: Evolving the Schema with Non-Blocking Online DDL" 中复习 Online DDL 的基础知识。

### 步骤 1. 检查 "Transactions List" 页面和数据库中的表

+ 访问 [Transaction List](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/transactions"|urlencode}) 的 "Transactions list" 页面。我们的目标是在本实验中为该表添加一个 `Discount` 列。

![204_5_1_1.png 85%](https://lab-static.pingcap.com/images/2026/1/22/9e0c98ea3fd1a12d505fd8f2c4aae194176e2136.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. 准备在线表结构变更

+ 在 VS Code 终端 2 中，使用 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 命令的影响。
有关详细信息，请参阅 [Introduction to TiDB Cloud Starter](/labs/demo_203) 中的 "Module 5: Evolving the Schema with Non-Blocking Online 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)`，通过重试来解决此问题，确保所有事务正常执行。有关更多详细信息，请参阅 [Introduction to TiDB Cloud Starter](https://labs.tidb.io/labs/demo_203) 中的 "Module 5: Evolving the Schema with Non-Blocking Online DDL"。

### 步骤 3. 从应用程序执行持续变更

- 点击 [Book Management](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode}) 访问管理页面，然后点击 `Start auto change` 按钮。从此刻起，页面将自动随机执行交易和库存补充。为方便观察，只会更新书名为 `Waterfalls` 的书籍。

![start-auto-change.jpg 85%](https://lab-static.pingcap.com/images/2026/2/9/e3dd6c1002045ae88ebf7280a575de63a34aa830.jpg)

> [!TIP]
> 如果你的浏览器中已经打开了 [Book Management](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode}) 页面，请在点击 `Start auto change` 按钮之前刷新网页。

- 保持页面打开，继续下一步。

### 步骤 4. 执行在线表结构变更

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

  ```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.
  ```

+ 在 VS Code 中，找到 `/database/migrations/` 目录下新生成的代码文件 `2026_01_22_171252_add_discount_to_transactions_table.php`。请注意，你的文件名可能与手册示例不同；格式为：`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');
        });
    }
  };
  ```

+ 返回终端，使用 `php artisan migration` 命令对 `SHOP`.`TRANSACTIONS` 表执行在线表结构变更。正在运行的事务不会阻塞表结构变更。

  ```bash
  php artisan migrate
  ```

  ```output
  INFO  Running migrations.

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

+ 观察 [Book Management](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/products/manage"|urlencode}) 页面；正在运行的事务未被表结构变更阻塞。

+ 继续在终端中使用 `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. 修改视图并检查新的交易列表页面

+ 在 VS Code 中，修改 Controller `APP/Http/Controllers/TransactionController.php` 中的函数 `public function index()`。此函数用于实现 "Transactions list" 功能。将变量 `$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` 渲染。再次访问 [Transaction List](${LAB:HTTPS_REVERSE_PROXY_URL}?next=${LAB:"/vscode/proxy/8000/transactions"|urlencode}) 的 "Transactions list" 页面，你会发现 `Discount` 列已出现。

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

+ 你现在已完成本模块。