Ispirer Website Ispirer Capabilities: MySQL Migration Free Trial

Sequences Conversion from Microsoft SQL Server to MySQL

MySQL does not support sequences, that’s why it requires code changes.

Let's consider the following piece of code: 

 CREATE SEQUENCE CountBy1  
   START WITH 1  
   INCREMENT BY 1 ;  
 GO
 CREATE TABLE dbo.[MyTable1]
 (
    [ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR dbo.CountBy1),
    [Title1] [nvarchar](64) NOT NULL
 );
 GO

To simulate a sequence in MySQL, you need to do the following:

1. A table is created that will store the name of the sequence, the current value, the increment, the maximum value, the minimum value, and whether the sequence will be cyclic. 

 create table SWT_Sequence
 (
   SeqName varchar(50), 
   SeqValue DECIMAL(30,0), 
   SeqInc bigint, 
   SeqMax DECIMAL(30,0), 
   SeqMin DECIMAL(30,0), 
   SeqCyc int
 );

2. The SWP_CrtSeq procedure is created that will add a record in the SWT_Sequence table. 

 create procedure SWP_CrtSeq(SeqName varchar(50), SeqValue DECIMAL(30,0), SeqInc bigint, SeqMax DECIMAL(30,0), SeqMin DECIMAL(30,0), SeqCyc int)
 begin
   insert into SWT_Sequence values(SeqName, SeqValue, SeqInc, SeqMax, SeqMin, SeqCyc);
 end

3. The SWF_CurrVal function is created to get the current value. The SWF_CurrVal function takes one parameter - the name of the sequence by which the current value in the SWT_Sequence table will be obtained. 

 create function SWF_CurrVal(SeqN varchar(50))
 returns DECIMAL(30,0)
 deterministic
 begin
   declare SeqVal DECIMAL(30,0);
   select SeqValue into SeqVal from SWT_Sequence where SeqName=SeqN for update;
   return SeqVal;
 end

4. The SWF_NextVal function is created to get the next value in the sequence. The function takes the name of the sequence as a parameter. Using this parameter, we get the value from the SWT_Sequence table. Next, we set a new value for sequence value. If sequence value is equal to the maximum value and the sequence is cyclic, then set the minimum value. Otherwise, we add the value of the increment to the sequence value. 

 create function SWF_NextVal(SeqN varchar(50))
 returns DECIMAL(30,0)
 deterministic
 begin
   declare SeqVal DECIMAL(30,0);
   select SeqValue into SeqVal from SWT_Sequence where SeqName=SeqN for update;
   update SWT_Sequence set SeqValue = case when SeqValue = SeqMax and SeqCyc = 1 then SeqMin else SeqValue + SeqInc end where SeqName=SeqN;
   return SeqVal;
 end

5. Call the SWP_CrtSeq procedure to create a new sequence named CountBy1. In the table, the SWF_NextVal function will be used to get the next value for ID column. 

 call SWP_CrtSeq('CountBy1',1,1,1,1,0); 
 CREATE TABLE MyTable1
 (
   ID BIGINT PRIMARY KEY NOT NULL   DEFAULT SWF_NextVal('CountBy1'),
   Title1 NVARCHAR(64) NOT NULL
 );

Let's compare the results:

Microsoft SQL Server MySQL
SELECT current_value FROM sys.sequences WHERE name = 'CountBy1' ; select SWF_CurrVal('CountBy1')
SELECT NEXT VALUE FOR CountBy1 select SWF_NextVal('CountBy1')

If IDENTITY is used to organize the counter, and not sequence, then its equivalent in MySQL would be AUTO_INCREMENT.

Microsoft SQL Server MySQL
CREATE TABLE dbo.MyTable1
(
ID BIGINT IDENTITY (1, 1) NOT NULL,
Title1 NVARCHAR(64) NOT NULL, PRIMARY KEY (ID)
); 		CREATE TABLE MyTable1
(
ID BIGINT NOT NULL AUTO_INCREMENT,
Title1 NVARCHAR(64)
NOT NULL,
PRIMARY KEY(ID)
) AUTO_INCREMENT = 1;

But if you need a sequence, then the conversion will take place as described in the article.

This solution is automatically executed by our software Ispirer Toolkit. You can learn more about other features of the conversion toolkit, as well as try out a demo license on our website.


If you have any questions or face any difficulties, please contact our support team: support@ispirer.com