TiDB auto_random returns the primary key ID of the newly inserted row as 0

Chinese Translated
1

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

Original topic: tidb auto_random返回刚插入的主键id为0

| username: xie123

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
V6.5.9
[Reproduction Path] What operations were performed when the issue occurred

  1. Create a table with the primary key id as auto_random random primary key
  2. Insert multiple rows, the ids returned by mybatis for the newly inserted rows are all 0 mysql如何获取刚插入的主键id_mysql 插入获取id-CSDN博客
  3. Seeking help, for random primary keys, batch insert multiple rows, is there a way to avoid the primary key id being 0?
    [Encountered Issue: Issue Phenomenon and Impact]
    [Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
    [Attachments: Screenshots / Logs / Monitoring]
2
| username: zhaokede | Original post link

This is usually because MyBatis does not handle the special behavior of TiDB’s LAST_INSERT_ID() function by default. In the MyBatis mapping file, make sure you have correctly configured how to retrieve the generated ID. For example, you may need to configure the useGeneratedKeys="true" and keyProperty="id" attributes so that MyBatis can capture and return the database-generated ID.

3
| username: xie123 | Original post link

Not all returns are 0, occasionally there are occurrences.

4
| username: zhaokede | Original post link

Write a test method to test it, print out all the logs, and see what logs are output when it returns 0.

5
| username: xie123 | Original post link

Based on the above configuration, it can return now, but it only returns LAST_INSERT_ID. It’s batch processing, inserting multiple rows. How can it return all of them?

6
| username: xfworld | Original post link

Is it feasible to first retrieve and then write?

7
| username: zhaokede | Original post link

According to the method mentioned above, first fetch, if the fetch returns 0, fetch again, then write. Adjust the business code accordingly.