How to Implement Custom SERIAL Auto-Increment within Categories in a Database?

Group by value to implement automatic increment of custom SERIAL

Challenge:

In a blog system, you have an article table with three columns: id (sequence), category (varchar) and category_id (int). You want the category_id to automatically increment within each category while remaining unique across the table.

Invalidity of custom logic:

Trying to implement this using two separate queries and logic code may cause concurrency issues in a multi-user environment.

Recommended solution:

Discard category_id column:

• Remove the category_id column as it does not provide any additional information besides the id and category columns.
• Use the auto-incrementing id column as the unique identifier for the article.

Dynamic calculation of category position:

• If you still need category-specific identifiers, use the row_number() function to calculate each article's position within a category:

<code class="language-sql">SELECT id, category, ROW_NUMBER() OVER (PARTITION BY category ORDER BY id) AS category_id
FROM article;</code>

Example:

<code class="language-sql">INSERT INTO article(category) VALUES ('stackoverflow');</code>

Result:

<code>SELECT * FROM article WHERE category = 'stackoverflow';
+----+-----------+---------+
| id  | category  | category_id |
+----+-----------+---------+
| 10  | stackoverflow | 1 |
| 11  | stackoverflow | 2 |
+----+-----------+---------+</code>

The above is the detailed content of How to Implement Custom SERIAL Auto-Increment within Categories in a Database?. For more information, please follow other related articles on the PHP Chinese website!