- 文档中心
- 关于 TiDB
- 快速上手
- 应用开发
- 概览
- 快速开始
- 示例程序
- 连接到 TiDB
- 数据库模式设计
- 数据写入
- 数据读取
- 事务
- 优化 SQL 性能
- 故障诊断
- 引用文档
- 云原生开发环境
- 第三方软件支持
- 部署标准集群
- 数据迁移
- 数据集成
- 运维操作
- 监控与告警
- 故障诊断
- 性能调优
- 优化手册
- 配置调优
- SQL 性能调优
- SQL 性能调优概览
- 理解 TiDB 执行计划
- SQL 优化流程
- 控制执行计划
- 教程
- 同城多中心部署
- 两地三中心部署
- 同城两中心部署
- 读取历史数据
- 使用 Stale Read 功能读取历史数据(推荐)
- 使用系统变量
tidb_snapshot
读取历史数据
- 最佳实践
- Placement Rules 使用文档
- Load Base Split 使用文档
- Store Limit 使用文档
- TiDB 工具
- 功能概览
- 适用场景
- 工具下载
- TiUP
- 文档地图
- 概览
- 术语及核心概念
- TiUP 组件管理
- FAQ
- 故障排查
- TiUP 命令参考手册
- 命令概览
- TiUP 命令
- TiUP Cluster 命令
- TiUP Cluster 命令概览
- tiup cluster audit
- tiup cluster check
- tiup cluster clean
- tiup cluster deploy
- tiup cluster destroy
- tiup cluster disable
- tiup cluster display
- tiup cluster edit-config
- tiup cluster enable
- tiup cluster help
- tiup cluster import
- tiup cluster list
- tiup cluster patch
- tiup cluster prune
- tiup cluster reload
- tiup cluster rename
- tiup cluster replay
- tiup cluster restart
- tiup cluster scale-in
- tiup cluster scale-out
- tiup cluster start
- tiup cluster stop
- tiup cluster template
- tiup cluster upgrade
- TiUP DM 命令
- TiUP DM 命令概览
- tiup dm audit
- tiup dm deploy
- tiup dm destroy
- tiup dm disable
- tiup dm display
- tiup dm edit-config
- tiup dm enable
- tiup dm help
- tiup dm import
- tiup dm list
- tiup dm patch
- tiup dm prune
- tiup dm reload
- tiup dm replay
- tiup dm restart
- tiup dm scale-in
- tiup dm scale-out
- tiup dm start
- tiup dm stop
- tiup dm template
- tiup dm upgrade
- TiDB 集群拓扑文件配置
- DM 集群拓扑文件配置
- TiUP 镜像参考指南
- TiUP 组件文档
- PingCAP Clinic 诊断服务
- TiDB Operator
- Dumpling
- TiDB Lightning
- TiDB Data Migration
- 关于 Data Migration
- 架构简介
- 快速开始
- 部署 DM 集群
- 入门指南
- 进阶教程
- 运维管理
- 参考手册
- 使用示例
- 异常解决
- 版本发布历史
- Backup & Restore (BR)
- TiDB Binlog
- TiCDC
- TiUniManager
- sync-diff-inspector
- TiSpark
- 参考指南
- 架构
- 监控指标
- 安全加固
- 权限
- SQL
- SQL 语言结构和语法
- SQL 语句
ADD COLUMN
ADD INDEX
ADMIN
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ADMIN SHOW TELEMETRY
ALTER DATABASE
ALTER INDEX
ALTER INSTANCE
ALTER PLACEMENT POLICY
ALTER TABLE
ALTER TABLE COMPACT
ALTER USER
ANALYZE TABLE
BACKUP
BATCH
BEGIN
CHANGE COLUMN
CHANGE DRAINER
CHANGE PUMP
COMMIT
CREATE [GLOBAL|SESSION] BINDING
CREATE DATABASE
CREATE INDEX
CREATE PLACEMENT POLICY
CREATE ROLE
CREATE SEQUENCE
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
CREATE VIEW
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP [GLOBAL|SESSION] BINDING
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP PLACEMENT POLICY
DROP ROLE
DROP SEQUENCE
DROP STATS
DROP TABLE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLASHBACK TABLE
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
GRANT <role>
INSERT
KILL [TIDB]
LOAD DATA
LOAD STATS
MODIFY COLUMN
PREPARE
RECOVER TABLE
RENAME INDEX
RENAME TABLE
RENAME USER
REPLACE
RESTORE
REVOKE <privileges>
REVOKE <role>
ROLLBACK
SELECT
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET ROLE
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW [BACKUPS|RESTORES]
SHOW ANALYZE STATUS
SHOW [GLOBAL|SESSION] BINDINGS
SHOW BUILTINS
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CONFIG
SHOW CREATE PLACEMENT POLICY
SHOW CREATE SEQUENCE
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DATABASES
SHOW DRAINER STATUS
SHOW ENGINES
SHOW ERRORS
SHOW [FULL] FIELDS FROM
SHOW GRANTS
SHOW INDEX [FROM|IN]
SHOW INDEXES [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW MASTER STATUS
SHOW PLACEMENT
SHOW PLACEMENT FOR
SHOW PLACEMENT LABELS
SHOW PLUGINS
SHOW PRIVILEGES
SHOW [FULL] PROCESSSLIST
SHOW PROFILES
SHOW PUMP STATUS
SHOW SCHEMAS
SHOW STATS_HEALTHY
SHOW STATS_HISTOGRAMS
SHOW STATS_META
SHOW STATUS
SHOW TABLE NEXT_ROW_ID
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [FULL] TABLES
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
SHUTDOWN
SPLIT REGION
START TRANSACTION
TABLE
TRACE
TRUNCATE
UPDATE
USE
WITH
- 数据类型
- 函数与操作符
- 聚簇索引
- 约束
- 生成列
- SQL 模式
- 表属性
- 事务
- 视图
- 分区表
- 临时表
- 缓存表
- 字符集和排序
- Placement Rules in SQL
- 系统表
mysql
- INFORMATION_SCHEMA
- Overview
ANALYZE_STATUS
CLIENT_ERRORS_SUMMARY_BY_HOST
CLIENT_ERRORS_SUMMARY_BY_USER
CLIENT_ERRORS_SUMMARY_GLOBAL
CHARACTER_SETS
CLUSTER_CONFIG
CLUSTER_HARDWARE
CLUSTER_INFO
CLUSTER_LOAD
CLUSTER_LOG
CLUSTER_SYSTEMINFO
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
DATA_LOCK_WAITS
DDL_JOBS
DEADLOCKS
ENGINES
INSPECTION_RESULT
INSPECTION_RULES
INSPECTION_SUMMARY
KEY_COLUMN_USAGE
METRICS_SUMMARY
METRICS_TABLES
PARTITIONS
PLACEMENT_POLICIES
PROCESSLIST
REFERENTIAL_CONSTRAINTS
SCHEMATA
SEQUENCES
SESSION_VARIABLES
SLOW_QUERY
STATISTICS
TABLES
TABLE_CONSTRAINTS
TABLE_STORAGE_STATS
TIDB_HOT_REGIONS
TIDB_HOT_REGIONS_HISTORY
TIDB_INDEXES
TIDB_SERVERS_INFO
TIDB_TRX
TIFLASH_REPLICA
TIKV_REGION_PEERS
TIKV_REGION_STATUS
TIKV_STORE_STATUS
USER_PRIVILEGES
VIEWS
METRICS_SCHEMA
- UI
- CLI
- 命令行参数
- 配置文件参数
- 系统变量
- 存储引擎
- 遥测
- 错误码
- 通过拓扑 label 进行副本调度
- 常见问题解答 (FAQ)
- 版本发布历史
- 术语表
更新数据
此页面将展示以下 SQL 语句,配合各种编程语言 TiDB 中的数据进行更新:
- UPDATE: 用于修改指定表中的数据。
- INSERT ON DUPLICATE KEY UPDATE: 用于插入数据,在有主键或唯一键冲突时,更新此数据。注意,不建议在有多个唯一键(包含主键)的情况下使用此语句。这是因为此语句在检测到任何唯一键(包括主键) 冲突时,将更新数据。在不止匹配到一行冲突时,将只会更新一行数据。
在开始之前
在阅读本页面之前,你需要准备以下事项:
使用 UPDATE
需更新表中的现有行,需要使用带有 WHERE 子句的 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
的简单用法,详细文档可参考 TiDB 的 UPDATE 语法页。
UPDATE
最佳实践
以下是更新行时需要遵循的一些最佳实践:
- 始终在更新语句中指定
WHERE
子句。如果UPDATE
没有WHERE
子句,TiDB 将更新这个表内的所有行。 - 需要更新大量行(数万或更多)的时候,使用批量更新,这是因为 TiDB 单个事务大小限制为 txn-total-size-limit(默认为 100MB),且一次性过多的数据更新,将导致持有锁时间过长(悲观事务),或产生大量冲突(乐观事务)。
UPDATE
例子
假设某位作者改名为 Helen Haruki,需要更改 authors 表。假设他的唯一标识 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 ...
语句进行插入或更新。
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
。此语句在检测到任何 唯一键 (包括主键) 冲突时,将更新数据。在不止匹配到一行冲突时,将只会一行数据。因此,除非能保证仅有一行冲突,否则不建议在有多个唯一键的表中使用INSERT ON DUPLICATE KEY UPDATE
语句。 - 在创建或更新的场景中使用此语句。
INSERT ON DUPLICATE KEY UPDATE
例子
例如,需要更新 ratings 表来写入用户对书籍的评价,如果用户还未评价此书籍,将新建一条评价,如果用户已经评价过,那么将会更新他之前的评价。
此处主键为 book_id
和 user_id
的联合主键。user_id
为 1 的用户,给 book_id
为 1000 的书籍,打出的 5 分的评价。
- 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();
}
批量更新
需要更新表中多行的数据,可选择 使用 UPDATE
,并使用 WHERE
子句过滤需要更新的数据。
但如果你需要更新大量行(数万或更多)的时候,建议使用一个迭代,每次都只更新一部分数据,直到更新全部完成。这是因为 TiDB 单个事务大小限制为 txn-total-size-limit(默认为 100MB),且一次性过多的数据更新,将导致持有锁时间过长(悲观事务),或产生大量冲突(乐观事务)。你可以在程序或脚本中使用循环来完成操作。
本页提供了编写脚本来处理循环更新的示例,该示例演示了应如何进行 SELECT
和 UPDATE
的组合,完成循环更新。
编写批量更新循环
首先,你应在你的应用或脚本的循环中,编写一个 SELECT
查询。这个查询的返回值可以作为需要更新的行的主键。需要注意的是,定义这个 SELECT
查询时,需要注意使用 WHERE
子句过滤需要更新的行。
例子
假设在过去的一年里,用户在 bookshop
网站进行了大量的书籍打分,但是原本设计为 5 分制的评分导致书籍评分的区分度不够,大量书籍评分集中在 3 分附近,因此,决定将 5 分制改为 10 分制。用来增大书籍评分的区分度。
这时需要对 ratings
表内之前 5 分制的数据进行乘 2 操作,同时需向 ratings
表内添加一个新列,以指示行是否已经被更新了。使用此列,可以在 SELECT
中过滤掉已经更新的行,这将防止脚本崩溃时对行进行多次更新,导致不合理的数据出现。
例如,你可以创建一个名为 ten_point
,数据类型为 BOOL 的列作为是否为 10 分制的标识:
ALTER TABLE `bookshop`.`ratings` ADD COLUMN `ten_point` BOOL NOT NULL DEFAULT FALSE;
此批量更新程序将使用 DDL 语句将进行数据表的模式更改。TiDB 的所有 DDL 变更操作全部都是在线进行的,可查看此处,了解此处使用的 ADD COLUMN 语句。
- 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
按主键顺序进行查询,最多选择 1000 行未更新到 10 分制(ten_point
为 false
)数据的主键值。每次 SELECT
都会选择比上一次 SELECT
结果的最大主键还要大的数据,防止重复。然后,使用批量更新的方式,对其 score
列乘 2,并且将 ten_point
设为 true
,更新 ten_point
的意义是在于防止更新程序崩溃重启后,反复更新同一行数据,导致数据损坏。每次循环中的 time.Sleep(time.Second)
将使得更新程序暂停 1 秒,防止批量更新程序占用过多的硬件资源。
在 Java (JDBC) 中,批量更新程序类似于以下内容:
Java 代码部分:
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
按主键顺序进行查询,最多选择 1000 行未更新到 10 分制(ten_point
为 false
)数据的主键值。每次 SELECT
都会选择比上一次 SELECT
结果的最大主键还要大的数据,防止重复。然后,使用批量更新的方式,对其 score
列乘 2,并且将 ten_point
设为 true
,更新 ten_point
的意义是在于防止更新程序崩溃重启后,反复更新同一行数据,导致数据损坏。每次循环中的 TimeUnit.SECONDS.sleep(1);
将使得更新程序暂停 1 秒,防止批量更新程序占用过多的硬件资源。