Insert Data

This document describes how to insert data into TiDB by using the SQL language with different programming languages.

Before you start

Before reading this document, you need to prepare the following:

Insert rows

There are two ways to insert multiple rows of data. For example, if you need to insert 3 players’ data.

  • A multi-line insertion statement:

    1. INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (1, 1000, 1), (2, 230, 2), (3, 300, 5);

  • Multiple single-line insertion statements:

    1. INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (1, 1000, 1);
    2. INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (2, 230, 2);
    3. INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (3, 300, 5);

Generally the multi-line insertion statement runs faster than the multiple single-line insertion statements.

  • SQL
  • Java
  • Golang
  • Python
  1. CREATE TABLE `player` (`id` INT, `coins` INT, `goods` INT);
  2. INSERT INTO `player` (`id`, `coins`, `goods`) VALUES (1, 1000, 1), (2, 230, 2);

For more information on how to use this SQL, see Connecting to a TiDB Cluster and follow the steps to enter the SQL statement after connecting to a TiDB cluster using a client.

  1. // ds is an entity of com.mysql.cj.jdbc.MysqlDataSource
  2. try (Connection connection = ds.getConnection()) {
  3.     connection.setAutoCommit(false);
  5.     PreparedStatement pstmt = connection.prepareStatement("INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)"))
  7.     // first player
  8.     pstmt.setInt(1, 1);
  9.     pstmt.setInt(2, 1000);
  10.     pstmt.setInt(3, 1);
  11.     pstmt.addBatch();
  13.     // second player
  14.     pstmt.setInt(1, 2);
  15.     pstmt.setInt(2, 230);
  16.     pstmt.setInt(3, 2);
  17.     pstmt.addBatch();
  19.     pstmt.executeBatch();
  20.     connection.commit();
  21. } catch (SQLException e) {
  22.     e.printStackTrace();
  23. }

Due to the default MySQL JDBC Driver settings, you need to change some parameters to get better bulk insert performance.

ParameterMeansRecommended ScenarioRecommended Configuration
useServerPrepStmtsWhether to use the server side to enable prepared statementsWhen you need to use a prepared statement more than oncetrue
cachePrepStmtsWhether the client caches prepared statementsuseServerPrepStmts=truetrue
prepStmtCacheSqlLimitMaximum size of a prepared statement (256 characters by default)When the prepared statement is greater than 256 charactersConfigured according to the actual size of the prepared statement
prepStmtCacheSizeMaximum number of prepared statement caches (25 by default)When the number of prepared statements is greater than 25Configured according to the actual number of prepared statements
rewriteBatchedStatementsWhether to rewrite Batched statementsWhen batch operations are requiredtrue
allowMultiQueriesStart batch operationsBecause a client bug requires this to be set when rewriteBatchedStatements = true and useServerPrepStmts = truetrue

MySQL JDBC Driver also provides an integrated configuration: useConfigs. When it is configured with maxPerformance, it is equivalent to configuring a set of configurations. Taking mysql:mysql-connector-java:8.0.28 as an example, useConfigs=maxPerformance contains:

  1. cachePrepStmts=true
  2. cacheCallableStmts=true
  3. cacheServerConfiguration=true
  4. useLocalSessionState=true
  5. elideSetAutoCommits=true
  6. alwaysSendSetIsolation=false
  7. enableQueryTimeouts=false
  8. connectionAttributes=none
  9. useInformationSchema=true

You can check mysql-connector-java-{version}.jar!/com/mysql/cj/configurations/maxPerformance.properties to get the configurations contained in useConfigs=maxPerformance for the corresponding version of MySQL JDBC Driver.

The following is a typical scenario of JDBC connection string configurations. In this example, Host: 127.0.0.1, Port: 4000, User name: root, Password: null, Default database: test:

  1. jdbc:mysql://127.0.0.1:4000/test?user=root&useConfigs=maxPerformance&useServerPrepStmts=true&prepStmtCacheSqlLimit=2048&prepStmtCacheSize=256&rewriteBatchedStatements=true&allowMultiQueries=true

For complete examples in Java, see:

  1. package main
  3. import (
  4.     "database/sql"
  5.     "strings"
  7.     _ "github.com/go-sql-driver/mysql"
  8. )
  10. type Player struct {
  11.     ID    string
  12.     Coins int
  13.     Goods int
  14. }
  16. func bulkInsertPlayers(db *sql.DB, players []Player, batchSize int) error {
  17.     tx, err := db.Begin()
  18.     if err != nil {
  19.         return err
  20.     }
  22.     stmt, err := tx.Prepare(buildBulkInsertSQL(batchSize))
  23.     if err != nil {
  24.         return err
  25.     }
  27.     defer stmt.Close()
  29.     for len(players) > batchSize {
  30.         if _, err := stmt.Exec(playerToArgs(players[:batchSize])...); err != nil {
  31.             tx.Rollback()
  32.             return err
  33.         }
  35.         players = players[batchSize:]
  36.     }
  38.     if len(players) != 0 {
  39.         if _, err := tx.Exec(buildBulkInsertSQL(len(players)), playerToArgs(players)...); err != nil {
  40.             tx.Rollback()
  41.             return err
  42.         }
  43.     }
  45.     if err := tx.Commit(); err != nil {
  46.         tx.Rollback()
  47.         return err
  48.     }
  50.     return nil
  51. }
  53. func playerToArgs(players []Player) []interface{} {
  54.     var args []interface{}
  55.     for _, player := range players {
  56.         args = append(args, player.ID, player.Coins, player.Goods)
  57.     }
  58.     return args
  59. }
  61. func buildBulkInsertSQL(amount int) string {
  62.     return "INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)" + strings.Repeat(",(?,?,?)", amount-1)
  63. }

For complete examples in Golang, see:

  1. import MySQLdb
  2. connection = MySQLdb.connect(
  3.     host="127.0.0.1",
  4.     port=4000,
  5.     user="root",
  6.     password="",
  7.     database="bookshop",
  8.     autocommit=True
  9. )
  11. with get_connection(autocommit=True) as connection:
  12.     with connection.cursor() as cur:
  13.         player_list = random_player(1919)
  14.         for idx in range(0, len(player_list), 114):
  15.             cur.executemany("INSERT INTO player (id, coins, goods) VALUES (%s, %s, %s)", player_list[idx:idx + 114])

For complete examples in Python, see:

Bulk-Insert

If you need to quickly import a large amount of data into a TiDB cluster, it is recommended that you use a range of tools provided by PingCAP for data migration. Using the INSERT statement is not the best way, because it is not efficient and requires to handle exceptions and other issues on your own.

The following are the recommended tools for bulk-insert:

  • Data export: Dumpling. You can export MySQL or TiDB data to local or Amazon S3.

  • Data import: TiDB Lightning. You can import Dumpling exported data, a CSV file, or Migrate Data from Amazon Aurora to TiDB. It also supports reading data from a local disk or Amazon S3 cloud disk.

  • Data replication: TiDB Data Migration. You can replicate MySQL, MariaDB, and Amazon Aurora databases to TiDB. It also supports merging and migrating the sharded instances and tables from the source databases.

  • Data backup and restore: Backup & Restore (BR). Compared to Dumpling, BR is more suitable for big data scenario.

  • Data import: Create Import page in the TiDB Cloud console. You can import Dumpling exported data, import a local CSV file, or Import CSV Files from Amazon S3 or GCS into TiDB Cloud. It also supports reading data from a local disk, Amazon S3 cloud disk, or GCS cloud disk.

  • Data replication: TiDB Data Migration. You can replicate MySQL, MariaDB, and Amazon Aurora databases to TiDB. It also supports merging and migrating the sharded instances and tables from the source databases.
  • Data backup and restore: Backup page in the TiDB Cloud console. Compared to Dumpling, backup and restore is more suitable for big data scenario.

Avoid hotspots

When designing a table, you need to consider if there is a large number of insert operations. If so, you need to avoid hotspots during table design. See the Select primary key section and follow the Rules when selecting primary key.

For more information on how to handle hotspot issues, see Troubleshoot Hotspot Issues.

Insert data to a table with the AUTO_RANDOM primary key

If the primary key of the table you insert has the AUTO_RANDOM attribute, then by default the primary key cannot be specified. For example, in the bookshop database, you can see that the id field of the users table contains the AUTO_RANDOM attribute.

In this case, you cannot use SQL like the following to insert:

  1. INSERT INTO `bookshop`.`users` (`id`, `balance`, `nickname`) VALUES (1, 0.00, 'nicky');

An error will occur:

  1. ERROR 8216 (HY000): Invalid auto random: Explicit insertion on auto_random column is disabled. Try to set @@allow_auto_random_explicit_insert = true.

It is not recommended to manually specify the AUTO_RANDOM column during insertion time.

There are two solutions to handle this error:

  • (Recommended) Remove this column from the insert statement and use the AUTO_RANDOM value that TiDB initialized for you. This fits the semantics of AUTO_RANDOM.

    1. INSERT INTO `bookshop`.`users` (`balance`, `nickname`) VALUES (0.00, 'nicky');

  • If you are sure that you must specify this column, then you can use the SET statement to allow the column of AUTO_RANDOM to be specified during insertion time by changing the user variable.

    1. SET @@allow_auto_random_explicit_insert = true;
    2. INSERT INTO `bookshop`.`users` (`id`, `balance`, `nickname`) VALUES (1, 0.00, 'nicky');

Use HTAP

In TiDB, HTAP capabilities save you from performing additional operations when inserting data. There is no additional insertion logic. TiDB automatically guarantees data consistency. All you need to do is turn on column-oriented replica synchronization after creating the table, and use the column-oriented replica to speed up your queries directly.