Why can't I add an auto-increment ID to a table when creating it in TiDB using Navicat?

translator_bot · June 21, 2024, 2:32am

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

Original topic: 为什么使用navcat在tidb创建表的时候，没办法往表里面添加自增id呢？

| username: TiDBer_微风轻吟

To improve efficiency, please provide the following information. A clear problem description can help resolve the issue faster:

[Problem] Unable to add auto-increment ID to the table

[TiDB Version] 7.3.0

translator_bot · June 21, 2024, 2:32am

| username: Soysauce520 | Original post link

You can only add it when creating the table. Refer to AUTO_INCREMENT | PingCAP 文档中心

translator_bot · June 21, 2024, 2:32am

| username: TiDBer_微风轻吟 | Original post link

Okay, thank you.

translator_bot · June 21, 2024, 2:32am

| username: zhanggame1 | Original post link

You can manually modify the table creation script in Navicat. If there are no extreme performance requirements, you can add the AUTO_ID_CACHE=1 parameter. This way, the increments will not be large or small and uncontrollable.

translator_bot · June 21, 2024, 2:32am

| username: TiDBer_微风轻吟 | Original post link

Oh? Is there something special about this? Isn’t it auto-increment? Another question is where to add AUTO_ID_CACHE=1

translator_bot · June 21, 2024, 2:32am

| username: zhanggame1 | Original post link

There is a link above AUTO_INCREMENT | PingCAP Documentation Center. Please read it carefully. By default, TiDB auto-increment works like this: if there are 3 TiDB instances, one will handle IDs from 1 to 30000, another from 30001 to 60000, and the third from 60001 to 90000. Once exhausted, it will request more.

You can set AUTO_ID_CACHE=1 in the table creation statement’s comment section like this:
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_id_cache] AUTO_ID_CACHE=1 */ COMMENT=‘Personnel table’;

translator_bot · June 21, 2024, 2:32am

| username: TiDBer_微风轻吟 | Original post link

Okay, thank you, boss.

translator_bot · June 21, 2024, 2:32am

| username: redgame | Original post link

CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT, c INT);

translator_bot · June 21, 2024, 2:32am

| username: WinterLiu | Original post link

ALTER TABLE is not supported for adding the AUTO_INCREMENT attribute

translator_bot · June 21, 2024, 2:32am

| username: TiDBer_小阿飞 | Original post link

AUTO_INCREMENT is a column attribute used to automatically fill in default column values. When the INSERT statement does not specify a specific value for the AUTO_INCREMENT column, the system will automatically assign a value to that column.

For performance reasons, auto-increment numbers are allocated in batches to each TiDB server (by default, 30,000 values), ensuring uniqueness. However, the values assigned to the INSERT statement are only monotonic on a single TiDB server.