每组值的自定义序列/自动增量 [英] Custom SERIAL / autoincrement per group of values

问题描述

我正在尝试制作一个博客系统，但遇到了一个小问题.

I'm trying to make a blog system of sort and I ran into a slight problem.

简单地说，我的文章表中有3列:

Simply put, there's 3 columns in my article table:

id SERIAL,
category VARCHAR FK,
category_id INT

id 列显然是 PK，它用作所有文章的全局标识符.

id column is obviously the PK and it is used as a global identifier for all articles.

category 列很好.. 类别.

category_id 用作类别中的 UNIQUE ID，因此当前存在 UNIQUE(category, category_id) 约束.

category_id is used as a UNIQUE ID within a category so currently there is a UNIQUE(category, category_id) constraint in place.

但是，我也希望 category_id 能够自动递增.

However, I also want for category_id to auto-increment.

我想要这样每次我执行像

I want it so that every time I execute a query like

INSERT INTO article(category) VALUES ('stackoverflow');

我希望 category_id 列根据 'stackoverflow' 类别的最新 category_id 自动填充.

I want the category_id column to be automatically be filled according to the latest category_id of the 'stackoverflow' category.

在我的逻辑代码中实现这一点非常容易.我只是选择最新的 num 并插入其中的 +1，但这涉及两个单独的查询.我正在寻找可以在一个查询中完成所有这些操作的 SQL 解决方案.

Achieving this in my logic code is quite easy. I just select latest num and insert +1 of that but that involves two separate queries. I am looking for a SQL solution that can do all this in one query.

推荐答案

概念

至少有几种方法可以解决这个问题.我想到的第一个:

Concept

There are at least several ways to approach this. First one that comes to my mind:

通过覆盖 INSERT 语句中的输入值，在为每行执行的触发器内为 category_id 列分配一个值.

Assign a value for category_id column inside a trigger executed for each row, by overwriting the input value from INSERT statement.

这里是 SQL Fiddle 看代码

为了进行简单的测试，我正在创建 article 表，其中包含类别和它们的 id，对于每个 category 应该是唯一的.我省略了约束创建 - 这与提出这一点无关.

For a simple test, I'm creating article table holding categories and their id's that should be unique for each category. I have omitted constraint creation - that's not relevant to present the point.

create table article ( id serial, category varchar, category_id int )

使用 generate_series() 函数为两个不同的类别插入一些值，以实现自动增量.

Inserting some values for two distinct categories using generate_series() function to have an auto-increment already in place.

insert into article(category, category_id)
  select 'stackoverflow', i from generate_series(1,1) i
  union all
  select 'stackexchange', i from generate_series(1,3) i

创建一个触发器函数，它会选择 MAX(category_id) 并将其值增加 1 为 category 我们插入一个行，然后在继续使用实际的 INSERT 到表之前覆盖该值(BEFORE INSERT 触发器负责).

Creating a trigger function, that would select MAX(category_id) and increment its value by 1 for a category we're inserting a row with and then overwrite the value right before moving on with the actual INSERT to table (BEFORE INSERT trigger takes care of that).

CREATE OR REPLACE FUNCTION category_increment()
RETURNS trigger
LANGUAGE plpgsql
AS
$$
DECLARE
  v_category_inc int := 0;
BEGIN
  SELECT MAX(category_id) + 1 INTO v_category_inc FROM article WHERE category = NEW.category;
  IF v_category_inc is null THEN
    NEW.category_id := 1;
  ELSE
    NEW.category_id := v_category_inc;
  END IF;
RETURN NEW;
END;
$$ 

使用函数作为触发器.

CREATE TRIGGER trg_category_increment 
  BEFORE INSERT ON article 
  FOR EACH ROW EXECUTE PROCEDURE category_increment()

为已经存在的类别和不存在的类别插入更多值(触发后设备).

Inserting some more values (post trigger appliance) for already existing categories and non-existing ones.

INSERT INTO article(category) VALUES 
  ('stackoverflow'),
  ('stackexchange'),
  ('nonexisting');

查询用于选择数据:

select category, category_id From article order by 1,2

初始插入的结果:

category    category_id
stackexchange   1
stackexchange   2
stackexchange   3
stackoverflow   1

最终插入后的结果:

category    category_id
nonexisting     1
stackexchange   1
stackexchange   2
stackexchange   3
stackexchange   4
stackoverflow   1
stackoverflow   2

这篇关于每组值的自定义序列/自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！