本頁面說明如何使用資料操縱語言 (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 陳述式，但這會強制 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 陳述式。
前往 Spanner「Instances」(執行個體) 頁面。
在工具列的下拉式清單中選取專案。
點選包含您的資料庫的執行個體名稱，前往「執行個體詳細資料」頁面。
在「總覽」分頁中，點選資料庫名稱。 「資料庫詳細資料」頁面便會出現。
按一下「Spanner Studio」。
輸入 DML 陳述式。例如，以下陳述式會在
Singers資料表新增資料列。INSERT Singers (SingerId, FirstName, LastName) VALUES (1, 'Marc', 'Richards')按一下「執行查詢」。 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('@google-cloud/spanner');
/**
* 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: 'INSERT Singers (SingerId, FirstName, LastName) VALUES (10, @firstName, @lastName)',
params: {
firstName: 'Virginia',
lastName: 'Watson',
},
});
console.log(
`Successfully inserted ${rowCount} record into the Singers table.`,
);
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});PHP
請使用 executeUpdate() 方法執行 DML 陳述式。
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
* Inserts sample data into the given database with a DML statement.
*
* 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 insert_data_with_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$rowCount = $t->executeUpdate(
'INSERT Singers (SingerId, FirstName, LastName) '
. " VALUES (10, 'Virginia', 'Watson')");
$t->commit();
printf('Inserted %d row(s).' . PHP_EOL, $rowCount);
});
}Python
請使用 execute_update() 方法執行 DML 陳述式。
# 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)
def insert_singers(transaction):
row_ct = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) "
" VALUES (10, 'Virginia', 'Watson')"
)
print("{} record(s) inserted.".format(row_ct))
database.run_in_transaction(insert_singers)Ruby
請使用 execute_update() 方法執行 DML 陳述式。
# 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
client.transaction do |transaction|
row_count = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')"
)
end
puts "#{row_count} record inserted."以下程式碼範例會依據 WHERE 子句，更新 Albums 資料表的 MarketingBudget 資料欄。
C++
void DmlStandardUpdate(google::cloud::spanner::Client client) {
using ::google::cloud::StatusOr;
namespace spanner = ::google::cloud::spanner;
auto commit_result = client.Commit(
[&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
auto update = client.ExecuteDml(
std::move(txn),
spanner::SqlStatement(
"UPDATE Albums SET MarketingBudget = MarketingBudget * 2"
" WHERE SingerId = 1 AND AlbumId = 1"));
if (!update) return std::move(update).status();
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Update was successful [spanner_dml_standard_update]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class UpdateUsingDmlCoreAsyncSample
{
public async Task<int> UpdateUsingDmlCoreAsync(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("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func updateUsingDML(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: `UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1`,
}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
return nil
})
return err
}
Java
static void updateUsingDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
String sql =
"UPDATE Albums "
+ "SET MarketingBudget = MarketingBudget * 2 "
+ "WHERE SingerId = 1 and AlbumId = 1";
long rowCount = transaction.executeUpdate(Statement.of(sql));
System.out.printf("%d record updated.\n", rowCount);
return null;
});
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* 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 Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1`,
});
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\Transaction;
/**
* Updates sample data in the database with a DML statement.
*
* This requires the `MarketingBudget` column which must be created before
* running this sample. You can add the column by running the `add_column`
* sample or by running this DDL statement against your database:
*
* ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
*
* Example:
* ```
* update_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$rowCount = $t->executeUpdate(
'UPDATE Albums '
. 'SET MarketingBudget = MarketingBudget * 2 '
. 'WHERE SingerId = 1 and AlbumId = 1');
$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)
def update_albums(transaction):
row_ct = transaction.execute_update(
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 1"
)
print("{} record(s) updated.".format(row_ct))
database.run_in_transaction(update_albums)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
client.transaction do |transaction|
row_count = transaction.execute_update(
"UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1"
)
end
puts "#{row_count} record updated."以下程式碼範例會刪除 Singers 資料表中 FirstName 資料欄為 Alice 的所有資料列。
C++
void DmlStandardDelete(google::cloud::spanner::Client client) {
using ::google::cloud::StatusOr;
namespace spanner = ::google::cloud::spanner;
auto commit_result = client.Commit([&client](spanner::Transaction txn)
-> StatusOr<spanner::Mutations> {
auto dele = client.ExecuteDml(
std::move(txn),
spanner::SqlStatement("DELETE FROM Singers WHERE FirstName = 'Alice'"));
if (!dele) return std::move(dele).status();
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Delete was successful [spanner_dml_standard_delete]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class DeleteUsingDmlCoreAsyncSample
{
public async Task<int> DeleteUsingDmlCoreAsync(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("DELETE FROM Singers WHERE FirstName = 'Alice'");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted...");
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func deleteUsingDML(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: `DELETE FROM Singers WHERE FirstName = 'Alice'`}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) deleted.\n", rowCount)
return nil
})
return err
}
Java
static void deleteUsingDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
String sql = "DELETE FROM Singers WHERE FirstName = 'Alice'";
long rowCount = transaction.executeUpdate(Statement.of(sql));
System.out.printf("%d record deleted.\n", rowCount);
return null;
});
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* 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: "DELETE FROM Singers WHERE FirstName = 'Alice'",
});
console.log(`Successfully deleted ${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\Transaction;
/**
* Deletes sample data in the database with a DML statement.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function delete_data_with_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$rowCount = $t->executeUpdate(
"DELETE FROM Singers WHERE FirstName = 'Alice'");
$t->commit();
printf('Deleted %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)
def delete_singers(transaction):
row_ct = transaction.execute_update(
"DELETE FROM Singers WHERE FirstName = 'Alice'"
)
print("{} record(s) deleted.".format(row_ct))
database.run_in_transaction(delete_singers)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
client.transaction do |transaction|
row_count = transaction.execute_update(
"DELETE FROM Singers WHERE FirstName = 'Alice'"
)
end
puts "#{row_count} record deleted."以下範例僅適用於 GoogleSQL 方言資料庫，使用STRUCT 與繫結參數，更新依 FirstName 和 LastName 篩選的資料列中的 LastName。
GoogleSQL
C++
void DmlStructs(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
std::int64_t rows_modified = 0;
auto commit_result =
client.Commit([&client, &rows_modified](spanner::Transaction const& txn)
-> google::cloud::StatusOr<spanner::Mutations> {
auto singer_info = std::make_tuple("Marc", "Richards");
auto sql = spanner::SqlStatement(
"UPDATE Singers SET FirstName = 'Keith' WHERE "
"STRUCT<FirstName String, LastName String>(FirstName, LastName) "
"= @name",
{{"name", spanner::Value(std::move(singer_info))}});
auto dml_result = client.ExecuteDml(txn, std::move(sql));
if (!dml_result) return std::move(dml_result).status();
rows_modified = dml_result->RowsModified();
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << rows_modified
<< " update was successful [spanner_dml_structs]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class UpdateUsingDmlWithStructCoreAsyncSample
{
public async Task<int> 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}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
using var 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...");
return rowCount;
}
}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(transaction -> {
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(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$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."使用傳回的 DML 陳述式修改資料
THEN RETURN 子句 (GoogleSQL 方言資料庫) 或 RETURNING 子句 (PostgreSQL 方言資料庫) 適用於要從修改過的資料列擷取資料的案例。如果您想查看 DML 陳述式中的未指定值、預設值或產生的資料欄，這項功能就特別實用。
如要使用用戶端程式庫執行傳回 DML 陳述式：
- 建立讀取/寫入交易。
- 呼叫用戶端程式庫方法以執行查詢，並傳入傳回的 DML 陳述式來取得結果。
以下程式碼範例會在 Singers 資料表中插入新資料列，並傳回插入記錄的產生資料欄 FullName。
GoogleSQL
C++
void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
// Insert records into SINGERS table and return the generated column
// FullName of the inserted records using `THEN RETURN FullName`.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (12, 'Melissa', 'Garcia'),
(13, 'Russell', 'Morales'),
(14, 'Jacqueline', 'Long'),
(15, 'Dylan', 'Shaw')
THEN RETURN FullName
)""");
using RowType = std::tuple<std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "FullName: " << std::get<0>(*row) << "\n";
}
std::cout << "Inserted row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class InsertUsingDmlReturningAsyncSample
{
public async Task<List<string>> InsertUsingDmlReturningAsync(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();
// Insert records into the SINGERS table and return the
// generated column FullName of the inserted records using
// 'THEN RETURN FullName'.
// It is also possible to return all columns of all the
// inserted records by using 'THEN RETURN *'.
using var cmd = connection.CreateDmlCommand(
@"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES
(6, 'Melissa', 'Garcia'),
(7, 'Russell', 'Morales'),
(8, 'Jacqueline', 'Long'),
(9, 'Dylan', 'Shaw') THEN RETURN FullName");
var reader = await cmd.ExecuteReaderAsync();
var insertedSingerNames = new List<string>();
while (await reader.ReadAsync())
{
insertedSingerNames.Add(reader.GetFieldValue<string>("FullName"));
}
Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");
return insertedSingerNames;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func insertUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Insert records into the SINGERS table and returns the
// generated column FullName of the inserted records using
// 'THEN RETURN FullName'.
// It is also possible to return all columns of all the
// inserted records by using 'THEN RETURN *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (21, 'Melissa', 'Garcia'),
(22, 'Russell', 'Morales'),
(23, 'Jacqueline', 'Long'),
(24, 'Dylan', 'Shaw')
THEN RETURN FullName`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var fullName string
if err := row.Columns(&fullName); err != nil {
return err
}
fmt.Fprintf(w, "%s\n", fullName)
}
fmt.Fprintf(w, "%d record(s) inserted.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class InsertUsingDmlReturningSample {
static void insertUsingDmlReturning() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
insertUsingDmlReturning(projectId, instanceId, databaseId);
}
static void insertUsingDmlReturning(String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Insert records into the SINGERS table and returns the
// generated column FullName of the inserted records using
// ‘THEN RETURN FullName’.
// It is also possible to return all columns of all the
// inserted records by using ‘THEN RETURN *’.
dbClient
.readWritreadWriteTransaction .run(
transaction -> {
String sql =
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
+ "(12, 'Melissa', 'Garcia'), "
+ "(13, 'Russell', 'Morales'), "
+ "(14, 'Jacqueline', 'Long'), "
+ "(15, 'Dylan', 'Shaw') THEN RETURN FullName";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSeResultSetet = transaction.executeQuery(StatemenStatement)) {
while (resultSet.next()) {
System.out.println(resultSet.getString(0));
}
System.out.printf(
"Inserted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* 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,
});
function insertUsingDmlReturning(instanceId, databaseId) {
// 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 [rows, stats] = await transaction.run({
sql: 'INSERT Singers (SingerId, FirstName, LastName) VALUES (@id, @firstName, @lastName) THEN RETURN FullName',
params: {
id: 18,
firstName: 'Virginia',
lastName: 'Watson',
},
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully inserted ${rowCount} record into the Singers table.`,
);
rows.forEach(row => {
console.log(row.toJSON().FullName);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
insertUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Inserts sample data into the given database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function insert_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
// Insert records into SINGERS table and returns the generated column
// FullName of the inserted records using ‘THEN RETURN FullName’. It is also
// possible to return all columns of all the inserted records by using
// ‘THEN RETURN *’.
$sql = 'INSERT INTO Singers (SingerId, FirstName, LastName) '
. "VALUES (12, 'Melissa', 'Garcia'), "
. "(13, 'Russell', 'Morales'), "
. "(14, 'Jacqueline', 'Long'), "
. "(15, 'Dylan', 'Shaw') "
. 'THEN RETURN FullName';
$transaction = $database->transaction();
$result = $transaction->execute($sql);
foreach ($result->rows() as $row) {
printf(
'%s inserted.' . PHP_EOL,
$row['FullName'],
);
}
printf(
'Inserted row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}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)
# Insert records into the SINGERS table and returns the
# generated column FullName of the inserted records using
# 'THEN RETURN FullName'.
# It is also possible to return all columns of all the
# inserted records by using 'THEN RETURN *'.
def insert_singers(transaction):
results = transaction.execute_sql(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
"(21, 'Luann', 'Chizoba'), "
"(22, 'Denis', 'Patricio'), "
"(23, 'Felxi', 'Ronan'), "
"(24, 'Dominik', 'Martyna') "
"THEN RETURN FullName"
)
for result in results:
print("FullName: {}".format(*result))
print("{} record(s) inserted.".format(results.stats.row_count_exact))
database.run_in_transaction(insert_singers)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with insert
# operation.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_insert_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Insert records into the SINGERS table and returns the generated column
# FullName of the inserted records using ‘THEN RETURN FullName’.
# It is also possible to return all columns of all the inserted records
# by using ‘THEN RETURN *’.
results = transaction.execute_query "INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')
THEN RETURN FullName"
results.rows.each do |row|
puts "Inserted singers with FullName: #{row[:FullName]}"
end
puts "Inserted row(s) count: #{results.row_count}"
end
end
PostgreSQL
C++
void InsertUsingDmlReturning(google::cloud::spanner::Client client) {
// Insert records into SINGERS table and return the generated column
// FullName of the inserted records using `RETURNING FullName`.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (12, 'Melissa', 'Garcia'),
(13, 'Russell', 'Morales'),
(14, 'Jacqueline', 'Long'),
(15, 'Dylan', 'Shaw')
RETURNING FullName
)""");
using RowType = std::tuple<std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "FullName: " << std::get<0>(*row) << "\n";
}
std::cout << "Inserted row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class InsertUsingDmlReturningAsyncPostgresSample
{
public async Task<List<string>> InsertUsingDmlReturningAsyncPostgres(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();
// Insert records into SINGERS table and return the
// generated column FullName of the inserted records
// using 'RETURNING FullName'.
// It is also possible to return all columns of all the
// inserted records by using 'RETURNING *'.
using var cmd = connection.CreateDmlCommand(
@"INSERT INTO Singers(SingerId, FirstName, LastName) VALUES
(6, 'Melissa', 'Garcia'),
(7, 'Russell', 'Morales'),
(8, 'Jacqueline', 'Long'),
(9, 'Dylan', 'Shaw') RETURNING FullName");
var reader = await cmd.ExecuteReaderAsync();
var insertedSingerNames = new List<string>();
while (await reader.ReadAsync())
{
insertedSingerNames.Add(reader.GetFieldValue<string>("fullname"));
}
Console.WriteLine($"{insertedSingerNames.Count} row(s) inserted...");
return insertedSingerNames;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func pgInsertUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Insert records into the SINGERS table and returns the
// generated column FullName of the inserted records using
// 'RETURNING FullName'.
// It is also possible to return all columns of all the
// inserted records by using 'RETURNING *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (21, 'Melissa', 'Garcia'),
(22, 'Russell', 'Morales'),
(23, 'Jacqueline', 'Long'),
(24, 'Dylan', 'Shaw')
RETURNING FullName`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var fullName string
if err := row.Columns(&fullName); err != nil {
return err
}
fmt.Fprintf(w, "%s\n", fullName)
}
fmt.Fprintf(w, "%d record(s) inserted.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class PgInsertUsingDmlReturningSample {
static void insertUsingDmlReturning() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
insertUsingDmlReturning(projectId, instanceId, databaseId);
}
static void insertUsingDmlReturning(String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Insert records into SINGERS table and returns the
// generated column FullName of the inserted records
// using ‘RETURNING FullName’.
// It is also possible to return all columns of all the
// inserted records by using ‘RETURNING *’.
dbClient
.readWritreadWriteTransaction .run(
transaction -> {
String sql =
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
+ "(12, 'Melissa', 'Garcia'), "
+ "(13, 'Russell', 'Morales'), "
+ "(14, 'Jacqueline', 'Long'), "
+ "(15, 'Dylan', 'Shaw') RETURNING FullName";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSeResultSetet = transaction.executeQuery(StatemenStatement)) {
while (resultSet.next()) {
System.out.println(resultSet.getString(0));
}
System.out.printf(
"Inserted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* 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,
});
function pgInsertUsingDmlReturning(instanceId, databaseId) {
// 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 [rows, stats] = await transaction.run({
sql: 'INSERT Into Singers (SingerId, FirstName, LastName) VALUES ($1, $2, $3) RETURNING FullName',
params: {
p1: 18,
p2: 'Virginia',
p3: 'Watson',
},
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully inserted ${rowCount} record into the Singers table.`,
);
rows.forEach(row => {
console.log(row.toJSON().fullname);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
pgInsertUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Inserts sample data into the given postgresql database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function pg_insert_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
// Insert records into SINGERS table and returns the generated column
// FullName of the inserted records using ‘RETURNING FullName’. It is also
// possible to return all columns of all the inserted records by using
// ‘RETURNING *’.
$sql = 'INSERT INTO Singers (Singerid, FirstName, LastName) '
. "VALUES (12, 'Melissa', 'Garcia'), "
. "(13, 'Russell', 'Morales'), "
. "(14, 'Jacqueline', 'Long'), "
. "(15, 'Dylan', 'Shaw') "
. 'RETURNING FullName';
$transaction = $database->transaction();
$result = $transaction->execute($sql);
foreach ($result->rows() as $row) {
printf(
'%s inserted.' . PHP_EOL,
$row['fullname'],
);
}
printf(
'Inserted row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}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)
# Insert records into the SINGERS table and returns the
# generated column FullName of the inserted records using
# 'RETURNING FullName'.
# It is also possible to return all columns of all the
# inserted records by using 'RETURNING *'.
def insert_singers(transaction):
results = transaction.execute_sql(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES "
"(21, 'Luann', 'Chizoba'), "
"(22, 'Denis', 'Patricio'), "
"(23, 'Felxi', 'Ronan'), "
"(24, 'Dominik', 'Martyna') "
"RETURNING FullName"
)
for result in results:
print("FullName: {}".format(*result))
print("{} record(s) inserted.".format(results.stats.row_count_exact))
database.run_in_transaction(insert_singers)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with insert
# operation in PostgreSql.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_insert_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Insert records into SINGERS table and returns the generated column
# FullName of the inserted records using ‘RETURNING FullName’.
# It is also possible to return all columns of all the inserted
# records by using ‘RETURNING *’.
results = transaction.execute_query "INSERT INTO Singers (SingerId, FirstName, LastName)
VALUES (12, 'Melissa', 'Garcia'), (13, 'Russell', 'Morales'), (14, 'Jacqueline', 'Long'), (15, 'Dylan', 'Shaw')
RETURNING FullName"
results.rows.each do |row|
puts "Inserted singers with FullName: #{row[:fullname]}"
end
puts "Inserted row(s) count: #{results.row_count}"
end
end
以下程式碼範例會依據 WHERE 子句，更新 Albums 資料表的 MarketingBudget 資料欄，並傳回更新後記錄的修改 MarketingBudget 資料欄。
GoogleSQL
C++
void UpdateUsingDmlReturning(google::cloud::spanner::Client client) {
// Update MarketingBudget column for records satisfying a particular
// condition and return the modified MarketingBudget column of the
// updated records using `THEN RETURN MarketingBudget`.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 AND AlbumId = 1
THEN RETURN MarketingBudget
)""");
using RowType = std::tuple<absl::optional<std::int64_t>>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "MarketingBudget: ";
if (std::get<0>(*row).has_value()) {
std::cout << *std::get<0>(*row);
} else {
std::cout << "NULL";
}
std::cout << "\n";
}
std::cout << "Updated row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class UpdateUsingDmlReturningAsyncSample
{
public async Task<List<long>> UpdateUsingDmlReturningAsync(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();
// Update MarketingBudget column for records satisfying
// a particular condition and return the modified
// MarketingBudget column of the updated records using
// 'THEN RETURN MarketingBudget'.
// It is also possible to return all columns of all the
// updated records by using 'THEN RETURN *'.
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1 THEN RETURN MarketingBudget");
var reader = await cmd.ExecuteReaderAsync();
var updatedMarketingBudgets = new List<long>();
while (await reader.ReadAsync())
{
updatedMarketingBudgets.Add(reader.GetFieldValue<long>("MarketingBudget"));
}
Console.WriteLine($"{updatedMarketingBudgets.Count} row(s) updated...");
return updatedMarketingBudgets;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func updateUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Update MarketingBudget column for records satisfying
// a particular condition and returns the modified
// MarketingBudget column of the updated records using
// 'THEN RETURN MarketingBudget'.
// It is also possible to return all columns of all the
// updated records by using 'THEN RETURN *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1
THEN RETURN MarketingBudget`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var marketingBudget int64
if err := row.Columns(&marketingBudget); err != nil {
return err
}
fmt.Fprintf(w, "%d\n", marketingBudget)
}
fmt.Fprintf(w, "%d record(s) updated.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class UpdateUsingDmlReturningSample {
static void updateUsingDmlReturning() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
updateUsingDmlReturning(projectId, instanceId, databaseId);
}
static void updateUsingDmlReturning(String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Update MarketingBudget column for records satisfying
// a particular condition and returns the modified
// MarketingBudget column of the updated records using
// ‘THEN RETURN MarketingBudget’.
// It is also possible to return all columns of all the
// updated records by using ‘THEN RETURN *’.
dbClient
.readWritreadWriteTransaction .run(
transaction -> {
String sql =
"UPDATE Albums "
+ "SET MarketingBudget = MarketingBudget * 2 "
+ "WHERE SingerId = 1 and AlbumId = 1 "
+ "THEN RETURN MarketingBudget";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSeResultSetet = transaction.executeQuery(StatemenStatement)) {
while (resultSet.next()) {
System.out.printf("%d\n", resultSet.getLong(0));
}
System.out.printf(
"Updated row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* 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,
});
function updateUsingDmlReturning(instanceId, databaseId) {
// 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 [rows, stats] = await transaction.run({
sql: 'UPDATE Albums SET MarketingBudget = 2000000 WHERE SingerId = 1 and AlbumId = 1 THEN RETURN MarketingBudget',
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully updated ${rowCount} record into the Albums table.`,
);
rows.forEach(row => {
console.log(row.toJSON().MarketingBudget);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
updateUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Update the given database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$transaction = $database->transaction();
// Update MarketingBudget column for records satisfying a particular
// condition and returns the modified MarketingBudget column of the updated
// records using ‘THEN RETURN MarketingBudget’. It is also possible to return
// all columns of all the updated records by using ‘THEN RETURN *’.
$result = $transaction->execute(
'UPDATE Albums '
. 'SET MarketingBudget = MarketingBudget * 2 '
. 'WHERE SingerId = 1 and AlbumId = 1 '
. 'THEN RETURN MarketingBudget'
);
foreach ($result->rows() as $row) {
printf('MarketingBudget: %s' . PHP_EOL, $row['MarketingBudget']);
}
printf(
'Updated row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}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)
# Update MarketingBudget column for records satisfying
# a particular condition and returns the modified
# MarketingBudget column of the updated records using
# 'THEN RETURN MarketingBudget'.
# It is also possible to return all columns of all the
# updated records by using 'THEN RETURN *'.
def update_albums(transaction):
results = transaction.execute_sql(
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 1 "
"THEN RETURN MarketingBudget"
)
for result in results:
print("MarketingBudget: {}".format(*result))
print("{} record(s) updated.".format(results.stats.row_count_exact))
database.run_in_transaction(update_albums)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with update
# operation.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_update_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Update MarketingBudget column for records satisfying a particular
# condition and returns the modified MarketingBudget column of the
# updated records using ‘THEN RETURN MarketingBudget’.
#
# It is also possible to return all columns of all the updated records
# by using ‘THEN RETURN *’.
results = transaction.execute_query "UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1
THEN RETURN MarketingBudget"
results.rows.each do |row|
puts "Updated Album with MarketingBudget: #{row[:MarketingBudget]}"
end
puts "Updated row(s) count: #{results.row_count}"
end
end
PostgreSQL
C++
void UpdateUsingDmlReturning(google::cloud::spanner::Client client) {
// Update MarketingBudget column for records satisfying a particular
// condition and return the modified MarketingBudget column of the
// updated records using `RETURNING MarketingBudget`.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 AND AlbumId = 1
RETURNING MarketingBudget
)""");
using RowType = std::tuple<absl::optional<std::int64_t>>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "MarketingBudget: ";
if (std::get<0>(*row).has_value()) {
std::cout << *std::get<0>(*row);
} else {
std::cout << "NULL";
}
std::cout << "\n";
}
std::cout << "Updated row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class UpdateUsingDmlReturningAsyncPostgresSample
{
public async Task<List<long>> UpdateUsingDmlReturningAsyncPostgres(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();
// Update MarketingBudget column for records satisfying
// a particular condition and return the modified
// MarketingBudget column of the updated records using
// 'RETURNING MarketingBudget'.
// It is also possible to return all columns of all the
// updated records by using 'RETURNING *'.
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 14 and AlbumId = 20 RETURNING MarketingBudget");
var reader = await cmd.ExecuteReaderAsync();
var updatedMarketingBudgets = new List<long>();
while (await reader.ReadAsync())
{
updatedMarketingBudgets.Add(reader.GetFieldValue<long>("marketingbudget"));
}
Console.WriteLine($"{updatedMarketingBudgets.Count} row(s) updated...");
return updatedMarketingBudgets;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func pgUpdateUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Update MarketingBudget column for records satisfying
// a particular condition and returns the modified
// MarketingBudget column of the updated records using
// 'RETURNING MarketingBudget'.
// It is also possible to return all columns of all the
// updated records by using 'RETURNING *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1
RETURNING MarketingBudget`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var marketingBudget int64
if err := row.Columns(&marketingBudget); err != nil {
return err
}
fmt.Fprintf(w, "%d\n", marketingBudget)
}
fmt.Fprintf(w, "%d record(s) updated.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class PgUpdateUsingDmlReturningSample {
static void updateUsingDmlReturning() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
updateUsingDmlReturning(projectId, instanceId, databaseId);
}
static void updateUsingDmlReturning(String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Update MarketingBudget column for records satisfying
// a particular condition and returns the modified
// MarketingBudget column of the updated records using
// ‘RETURNING MarketingBudget’.
// It is also possible to return all columns of all the
// updated records by using ‘RETURNING *’.
dbClient
.readWritreadWriteTransaction .run(
transaction -> {
String sql =
"UPDATE Albums "
+ "SET MarketingBudget = MarketingBudget * 2 "
+ "WHERE SingerId = 1 and AlbumId = 1 "
+ "RETURNING MarketingBudget";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSeResultSetet = transaction.executeQuery(StatemenStatement)) {
while (resultSet.next()) {
System.out.printf("%d\n", resultSet.getLong(0));
}
System.out.printf(
"Updated row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* 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,
});
function pgUpdateUsingDmlReturning(instanceId, databaseId) {
// 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 [rows, stats] = await transaction.run({
sql: 'UPDATE singers SET FirstName = $1, LastName = $2 WHERE singerid = $3 RETURNING FullName',
params: {
p1: 'Virginia1',
p2: 'Watson1',
p3: 18,
},
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully updated ${rowCount} record into the Singers table.`,
);
rows.forEach(row => {
console.log(row.toJSON().fullname);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
pgUpdateUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Update the given postgresql database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function pg_update_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$transaction = $database->transaction();
// Update MarketingBudget column for records satisfying a particular
// condition and returns the modified MarketingBudget column of the updated
// records using ‘RETURNING MarketingBudget’. It is also possible to return
// all columns of all the updated records by using ‘RETURNING *’.
$result = $transaction->execute(
'UPDATE Albums '
. 'SET MarketingBudget = MarketingBudget * 2 '
. 'WHERE SingerId = 1 and AlbumId = 1'
. 'RETURNING MarketingBudget'
);
foreach ($result->rows() as $row) {
printf('MarketingBudget: %s' . PHP_EOL, $row['marketingbudget']);
}
printf(
'Updated row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}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)
# Update MarketingBudget column for records satisfying
# a particular condition and returns the modified
# MarketingBudget column of the updated records using
# 'RETURNING MarketingBudget'.
# It is also possible to return all columns of all the
# updated records by using 'RETURNING *'.
def update_albums(transaction):
results = transaction.execute_sql(
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 1 "
"RETURNING MarketingBudget"
)
for result in results:
print("MarketingBudget: {}".format(*result))
print("{} record(s) updated.".format(results.stats.row_count_exact))
database.run_in_transaction(update_albums)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with update
# operation in PostgreSql.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_update_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Update MarketingBudget column for records satisfying a particular
# condition and returns the modified MarketingBudget column of the
# updated records using ‘RETURNING MarketingBudget’.
# It is also possible to return all columns of all the updated records
# by using ‘RETURNING *’.
results = transaction.execute_query "UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 1
RETURNING MarketingBudget"
results.rows.each do |row|
puts "Updated Albums with MarketingBudget: #{row[:marketingbudget]}"
end
puts "Updated row(s) count: #{results.row_count}"
end
end
以下程式碼範例會刪除 Singers 資料表中 FirstName 資料欄為 Alice 的所有資料列，並傳回已刪除記錄的 SingerId 和 FullName 資料欄。
GoogleSQL
C++
void DeleteUsingDmlReturning(google::cloud::spanner::Client client) {
// Delete records from SINGERS table satisfying a particular condition
// and return the SingerId and FullName column of the deleted records
// using `THEN RETURN SingerId, FullName'.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
DELETE FROM Singers
WHERE FirstName = 'Alice'
THEN RETURN SingerId, FullName
)""");
using RowType = std::tuple<std::int64_t, std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "SingerId: " << std::get<0>(*row) << " ";
std::cout << "FullName: " << std::get<1>(*row) << "\n";
}
std::cout << "Deleted row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
public class DeleteUsingDmlReturningAsyncSample
{
public async Task<List<string>> DeleteUsingDmlReturningAsync(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();
// Delete records from SINGERS table satisfying a
// particular condition and return the SingerId
// and FullName column of the deleted records using
// 'THEN RETURN SingerId, FullName'.
// It is also possible to return all columns of all the
// deleted records by using 'THEN RETURN *'.
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName");
var reader = await cmd.ExecuteReaderAsync();
var deletedSingerNames = new List<string>();
while (await reader.ReadAsync())
{
deletedSingerNames.Add(reader.GetFieldValue<string>("FullName"));
}
Console.WriteLine($"{deletedSingerNames.Count} row(s) deleted...");
return deletedSingerNames;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func deleteUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Delete records from SINGERS table satisfying a
// particular condition and returns the SingerId
// and FullName column of the deleted records using
// 'THEN RETURN SingerId, FullName'.
// It is also possible to return all columns of all the
// deleted records by using 'THEN RETURN *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `DELETE FROM Singers WHERE FirstName = 'Alice'
THEN RETURN SingerId, FullName`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var (
singerID int64
fullName string
)
if err := row.Columns(&singerID, &fullName); err != nil {
return err
}
fmt.Fprintf(w, "%d %s\n", singerID, fullName)
}
fmt.Fprintf(w, "%d record(s) deleted.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class DeleteUsingDmlReturningSample {
static void deleteUsingDmlReturningSample() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
deleteUsingDmlReturningSample(projectId, instanceId, databaseId);
}
static void deleteUsingDmlReturningSample(
String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Delete records from SINGERS table satisfying a
// particular condition and returns the SingerId
// and FullName column of the deleted records using
// ‘THEN RETURN SingerId, FullName’.
// It is also possible to return all columns of all the
// deleted records by using ‘THEN RETURN *’.
dbClient
.readWritreadWriteTransaction .run(
transaction -> {
String sql =
"DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSeResultSetet = transaction.executeQuery(StatemenStatement)) {
while (resultSet.next()) {
System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
}
System.out.printf(
"Deleted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* 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,
});
function deleteUsingDmlReturning(instanceId, databaseId) {
// 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 [rows, stats] = await transaction.run({
sql: 'DELETE FROM Singers WHERE SingerId = 18 THEN RETURN FullName',
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully deleted ${rowCount} record from the Singers table.`,
);
rows.forEach(row => {
console.log(row.toJSON().FullName);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
deleteUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Delete data from the given database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function delete_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$transaction = $database->transaction();
// Delete records from SINGERS table satisfying a particular condition and
// returns the SingerId and FullName column of the deleted records using
// 'THEN RETURN SingerId, FullName'. It is also possible to return all columns
// of all the deleted records by using 'THEN RETURN *'.
$result = $transaction->execute(
"DELETE FROM Singers WHERE FirstName = 'Alice' "
. 'THEN RETURN SingerId, FullName',
);
foreach ($result->rows() as $row) {
printf(
'%d %s.' . PHP_EOL,
$row['SingerId'],
$row['FullName']
);
}
printf(
'Deleted row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}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)
# Delete records from SINGERS table satisfying a
# particular condition and returns the SingerId
# and FullName column of the deleted records using
# 'THEN RETURN SingerId, FullName'.
# It is also possible to return all columns of all the
# deleted records by using 'THEN RETURN *'.
def delete_singers(transaction):
results = transaction.execute_sql(
"DELETE FROM Singers WHERE FirstName = 'David' "
"THEN RETURN SingerId, FullName"
)
for result in results:
print("SingerId: {}, FullName: {}".format(*result))
print("{} record(s) deleted.".format(results.stats.row_count_exact))
database.run_in_transaction(delete_singers)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with delete
# operation.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_delete_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Delete records from SINGERS table satisfying a particular condition and
# returns the SingerId and FullName column of the deleted records using
# ‘THEN RETURN SingerId, FullName’.
# It is also possible to return all columns of all the deleted records
# by using ‘THEN RETURN *’.
results = transaction.execute_query "DELETE FROM Singers WHERE FirstName = 'Alice' THEN RETURN SingerId, FullName"
results.rows.each do |row|
puts "Deleted singer with SingerId: #{row[:SingerId]}, FullName: #{row[:FullName]}"
end
puts "Deleted row(s) count: #{results.row_count}"
end
end
PostgreSQL
C++
void DeleteUsingDmlReturning(google::cloud::spanner::Client client) {
// Delete records from SINGERS table satisfying a particular condition
// and return the SingerId and FullName column of the deleted records
// using `RETURNING SingerId, FullName'.
auto commit = client.Commit(
[&client](google::cloud::spanner::Transaction txn)
-> google::cloud::StatusOr<google::cloud::spanner::Mutations> {
auto sql = google::cloud::spanner::SqlStatement(R"""(
DELETE FROM Singers
WHERE FirstName = 'Alice'
RETURNING SingerId, FullName
)""");
using RowType = std::tuple<std::int64_t, std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(sql));
for (auto& row : google::cloud::spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "SingerId: " << std::get<0>(*row) << " ";
std::cout << "FullName: " << std::get<1>(*row) << "\n";
}
std::cout << "Deleted row(s) count: " << rows.RowsModified() << "\n";
return google::cloud::spanner::Mutations{};
});
if (!commit) throw std::move(commit).status();
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
public class DeleteUsingDmlReturningAsyncPostgresSample
{
public async Task<List<string>> DeleteUsingDmlReturningAsyncPostgres(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();
// Delete records from SINGERS table satisfying a
// particular condition and return the SingerId
// and FullName column of the deleted records using
// 'RETURNING SingerId, FullName'.
// It is also possible to return all columns of all the
// deleted records by using 'RETURNING *'.
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Lata' RETURNING SingerId, FullName");
var reader = await cmd.ExecuteReaderAsync();
var deletedSingerNames = new List<string>();
while (await reader.ReadAsync())
{
deletedSingerNames.Add(reader.GetFieldValue<string>("fullname"));
}
Console.WriteLine($"{deletedSingerNames.Count} row(s) deleted...");
return deletedSingerNames;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func pgDeleteUsingDMLReturning(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
// Delete records from SINGERS table satisfying a
// particular condition and returns the SingerId
// and FullName column of the deleted records using
// 'RETURNING SingerId, FullName'.
// It is also possible to return all columns of all the
// deleted records by using 'RETURNING *'.
_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
stmt := spanner.Statement{
SQL: `DELETE FROM Singers WHERE FirstName = 'Alice'
RETURNING SingerId, FullName`,
}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done {
break
}
if err != nil {
return err
}
var (
singerID int64
fullName string
)
if err := row.Columns(&singerID, &fullName); err != nil {
return err
}
fmt.Fprintf(w, "%d %s\n", singerID, fullName)
}
fmt.Fprintf(w, "%d record(s) deleted.\n", iter.RowCount)
return nil
})
return err
}
Java
import com.google.cloud.spanner.DatabaseClient;
import com.google.cloud.spanner.DatabaseId;
import com.google.cloud.spanner.ResultSet;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.google.cloud.spanner.Statement;
public class PgDeleteUsingDmlReturningSample {
static void deleteUsingDmlReturningSample() {
// TODO(developer): Replace these variables before running the sample.
final String projectId = "my-project";
final String instanceId = "my-instance";
final String databaseId = "my-database";
deleteUsingDmlReturningSample(projectId, instanceId, databaseId);
}
static void deleteUsingDmlReturningSample(
String projectId, String instanceId, String databaseId) {
try (Spanner spanner =
SpannerOptions.newBuilder()
.setProjectId(projectId)
.build()
.getService()) {
final DatabaseClient dbClient =
spanner.getDatabaseClient(DatabaseId.of(projectId, instanceId, databaseId));
// Delete records from SINGERS table satisfying a
// particular condition and returns the SingerId
// and FullName column of the deleted records using
// ‘RETURNING SingerId, FullName’.
// It is also possible to return all columns of all the
// deleted records by using ‘RETURNING *’.
dbClient
.readWritreadWriteTransaction .run(
transaction -> {
String sql =
"DELETE FROM Singers WHERE FirstName = 'Alice' RETURNING SingerId, FullName";
// readWriteTransaction.executeQuery(..) API should be used for executing
// DML statements with RETURNING clause.
try (ResultSeResultSetet = transaction.executeQuery(StatemenStatement)) {
while (resultSet.next()) {
System.out.printf("%d %s\n", resultSet.getLong(0), resultSet.getString(1));
}
System.out.printf(
"Deleted row(s) count: %d\n", resultSet.getStats().getRowCountExact());
}
return null;
});
}
}
}Node.js
// Imports the Google Cloud client library.
const {Spanner} = require('@google-cloud/spanner');
/**
* 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,
});
function pgDeleteUsingDmlReturning(instanceId, databaseId) {
// 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 [rows, stats] = await transaction.run({
sql: 'DELETE FROM Singers WHERE SingerId = 18 RETURNING FullName',
});
const rowCount = Math.floor(stats[stats.rowCount]);
console.log(
`Successfully deleted ${rowCount} record from the Singers table.`,
);
rows.forEach(row => {
console.log(row.toJSON().fullname);
});
await transaction.commit();
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}
});
}
pgDeleteUsingDmlReturning(instanceId, databaseId);PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Delete data from the given postgresql database using DML returning.
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function pg_delete_dml_returning(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$transaction = $database->transaction();
// Delete records from SINGERS table satisfying a particular condition and
// returns the SingerId and FullName column of the deleted records using
// ‘RETURNING SingerId, FullName’. It is also possible to return all columns
// of all the deleted records by using ‘RETURNING *’.
$result = $transaction->execute(
"DELETE FROM Singers WHERE FirstName = 'Alice' "
. 'RETURNING SingerId, FullName',
);
foreach ($result->rows() as $row) {
printf(
'%d %s.' . PHP_EOL,
$row['singerid'],
$row['fullname']
);
}
printf(
'Deleted row(s) count: %d' . PHP_EOL,
$result->stats()['rowCountExact']
);
$transaction->commit();
}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)
# Delete records from SINGERS table satisfying a
# particular condition and returns the SingerId
# and FullName column of the deleted records using
# 'RETURNING SingerId, FullName'.
# It is also possible to return all columns of all the
# deleted records by using 'RETURNING *'.
def delete_singers(transaction):
results = transaction.execute_sql(
"DELETE FROM Singers WHERE FirstName = 'David' "
"RETURNING SingerId, FullName"
)
for result in results:
print("SingerId: {}, FullName: {}".format(*result))
print("{} record(s) deleted.".format(results.stats.row_count_exact))
database.run_in_transaction(delete_singers)Ruby
require "google/cloud/spanner"
##
# This is a snippet for showcasing how to use DML return feature with delete
# operation in PostgreSql.
#
# @param project_id [String] The ID of the Google Cloud project.
# @param instance_id [String] The ID of the spanner instance.
# @param database_id [String] The ID of the database.
#
def spanner_postgresql_delete_dml_returning project_id:, instance_id:, database_id:
spanner = Google::Cloud::Spanner.new project: project_id
client = spanner.client instance_id, database_id
client.transaction do |transaction|
# Delete records from SINGERS table satisfying a particular condition and
# returns the SingerId and FullName column of the deleted records using
# ‘RETURNING SingerId, FullName’.
# It is also possible to return all columns of all the deleted records
# by using ‘RETURNING *’.
results = transaction.execute_query "DELETE FROM singers WHERE firstname = 'Alice' RETURNING SingerId, FullName"
results.rows.each do |row|
puts "Deleted singer with SingerId: #{row[:singerid]}, FullName: #{row[:fullname]}"
end
puts "Deleted row(s) count: #{results.row_count}"
end
end
讀取在相同交易中寫入的資料
使用 DML 陳述式所做的變更，對於相同交易中的後續陳述式而言是可見的。這與使用變異不同，變更必須在交易修訂後才可見。
Spanner 會檢查每個 DML 陳述式之後的限制。相較之下，使用變異時，Spanner 會緩衝用戶端的變異直到修訂為止，並在修訂時檢查限制。評估每個陳述式之後的限制可讓 Spanner 確保 DML 陳述式傳回的資料與結構定義一致。
下列範例會更新 Singers 資料表中的資料列，然後執行 SELECT 陳述式以列印新值。
C++
void DmlWriteThenRead(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
using ::google::cloud::StatusOr;
auto commit_result = client.Commit(
[&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
auto insert = client.ExecuteDml(
txn, spanner::SqlStatement(
"INSERT INTO Singers (SingerId, FirstName, LastName)"
" VALUES (11, 'Timothy', 'Campbell')"));
if (!insert) return std::move(insert).status();
// Read newly inserted record.
spanner::SqlStatement select(
"SELECT FirstName, LastName FROM Singers where SingerId = 11");
using RowType = std::tuple<std::string, std::string>;
auto rows = client.ExecuteQuery(std::move(txn), std::move(select));
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (auto const& row : spanner::StreamOf<RowType>(rows)) {
if (!row) return std::move(row).status();
std::cout << "FirstName: " << std::get<0>(*row) << "\t";
std::cout << "LastName: " << std::get<1>(*row) << "\n";
}
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Write then read succeeded [spanner_dml_write_then_read]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class WriteAndReadUsingDmlCoreAsyncSample
{
public async Task<int> WriteAndReadUsingDmlCoreAsync(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 createDmlCmd = connection.CreateDmlCommand(@"INSERT Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')");
int rowCount = await createDmlCmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) inserted...");
// Read newly inserted record.
using var createSelectCmd = connection.CreateSelectCommand(@"SELECT FirstName, LastName FROM Singers WHERE SingerId = 11");
using var reader = await createSelectCmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
Console.WriteLine($"{reader.GetFieldValue<string>("FirstName")} {reader.GetFieldValue<string>("LastName")}");
}
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
"google.golang.org/api/iterator"
)
func writeAndReadUsingDML(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 {
// Insert Record
stmt := spanner.Statement{
SQL: `INSERT Singers (SingerId, FirstName, LastName)
VALUES (11, 'Timothy', 'Campbell')`,
}
rowCount, err := txn.Update(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) inserted.\n", rowCount)
// Read newly inserted record
stmt = spanner.Statement{SQL: `SELECT FirstName, LastName FROM Singers WHERE SingerId = 11`}
iter := txn.Query(ctx, stmt)
defer iter.Stop()
for {
row, err := iter.Next()
if err == iterator.Done || err != nil {
break
}
var firstName, lastName string
if err := row.ColumnByName("FirstName", &firstName); err != nil {
return err
}
if err := row.ColumnByName("LastName", &lastName); err != nil {
return err
}
fmt.Fprintf(w, "Found record name with %s, %s", firstName, lastName)
}
return err
})
return err
}
Java
static void writeAndReadUsingDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
// Insert record.
String sql =
"INSERT INTO Singers (SingerId, FirstName, LastName) "
+ " VALUES (11, 'Timothy', 'Campbell')";
long rowCount = transaction.executeUpdate(Statement.of(sql));
System.out.printf("%d record inserted.\n", rowCount);
// Read newly inserted record.
sql = "SELECT FirstName, LastName FROM Singers WHERE SingerId = 11";
// We use a try-with-resource block to automatically release resources held by
// ResultSet.
try (ResultSet resultSet = transaction.executeQuery(Statement.of(sql))) {
while (resultSet.next()) {
System.out.printf(
"%s %s\n",
resultSet.getString("FirstName"), resultSet.getString("LastName"));
}
}
return null;
});
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* 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 {
await transaction.runUpdate({
sql: `INSERT Singers (SingerId, FirstName, LastName)
VALUES (11, 'Timothy', 'Campbell')`,
});
const [rows] = await transaction.run({
sql: 'SELECT FirstName, LastName FROM Singers',
});
rows.forEach(row => {
const json = row.toJSON();
console.log(`${json.FirstName} ${json.LastName}`);
});
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\Transaction;
/**
* Writes then reads data inside a Transaction with a DML statement.
*
* 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 write_read_with_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$database->runTransaction(function (Transaction $t) {
$rowCount = $t->executeUpdate(
'INSERT Singers (SingerId, FirstName, LastName) '
. " VALUES (11, 'Timothy', 'Campbell')");
printf('Inserted %d row(s).' . PHP_EOL, $rowCount);
$results = $t->execute('SELECT FirstName, LastName FROM Singers WHERE SingerId = 11');
foreach ($results as $row) {
printf('%s %s' . PHP_EOL, $row['FirstName'], $row['LastName']);
}
$t->commit();
});
}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)
def write_then_read(transaction):
# Insert record.
row_ct = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) "
" VALUES (11, 'Timothy', 'Campbell')"
)
print("{} record(s) inserted.".format(row_ct))
# Read newly inserted record.
results = transaction.execute_sql(
"SELECT FirstName, LastName FROM Singers WHERE SingerId = 11"
)
for result in results:
print("FirstName: {}, LastName: {}".format(*result))
database.run_in_transaction(write_then_read)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
client.transaction do |transaction|
row_count = transaction.execute_update(
"INSERT INTO Singers (SingerId, FirstName, LastName) VALUES (11, 'Timothy', 'Campbell')"
)
puts "#{row_count} record updated."
transaction.execute("SELECT FirstName, LastName FROM Singers WHERE SingerId = 11").rows.each do |row|
puts "#{row[:FirstName]} #{row[:LastName]}"
end
end取得查詢計畫
您可以使用 Google Cloud 控制台、用戶端程式庫和 gcloud 指令列工具來擷取查詢計畫。
使用分區 DML
分區 DML 是為了大量資料更新與刪除而設計，特別是定期的清理和補充作業。
使用 Google Cloud CLI 執行陳述式
如要執行分區 DML 陳述式，請使用具備 --enable-partitioned-dml 選項的 gcloud spanner databases execute-sql 指令。以下範例會更新 Albums 資料表中的資料列。
gcloud spanner databases execute-sql example-db \ --instance=test-instance --enable-partitioned-dml \ --sql='UPDATE Albums SET MarketingBudget = 0 WHERE MarketingBudget IS NULL'
使用用戶端程式庫修改資料
以下程式碼範例會更新 Albums 資料表的 MarketingBudget 資料欄。
C++
使用 ExecutePartitionedDml() 函式執行分區 DML 陳述式。
void DmlPartitionedUpdate(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto result = client.ExecutePartitionedDml(
spanner::SqlStatement("UPDATE Albums SET MarketingBudget = 100000"
" WHERE SingerId > 1"));
if (!result) throw std::move(result).status();
std::cout << "Updated at least " << result->row_count_lower_bound
<< " row(s) [spanner_dml_partitioned_update]\n";
}C#
使用 ExecutePartitionedUpdateAsync() 方法執行分區 DML 陳述式。
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class UpdateUsingPartitionedDmlCoreAsyncSample
{
public async Task<long> UpdateUsingPartitionedDmlCoreAsync(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("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1");
long rowCount = await cmd.ExecutePartitionedUpdateAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return rowCount;
}
}Go
使用 PartitionedUpdate() 方法執行分區 DML 陳述式。
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func updateUsingPartitionedDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
stmt := spanner.Statement{SQL: "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"}
rowCount, err := client.PartitionedUpdate(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
return nil
}
Java
使用 executePartitionedUpdate() 方法執行分區 DML 陳述式。
static void updateUsingPartitionedDml(DatabaseClient dbClient) {
String sql = "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1";
long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
System.out.printf("%d records updated.\n", rowCount);
}Node.js
使用 runPartitionedUpdate() 方法執行分區 DML 陳述式。
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* 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);
try {
const [rowCount] = await database.runPartitionedUpdate({
sql: 'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1',
});
console.log(`Successfully updated ${rowCount} records.`);
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}PHP
使用 executePartitionedUpdate() 方法執行分區 DML 陳述式。
use Google\Cloud\Spanner\SpannerClient;
/**
* Updates sample data in the database by partition with a DML statement.
*
* This updates the `MarketingBudget` column which must be created before
* running this sample. You can add the column by running the `add_column`
* sample or by running this DDL statement against your database:
*
* ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
*
* Example:
* ```
* update_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_partitioned_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$rowCount = $database->executePartitionedUpdate(
'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1'
);
printf('Updated %d row(s).' . PHP_EOL, $rowCount);
}Python
使用 execute_partitioned_dml() 方法執行分區 DML 陳述式。
# 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)
row_ct = database.execute_partitioned_dml(
"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)
print("{} records updated.".format(row_ct))Ruby
使用 execute_partitioned_update() 方法執行分區 DML 陳述式。
# 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 = client.execute_partition_update(
"UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)
puts "#{row_count} records updated."以下程式碼範例會依據 SingerId 資料欄，從 Singers 資料表刪除列。
C++
void DmlPartitionedDelete(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto result = client.ExecutePartitionedDml(
spanner::SqlStatement("DELETE FROM Singers WHERE SingerId > 10"));
if (!result) throw std::move(result).status();
std::cout << "Deleted at least " << result->row_count_lower_bound
<< " row(s) [spanner_dml_partitioned_delete]\n";
}C#
using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;
public class DeleteUsingPartitionedDmlCoreAsyncSample
{
public async Task<long> DeleteUsingPartitionedDmlCoreAsync(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("DELETE FROM Singers WHERE SingerId > 10");
long rowCount = await cmd.ExecutePartitionedUpdateAsync();
Console.WriteLine($"{rowCount} row(s) deleted...");
return rowCount;
}
}Go
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func deleteUsingPartitionedDML(w io.Writer, db string) error {
ctx := context.Background()
client, err := spanner.NewClient(ctx, db)
if err != nil {
return err
}
defer client.Close()
stmt := spanner.Statement{SQL: "DELETE FROM Singers WHERE SingerId > 10"}
rowCount, err := client.PartitionedUpdate(ctx, stmt)
if err != nil {
return err
}
fmt.Fprintf(w, "%d record(s) deleted.", rowCount)
return nil
}
Java
static void deleteUsingPartitionedDml(DatabaseClient dbClient) {
String sql = "DELETE FROM Singers WHERE SingerId > 10";
long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
System.out.printf("%d records deleted.\n", rowCount);
}Node.js
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* 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);
try {
const [rowCount] = await database.runPartitionedUpdate({
sql: 'DELETE FROM Singers WHERE SingerId > 10',
});
console.log(`Successfully deleted ${rowCount} records.`);
} catch (err) {
console.error('ERROR:', err);
} finally {
// Close the database when finished.
database.close();
}PHP
use Google\Cloud\Spanner\SpannerClient;
/**
* Delete sample data in the database by partition with a DML statement.
*
* This updates the `MarketingBudget` column which must be created before
* running this sample. You can add the column by running the `add_column`
* sample or by running this DDL statement against your database:
*
* ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
*
* Example:
* ```
* update_data($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function delete_data_with_partitioned_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$rowCount = $database->executePartitionedUpdate(
'DELETE FROM Singers WHERE SingerId > 10'
);
printf('Deleted %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)
row_ct = database.execute_partitioned_dml("DELETE FROM Singers WHERE SingerId > 10")
print("{} record(s) deleted.".format(row_ct))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 = client.execute_partition_update(
"DELETE FROM Singers WHERE SingerId > 10"
)
puts "#{row_count} records deleted."使用批次 DML
如要避免多個序列請求造成延遲時間增加，則可使用批次 DML 在單一交易中傳送數個 INSERT、UPDATE 或 DELETE 陳述式：
C++
使用 ExecuteBatchDml() 函式執行 DML 陳述式清單。
void DmlBatchUpdate(google::cloud::spanner::Client client) {
namespace spanner = ::google::cloud::spanner;
auto commit_result =
client.Commit([&client](spanner::Transaction const& txn)
-> google::cloud::StatusOr<spanner::Mutations> {
std::vector<spanner::SqlStatement> statements = {
spanner::SqlStatement("INSERT INTO Albums"
" (SingerId, AlbumId, AlbumTitle,"
" MarketingBudget)"
" VALUES (1, 3, 'Test Album Title', 10000)"),
spanner::SqlStatement("UPDATE Albums"
" SET MarketingBudget = MarketingBudget * 2"
" WHERE SingerId = 1 and AlbumId = 3")};
auto result = client.ExecuteBatchDml(txn, statements);
if (!result) return std::move(result).status();
// Note: This mutator might be re-run, or its effects discarded, so
// changing non-transactional state (e.g., by producing output) is,
// in general, not something to be imitated.
for (std::size_t i = 0; i < result->stats.size(); ++i) {
std::cout << result->stats[i].row_count << " rows affected"
<< " for the statement " << (i + 1) << ".\n";
}
// Batch operations may have partial failures, in which case
// ExecuteBatchDml returns with success, but the application should
// verify that all statements completed successfully
if (!result->status.ok()) return result->status;
return spanner::Mutations{};
});
if (!commit_result) throw std::move(commit_result).status();
std::cout << "Update was successful [spanner_dml_batch_update]\n";
}C#
使用 connection.CreateBatchDmlCommand() 方法建立批次指令，接著使用 Add 方法新增 DML 陳述式，再透過 ExecuteNonQueryAsync() 方法執行這些陳述式。
using Google.Cloud.Spanner.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
public class UpdateUsingBatchDmlCoreAsyncSample
{
public async Task<int> UpdateUsingBatchDmlCoreAsync(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();
SpannerBatchCommand cmd = connection.CreateBatchDmlCommand();
cmd.Add("INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget) VALUES (1, 3, 'Test Album Title', 10000)");
cmd.Add("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 3");
IEnumerable<long> affectedRows = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"Executed {affectedRows.Count()} " + "SQL statements using Batch DML.");
return affectedRows.Count();
}
}Go
請使用 BatchUpdate() 方法執行 DML Statement 物件的陣列。
import (
"context"
"fmt"
"io"
"cloud.google.com/go/spanner"
)
func updateUsingBatchDML(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 {
stmts := []spanner.Statement{
{SQL: `INSERT INTO Albums
(SingerId, AlbumId, AlbumTitle, MarketingBudget)
VALUES (1, 3, 'Test Album Title', 10000)`},
{SQL: `UPDATE Albums
SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 3`},
}
rowCounts, err := txn.BatchUpdate(ctx, stmts)
if err != nil {
return err
}
fmt.Fprintf(w, "Executed %d SQL statements using Batch DML.\n", len(rowCounts))
return nil
})
return err
}
Java
請使用 transaction.batchUpdate() 方法執行多個 DML Statement 物件的 ArrayList。
static void updateUsingBatchDml(DatabaseClient dbClient) {
dbClient
.readWriteTransaction()
.run(transaction -> {
List<Statement> stmts = new ArrayList<Statement>();
String sql =
"INSERT INTO Albums "
+ "(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
+ "VALUES (1, 3, 'Test Album Title', 10000) ";
stmts.add(Statement.of(sql));
sql =
"UPDATE Albums "
+ "SET MarketingBudget = MarketingBudget * 2 "
+ "WHERE SingerId = 1 and AlbumId = 3";
stmts.add(Statement.of(sql));
long[] rowCounts;
try {
rowCounts = transaction.batchUpdate(stmts);
} catch (SpannerBatchUpdateException e) {
rowCounts = e.getUpdateCounts();
}
for (int i = 0; i < rowCounts.length; i++) {
System.out.printf("%d record updated by stmt %d.\n", rowCounts[i], i);
}
return null;
});
}Node.js
請使用 transaction.batchUpdate() 執行 DML 陳述式的清單。
// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');
/**
* 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);
const insert = {
sql: `INSERT INTO Albums (SingerId, AlbumId, AlbumTitle, MarketingBudget)
VALUES (1, 3, "Test Album Title", 10000)`,
};
const update = {
sql: `UPDATE Albums SET MarketingBudget = MarketingBudget * 2
WHERE SingerId = 1 and AlbumId = 3`,
};
const dmlStatements = [insert, update];
try {
await database.runTransactionAsync(async transaction => {
const [rowCounts] = await transaction.batchUpdate(dmlStatements);
await transaction.commit();
console.log(
`Successfully executed ${rowCounts.length} SQL statements using Batch DML.`,
);
});
} catch (err) {
console.error('ERROR:', err);
throw err;
} finally {
// Close the database when finished.
database.close();
}PHP
請使用 executeUpdateBatch() 建立 DML 陳述式的清單，然後使用 commit() 執行陳述式。
use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\Transaction;
/**
* Updates sample data in the database with Batch DML.
*
* This requires the `MarketingBudget` column which must be created before
* running this sample. You can add the column by running the `add_column`
* sample or by running this DDL statement against your database:
*
* ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
*
* Example:
* ```
* update_data_with_batch_dml($instanceId, $databaseId);
* ```
*
* @param string $instanceId The Spanner instance ID.
* @param string $databaseId The Spanner database ID.
*/
function update_data_with_batch_dml(string $instanceId, string $databaseId): void
{
$spanner = new SpannerClient();
$instance = $spanner->instance($instanceId);
$database = $instance->database($databaseId);
$batchDmlResult = $database->runTransaction(function (Transaction $t) {
$result = $t->executeUpdateBatch([
[
'sql' => 'INSERT INTO Albums '
. '(SingerId, AlbumId, AlbumTitle, MarketingBudget) '
. "VALUES (1, 3, 'Test Album Title', 10000)"
],
[
'sql' => 'UPDATE Albums '
. 'SET MarketingBudget = MarketingBudget * 2 '
. 'WHERE SingerId = 1 and AlbumId = 3'
],
]);
$t->commit();
$rowCounts = count($result->rowCounts());
printf('Executed %s SQL statements using Batch DML.' . PHP_EOL,
$rowCounts);
});
}Python
請使用 transaction.batch_update() 執行多個 DML 陳述式字串。
from google.rpc.code_pb2 import OK
# 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)
insert_statement = (
"INSERT INTO Albums "
"(SingerId, AlbumId, AlbumTitle, MarketingBudget) "
"VALUES (1, 3, 'Test Album Title', 10000)"
)
update_statement = (
"UPDATE Albums "
"SET MarketingBudget = MarketingBudget * 2 "
"WHERE SingerId = 1 and AlbumId = 3"
)
def update_albums(transaction):
status, row_cts = transaction.batch_update([insert_statement, update_statement])
if status.code != OK:
# Do handling here.
# Note: the exception will still be raised when
# `commit` is called by `run_in_transaction`.
return
print("Executed {} SQL statements using Batch DML.".format(len(row_cts)))
database.run_in_transaction(update_albums)Ruby
請使用 transaction.batch_update 執行多個 DML 陳述式字串。
# 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_counts = nil
client.transaction do |transaction|
row_counts = transaction.batch_update do |b|
b.batch_update(
"INSERT INTO Albums " \
"(SingerId, AlbumId, AlbumTitle, MarketingBudget) " \
"VALUES (1, 3, 'Test Album Title', 10000)"
)
b.batch_update(
"UPDATE Albums " \
"SET MarketingBudget = MarketingBudget * 2 " \
"WHERE SingerId = 1 and AlbumId = 3"
)
end
end
statement_count = row_counts.count
puts "Executed #{statement_count} SQL statements using Batch DML."