- 关于 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 诊断服务 (Technical Preview)
- 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
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 中。
在开始之前
在阅读本页面之前,你需要准备以下事项:
插入行
假设你需要插入多行数据,那么会有两种插入的办法,假设需要插入 3 个玩家数据:
一个多行插入语句:
INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (1, 1000, 1), (2, 230, 2), (3, 300, 5);
多个单行插入语句:
INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (1, 1000, 1); INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (2, 230, 2); INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (3, 300, 5);
一般来说使用一个多行插入语句
,会比多个单行插入语句
快。
CREATE TABLE `player` (`id` INT, `coins` INT, `goods` INT);
INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (1, 1000, 1), (2, 230, 2);
有关如何使用此 SQL,可查阅连接到 TiDB 集群文档部分,按文档步骤使用客户端连接到 TiDB 集群后,输入 SQL 语句即可。
// ds is an entity of com.mysql.cj.jdbc.MysqlDataSource
try (Connection connection = ds.getConnection()) {
connection.setAutoCommit(false);
PreparedStatement pstmt = connection.prepareStatement("INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)"))
// first player
pstmt.setInt(1, 1);
pstmt.setInt(2, 1000);
pstmt.setInt(3, 1);
pstmt.addBatch();
// second player
pstmt.setInt(1, 2);
pstmt.setInt(2, 230);
pstmt.setInt(3, 2);
pstmt.addBatch();
pstmt.executeBatch();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
另外,由于 MySQL JDBC Driver 默认设置问题,你需更改部分参数,以获得更好的批量插入性能:
参数 | 作用 | 推荐场景 | 推荐配置 |
---|---|---|---|
useServerPrepStmts | 是否使用服务端开启预处理语句支持 | 在需要多次使用预处理语句时 | true |
cachePrepStmts | 客户端是否缓存预处理语句 | useServerPrepStmts=true 时 | true |
prepStmtCacheSqlLimit | 预处理语句最大大小(默认 256 字符) | 预处理语句大于 256 字符时 | 按实际预处理语句大小配置 |
prepStmtCacheSize | 预处理语句最大缓存数量 (默认 25 条) | 预处理语句数量大于 25 条时 | 按实际预处理语句数量配置 |
rewriteBatchedStatements | 是否重写 Batch 语句 | 需要批量操作时 | true |
allowMultiQueries | 开启批量操作 | 因为一个客户端 Bug 在 rewriteBatchedStatements = true 和 useServerPrepStmts = true 时,需设置此项 | true |
MySQL JDBC Driver 还提供了一个集成配置项:useConfigs
。当它配置为 maxPerformance
时,相当于配置了一组配置,以 mysql:mysql-connector-java:8.0.28
为例,useConfigs=maxPerformance
包含:
cachePrepStmts=true
cacheCallableStmts=true
cacheServerConfiguration=true
useLocalSessionState=true
elideSetAutoCommits=true
alwaysSendSetIsolation=false
enableQueryTimeouts=false
connectionAttributes=none
useInformationSchema=true
你可以自行查看 mysql-connector-java-{version}.jar!/com/mysql/cj/configurations/maxPerformance.properties
来获得对应版本MySQL JDBC Driver 的 useConfigs=maxPerformance
包含配置。
在此处给出一个较为的通用场景的 JDBC 连接字符串配置,以 Host: 127.0.0.1
,Port: 4000
,用户名: root
,密码: 空 ,默认数据库: test
为例:
jdbc:mysql://127.0.0.1:4000/test?user=root&useConfigs=maxPerformance&useServerPrepStmts=true&prepStmtCacheSqlLimit=2048&prepStmtCacheSize=256&rewriteBatchedStatements=true&allowMultiQueries=true
有关 Java 的完整示例,可参阅:
package main
import (
"database/sql"
"strings"
_ "github.com/go-sql-driver/mysql"
)
type Player struct {
ID string
Coins int
Goods int
}
func bulkInsertPlayers(db *sql.DB, players []Player, batchSize int) error {
tx, err := db.Begin()
if err != nil {
return err
}
stmt, err := tx.Prepare(buildBulkInsertSQL(batchSize))
if err != nil {
return err
}
defer stmt.Close()
for len(players) > batchSize {
if _, err := stmt.Exec(playerToArgs(players[:batchSize])...); err != nil {
tx.Rollback()
return err
}
players = players[batchSize:]
}
if len(players) != 0 {
if _, err := tx.Exec(buildBulkInsertSQL(len(players)), playerToArgs(players)...); err != nil {
tx.Rollback()
return err
}
}
if err := tx.Commit(); err != nil {
tx.Rollback()
return err
}
return nil
}
func playerToArgs(players []Player) []interface{} {
var args []interface{}
for _, player := range players {
args = append(args, player.ID, player.Coins, player.Goods)
}
return args
}
func buildBulkInsertSQL(amount int) string {
return "INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)" + strings.Repeat(",(?,?,?)", amount-1)
}
有关 Golang 的完整示例,可参阅:
批量插入
如果你需要快速地将大量数据导入 TiDB 集群,最好的方式并不是使用 INSERT
语句,这并不是最高效的方法,而且需要你自行处理异常等问题。推荐使用 PingCAP 提供的一系列工具进行数据迁移:
- 数据导出工具:Dumpling。可以导出 MySQL 或 TiDB 的数据到本地或 Amazon S3 中。
- 数据导入工具:TiDB Lightning。可以导入
Dumpling
导出的数据、CSV 文件,或者 Amazon Aurora 生成的 Apache Parquet 文件。同时支持在本地盘或 Amazon S3 云盘读取数据。 - 数据同步工具:TiDB Data Migration。可同步 MySQL、MariaDB、Amazon Aurora 数据库到 TiDB 中。且支持分库分表数据库的迁移。
- 数据备份恢复工具:Backup & Restore (BR)。相对于
Dumpling
,BR 更适合大数据量的场景。
避免热点
在设计表时需要考虑是否存在大量插入行为,若有,需在表设计期间对热点进行规避。请查看创建表 - 选择主键部分,并遵从选择主键时应遵守的规则。
更多有关热点问题的处理办法,请参考 TiDB 热点问题处理文档。
主键为 AUTO_RANDOM
表插入数据
在插入的表主键为 AUTO_RANDOM
时,这时默认情况下,不能指定主键。例如 bookshop 数据库中,可以看到 users 表 的 id
字段含有 AUTO_RANDOM
属性。
此时,不可使用类似以下 SQL 进行插入:
INSERT INTO `bookshop`.`users` (`id`, `balance`, `nickname`) VALUES (1, 0.00, 'nicky');
将会产生错误:
ERROR 8216 (HY000): Invalid auto random: Explicit insertion on auto_random column is disabled. Try to set @@allow_auto_random_explicit_insert = true.
这是旨在提示你,不建议在插入时手动指定 AUTO_RANDOM
的列。这时,你有两种解决办法处理此错误:
(推荐) 插入语句中去除此列,使用 TiDB 帮你初始化的
AUTO_RANDOM
值。这样符合AUTO_RANDOM
的语义。INSERT INTO `bookshop`.`users` (`balance`, `nickname`) VALUES (0.00, 'nicky');
如果你确认一定需要指定此列,那么可以使用 SET 语句通过更改用户变量的方式,允许在插入时,指定
AUTO_RANDOM
的列。SET @@allow_auto_random_explicit_insert = true; INSERT INTO `bookshop`.`users` (`id`, `balance`, `nickname`) VALUES (1, 0.00, 'nicky');
使用 HTAP
在 TiDB 中,使用 HTAP 能力无需你在插入数据时进行额外操作。不会有任何额外的插入逻辑,由 TiDB 自动进行数据的一致性保证。你只需要在创建表后,开启列存副本同步,就可以直接使用列存副本来加速你的查询。