データの更新

このドキュメントでは、さまざまなプログラミング言語で次の 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句を指定します。5 ステートメントWHERE UPDATEがない場合、TiDB はテーブル内のすべての行を更新します。

UPDATE

ある著者がHelen Harukiに名前を変更したとします。 著者テーブルを変更する必要があります。 彼女の固有のid1 であると仮定すると、フィルターは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 つあるテーブルにのみ使用してください。このステートメントは、一意のキー(主キーを含む) の競合が検出されるとデータを更新します。競合行が複数ある場合は、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(); }

一括更新

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

ただし、多数の行 (たとえば、1 万行以上) を更新する必要がある場合は、データを繰り返し更新することをお勧めします。つまり、更新が完了するまで、各反復でデータの一部のみを更新します。これは、TiDB が単一トランザクションのサイズを制限しているためです ( トランザクションの合計サイズ制限 、デフォルトでは 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結果の最大値よりも大きい主キーを選択します。次に、一括更新を使用し、そのscore列を2倍にして、 ten_pointtrueに設定します。 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結果の最大値よりも大きい主キーを選択します。次に、一括更新を使用し、そのscore列を2倍にして、 ten_pointtrueに設定します。 ten_pointを更新する目的は、クラッシュ後の再起動時に更新アプリケーションが同じ行を繰り返し更新してデータ破損を引き起こすのを防ぐことです。 TimeUnit.SECONDS.sleep(1);各ループで、更新アプリケーションが 1 秒間一時停止して、更新アプリケーションがハードウェア リソースを大量に消費するのを防ぎます。

ヘルプが必要ですか?

TiDB コミュニティ 、またはサポートチケットを作成するについて質問します。

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