MySQL自增長與Oracle序列的區別:
自增長只能用于表中的其中一個字段
自增長只能被分配給固定表的固定的某一字段，不能被多個表共用.
自增長會把一個未指定或NULL值的字段自動填上.
在mysql中添加序列，請看下面的實例：
在MYSQL里有這樣一張表:
- CREATE TABLE Movie(
- id INT NOT NULL AUTO_INCREMENT,
- name VARCHAR(60) NOT NULL,
- released YEAR NOT NULL,
- PRIMARY KEY (id)
- ) ENGINE=InnoDB;
- INSERT INTO Movie (name,released) VALUES ('Gladiator',2000);
- INSERT INTO Movie (id,name,released) VALUES (NULL,'The Bourne Identity',1998);
在ORACLE是這樣的:
- CREATE TABLE Movie(
- id INT NOT NULL,
- name VARCHAR2(60) NOT NULL,
- released INT NOT NULL,
- PRIMARY KEY (id)
- );
- CREATE SEQUENCE MovieSeq;
- INSERT INTO Movie (id,name,released) VALUES (MovieSeq.NEXTVAL,'Gladiator',2000);
在oracle下為表添加一個觸發器，就可以實現mysql自增長功能:
- 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風格：
- INSERT INTO Movie (name,released) VALUES ('The Lion King',1994);
下面我們來看看如何在mysql數據里使用Oracle序列語法.NEXTVAL 和 .CURVAL.
我們假設在mysql中序列的語法是:
NEXTVAL(’sequence’);
CURRVAL(’sequence’);
SETVAL(’sequence’,value);
下面就是
CURRRVAL的實現方案:
- 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 ;
測試一下結果：
- 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)
- DROP FUNCTION IF EXISTS nextval;
- DELIMITER $
- CREATE FUNCTION nextval (seq_name VARCHAR(50))
- RETURNS INTEGER
- CONTAINS SQL
- BEGIN
- UPDATE sequence
- SET current_value = current_value + increment
- WHERE name = seq_name;
- RETURN currval(seq_name);
- END$
- DELIMITER ;
- mysql> select nextval('MovieSeq');
- +---------------------+
- | nextval('MovieSeq') |
- +---------------------+
- | 15 |
- +---------------------+
- 1 row in set (0.09 sec)
-
- mysql> select nextval('MovieSeq');
- +---------------------+
- | nextval('MovieSeq') |
- +---------------------+
- | 20 |
- +---------------------+
- 1 row in set (0.01 sec)
-
- mysql> select nextval('MovieSeq');
- +---------------------+
- | nextval('MovieSeq') |
- +---------------------+
- | 25 |
- +---------------------+
- 1 row in set (0.00 sec)
- DROP FUNCTION IF EXISTS setval;
- DELIMITER $
- CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)
- RETURNS INTEGER
- CONTAINS SQL
- BEGIN
- UPDATE sequence
- SET current_value = value
- WHERE name = seq_name;
- RETURN currval(seq_name);
- END$
- DELIMITER ;
- mysql> select setval('MovieSeq',150);
- +------------------------+
- | setval('MovieSeq',150) |
- +------------------------+
- | 150 |
- +------------------------+
- 1 row in set (0.06 sec)
-
- mysql> select curval('MovieSeq');
- +---------------------+
- | currval('MovieSeq') |
- +---------------------+
- | 150 |
- +---------------------+
- 1 row in set (0.00 sec)
-
- mysql> select nextval('MovieSeq');
- +---------------------+
- | nextval('MovieSeq') |
- +---------------------+
- | 155 |
- +---------------------+
- 1 row in set (0.00 sec)
