数据操纵语言语法

借助 Cloud Spanner 数据操纵语言 (DML),您可以在 Cloud Spanner 表中更新、插入和删除数据。

如需了解如何使用 DML 语句,请参阅使用数据操纵语言插入、更新和删除数据。您还可以使用变量修改数据。

示例中使用的表

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  BirthDate  DATE,
  LastUpdated TIMESTAMP,
) PRIMARY KEY(SingerId);

CREATE TABLE Albums (
  SingerId        INT64 NOT NULL,
  AlbumId         INT64 NOT NULL,
  AlbumTitle      STRING(MAX),
  MarketingBudget INT64,
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE TABLE Songs (
  SingerId  INT64 NOT NULL,
  AlbumId   INT64 NOT NULL,
  TrackId   INT64 NOT NULL,
  SongName  STRING(MAX),
  Duration  INT64,
  SongGenre STRING(25),
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE TABLE Concerts (
  VenueId      INT64 NOT NULL,
  SingerId     INT64 NOT NULL,
  ConcertDate  DATE NOT NULL,
  BeginTime    TIMESTAMP,
  EndTime      TIMESTAMP,
  TicketPrices ARRAY<INT64>,
) PRIMARY KEY(VenueId, SingerId, ConcertDate);

CREATE TABLE AckworthSingers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  BirthDate  DATE,
) PRIMARY KEY(SingerId);

语法中使用的表示法

  • 方括号 [ ] 表示可选子句。
  • 圆括号 ( ) 表示文本括号。
  • 竖线 | 表示“或”(OR) 逻辑。
  • 大括号 { } 括起一组可选项。
  • 英文逗号后跟英文省略号表示前一项可能在英文逗号分隔列表中重复。item [, ...] 表示一或多项,而 [item, ...] 表示零或更多项。
  • 英文逗号 , 表示文本逗号。
  • 英文尖括号 <> 表示文本尖括号。
  • 英文冒号 : 表示定义。
  • 大写单词(例如 INSERT)表示关键字。

INSERT 语句

使用 INSERT 语句可向表中添加新行。INSERT 语句可以插入由值表达式指定的一或多行,或由查询生成的零或多行。该语句会返回表中插入的行数。

INSERT [INTO] target_name
 (column_name_1 [, ..., column_name_n] )
 input

input:
 VALUES (row_1_column_1_expr [, ..., row_1_column_n_expr ] )
        [, ..., (row_k_column_1_expr [, ..., row_k_column_n_expr ] ) ]
| select_query

expr: value_expression | DEFAULT

INSERT 语句必须符合以下规则:

  • 列名可以按任何顺序排列。
  • 列的列表中不允许存在重复的名称。
  • 列数必须与值的数量相符。
  • Cloud Spanner 会将 VALUES 子句或 SELECT 查询中的值与列列表进行位置比对。
  • 每个值的类型必须与其关联列的类型兼容。
  • 值必须满足架构中的所有约束条件,例如,二级索引唯一。
  • 所有非空列都必须在列列表中显示,并且必须指定非空值。

如果语句不符合上述规则,Cloud Spanner 会产生错误,整个语句也将失败。

如果语句尝试插入重复行(根据主键判断),则整个语句将失败。

值类型兼容性

INSERT 语句中添加的值必须与目标列的类型兼容。只要值满足以下条件之一,即可认为值的类型与目标列的类型兼容:

  • 值类型与列类型完全匹配。例如,在类型为 INT64 的列中插入 INT64 类型的值。
  • Cloud Spanner 可以将值隐式强制转换为目标类型。

默认值

使用 DEFAULT 关键字可插入列的默认值。Cloud Spanner 会将默认值 NULL 分配给未包含到列列表中的列。

INSERT 示例

使用文本值的 INSERT

以下示例向 Singers 表添加三行。

INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES(1, 'Marc', 'Richards'),
      (2, 'Catalina', 'Smith'),
      (3, 'Alice', 'Trentor');

添加到表中的三个新行如下所示:

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

使用 SELECT 语句的 INSERT

以下示例说明如何使用 SELECT 语句作为输入,将数据从一个表复制到另一个表中:

INSERT INTO Singers (SingerId, FirstName, LastName)
SELECT SingerId, FirstName, LastName
FROM AckworthSingers;

如果 Singers 表中原本没有行,而 AckworthSingers 表有三行,那么现在 Singers 表中也有三行:

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

以下示例显示如何使用 UNNEST 返回一个表作为 INSERT 命令的输入。

INSERT INTO Singers (SingerId, FirstName, LastName)
SELECT *
FROM UNNEST ([(4, 'Lea', 'Martin'),
      (5, 'David', 'Lomond'),
      (6, 'Elena', 'Campbell')]);

将这额外的三行添加到上一个示例中的表后,现在 Singers 表中有六行:

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

使用子查询的 INSERT

以下示例展示了如何向表中插入一行,并且其中一个值使用子查询计算得出:

INSERT INTO Singers (SingerId, FirstName)
VALUES (4, (SELECT FirstName FROM AckworthSingers WHERE SingerId = 4));

下表显示了执行语句之前的数据。

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL

AckworthSingers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL

下表显示了执行语句之后的数据。

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
4 Lea NULL NULL

要包含多个列,请包含多个子查询:

INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (4,
        (SELECT FirstName FROM AckworthSingers WHERE SingerId = 4),
        (SELECT LastName  FROM AckworthSingers WHERE SingerId = 4));

DELETE 语句

使用 DELETE 语句从表中删除行。

DELETE [FROM] target_name [[AS] alias] WHERE condition;

WHERE 子句

WHERE 子句是必需的。此要求有助于防止意外删除表中的所有行。要删除表中的所有行,请将 condition 设置为 true

DELETE FROM target_name WHERE true;

WHERE 子句可以包含任何有效的 SQL 语句,包括引用其他表的子查询。

别名

WHERE 子句具有 target_name 的隐式别名,让您不需要将列限定于 target_name,便可以引用 target_name 中的列。例如,如果您的语句以 DELETE FROM Singers 开头,那么意味着您可以在 WHERE 子句中访问 Singers 的任何列。在下面的示例中,FirstNameSingers 表中的一列:

DELETE FROM Singers WHERE FirstName = 'Alice';

您还可以使用可选的 AS 关键字创建显式别名。如需详细了解别名,请参阅查询语法

DELETE 示例

使用 WHERE 子句的 DELETE

以下 DELETE 语句删除名字为 Alice 的所有歌手。

DELETE FROM Singers WHERE FirstName = 'Alice';

下表显示了执行语句之前的数据。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

下表显示了执行语句之后的数据。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL

使用子查询的 DELETE

下面的语句删除 SINGERS 中名字未列于 AckworthSingers 中的所有歌手。

DELETE FROM Singers
WHERE FirstName NOT IN (SELECT FirstName from AckworthSingers);

下表显示了执行语句之前的数据。

Singers

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

AckworthSingers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

下表显示了执行语句之后的数据。

Singers

SingerId FirstName LastName BirthDate
4 Lea Martin NULL
5 David Lomond NULL
6 Elena Campbell NULL

UPDATE 语句

使用 UPDATE 语句更新表中的现有行。

UPDATE target_name [[AS] alias]
SET update_item [, ...]
WHERE condition;

update_item: path_expression = expression | path_expression = DEFAULT

其中:

  • target_name 是需要更新的表的名称。
  • SET 子句会列出要在 WHERE 条件为 true 的每行上执行的 update_items。
  • path_expression 是列名。
  • expression 是更新表达式。表达式可以是文本、SQL 表达式或 SQL 子查询。

UPDATE 语句必须符合以下规则:

  • 一个列只能在 SET 子句中出现一次。
  • SET 子句中的列可以按任何顺序列出。
  • 每个值的类型必须与其关联列的类型兼容。
  • 值必须满足架构中的所有约束条件,例如,二级索引唯一或列不可为空。
  • 不支持使用联接的更新。
  • 不能更新主键列。

如果语句不符合上述规则,Cloud Spanner 会产生错误,整个语句也将失败。

不会修改未包含在 SET 子句中的列。

列更新会同时执行。例如,您可以使用单个 SET 子句交换两个列的值:

SET x = y, y = x

值类型兼容性

使用 UPDATE 语句更新的值必须与目标列类型兼容。只要值满足以下条件之一,即可认为值的类型与目标列的类型兼容:

  • 值类型与列类型完全匹配。例如,值类型为 INT64,列类型为 INT64
  • Cloud Spanner 可以将值隐式强制转换为目标类型。

默认值

DEFAULT 关键字将列的值设置为 NULL

WHERE 子句

WHERE 子句是必需的。此要求有助于防止意外更新表中的所有行。要更新表中的所有行,请将 condition 设置为 true

WHERE 子句可以包含任何有效的 SQL 布尔表达式,包括引用其他表的子查询。

别名

WHERE 子句具有 target_name 的隐式别名,让您不需要将列限定于 target_name,便可以引用 target_name 中的列。例如,如果您的语句以 UPDATE Singers 开头,那么意味着您可以在 WHERE 子句中访问 Singers 的任何列。在下面的示例中,FirstNameLastNameSingers 表中的列:

UPDATE Singers
SET BirthDate = '1990-10-10'
WHERE FirstName = 'Marc' AND LastName = 'Richards';

您还可以使用可选的 AS 关键字创建显式别名。如需详细了解别名,请参阅查询语法

UPDATE 示例

使用文本值的 UPDATE

以下示例更新 Singers 表中其中一行的 BirthDate 列。

UPDATE Singers
SET BirthDate = '1990-10-10'
WHERE FirstName = 'Marc' AND LastName = 'Richards';

下表显示了执行语句之前的数据。

SingerId FirstName LastName BirthDate
1 Marc Richards NULL
2 Catalina Smith NULL
3 Alice Trentor NULL

下表显示了执行语句之后的数据。

SingerId FirstName LastName BirthDate
1 Marc Richards 1990-10-10
2 Catalina Smith NULL
3 Alice Trentor NULL

使用 UPDATE 更新 ARRAY 列

以下示例更新 ARRAY 列。

UPDATE Concerts SET TicketPrices = [25, 50, 100] WHERE VenueId = 1;

下表显示了执行语句之前的数据。

VenueId SingerId ConcertDate BeginTime EndTime TicketPrices
1 1 NULL NULL NULL NULL
1 2 NULL NULL NULL NULL
2 3 NULL NULL NULL NULL

下表显示了执行语句之后的数据。

VenueId SingerId ConcertDate BeginTime EndTime TicketPrices
1 1 2018-01-01 NULL NULL [25, 50, 100]
1 2 2018-01-01 NULL NULL [25, 50, 100]
2 3 2018-01-01 NULL NULL NULL

绑定的 STRUCT 参数

您可以在 DML 语句的 WHERE 子句中使用绑定的 STRUCT 参数。下面的代码示例先按照 FirstNameLastName 过滤行,再更新其中的 LastName

C#

public static async Task UpdateUsingDmlWithStructCoreAsync(
    string projectId,
    string instanceId,
    string databaseId)
{
    var nameStruct = new SpannerStruct
    {
        { "FirstName", SpannerDbType.String, "Timothy" },
        { "LastName", SpannerDbType.String, "Campbell" },
    };
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";

    // Create connection to Cloud Spanner.
    using (var connection =
        new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        SpannerCommand cmd = connection.CreateDmlCommand(
            "UPDATE Singers SET LastName = 'Grant' "
           + "WHERE STRUCT<FirstName STRING, LastName STRING>"
           + "(FirstName, LastName) = @name");
        cmd.Parameters.Add("name", nameStruct.GetSpannerDbType(), nameStruct);
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) updated...");
    }
}

Go


import (
	"context"
	"fmt"
	"io"

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

func updateUsingDMLStruct(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 {
		type name struct {
			FirstName string
			LastName  string
		}
		var singerInfo = name{"Timothy", "Campbell"}

		stmt := spanner.Statement{
			SQL: `Update Singers Set LastName = 'Grant'
				WHERE STRUCT<FirstName String, LastName String>(Firstname, LastName) = @name`,
			Params: map[string]interface{}{"name": singerInfo},
		}
		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

static void updateUsingDmlWithStruct(DatabaseClient dbClient) {
  Struct name =
      Struct.newBuilder().set("FirstName").to("Timothy").set("LastName").to("Campbell").build();
  Statement s =
      Statement.newBuilder(
              "UPDATE Singers SET LastName = 'Grant' "
                  + "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
                  + "= @name")
          .bind("name")
          .to(name)
          .build();
  dbClient
      .readWriteTransaction()
      .run(
          new TransactionCallable<Void>() {
            @Override
            public Void run(TransactionContext transaction) throws Exception {
              long rowCount = transaction.executeUpdate(s);
              System.out.printf("%d record updated.\n", rowCount);
              return null;
            }
          });
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

const nameStruct = Spanner.struct({
  FirstName: 'Timothy',
  LastName: 'Campbell',
});

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: `UPDATE Singers SET LastName = 'Grant'
      WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name`,
      params: {
        name: nameStruct,
      },
    });

    console.log(`Successfully updated ${rowCount} record.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    database.close();
  }
});

PHP

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Database;
use Google\Cloud\Spanner\Transaction;
use Google\Cloud\Spanner\StructType;
use Google\Cloud\Spanner\StructValue;

/**
 * Update data with a DML statement using Structs.
 *
 * The database and table must already exist and can be created using
 * `create_database`.
 * Example:
 * ```
 * insert_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_dml_structs($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $database->runTransaction(function (Transaction $t) use ($spanner) {
        $nameValue = (new StructValue)
            ->add('FirstName', 'Timothy')
            ->add('LastName', 'Campbell');
        $nameType = (new StructType)
            ->add('FirstName', Database::TYPE_STRING)
            ->add('LastName', Database::TYPE_STRING);

        $rowCount = $t->executeUpdate(
            "UPDATE Singers SET LastName = 'Grant' "
             . "WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
             . "= @name",
            [
                'parameters' => [
                    'name' => $nameValue
                ],
                'types' => [
                    'name' => $nameType
                ]
            ]);
        $t->commit();
        printf('Updated %d row(s).' . PHP_EOL, $rowCount);
    });
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

record_type = param_types.Struct(
    [
        param_types.StructField("FirstName", param_types.STRING),
        param_types.StructField("LastName", param_types.STRING),
    ]
)
record_value = ("Timothy", "Campbell")

def write_with_struct(transaction):
    row_ct = transaction.execute_update(
        "UPDATE Singers SET LastName = 'Grant' "
        "WHERE STRUCT<FirstName STRING, LastName STRING>"
        "(FirstName, LastName) = @name",
        params={"name": record_value},
        param_types={"name": record_type},
    )
    print("{} record(s) updated.".format(row_ct))

database.run_in_transaction(write_with_struct)

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id
row_count = 0
name_struct = { FirstName: "Timothy", LastName: "Campbell" }

client.transaction do |transaction|
  row_count = transaction.execute_update(
    "UPDATE Singers SET LastName = 'Grant'
     WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) = @name",
    params: { name: name_struct }
  )
end

puts "#{row_count} record updated."

提交时间戳

使用 PENDING_COMMIT_TIMESTAMP 函数在 TIMESTAMP 列中写入提交时间戳。必须将该列的 allow_commit_timestamp 选项设置为 true。以下 DML 语句使用提交时间戳更新 Singers 表中的 LastUpdated 列:

UPDATE Singers SET LastUpdated = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1;

如需详细了解如何在 DML 中使用提交时间戳,请参阅写入提交时间戳