MySQL序列的解決方法

MySQL序列的解決方法，相信很多沒有經(jīng)驗的人對此束手無策，為此本文總結了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個問題。

MySQL自增長與Oracle序列的區(qū)別:
自增長只能用于表中的其中一個字段
自增長只能被分配給固定表的固定的某一字段，不能被多個表共用.
自增長會把一個未指定或NULL值的字段自動填上.
在中添加序列，請看下面的實例：
在MYSQL里有這樣一張表:
 
Java代碼
 
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代碼
 
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);
 
在ORACLE是這樣的:
 
Java代碼
 
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代碼
 
INSERT INTO Movie (id,name,released) VALUES (MovieSeq.NEXTVAL,'Gladiator',2000);  
INSERT INTO Movie (id,name,released) VALUES (MovieSeq.NEXTVAL,'Gladiator',2000);
 
在oracle下為表添加一個觸發(fā)器，就可以實現(xiàn)mysql自增長功能:
 
Java代碼
 
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;
 
這樣，插件記錄就可以成為MYSQL風格：
 
Java代碼
 
INSERT INTO Movie (name,released) VALUES ('The Lion King',1994);  
INSERT INTO Movie (name,released) VALUES ('The Lion King',1994);
 
下面我們來看看如何在mysql數(shù)據(jù)里使用序列語法.NEXTVAL 和 .CURVAL.
我們假設在mysql中序列的語法是:
 
NEXTVAL(’sequence’);
CURRVAL(’sequence’);
SETVAL(’sequence’,value);
 
下面就是CURRRVAL的實現(xiàn)方案:
 
Java代碼
 
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 ;
 
測試一下結果：
 
Java代碼
 
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代碼
 
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代碼
 
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 |  
22. +---------------------+  
23. 1 row in set (0.00 sec)
 
setval
 
Java代碼
 
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代碼
 
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 |  
22. +---------------------+  
23. 1 row in set (0.00 sec) 

看完上述內(nèi)容，你們掌握MySQL序列的解決方法的方法了嗎？如果還想學到更多技能或想了解更多相關內(nèi)容，歡迎關注億速云行業(yè)資訊頻道，感謝各位的閱讀！