データを更新する

このドキュメントでは、次の SQL ステートメントを使用して、さまざまなプログラミング言語で TiDB 内のデータを更新する方法について説明します。

  • アップデート : 指定されたテーブル内のデータを変更するために使用されます。
  • 重複キーの更新時に挿入 : 主キーまたは一意キーの競合がある場合に、データを挿入し、このデータを更新するために使用されます。複数の一意のキー (主キーを含む) がある場合、このステートメントを使用することはお勧めできません。これは、このステートメントが一意のキー (主キーを含む) の競合を検出するとデータを更新するためです。複数の行の競合がある場合、1 つの行のみが更新されます。

始める前に

このドキュメントを読む前に、以下を準備する必要があります。

UPDATEを使用する

テーブル内の既存の行を更新するには、 UPDATEステートメントWHERE句を使用して、更新する列をフィルタリングする必要があります。

注記:

大量の行 (たとえば、1 万行を超える行) を更新する必要がある場合は、一度に完全な更新を実行せ、すべての行が更新されるまで一度に一部を繰り返し更新することをお勧めします。この操作をループするスクリプトまたはプログラムを作成できます。詳細は一括更新参照してください。

UPDATE SQL 構文

SQL では、 UPDATEステートメントは通常次の形式になります。

UPDATE {table} SET {update_column} = {update_value} WHERE {filter_column} = {filter_value}
パラメータ名説明
{table}テーブル名
{update_column}更新するカラム名
{update_value}更新するカラムの値
{filter_column}フィルターに一致するカラム名
{filter_value}フィルタに一致するカラムの値

詳細については、 UPDATE 構文を参照してください。

ベストプラクティスUPDATE

データを更新するためのいくつかのベスト プラクティスを次に示します。

  • UPDATEステートメントには必ずWHERE句を指定します。 UPDATEステートメントにWHERE句がない場合、TiDB はテーブル内のすべての行を更新します。
  • 多数の行 (たとえば、1 万行以上) を更新する必要がある場合は、 一括更新使用します。 TiDB は 1 つのトランザクションのサイズを制限しているため (デフォルトではtxn 合計サイズ制限 MB)、一度に更新するデータが多すぎると、ロックが長時間保持されすぎたり ( 悲観的取引 )、競合が発生したり ( 楽観的取引 ) することがあります。
  • 多数の行 (たとえば、1 万行以上) を更新する必要がある場合は、 一括更新使用します。 TiDB はデフォルトで 1 つのトランザクションのサイズを 100 MB に制限しているため、一度に更新するデータが多すぎると、ロックが長時間保持されすぎたり ( 悲観的取引 )、競合が発生したり ( 楽観的取引 ) する可能性があります。

UPDATE

著者が名前を春木ヘレンに変更したとします。 著者テーブルを変更する必要があります。彼女の一意のid 1であると仮定し、フィルターはid = 1である必要があります。

  • SQL
  • Java
UPDATE `authors` SET `name` = "Helen Haruki" WHERE `id` = 1;
// ds is an entity of com.mysql.cj.jdbc.MysqlDataSource try (Connection connection = ds.getConnection()) { PreparedStatement pstmt = connection.prepareStatement("UPDATE `authors` SET `name` = ? WHERE `id` = ?"); pstmt.setString(1, "Helen Haruki"); pstmt.setInt(2, 1); pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }

INSERT ON DUPLICATE KEY UPDATE使用する

新しいデータをテーブルに挿入する必要があるが、一意のキー (主キーも一意のキー) の競合がある場合、最初に競合したレコードが更新されます。 INSERT ... ON DUPLICATE KEY UPDATE ...ステートメントを使用して挿入または更新できます。

INSERT ON DUPLICATE KEY UPDATE SQL 構文

SQL では、 INSERT ... ON DUPLICATE KEY UPDATE ...ステートメントは通常次の形式になります。

INSERT INTO {table} ({columns}) VALUES ({values}) ON DUPLICATE KEY UPDATE {update_column} = {update_value};
パラメータ名説明
{table}テーブル名
{columns}挿入するカラム名
{values}挿入するカラムの値
{update_column}更新するカラム名
{update_value}更新するカラムの値

INSERT ON DUPLICATE KEY UPDATEベスト プラクティス

  • INSERT ON DUPLICATE KEY UPDATE一意のキーが 1 つあるテーブルにのみ使用します。このステートメントは、 UNIQUE KEY (主キーを含む) の競合が検出された場合にデータを更新します。競合する行が複数ある場合は、1 行のみが更新されます。したがって、競合する行が 1 行だけであることが保証できない限り、複数の一意キーを持つテーブルでINSERT ON DUPLICATE KEY UPDATEステートメントを使用することはお勧めできません。
  • このステートメントは、データを作成または更新するときに使用します。

INSERT ON DUPLICATE KEY UPDATE

たとえば、書籍に対するユーザーの評価を含めるように評価テーブルを更新する必要があります。ユーザーがまだ書籍を評価していない場合は、新しい評価が作成されます。ユーザーがすでに評価している場合は、以前の評価が更新されます。

次の例では、主キーはbook_iduser_idの結合主キーです。ユーザuser_id = 1は、書籍book_id = 10005の評価を与える。

  • SQL
  • Java
INSERT INTO `ratings` (`book_id`, `user_id`, `score`, `rated_at`) VALUES (1000, 1, 5, NOW()) ON DUPLICATE KEY UPDATE `score` = 5, `rated_at` = NOW();
// ds is an entity of com.mysql.cj.jdbc.MysqlDataSource try (Connection connection = ds.getConnection()) { PreparedStatement p = connection.prepareStatement("INSERT INTO `ratings` (`book_id`, `user_id`, `score`, `rated_at`) VALUES (?, ?, ?, NOW()) ON DUPLICATE KEY UPDATE `score` = ?, `rated_at` = NOW()"); p.setInt(1, 1000); p.setInt(2, 1); p.setInt(3, 5); p.setInt(4, 5); p.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); }

一括更新

テーブル内の複数行のデータを更新する必要がある場合は、 WHERE句をINSERT ON DUPLICATE KEY UPDATE使用するして、更新する必要があるデータをフィルタリングできます。

ただし、多数の行 (たとえば、1 万行以上) を更新する必要がある場合は、データを繰り返し更新することをお勧めします。つまり、更新が完了するまで、各繰り返しでデータの一部のみを更新します。 。これは、TiDB が単一トランザクションのサイズを制限しているためです (デフォルトではtxn 合計サイズ制限 MB)。一度にあまりに多くのデータ更新を行うと、ロックが長時間保持されすぎたり ( 悲観的取引 )、競合が発生したり ( 楽観的取引 ) します。プログラムまたはスクリプトでループを使用すると、操作を完了できます。

ただし、多数の行 (たとえば、1 万行以上) を更新する必要がある場合は、データを繰り返し更新することをお勧めします。つまり、更新が完了するまで、各繰り返しでデータの一部のみを更新します。 。これは、TiDB がデフォルトで 1 つのトランザクションのサイズを 100 MB に制限しているためです。一度にあまりに多くのデータ更新を行うと、ロックが長時間保持されすぎたり ( 悲観的取引 )、競合が発生したり ( 楽観的取引 ) します。プログラムまたはスクリプトでループを使用すると、操作を完了できます。

このセクションでは、反復的な更新を処理するスクリプトの作成例を示します。この例は、一括更新を完了するためにSELECTUPDATEを組み合わせて実行する方法を示しています。

一括更新ループの作成

まず、アプリケーションまたはスクリプトのループ内にSELECTクエリを作成する必要があります。このクエリの戻り値は、更新する必要がある行の主キーとして使用できます。このSELECTクエリを定義するときは、 WHERE句を使用して、更新する必要がある行をフィルタリングする必要があることに注意してください。

過去 1 年間、あなたのbookshop Web サイトでユーザーから多くの書籍評価があったとします。しかし、元のデザインの 5 段階評価では書籍評価に差別化が欠けていたとします。ほとんどの本の評価は3です。評価を区別するために、5 ポイント スケールから 10 ポイント スケールに切り替えることにしました。

前の 5 段階評価のratingsテーブルのデータに2を掛けて、行が更新されたかどうかを示す新しい列を評価テーブルに追加する必要があります。この列を使用すると、 SELECTで更新された行をフィルターで除外できます。これにより、スクリプトがクラッシュして行が複数回更新され、結果として不合理なデータが生成されるのを防ぐことができます。

たとえば、10 点スケールかどうかの識別子として、データ型ブールを使用してten_pointという名前の列を作成します。

ALTER TABLE `bookshop`.`ratings` ADD COLUMN `ten_point` BOOL NOT NULL DEFAULT FALSE;

注記:

この一括更新アプリケーションは、 DDLステートメントを使用してデータ テーブルにスキーマ変更を加えます。 TiDB のすべての DDL 変更操作はオンラインで実行されます。詳細については、 列の追加を参照してください。

  • Golang
  • Java (JDBC)

Golangでは、一括更新アプリケーションは次のようになります。

package main import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "strings" "time" ) func main() { db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:4000)/bookshop") if err != nil { panic(err) } defer db.Close() bookID, userID := updateBatch(db, true, 0, 0) fmt.Println("first time batch update success") for { time.Sleep(time.Second) bookID, userID = updateBatch(db, false, bookID, userID) fmt.Printf("batch update success, [bookID] %d, [userID] %d\n", bookID, userID) } } // updateBatch select at most 1000 lines data to update score func updateBatch(db *sql.DB, firstTime bool, lastBookID, lastUserID int64) (bookID, userID int64) { // select at most 1000 primary keys in five-point scale data var err error var rows *sql.Rows if firstTime { rows, err = db.Query("SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` " + "WHERE `ten_point` != true ORDER BY `book_id`, `user_id` LIMIT 1000") } else { rows, err = db.Query("SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` "+ "WHERE `ten_point` != true AND `book_id` > ? AND `user_id` > ? "+ "ORDER BY `book_id`, `user_id` LIMIT 1000", lastBookID, lastUserID) } if err != nil || rows == nil { panic(fmt.Errorf("error occurred or rows nil: %+v", err)) } // joint all id with a list var idList []interface{} for rows.Next() { var tempBookID, tempUserID int64 if err := rows.Scan(&tempBookID, &tempUserID); err != nil { panic(err) } idList = append(idList, tempBookID, tempUserID) bookID, userID = tempBookID, tempUserID } bulkUpdateSql := fmt.Sprintf("UPDATE `bookshop`.`ratings` SET `ten_point` = true, "+ "`score` = `score` * 2 WHERE (`book_id`, `user_id`) IN (%s)", placeHolder(len(idList))) db.Exec(bulkUpdateSql, idList...) return bookID, userID } // placeHolder format SQL place holder func placeHolder(n int) string { holderList := make([]string, n/2, n/2) for i := range holderList { holderList[i] = "(?,?)" } return strings.Join(holderList, ",") }

各反復では、主キーの順にSELECTが実行されます。 10 点スケール ( ten_pointfalse ) に更新されていない最大1000行の主キー値を選択します。 SELECTつの各ステートメントは、重複を防ぐために、前のSELECTの結果の最大のものよりも大きい主キーを選択します。次に、Bulk-update を使用し、そのscore列を2で乗算し、 ten_point trueに設定します。更新ten_pointの目的は、クラッシュ後の再起動の場合に、更新アプリケーションが同じ行を繰り返し更新することを防ぐことであり、これによりデータ破損が発生する可能性があります。各ループにtime.Sleep(time.Second)指定すると、更新アプリケーションがハードウェア リソースを過剰に消費するのを防ぐために、更新アプリケーションが 1 秒間一時停止します。

Java (JDBC) では、一括更新アプリケーションは次のようになります。

コード:

package com.pingcap.bulkUpdate; import com.mysql.cj.jdbc.MysqlDataSource; import java.sql.*; import java.util.LinkedList; import java.util.List; import java.util.concurrent.TimeUnit; public class BatchUpdateExample { static class UpdateID { private Long bookID; private Long userID; public UpdateID(Long bookID, Long userID) { this.bookID = bookID; this.userID = userID; } public Long getBookID() { return bookID; } public void setBookID(Long bookID) { this.bookID = bookID; } public Long getUserID() { return userID; } public void setUserID(Long userID) { this.userID = userID; } @Override public String toString() { return "[bookID] " + bookID + ", [userID] " + userID ; } } public static void main(String[] args) throws InterruptedException { // Configure the example database connection. // Create a mysql data source instance. MysqlDataSource mysqlDataSource = new MysqlDataSource(); // Set server name, port, database name, username and password. mysqlDataSource.setServerName("localhost"); mysqlDataSource.setPortNumber(4000); mysqlDataSource.setDatabaseName("bookshop"); mysqlDataSource.setUser("root"); mysqlDataSource.setPassword(""); UpdateID lastID = batchUpdate(mysqlDataSource, null); System.out.println("first time batch update success"); while (true) { TimeUnit.SECONDS.sleep(1); lastID = batchUpdate(mysqlDataSource, lastID); System.out.println("batch update success, [lastID] " + lastID); } } public static UpdateID batchUpdate (MysqlDataSource ds, UpdateID lastID) { try (Connection connection = ds.getConnection()) { UpdateID updateID = null; PreparedStatement selectPs; if (lastID == null) { selectPs = connection.prepareStatement( "SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` " + "WHERE `ten_point` != true ORDER BY `book_id`, `user_id` LIMIT 1000"); } else { selectPs = connection.prepareStatement( "SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` "+ "WHERE `ten_point` != true AND `book_id` > ? AND `user_id` > ? "+ "ORDER BY `book_id`, `user_id` LIMIT 1000"); selectPs.setLong(1, lastID.getBookID()); selectPs.setLong(2, lastID.getUserID()); } List<Long> idList = new LinkedList<>(); ResultSet res = selectPs.executeQuery(); while (res.next()) { updateID = new UpdateID( res.getLong("book_id"), res.getLong("user_id") ); idList.add(updateID.getBookID()); idList.add(updateID.getUserID()); } if (idList.isEmpty()) { System.out.println("no data should update"); return null; } String updateSQL = "UPDATE `bookshop`.`ratings` SET `ten_point` = true, "+ "`score` = `score` * 2 WHERE (`book_id`, `user_id`) IN (" + placeHolder(idList.size() / 2) + ")"; PreparedStatement updatePs = connection.prepareStatement(updateSQL); for (int i = 0; i < idList.size(); i++) { updatePs.setLong(i + 1, idList.get(i)); } int count = updatePs.executeUpdate(); System.out.println("update " + count + " data"); return updateID; } catch (SQLException e) { e.printStackTrace(); } return null; } public static String placeHolder(int n) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < n ; i++) { sb.append(i == 0 ? "(?,?)" : ",(?,?)"); } return sb.toString(); } }
  • hibernate.cfg.xml構成:
<?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- Database connection settings --> <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property> <property name="hibernate.dialect">org.hibernate.dialect.TiDBDialect</property> <property name="hibernate.connection.url">jdbc:mysql://localhost:4000/movie</property> <property name="hibernate.connection.username">root</property> <property name="hibernate.connection.password"></property> <property name="hibernate.connection.autocommit">false</property> <property name="hibernate.jdbc.batch_size">20</property> <!-- Optional: Show SQL output for debugging --> <property name="hibernate.show_sql">true</property> <property name="hibernate.format_sql">true</property> </session-factory> </hibernate-configuration>

各反復では、主キーの順にSELECTが実行されます。 10 点スケール ( ten_pointfalse ) に更新されていない最大1000行の主キー値を選択します。 SELECTつの各ステートメントは、重複を防ぐために、前のSELECTの結果の最大のものよりも大きい主キーを選択します。次に、Bulk-update を使用し、そのscore列を2で乗算し、 ten_point trueに設定します。更新ten_pointの目的は、クラッシュ後の再起動の場合に、更新アプリケーションが同じ行を繰り返し更新することを防ぐことであり、これによりデータ破損が発生する可能性があります。各ループにTimeUnit.SECONDS.sleep(1);指定すると、更新アプリケーションがハードウェア リソースを過剰に消費するのを防ぐために、更新アプリケーションが 1 秒間一時停止します。

このページは役に立ちましたか?

Playground
新規
登録なしで TiDB の機能をワンストップでインタラクティブに体験できます。
製品
TiDB Cloud
TiDB
価格
PoC お問い合わせ
エコシステム
TiKV
TiFlash
OSS Insight
© 2024 PingCAP. All Rights Reserved.
Privacy Policy.