亚洲日韩中文字幕天堂不卡,亚洲精品高清在线,亚洲黄色免费观看

MySQL序列解決方案[轉(zhuǎn)載]

原文地址: http://meetrice.javaeye.com/blog/89426

MySQL自增長與Oracle序列的區(qū)別:
自增長只能用于表中的其中一個字段
自增長只能被分配給固定表的固定的某一字段，不能被多個表共用.
自增長會把一個未指定或NULL值的字段自動填上.

在mysql中添加序列，請看下面的實(shí)例：
在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ā)器，就可以實(shí)現(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風(fēng)格：

Java代碼

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

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

下面我們來看看如何在mysql數(shù)據(jù)里使用Oracle序列語法.NEXTVAL 和 .CURVAL.
我們假設(shè)在mysql中序列的語法是:

下面就是CURRRVAL的實(shí)現(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 ;

測試一下結(jié)果：

Java代碼

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)

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代碼

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 ;

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 ;

Java代碼

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)

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)

setval

Java代碼

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 ;

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 ;

Java代碼

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)

posted on 2010-03-09 10:25 周銳閱讀(1127) 評論(0) 編輯收藏所屬分類: MySQL

生命科學(xué)領(lǐng)域的專業(yè)信息解決方案！

留言簿(15)

隨筆分類(1019)

文章分類(3)

文章檔案(21)

收藏夾

Link

好友博客

最新隨筆

搜索

積分與排名

最新評論

閱讀排行榜


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關(guān)文章: 存儲BitSet到MySQL中--相似度搜索如何調(diào)整MySQL 查詢緩沖【轉(zhuǎn)載】 mysql 百萬數(shù)據(jù)存儲過程查詢優(yōu)化技巧【轉(zhuǎn)載】 MySQL存儲過程中的3種循環(huán)【轉(zhuǎn)載】 ERROR 1126 (HY000): Can't Open Shared Library(install UDF to mysql 5.1 in ubuntu 9.10) 在Ubuntu下使MySQL數(shù)據(jù)庫可以被遠(yuǎn)程訪問 intersect in mysql MySQL序列解決方案[轉(zhuǎn)載] 項目中遇到的性能問題總結(jié) 使用CSVJDBC導(dǎo)入CSV文件數(shù)據(jù)到MySQL中