How to add GENERATED BY DEFAULT AS IDENTITY in MySQL table?

Question

I have a DB2 Table and i am trying to convert into MySQL table . This is the DB2 table :

CREATE TABLE MY_TABLE (
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE CACHE 100),
  ACTIVE SMALLINT NOT NULL,
  PRIMARY KEY (ID)
)# 

i have converted to MySQL like :

CREATE TABLE MY_TABLE (
  ID BIGINT ,
  ACTIVE SMALLINT NOT NULL,
  PRIMARY KEY (ID)
);

and i am running one procedure

create procedure test_proced(
    in to_create bigint,
    out created bigint
)
begin
     set created = (select count(vnr.id) from MY_TABLE vnr);
    while (created < to_create) do
      insert into MY_TABLE (active) values(0);
      set created = created + 1;
    end while;
end;

But after adding with above procedure on this table , I am getting some error like :

Caused by: java.sql.SQLException: Field 'id' doesn't have a default value

Now i am suspecting the GENERATED BY DEFAULT AS IDENTITY . Is this error due to this problem ? If it is , How to convert to corresponding MySQL Table ?

Answer 1

In MySQL you can use AUTO_INCREMENT:

CREATE TABLE MY_TABLE (
  ID BIGINT NOT NULL AUTO_INCREMENT,
  ACTIVE SMALLINT NOT NULL,
  PRIMARY KEY (ID)
);

A new value is going to be automatically generated for column ID every time a new row is inserted in MY_TABLE. Each auto-generated value is by default equal to the value of the immediately preceding (in order of insertion) record plus 1.

Note: The default is to start at 1 with a 1 step value.

Answer 2

Adding AUTO INCREMENT to your primary key would fix this issue.