使用数据操纵语言 (DML) 插入、更新和删除数据

本页面介绍如何使用数据操纵语言 (DML) 语句插入、更新和删除 Spanner 数据。您可以使用客户端库、Google Cloud 控制台和 gcloud 命令行工具来运行 DML 语句。您可以使用客户端库和 gcloud 命令行工具来运行分区 DML 语句。

如需了解完整的 DML 语法参考，请参阅适用于 GoogleSQL 方言数据库的数据操纵语言语法或适用于 PostgreSQL 方言数据库的 PostgreSQL 数据操纵语言

使用 DML

DML 支持在Google Cloud 控制台、Google Cloud CLI 以及客户端库中运行 INSERT、UPDATE 和 DELETE 语句。

锁定

在读写事务中执行 DML 语句。当 Spanner 读取数据时，它会对其读取的有限范围的行获取共享读取锁定。具体而言，它仅会对您访问的列获取这些锁定。锁定可能包含不满足 WHERE 子句的过滤条件的数据。

当 Spanner 使用 DML 语句修改数据时，它会对您所修改的特定数据获取独占锁定。此外，它还会采用与读取数据时相同的方式获取共享锁定。如果您的请求包含大范围的行或整个表，则共享锁定可能会阻止其他事务并行执行。

为尽可能高效地修改数据，请使用 WHERE 子句，以使 Spanner 只读取必要的行。您可以通过按主键进行过滤或按二级索引的键进行过滤来实现此目标。WHERE 子句限制了共享锁定的范围，使 Spanner 能够更高效地处理更新。

例如，假设 Singers 表中的某位音乐人更改了其名字，则您需要在您的数据库中更新该名字。您可以执行以下 DML 语句，但该 DML 语句会强制 Spanner 扫描整个表，并获取共享锁定以覆盖整个表。因此，Spanner 必须读取数据超出需要的数据，因此并发事务不能并行修改数据：

-- ANTI-PATTERN: SENDING AN UPDATE WITHOUT THE PRIMARY KEY COLUMN
-- IN THE WHERE CLAUSE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards";

为使该更新更加高效，请在 WHERE 子句中添加 SingerId 列。SingerId 列是 Singers 表的唯一主键列：

-- ANTI-PATTERN: SENDING AN UPDATE THAT MUST SCAN THE ENTIRE TABLE

UPDATE Singers SET FirstName = "Marcel"
WHERE FirstName = "Marc" AND LastName = "Richards"

如果没有用于 FirstName 或 LastName 的索引，您需要扫描整个表以查找目标歌手。如果您不想添加二级索引来提高更新效率，请在 WHERE 子句中添加 SingerId 列。

SingerId 列是 Singers 表的唯一主键列。如需查找该列，请在更新事务之前，在单独的只读事务中运行 SELECT：


  SELECT SingerId
  FROM Singers
  WHERE FirstName = "Marc" AND LastName = "Richards"

  -- Recommended: Including a seekable filter in the where clause

  UPDATE Singers SET FirstName = "Marcel"
  WHERE SingerId = 1;

并发

Spanner 按顺序执行事务中的所有 SQL 语句（SELECT、INSERT、UPDATE 和 DELETE）。这些语句不是同时执行的。唯一的例外是 Spanner 可能会同时执行多个 SELECT 语句，因为它们是只读操作。

事务限制

包含 DML 语句的事务与任何其他事务具有相同的限制。如果您进行大规模更改，请考虑使用分区 DML。

  • 如果事务中的 DML 语句导致变更数超过 80,000，则致使该事务超出限制的 DML 语句将返回 BadUsage 错误，并显示指明变更数过多的消息。

  • 如果事务中的 DML 语句导致事务大于 100 MiB，则致使该事务超出限制的 DML 语句将返回 BadUsage 错误，并显示指明事务超出大小限制的消息。

使用 DML 执行的变更不会返回给客户端。这些变更会在提交时合并到提交请求中，并计入最大大小限制。即使您发送的提交请求的很小，该事务仍可能超过允许的大小限制。

在 Google Cloud 控制台中运行语句

请按照以下步骤在Google Cloud 控制台中执行 DML 语句。

  1. 前往 Spanner 实例页面。

    转到实例页面

  2. 在工具栏的下拉列表中选择您的项目。

  3. 点击包含您的数据库的实例的名称，以转到实例详情页面。

  4. 在概览标签页中，点击数据库的名称。此时将显示数据库详细信息页面。

  5. 点击 Spanner Studio。

  6. 输入 DML 语句。例如，以下语句向 Singers 表中添加一个新行。

    INSERT Singers (SingerId, FirstName, LastName)
VALUES (1, 'Marc', 'Richards')

  7. 点击运行查询。 Google Cloud 控制台会显示结果。

使用 Google Cloud CLI 执行语句

如需执行 DML 语句，请使用 gcloud spanner databases execute-sql 命令。以下示例向 Singers 表中添加一个新行。

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql="INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')"

使用客户端库修改数据

要使用客户端库执行 DML 语句，请执行以下操作：

  • 创建一个读写事务。
  • 调用客户端库方法执行 DML 并传入 DML 语句。
  • 使用 DML 执行方法的返回值来获取插入、更新或删除的行数。

以下代码示例在 Singers 表中插入一个新行。

C++

使用 ExecuteDml() 函数来执行 DML 语句。

void DmlStandardInsert(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;
  std::int64_t rows_inserted;
  auto commit_result = client.Commit(
      [&client, &rows_inserted](
          spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto insert = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "INSERT INTO Singers (SingerId, FirstName, LastName)"
                "  VALUES (10, 'Virginia', 'Watson')"));
        if (!insert) return std::move(insert).status();
        rows_inserted = insert->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Rows inserted: " << rows_inserted;
  std::cout << "Insert was successful [spanner_dml_standard_insert]\n";
}

C#

使用 ExecuteNonQueryAsync() 方法来执行 DML 语句。


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class InsertUsingDmlCoreAsyncSample
{
    public async Task<int> InsertUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("INSERT Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')");
        int rowCount = await cmd.ExecuteNonQueryAsync();

        Console.WriteLine($"{rowCount} row(s) inserted...");
        return rowCount;
    }
}

Go

使用 Update() 方法来执行 DML 语句。


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
)

func insertUsingDML(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT Singers (SingerId, FirstName, LastName)
					VALUES (10, 'Virginia', 'Watson')`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
		return nil
	})
	return err
}

Java

使用 executeUpdate() 方法来执行 DML 语句。

static void insertUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(transaction -> {
        String sql =
            "INSERT INTO Singers (SingerId, FirstName, LastName) "
                + " VALUES (10, 'Virginia', 'Watson')";
        long rowCount = transaction.executeUpdate(Statement.of(sql));
        System.out.printf("%d record inserted.\n", rowCount);
        return null;
      });
}

Node.js

使用 runUpdate() 方法来执行 DML 语句。

// Imports the Google Cloud client library
const {Spanner} = require