3

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.

  • Despite common belief, an AUTO_INCREMENT column does not have to be unique or primary key in MySQL. It just has to have an index on it (but not necessarily unique). I'm not sure if that addresses your problem though. The suggestion in mustaccio's answer seems good. – ypercubeᵀᴹ Aug 13 '15 at 9:14
  • And is that SELECT SECUENCIA correct? Shouldn't it be SELECT sequences? – ypercubeᵀᴹ Aug 13 '15 at 9:15
  • yupercube that's correct, i changed the variable name, i'm updating the question. – kriegu Aug 13 '15 at 14:57
  • 99% of uses of SEQUENCEs can be done somewhat directly by AUTO_INCREMENT. I recommend you learn how the latter works, and stop thinking in sequences. – Rick James Aug 17 '15 at 20:14
3

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.

  • Thank you! I think i can create a table for each sequence like this one. With an auto_increment column as you sugest. Nice answer! – kriegu Aug 13 '15 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.