MySQL序列解决方案

    <div class="news_tag"><a href="http://www.iteye.com/blogs/tag/MySQL">MySQL</a><a href="http://www.iteye.com/blogs/tag/Oracle">Oracle</a><a href="http://www.iteye.com/blogs/tag/SQL">SQL</a>&nbsp;</div>
	  
	
	
	
  </div>

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;

Java代码

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;

Java代码

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;

这样，插件记录就可以成为MYSQL风格：

Java代码

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 ;

测试一下结果：

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)

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 ;

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)

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 ;

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)

<div id="share_weibo">分享到：
  <a data-type="sina" href="javascript:;" title="分享到新浪微博"><img src="/images/sina.jpg"></a>
  <a data-type="qq" href="javascript:;" title="分享到腾讯微博"><img src="/images/tec.jpg"></a>
</div>

使用spring提供的测试功能结合JUnit4进行单 ... | Spring2.5+JUnit4单元测试

2011-02-12 00:12
浏览 376
评论(0)

  <li class="last"><a href="http://www.iteye.com/wiki/blog/1093304" target="_blank" class="more">相关推荐</a></li>
</ul>

参考知识库

    <dl class="dlnewlist">
    
      <dd><a target="_blank" href="http://lib.csdn.net/base/mysql"><img src="http://img.knowledge.csdn.net/upload/base/1454051093684_684.jpg" width="58" height="58" alt=""></a></dd>
      <dt>
          <a target="_blank" href="http://lib.csdn.net/base/mysql" classs="title">MySQL知识库</a>
          <span>
             <em>20189</em>&nbsp;&nbsp;关注 <i>|</i> <em>1446</em>&nbsp;&nbsp;收录                  
          </span>
      </dt>
  
    </dl>
    
  </div>

发表评论

您还没有登录,请您登录后再发表评论