MySQL自增長與Oracle序列的區別:
自增長只能用於表中的其中一個字段
自增長只能被分配給固定表的固定的某一字段，不能被多個表共用.
自增長會把一個未指定或NULL值的字段自動填上.
在mysql中添加序列，請看下面的實例：
在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下為表添加一個觸發器，就可以實現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數據裡使用Oracle序列語法.NEXTVAL 和 .CURVAL.
我們假設在mysql中序列的語法是:
NEXTVAL(’sequence’);
CURRVAL(’sequence’);
SETVAL(’sequence’,value);
下面就是CURRRVAL的實現方案:
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)
作者“ERDP技術架構”
