Field 'id' doesn't have a default value?

Translated
1

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

Original topic: Field ‘id’ doesn’t have a default value？

| username: 数据库菜鸡

[Test Environment] TiDB
[TiDB Version] 7.1
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Phenomenon and Impact]
The original MySQL used auto-increment IDs, causing storage hotspots. Now SHARD_ROW_ID_BITS is needed, but the following syntax results in an error. Please help check, thank you.
drop table test
[2023-07-10 16:15:00] completed in 526 ms
mysql> CREATE TABLE test (
id BIGINT PRIMARY KEY NONCLUSTERED,
name varchar(1000)
) SHARD_ROW_ID_BITS = 4 CHARACTER SET=utf8mb4 COLLATE utf8mb4_general_ci
[2023-07-10 16:15:01] completed in 529 ms
mysql> insert into test(name) values (‘safasf’)
[2023-07-10 16:15:01] [HY000][1364] Field ‘id’ doesn’t have a default value
[2023-07-10 16:15:01] [HY000][1364] Field ‘id’ doesn’t have a default value
[Resource Configuration]
SHARD_ROW_ID_BITS
[Attachments: Screenshots/Logs/Monitoring]

2
| username: zhanggame1 | Original post link

It’s very simple, the table creation id is neither an auto-increment column nor a random column, and no default value is given.
Either modify the table creation statement or include the id in the insert statement.

3
| username: 裤衩儿飞上天 | Original post link

  1. id BIGINT PRIMARY KEY NONCLUSTERED
    You need to insert values for it; bigint won’t automatically assign values.

  2. You’re already on version 7.1, you can try the auto random clustered table method to distribute the data, so you don’t need to set SHARD_ROW_ID_BITS anymore.

4
| username: 数据库菜鸡 | Original post link

Now I just want to generate a unique number, even if it’s a bit random. I heard that using auto-increment might cause hotspot issues.

5
| username: 裤衩儿飞上天 | Original post link

You can refer to: TiDB Hot Spot Issue Handling | PingCAP Documentation Center

6
| username: tidb菜鸟一只 | Original post link

Change the id column directly to anto_random, as per your requirement.

7
| username: zhanggame1 | Original post link

SHARD_ROW_ID_BITS, it is used to set the number of bits for sharding the implicit _tidb_rowid.

CREATE TABLE test (
id BIGINT PRIMARY KEY AUTO_RANDOM,
name varchar(1000)
)
8
| username: zhanggame1 | Original post link

Try this:
Use int or bigint for the primary key

CREATE TABLE test (
id BIGINT PRIMARY KEY AUTO_RANDOM,
name varchar(1000)
) pre_split_regions=4
9
| username: redgame | Original post link

AUTO_RANDOM meets

10
| username: Hacker007 | Original post link

The primary key needs a default value, and the TiDB primary key is used to mark a unique piece of data.

11
| username: system | Original post link

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