Oracle sequences to MySQL

Question

Migrating from Oracle to MySQL we've found some many incompatibility issues. We've some workarounds for many of them, however we still have one: The Sequences

We have this table

CREATE TABLE "HEADS"(   
    "HEAD_ID" NUMBER NOT NULL ENABLE,
    "COLUMN" NUMBER NOT NULL ENABLE,
    "ROW" NUMBER NOT NULL ENABLE,
    "VALUE" VARCHAR2(200),
    "CREATED_BY" VARCHAR2(40),
    "CREATED_DATE" DATE,
    "MODIFIED_BY" VARCHAR2(40),
    "MODIFIED_DATE" DATE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
   )

Notice HEAD_ID is not primary key, and thus, it's not unique. However, on a Stored Procedure we have this code:

   declare var_id,sequences number;
   SELECT CRM_SEQ_GIS_FCEHEAD.NEXTVAL INTO sequences FROM DUAL;
   INSERT INTO HEADS (
            HEAD_ID,
            COLUMN,
            ROW,
            VALUE,
            CREATED_BY,
            CREATED_DATE
            ) ( SELECT
               sequences,
               COLUMN,
               ROW,
               VALUE,
               CREATED_BY,
               SYSDATE
               FROM HEADS
               WHERE HEAD_ID = var_id );

Being var_id any given numeric value.

It uses a sequence to get the next value for HEAD_ID. What would be the approach to do this in MySQL?

We know that sequences equivalent in MySQL are the auto_increment columns. But for a column to be auto_increment it should be the primary key and be unique. The column in oracle is not PK nor UNIQUE. It has repeated values for HEAD_ID.

Answer 1

You could write your own user-defined function to generate sequence numbers. Alternatively, you could create a separate table with a single AUTO_INCREMENT column to generate new IDs: in your stored procedure you would insert a row into that table, use LAST_INSERT_ID() to fetch the generated value, then delete the inserted row.