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

化學結(jié)構(gòu)搜索，化學信息學，生物信息學，實驗室信息學等。
以高科技的生物、化學信息技術(shù)實現(xiàn)生命科學領(lǐng)域中專業(yè)數(shù)據(jù)的計算和管理、提高研發(fā)能力、增強在科研和成本效率方面的國際競爭力，為生物、化學、醫(yī)藥和學術(shù)機構(gòu)提供一流的解決方案和技術(shù)咨詢。

子曰：危邦不入，亂邦不居。天下有道則見，無道則隱。

BlogJava :: 首頁 :: 新隨筆 :: 聯(lián)系 :: 聚合

:: 管理

posts - 431, comments - 344, trackbacks - 0

公告

Don't Repeat Yourself
座右銘：you can lose your money, you can spent all of it, and if you work hard you get it all back. But if you waste your time, you're never gonna get it back.
公告：本博客在此聲明部分文章為轉(zhuǎn)摘，只做資料收集使用。

微信: szhourui
QQ：109450684
Email
：lsi.zhourui@gmail.com

<

2010年3月

>

日

一

二

三

四

五

六

28

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

1

2

3

4

5

6

7

8

9

10

留言簿(15)

隨筆分類(1019)

文章分類(3)

文章檔案(21)

收藏夾

Java
Struts

Link

DHTML 參考手冊
speedtest
UML軟件工程組織
手冊中心

好友博客

Andy Yao
Charlie Zhu
Seal's Blog
叉的博客
姜海英
肖西洋
bio & chem

搜索

積分與排名

積分 - 862642
排名 - 44

閱讀排行榜

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

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

MySQL自增長與Oracle序列的區(qū)別:
自增長只能用于表中的其中一個字段
自增長只能被分配給固定表的固定的某一字段，不能被多個表共用.
自增長會把一個未指定或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下為表添加一個觸發(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ù)里使用Oracle序列語法.NEXTVAL 和 .CURVAL.
我們假設(shè)在mysql中序列的語法是:

下面就是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 ;

測試一下結(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 周銳閱讀(1122) 評論(0) 編輯收藏所屬分類: MySQL

新用戶注冊刷新評論列表


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




網(wǎng)站導航: 博客園 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ù)庫可以被遠程訪問 intersect in mysql MySQL序列解決方案[轉(zhuǎn)載] 項目中遇到的性能問題總結(jié) 使用CSVJDBC導入CSV文件數(shù)據(jù)到MySQL中

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

留言簿(15)

隨筆分類(1019)

文章分類(3)

文章檔案(21)

收藏夾

Link

好友博客

最新隨筆

搜索

積分與排名

最新評論

閱讀排行榜