作用
指定列数据自动递增。在自动生成的情况下，该列的值在表中是唯一的
限制
1、如果是手动指定该列的值，则会导致不唯一。 2、该列字段不能做排序顺序
操作
创建
-- 创建自增列
create table if not exists doris_ts.incr_tb(
create_time datetime(6),
id bigint not null auto_increment,
username string(255) comment ""
)engine =olap
duplicate key (create_time)
distributed by hash(id) buckets 10
properties(
"replication_num"="1"
);
insert into doris_ts.incr_tb (username,create_time) values("zkz","2023-12-12");
insert into doris_ts.incr_tb (username,create_time) values("zkz","2023-12-12");
insert into doris_ts.incr_tb (id,username,create_time) values(1,"zkz","2023-12-12"); --手动插入该字段
select * from doris_ts.incr_tb;
制定初始值
auto_increment(start_num)
-- 创建自增列 初始值
create table if not exists doris_ts.incr_tb2(
create_time datetime(6),
id bigint not null auto_increment(10008),
username string(255) comment ""
)engine =olap
duplicate key (create_time)
distributed by hash(id) buckets 10
properties(
"replication_num"="1"
);
insert into doris_ts.incr_tb2 (username,create_time) values("zkz","2023-12-12");
insert into doris_ts.incr_tb2 (username,create_time) values("zkz","2023-12-12");
select * from doris_ts.incr_tb2;
以自增列作为key列
1、
-- 以自增列作为key列
create table if not exists doris_ts.incr_tb3(
id bigint not null auto_increment(5000),
create_time datetime(6),
username string(255) comment ""
)engine =olap
duplicate key (id)
distributed by hash(id) buckets 10
properties(
"replication_num"="1"
);
insert into doris_ts.incr_tb3 (username,create_time) values("zkz","2023-12-12");
insert into doris_ts.incr_tb3 (username,create_time) values("zkz","2023-12-12");
insert into doris_ts.incr_tb3 (id,username,create_time) values(2,"zkz","2023-12-12");
select * from doris_ts.incr_tb3;
2、
-- 以自增列作为key列
create table if not exists doris_ts.incr_tb4(
id bigint not null auto_increment(5000),
create_time datetime(6),
username string(255) comment ""
)engine =olap
unique key (id)
distributed by hash(id) buckets 10
properties(
"replication_num"="1"
);
insert into doris_ts.incr_tb4 (username,create_time) values("zkz","2023-12-12");
insert into doris_ts.incr_tb4 (username,create_time) values("zkz","2023-12-12");
insert into doris_ts.incr_tb4 (id,username,create_time) values(2,"zkz","2023-12-12");
insert into doris_ts.incr_tb4 (id,username,create_time) values(null,"zkz","2023-12-12"); --自增列值不会为Null，null值会被替换成自动递增的值
select * from doris_ts.incr_tb4;
insert into doris_ts.incr_tb4 (id,username,create_time) values(5000,"zkz","2023-11-12"); -- 唯一列，有重复的值会执行replace替换掉
限制
1、一张表中只能有一个自增列 2、自增列的起始值必须大于等于0 3、自增列必须设置为bigint类型否则报错 4、自增列的值不会为null 5、自增列只能在deplicate或者unique模型中使用
自增列的更新
自增列是键列，那么必须在更新语句中显示指示它，也就是更新的目标列需要包含自增列。
此时就是找到有的数据就更新对应值（unique模型下的insert语句也一样），没有找到的话如果是Insert语句就新增。否则不更新 自增列的更新
自增列不是键列时，如果是insert的null值就替换成自增值，否则就用insert的值
使用场景
高效分页
可以使用自增列来做深度分页场景下的高效查询。比单纯的limit xxx offset xxx性能要好
例子：查询第101页的100条数据
select key, name, address, city, nation, region, phone, mktsegment
from records_tbl2, (select unique_value as max_value from records_tbl2 order by unique_value limit 1 offset 9999) as previous_data
where records_tbl2.unique_value > previous_data.max_value
order by records_tbl2.unique_value limit 100;
比下面语句性能好
select * from records_tbl order by `key`, `name` limit 100 offset 10000;
