Daten löschen

Einzelne Zeilen aus einer Tabelle löschen.

Dokumentationsseiten mit diesem Codebeispiel

Die folgenden Dokumente enthalten das Codebeispiel im Kontext:

Codebeispiel

C#

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken. 

public static async Task DeleteIndividualRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    const int singerId = 2;
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    List<Album> albums = new List<Album>
    {
        new Album { SingerId = singerId, AlbumId = 1, AlbumTitle = "Green" },
        new Album { SingerId = singerId, AlbumId = 3, AlbumTitle = "Terrified" },
    };
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete individual rows from the UpcomingAlbums table.
        await Task.WhenAll(albums.Select(album =>
        {
            var cmd = connection.CreateDeleteCommand(
                "UpcomingAlbums",
                new SpannerParameterCollection
                {
                    { "SingerId", SpannerDbType.Int64, album.SingerId },
                    { "AlbumId", SpannerDbType.Int64, album.AlbumId }
                }
            );
            return cmd.ExecuteNonQueryAsync();
        }));

        Console.WriteLine("Deleted individual rows in UpcomingAlbums.");
    }
}

public static async Task DeleteRangeOfRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete a range of rows from the UpcomingSingers table where the column key is >=3 and <5.
        var cmd = connection.CreateDmlCommand(
           "DELETE FROM UpcomingSingers WHERE SingerId >= 3 AND SingerId < 5");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) deleted from UpcomingSingers.");
    }
}

public static async Task DeleteAllRowsAsync(
    string projectId, string instanceId, string databaseId)
{
    string connectionString =
        $"Data Source=projects/{projectId}/instances/{instanceId}"
        + $"/databases/{databaseId}";
    // Create connection to Cloud Spanner.
    using (var connection = new SpannerConnection(connectionString))
    {
        await connection.OpenAsync();

        // Delete remaining UpcomingSingers rows, which will also delete the remaining
        // UpcomingAlbums rows since it was defined with ON DELETE CASCADE.
        var cmd = connection.CreateDmlCommand(
           "DELETE FROM UpcomingSingers WHERE true");
        int rowCount = await cmd.ExecuteNonQueryAsync();
        Console.WriteLine($"{rowCount} row(s) deleted from UpcomingSingers.");
    }
}

C++

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken. 

void DeleteData(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;

  // Delete the albums with key (2,1) and (2,3).
  //! [make-key] [keyset-add-key]
  auto delete_albums = spanner::DeleteMutationBuilder(
                           "Albums", spanner::KeySet()
                                         .AddKey(spanner::MakeKey(2, 1))
                                         .AddKey(spanner::MakeKey(2, 3)))
                           .Build();
  //! [make-key] [keyset-add-key]

  // Delete some singers using the keys in the range [3, 5]
  //! [make-keybound-closed]
  auto delete_singers_range =
      spanner::DeleteMutationBuilder(
          "Singers", spanner::KeySet().AddRange(spanner::MakeKeyBoundClosed(3),
                                                spanner::MakeKeyBoundOpen(5)))
          .Build();
  //! [make-keybound-closed]

  // Deletes remaining rows from the Singers table and the Albums table, because
  // the Albums table is defined with ON DELETE CASCADE.
  auto delete_singers_all =
      spanner::MakeDeleteMutation("Singers", spanner::KeySet::All());

  auto commit_result = client.Commit(spanner::Mutations{
      delete_albums, delete_singers_range, delete_singers_all});
  if (!commit_result) {
    throw std::runtime_error(commit_result.status().message());
  }
  std::cout << "Delete was successful [spanner_delete_data]\n";
}

Go

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken. 


import (
	"context"
	"io"

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

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

	m := []*spanner.Mutation{
		// Delete individual rows.
		spanner.Delete("Albums", spanner.Key{2, 1}),
		spanner.Delete("Albums", spanner.Key{2, 3}),
		// Delete a range of rows where the column key is >=3 and <5.
		spanner.Delete("Singers", spanner.KeyRange{Start: spanner.Key{3}, End: spanner.Key{5}, Kind: spanner.ClosedOpen}),
		// Delete remaining Singers rows, which will also delete the remaining
		// Albums rows because Albums was defined with ON DELETE CASCADE.
		spanner.Delete("Singers", spanner.AllKeys()),
	}
	_, err = client.Apply(ctx, m)
	return err
}

Java

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken. 

static void deleteExampleData(DatabaseClient dbClient) {
  List<Mutation> mutations = new ArrayList<>();

  // KeySet.Builder can be used to delete a specific set of rows.
  // Delete the Albums with the key values (2,1) and (2,3).
  mutations.add(
      Mutation.delete(
          "Albums", KeySet.newBuilder().addKey(Key.of(2, 1)).addKey(Key.of(2, 3)).build()));

  // KeyRange can be used to delete rows with a key in a specific range.
  // Delete a range of rows where the column key is >=3 and <5
  mutations.add(
      Mutation.delete("Singers", KeySet.range(KeyRange.closedOpen(Key.of(3), Key.of(5)))));

  // KeySet.all() can be used to delete all the rows in a table.
  // Delete remaining Singers rows, which will also delete the remaining Albums rows since it was
  // defined with ON DELETE CASCADE.
  mutations.add(Mutation.delete("Singers", KeySet.all()));

  dbClient.write(mutations);
  System.out.printf("Records deleted.\n");
}

Node.js

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken. 

// 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);

// Instantiate Spanner table object
const albumsTable = database.table('Albums');

// Deletes individual rows from the Albums table.
try {
  const keys = [
    [2, 1],
    [2, 3],
  ];
  await albumsTable.deleteRows(keys);
  console.log('Deleted individual rows in Albums.');
} catch (err) {
  console.error('ERROR:', err);
}

// Delete a range of rows where the column key is >=3 and <5
database.runTransaction(async (err, transaction) => {
  if (err) {
    console.error(err);
    return;
  }
  try {
    const [rowCount] = await transaction.runUpdate({
      sql: 'DELETE FROM Singers WHERE SingerId >= 3 AND SingerId < 5',
    });
    console.log(`${rowCount} records deleted from Singers.`);
  } catch (err) {
    console.error('ERROR:', err);
  }

  // Deletes remaining rows from the Singers table and the Albums table,
  // because Albums table is defined with ON DELETE CASCADE.
  try {
    // The WHERE clause is required for DELETE statements to prevent
    // accidentally deleting all rows in a table.
    // https://cloud.google.com/spanner/docs/dml-syntax#where_clause
    const [rowCount] = await transaction.runUpdate({
      sql: 'DELETE FROM Singers WHERE true',
    });
    console.log(`${rowCount} records deleted from Singers.`);
    await transaction.commit();
  } catch (err) {
    console.error('ERROR:', err);
  } finally {
    // Close the database when finished.
    await database.close();
  }
});

PHP

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken. 

use Google\Cloud\Spanner\SpannerClient;
use Google\Cloud\Spanner\KeyRange;
use Google\Cloud\Core\Exception\GoogleException;

/**
 * Deletes sample data from the given database.
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 * @throws GoogleException
 */
function delete_data($instanceId, $databaseId)
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    // Delete individual rows
    $albumsToDelete = $spanner->keySet([
        'keys' => [[2, 1], [2, 3]]
    ]);
    $database->delete('Albums', $albumsToDelete);

    // Delete a range of rows where the column key is >=3 and <5
    // NOTE: A KeyRange must include a start and end.
    // NOTE: startType and endType both default to KeyRange::TYPE_OPEN.
    $singersRange = $spanner->keyRange([
        'startType' => KeyRange::TYPE_CLOSED,
        'start' => [3],
        'endType' => KeyRange::TYPE_OPEN,
        'end' => [5]
    ]);
    $singersToDelete = $spanner->keySet([
        'ranges' => [$singersRange]
    ]);
    $database->delete('Singers', $singersToDelete);

    // Delete remaining Singers rows, which will also delete the remaining
    // Albums rows because Albums was defined with ON DELETE CASCADE
    $remainingSingers = $spanner->keySet([
        'all' => true
    ]);
    $database->delete('Singers', $remainingSingers);

    print('Deleted data.' . PHP_EOL);
}

Python

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken. 

def delete_data(instance_id, database_id):
    """Deletes sample data from the given database.

    The database, table, and data must already exist and can be created using
    `create_database` and `insert_data`.
    """
    spanner_client = spanner.Client()
    instance = spanner_client.instance(instance_id)
    database = instance.database(database_id)

    # Delete individual rows
    albums_to_delete = spanner.KeySet(keys=[[2, 1], [2, 3]])

    # Delete a range of rows where the column key is >=3 and <5
    singers_range = spanner.KeyRange(start_closed=[3], end_open=[5])
    singers_to_delete = spanner.KeySet(ranges=[singers_range])

    # Delete remaining Singers rows, which will also delete the remaining
    # Albums rows because Albums was defined with ON DELETE CASCADE
    remaining_singers = spanner.KeySet(all_=True)

    with database.batch() as batch:
        batch.delete("Albums", albums_to_delete)
        batch.delete("Singers", singers_to_delete)
        batch.delete("Singers", remaining_singers)

    print("Deleted data.")

Ruby

Informationen zum Installieren und Verwenden der Clientbibliothek für Cloud Spanner finden Sie unter Cloud Spanner-Clientbibliotheken. 

# 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

# Delete individual rows
client.delete "Albums", [[2, 1], [2, 3]]

# Delete a range of rows where the column key is >=3 and <5
key_range = client.range 3, 5, exclude_end: true
client.delete "Singers", key_range

# Delete remaining Singers rows, which will also delete the remaining
# Albums rows because Albums was defined with ON DELETE CASCADE
client.delete "Singers"

Nächste Schritte

Informationen zum Suchen und Filtern von Codebeispielen für andere Google Cloud-Produkte finden Sie im Google Cloud-Beispielbrowser.