Can TiDB support auto-increment?

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

Original topic: tidb可以写自增这种吗?

| username: Jjjjayson_zeng

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed to cause the issue
[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]

Requirement: Query auto-increment by date for each person
date is an input value
Example
Initial data:
Name Start Time End Time
AAA 2021-01-01 2021-01-03
BBB 2021-01-02 2021-01-04

Required generated data:
Name Time
AAA 2021-01-01
AAA 2021-01-02
AAA 2021-01-03
BBB 2021-01-02
BBB 2021-01-03
BBB 2021-01-04

| username: WalterWj | Original post link

I don’t understand this requirement.

| username: 饭光小团 | Original post link

Auto-increment the date with each inserted personid?

| username: Jjjjayson_zeng | Original post link

Right, so I’m asking how to implement this TiDB.

| username: Jjjjayson_zeng | Original post link

You need to create a temporary table, then insert data based on the id and date. The date is given as a start date and an end date. MySQL can achieve this through loop insertion.

| username: CuteRay | Original post link

You implemented similar functionality using stored procedures in MySQL, right?

| username: WalterWj | Original post link

Sounds like a stored procedure? The call is call id, start_time, end_time to generate this table, right?

| username: TiDBer_jYQINSnf | Original post link

ChatGPT replied, but TiDB does not support stored procedures.

| username: Jjjjayson_zeng | Original post link

So my question is whether it is possible not to use stored procedures.

| username: 小龙虾爱大龙虾 | Original post link

I didn’t quite understand your requirement, it looks like:
Initial data:
Name Start Time End Time
AAA 2021-01-01 2021-01-03
BBB 2021-01-02 2021-01-04

You need to generate data:
Name Time
AAA 2021-01-01
AAA 2021-01-02
AAA 2021-01-03
BBB 2021-01-02
BBB 2021-01-03
BBB 2021-01-04

If this is the requirement, you can use recursive CTE to achieve it, but it’s not recommended to go too deep with recursion, otherwise, you should implement it with a program.

| username: CuteRay | Original post link

It is recommended to implement it at the code level, the performance is still good.

| username: oceanzhang | Original post link

I didn’t understand what that is.

| username: 像风一样的男子 | Original post link

I’ve encountered another strange requirement that can’t be achieved with the database itself.

| username: Jjjjayson_zeng | Original post link

Yes, this is exactly the requirement.

| username: TiDB_C罗 | Original post link

Guessing contest, you can implement this using a program. Find the maximum value for each person and increment it. You can try using window functions.

| username: Jjjjayson_zeng | Original post link

Could you provide a link for me to study?

| username: WalterWj | Original post link

-- Create table
CREATE TABLE your_table (
    name VARCHAR(255),
    start_date DATE,
    end_date DATE
);

-- Insert data
INSERT INTO your_table (name, start_date, end_date) VALUES 
('AAA', '2021-01-01', '2021-01-03'),
('BBB', '2021-01-02', '2021-01-04');

-- Use recursive CTE to generate the required data
WITH RECURSIVE DateRanges AS (
    SELECT name, start_date AS date, end_date
    FROM your_table
    UNION ALL
    SELECT name, DATE_ADD(date, INTERVAL 1 DAY), end_date
    FROM DateRanges
    WHERE date < end_date
)
SELECT name, date FROM DateRanges
ORDER BY name, date;
+--------+------------+
| name   | date       |
+--------+------------+
| AAA    | 2021-01-01 |
| AAA    | 2021-01-02 |
| AAA    | 2021-01-03 |
| BBB    | 2021-01-02 |
| BBB    | 2021-01-03 |
| BBB    | 2021-01-04 |
+--------+------------+
6 rows in set (0.01 sec)
| username: 江湖故人 | Original post link

I only guessed the original poster’s meaning after reading the answer :rofl:

| username: vincentLi | Original post link

It looks like you have this kind of requirement. Using awk to process this data shouldn’t be difficult.

| username: 这里介绍不了我 | Original post link

This feels easy to use.