MySQL sequence solution _ MySQL

MySQL sequence solution bitsCN.com

Differences between MySQL auto-growth and Oracle sequence:
Auto-increment can only be used for one field in the table
Auto-growth can only be assigned to a fixed field of a fixed table, and cannot be shared by multiple tables.
Auto-increment will automatically fill in a field with unspecified or NULL values.
To add a sequence in mysql, see the following example:
There is such a table in MYSQL:

Java code

Create table Movie (
Id int not null AUTO_INCREMENT,
Name VARCHAR (60) not null,
Released year not null,
Primary key (id)
) ENGINE = InnoDB;

Create table Movie (
Id int not null AUTO_INCREMENT,
Name VARCHAR (60) not null,
Released year not null,
Primary key (id)
) ENGINE = InnoDB;

Java code

Insert into Movie (name, released) VALUES ('Gladiator', 2000 );
Insert into Movie (id, name, released) VALUES (NULL, 'The Bourne Identity ', 1998 );
Insert into Movie (name, released) VALUES ('Gladiator', 2000 );
Insert into Movie (id, name, released) VALUES (NULL, 'The Bourne Identity ', 1998 );

In ORACLE:

Java code

Create table Movie (
Id int not null,
Name VARCHAR2 (60) not null,
Released int not null,
Primary key (id)
);

Create sequence MovieSeq;

Create table Movie (
Id int not null,
Name VARCHAR2 (60) not null,
Released int not null,
Primary key (id)
);

Create sequence MovieSeq;

Java code

Insert into Movie (id, name, released) VALUES (MovieSeq. NEXTVAL, 'Gladiator', 2000 );
Insert into Movie (id, name, released) VALUES (MovieSeq. NEXTVAL, 'Gladiator', 2000 );

Add a trigger to the table in oracle to implement the mysql auto-growth function:

Java code

Create or replace trigger BRI_MOVIE_TRG
Before insert on Movie
FOR EACH ROW
BEGIN
SELECT MovieSeq. nextval into: new. id from dual;
END BRI_MOVIE_TRG;
.
RUN;

Create or replace trigger BRI_MOVIE_TRG

Before insert on Movie
FOR EACH ROW
BEGIN
SELECT MovieSeq. nextval into: new. id from dual;
END BRI_MOVIE_TRG;
.
RUN;

In this way, the plug-in record can become a MYSQL style:

Java code

Insert into Movie (name, released) VALUES ('The Lion', 1994 );
Insert into Movie (name, released) VALUES ('The Lion', 1994 );

Next let's take a look at how to use Oracle sequence syntax. NEXTVAL and. CURVAL in mysql data.
We assume that the sequence syntax in mysql is:

NEXTVAL ('Sequence ');
CURRVAL ('Sequence ');
SETVAL ('Sequence ', value );

The following is the implementation scheme of CURRRVAL:

Java code

Drop table if exists sequence;
Create table sequence (
Name VARCHAR (50) not null,
Current_value int not null,
Increment int not null default 1,
Primary key (name)
) ENGINE = InnoDB;
Insert into sequence VALUES ('movieseq ', 3, 5 );
Drop function if exists currval;
DELIMITER $
Create function currval (seq_name VARCHAR (50 ))
RETURNS INTEGER
CONTAINS SQL
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END $
DELIMITER;

Drop table if exists sequence;

Create table sequence (
Name VARCHAR (50) not null,
Current_value int not null,
Increment int not null default 1,
Primary key (name)
) ENGINE = InnoDB;

Insert into sequence VALUES ('movieseq ', 3, 5 );
Drop function if exists currval;
DELIMITER $
Create function currval (seq_name VARCHAR (50 ))
RETURNS INTEGER
CONTAINS SQL
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END $
DELIMITER;

Test results:

Java code

1. mysql> SELECT currval ('movieseq ');
2. + --------------------- +
3. | currval ('movieseq ') |
4. + --------------------- +
5. | 3 |
6. + --------------------- +
0.00 row in set (sec)
8. mysql> SELECT currval ('x ');
9. + -------------- +
10. | currval ('x') |
11. + -------------- +
12. | 0 |
13. + -------------- +
14. 1 row in set, 1 warning (0.00 sec)
15. mysql> show warnings;
16. + --------- + ------ + ------------------ +
17. | Level | Code | Message |
18. + --------- + ------ + ------------------ +
19. | Warning | 1329 | No data to FETCH |
20. + --------- + ------ + ------------------ +
21. 1 row in set (0.00 sec)

Mysql> SELECT currval ('movieseq ');

+ --------------------- +

| Currval ('movieseq ') |

+ --------------------- +

| 3 |

+ --------------------- +

1 row in set (0.00 sec)

Mysql> SELECT currval ('x ');

+ -------------- +

| Currval ('x') |

+ -------------- +

| 0 |

+ -------------- +

1 row in set, 1 warning (0.00 sec)

Mysql> show warnings;

+ --------- + ------ + ------------------ +

| Level | Code | Message |

+ --------- + ------ + ------------------ +

| Waring | 1329 | No data to FETCH |

+ --------- + ------ + ------------------ +

1 row in set (0.00 sec)

Nextval

Java code

1. drop function if exists nextval;
2. DELIMITER $
3. create function nextval (seq_name VARCHAR (50 ))
4. RETURNS INTEGER
5. CONTAINS SQL
6. BEGIN
7. UPDATE sequence
8. SET current_value = current_value + increment
9. WHERE name = seq_name;
10. RETURN currval (seq_name );
11. END $
12. DELIMITER;

Java code

1. mysql> select nextval ('movieseq ');
2. + --------------------- +
3. | nextval ('movieseq ') |
4. + --------------------- +
5. | 15 |
6. + --------------------- +
0.09 row in set (sec)
8.
9. mysql> select nextval ('movieseq ');
10. + --------------------- +
11. | nextval ('movieseq ') |
12. + --------------------- +
13. | 20 |
14. + --------------------- +
15. 1 row in set (0.01 sec)
16.
17. mysql> select nextval ('movieseq ');
18. + --------------------- +
19. | nextval ('movieseq ') |
20. + --------------------- +
21. | 25 |
22. + --------------------- +
23. 1 row in set (0.00 sec)

Setval

Java code

1. drop function if exists setval;
2. DELIMITER $
3. create function setval (seq_name VARCHAR (50), value INTEGER)
4. RETURNS INTEGER
5. CONTAINS SQL
6. BEGIN
7. UPDATE sequence
8. SET current_value = value
9. WHERE name = seq_name;
10. RETURN currval (seq_name );
11. END $
12. DELIMITER;

Java code

1. mysql> select setval ('movieseq ', 150 );
2. + ------------------------ +
3. | setval ('movieseq ', 150) |
4. + ------------------------ +
5. | 150 |
6. + ------------------------ +
0.06 row in set (sec)
8.
9. mysql> select curval ('movieseq ');
10. + --------------------- +
11. | currval ('movieseq ') |
12. + --------------------- +
13. | 150 |
14. + --------------------- +
15. 1 row in set (0.00 sec)
16.
17. mysql> select nextval ('movieseq ');
18. + --------------------- +
19. | nextval ('movieseq ') |
20. + --------------------- +
21. | 155 |
22. + --------------------- +
23. 1 row in set (0.00 sec)

Author: ERDP technical architecture"

BitsCN.com