Insert to mysql table with a column automatically has same value as another column

Question

I has a mysql table with 2 int columns. The first column is auto_increment. Then, I want to insert whether a certain value or the value of the first column to the 2rd column. For example

insert into tab(id,keyy) values (null, id);

I want this statement to insert a row with the id being the current ai number and the keyy the same as the id. Is there any simple way to do that? Thank in advance.

Answer 1

Note that since you only want labelId to be rowId when creating a new label, not when inserting a new row because a label was updated, you don't want to use a generated column, you just want labelId to default to rowId.

You can't do this with auto_increment, but you can do it if the id is set from a sequence:

create sequence label_sequence;
create table labels (
    rowId int not null primary key default next value for label_sequence,
    labelId int not null default rowId
);

Sequences are only available in mariadb, not mysql.

If you are using last_insert_id(), this will not work; last_insert_id is only available for auto_increment.