如何在sql中用autoincrement在id列中添加前缀?

create table tbl_usertypes(
 usertypes_id varchar2(50),
 name varchar2(50),
 usertype varchar2(50),
 CONSTRAINT pk_usertypes_id PRIMARY KEY(usertypes_id)
);

desc tbl_usertypes;

CREATE SEQUENCE sq_usertypes_id START WITH 1;

create or replace trigger add_random_id
BEFORE INSERT ON tbl_usertypes
FOR EACH ROW
BEGIN
  IF :NEW.usertype = 'admin' THEN
        SELECT 'A_' || sq_usertypes_id.NEXTVAL INTO :NEW.usertypes_id FROM dual;
    ELSE 
        SELECT 'B_' || sq_usertypes_id.NEXTVAL INTO :NEW.usertypes_id FROM dual;
    END IF;
END;

INSERT INTO tbl_usertypes values(null,'hoax', 'admin');
INSERT INTO tbl_usertypes values(null,'hoax', 'user');
select * from tbl_usertypes;

结果呢

auto increment 在sql上:

CREATE TABLE Test_AutoIncrement(
    ID int AUTO_INCREMENT,
    Name varchar(50) NULL,
    Something varchar(100) NOT NULL,
    PRIMARY KEY(ID));

在本例中,您不必指定 ID 在任何 INSERT

例如,我们有一些关于这个标签的记录,比如:

ID  |  Name  |  Something  |
1     "Simo"      "Foo"
2     "Fred"      "Bar"    

现在,我们在 Test_AutoIncrement 表格:

INSERT INTO Test_AutoIncrement
VALUES(
        NULL,
        "FooBar()"
      );

现在我们的桌子如下:

ID  |  Name  |  Something  |
 1    "Simo"     "Foo"
 2    "Fred"     "Bar"   
 3     NULL      "FooBar()"  

在上述情况下,我们从 ID=1

您可以指定 ID=0 如果你想从零开始计数但是 NO_AUTO_VALUE_ON_ZERO disabled 来自MySQL。