In batch insert scenarios, the primary key ID value obtained using mybatis's useGeneratedKeys is inaccurate when the primary key ID uses the AUTO_RANDOM type

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 在批量insert场景下，使用mybatis的useGeneratedKeys取到的主键ID值不准确 ，主键ID使用的AUTO_RANDOM类型的不准

| username: wwb519

[TiDB Usage Environment] Production Environment / Testing / Poc
Production Environment
[TiDB Version]
V6.1.5
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Problem Phenomenon and Impact]
In a batch insert scenario, the primary key ID value obtained using mybatis’s useGeneratedKeys is inaccurate. The primary key ID using the AUTO_RANDOM type is incorrect. The obtained value does not match the actual inserted value.
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

2
| username: MrSylar | Original post link

I remember seeing an issue analysis before; it’s a compatibility problem.

3
| username: wwb519 | Original post link

Do you remember the link to the article?

4
| username: Billmay表妹 | Original post link

When using the TiDB database, if you use useGeneratedKeys during batch data insertion, you may encounter inaccurate primary key ID values. This is because the AUTO_RANDOM type primary key in TiDB is generated on the TiDB Server side, not on the client side. Therefore, if you use useGeneratedKeys on the client side, you may encounter inaccurate primary key ID values.

To solve this problem, you can try the following two methods:

  1. Use the LAST_INSERT_ID() function to get the primary key ID value. When inserting data using the INSERT statement, you can use the LAST_INSERT_ID() function in the SQL statement to get the primary key ID value of the inserted data. For example:

    @Insert("INSERT INTO users(name, age) VALUES(#{name}, #{age})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insertUser(User user);

// Get the primary key ID value of the inserted data
Long id = jdbcTemplate.queryForObject("SELECT LAST_INSERT_ID()", Long.class);

    In this example, the useGeneratedKeys parameter is set to true, indicating that the automatically generated primary key ID value is used. The keyProperty parameter is set to id, indicating that the automatically generated primary key ID value is set to the id property of the User object. After inserting the data, you can use the LAST_INSERT_ID() function to get the primary key ID value of the inserted data.

  2. Use the REPLACE INTO statement to insert data. When using the REPLACE INTO statement to insert data, TiDB will automatically generate the primary key ID value and return it to the client. For example:

    @Insert("REPLACE INTO users(id, name, age) VALUES(#{id}, #{name}, #{age})")
int insertUser(User user);

// Get the automatically generated primary key ID value
Long id = user.getId();

    In this example, using the REPLACE INTO statement to insert data, TiDB will automatically generate the primary key ID value and return it to the client. After inserting the data, you can directly get the automatically generated primary key ID value from the User object.

I hope the above suggestions can help you solve the problem. If you need more help, please provide more detailed information, and I will do my best to assist you.

5
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.