不同数据库设置自增方式

 

• sql server

  • IF OBJECT_ID('Autotest.SA.CUSTOMER','U') is not null
        Drop table Autotest.SA.CUSTOMER;
    CREATE TABLE Autotest.SA.CUSTOMER (
    customer_id int IDENTITY (1,1) PRIMARY KEY ,
    c_custkey nVarChar(50) default '',
    c_mktsegment nVarChar(50) default '',
    c_privilege_level int
    );
    

  • 使用IDENTITY(m,n)
    • m表示的是初始值，n表示的是每次自动增加的值
    • m和n的值都没有指定，则默认为（1,1）
• mysql

  • drop table if exists Autotest.CUSTOMER;
    
    CREATE TABLE autotest.CUSTOMER (
    customer_id int auto_increment PRIMARY KEY,
    c_custkey nVarChar(50) default '',
    c_mktsegment nVarChar(50) default '',
    c_privilege_level int
    );
    

  • 使用auto_increament

    • 必须指定索引，上面的例子用PRIMARY KEY定义了主键索引
    • 默认自增从1开始　　
    • 设置自增的起始值n:  alter table table_name AUTO_INCREMENT=n 　
• Oracle
  • 通过序列+触发器建立自增序列
  • 创建table

  • create table SYSTEM.customer(
    id int not null PRIMARY KEY,
    column1 varchar(50) null,
    column2 varchar(50) null
    )
    

  • 创建序列

  • create sequence seq_perftest
    minvalue 1
    maxvalue 99999999
    start with 1
    increment by 1
    cache 50
    
    drop sequence seq_perftest

    　　起始值为1，增量为1

  • 创建触发器

  • create or replace trigger "perfest_trig"
        before insert on SYSTEM.customer
        for each row
    begin
      select seq_perftest.nextval into :new.id from dual;
    end;
    

  • 验证自增是否生效  insert into system.customer(column1,column2)  values('test1','test2')
  • 用存储过程批量生成数据

  • create procedure system.pro_test(
    init in number,
    loop_time in number
    )
    IS
    vars number;
    i INTEGER;
    begin 
     vars := 0; 
     i := init;
      while vars<loop_time loop
       insert into system.customer(column1,column2) values (('test'||i),('test'||i))
        i := i+1;
        vars := vars+1;
      end loop;
      commit;
    end pro_test;
    
    exec system.pro_test (1,100000)

• Hana
  • 通过序列来创建自增
  • 创建table

  •  create table SYSTEM.customer(
    id int,
    column1 varchar(50) null,
    column2 varchar(50) null
    );
    

    　　

  • 创建序列

  • create sequence system.seq_perftest
    increment by 1
    maxvalue 99999999
    minvalue 1
    NO CYCLE
    start with 1;
    
    
    drop sequence seq_perftest;
    

    　　

  • 验证自增是否生效  insert into system.customer(id, column1) values(system.seq_perftest.nextval, 'test123')
  • 使用存储过程批量增加数据

  • create procedure system.pro_test(
    i INTEGER,
    loop_time INTEGER
    )
    As
    begin 
      For i in 1 .. loop_time DO
       insert into system.customer(id,column1,column2)values (system.seq_perftest.nextval,('test'||i),('test'||i));
        i := i+1;
      end FOR;
    end;
    
    
    
    call system.pro_test(1, 100000)