如何在BigQuery中生成自增序列号？实现续插时延续计数

当然可以实现这个需求！BigQuery虽然没有像传统关系型数据库那样的AUTO_INCREMENT内置自增列，但我们有几种可靠的方案来生成连续递增的序列号，完全满足你首次插入1000条从1开始、后续插入从上次最大值+1起步的要求。

方案1：批量插入时结合现有最大序号（适合无并发场景） #

如果是单批次插入数据，没有并发写入的情况，可以先查询目标表当前的最大序列号，再用窗口函数生成新的序号范围：

1. 先获取当前表的最大序列号（如果表为空则默认0）：

DECLARE max_seq INT64;
SET max_seq = (SELECT IFNULL(MAX(seq_no), 0) FROM `your-project.your-dataset.your-target-table`);

2. 插入数据时生成递增序号：

INSERT INTO `your-project.your-dataset.your-target-table` (seq_no, column1, column2)
SELECT 
  max_seq + ROW_NUMBER() OVER () AS seq_no,
  source_data.column1,
  source_data.column2
FROM (
  -- 替换成你的待插入数据，可以是外部表查询、VALUES子句等
  SELECT 'sample-val-1' AS column1, 'sample-val-2' AS column2 UNION ALL
  SELECT 'sample-val-3' AS column1, 'sample-val-4' AS column2
) AS source_data;

注意：这个方案仅适合没有并发写入的场景，如果同时有多个写入操作，可能会因为竞态条件导致序号重复。

方案2：维护计数器表（支持并发写入） #

如果你的业务存在多并发插入的情况，上面的方案就有风险了。这时候可以维护一个专门的计数器表，通过原子性更新来确保序号连续且唯一：

1. 创建计数器表：

CREATE TABLE `your-project.your-dataset.seq_counter` (
  table_name STRING PRIMARY KEY, -- 用于区分不同目标表的计数器
  current_max_seq INT64 NOT NULL DEFAULT 0
);

-- 初始化目标表的计数器（如果不存在的话）
INSERT INTO `your-project.your-dataset.seq_counter` (table_name, current_max_seq)
VALUES ('your-target-table', 0)
ON CONFLICT(table_name) DO NOTHING;

2. 创建存储过程来原子性生成序号并插入数据：

CREATE OR REPLACE PROCEDURE `your-project.your-dataset.insert_with_continuous_seq`(insert_data ARRAY<STRUCT<column1 STRING, column2 STRING>>)
BEGIN
  DECLARE new_start_seq INT64;
  DECLARE insert_row_count INT64;
  
  SET insert_row_count = ARRAY_LENGTH(insert_data);
  
  -- 原子性更新计数器，获取本次插入的起始序号
  UPDATE `your-project.your-dataset.seq_counter`
  SET current_max_seq = current_max_seq + insert_row_count
  WHERE table_name = 'your-target-table'
  RETURNING current_max_seq - insert_row_count + 1 INTO new_start_seq;
  
  -- 插入数据并生成对应的序列号
  INSERT INTO `your-project.your-dataset.your-target-table` (seq_no, column1, column2)
  SELECT
    new_start_seq + ROW_NUMBER() OVER () - 1 AS seq_no,
    item.column1,
    item.column2
  FROM UNNEST(insert_data) AS item;
END;

3. 调用存储过程插入数据：

CALL `your-project.your-dataset.insert_with_continuous_seq`([
  STRUCT('val-1' AS column1, 'val-2' AS column2),
  STRUCT('val-3' AS column1, 'val-4' AS column2)
]);

这个方案利用BigQuery的UPDATE ... RETURNING语法实现原子性操作，即使多个请求同时写入，也能保证序列号不会重复且严格递增。

方案3：非连续唯一ID（适合对连续性要求不高的场景） #

如果你的业务不需要严格连续的序列号，只是需要唯一标识每条记录，也可以用GENERATE_UUID()生成UUID，或者自定义雪花算法生成分布式唯一ID。不过这两种方式生成的ID不是连续递增的，适合对序号连续性没有要求的场景。

内容的提问来源于stack exchange，提问作者Balajee Venkatesh