Shulou(Shulou.com)05/31 Report--

MySQL sequence solution, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

The difference between MySQL self-growth and Oracle sequences:

Self-growth can only be used for one of the fields in the table

Self-growth can only be assigned to a fixed field of a fixed table and cannot be shared by multiple tables.

Self-growth will automatically fill in a field with an unspecified or null value.

To add a sequence in, see the following example:

There is 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, it goes like this:

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)

By adding a trigger to the table under oracle, you can achieve mysql self-growth:

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 be MYSQL-style:

Java code

INSERT INTO Movie (name,released) VALUES ('The Lion King',1994)

INSERT INTO Movie (name,released) VALUES ('The Lion King',1994)

Let's look at how to use sequence syntax in mysql data. NEXTVAL and .CURVAL.

Let's assume that the syntax of a sequence in mysql is:

NEXTVAL ('sequence')

CURRVAL ('sequence')

SETVAL ('sequence',value)

Here is the implementation 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 the results:

Java code

1. Mysql > SELECT currval ('MovieSeq')

2. +-+

3. | currval ('MovieSeq') |

4. +-+

5. | 3 |

6. +-

7. 1 row in set (0.00 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 | |

+-+

| | Warning | 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. +-

7. 1 row in set (0.09 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 |

twenty-two。 +-+

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. +

7. 1 row in set (0.06 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 |

twenty-two。 +-+

23. 1 row in set (0.00 sec)

After reading the above, have you mastered the solution of MySQL sequence? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.