MySQL序列解决方案
来源:互联网 发布:京东云主机 绑定域名 编辑:程序博客网 时间:2024/05/18 00:04
MySQL自增长与Oracle序列的区别:
自增长只能用于表中的其中一个字段
自增长只能被分配给固定表的固定的某一字段,不能被多个表共用.
自增长会把一个未指定或NULL值的字段自动填上.
在mysql中添加序列,请看下面的实例:
在MYSQL里有这样一张表:
自增长只能用于表中的其中一个字段
自增长只能被分配给固定表的固定的某一字段,不能被多个表共用.
自增长会把一个未指定或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;
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);
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;
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);
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;
CREATE OR REPLACE TRIGGER BRI_MOVIE_TRGBEFORE INSERT ON MovieFOR EACH ROWBEGIN SELECT MovieSeq.NEXTVAL INTO :new.id FROM DUAL;END BRI_MOVIE_TRG;.RUN;
这样,插件记录就可以成为MYSQL风格:
- 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的实现方案:
- 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 INTEGERCONTAINS SQLBEGIN 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)
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
- 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 INTEGERCONTAINS SQLBEGIN 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)
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
- 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 INTEGERCONTAINS SQLBEGIN 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)
- MySQL序列解决方案
- MySQL---ORACLE序列解决方案
- MySQL序列解决方案
- mysql序列解决方案
- MySQL序列解决方案
- MySQL序列解决方案
- MySQL序列解决方案
- MySQL序列解决方案
- MySQL实现ORACLE序列(Sequence)的解决方案
- mysql 序列
- python默认json库处理mysql中datetime,date类型不能序列化json的解决方案
- 【MySql】MySql之序列
- oracle序列和mysql序列
- mysql模仿oracle序列
- mysql创建序列
- MySQL 简易序列
- mysql 如何创建序列
- MySQL 简易序列
- CodeGear.Delphi.for.PHP 2.1.0.1083 最新版本(包含破解)
- 让bugzilla使用第三方SMTP发邮件
- error listener start
- Ground loop problems and how to get rid of them
- IT专业开发类--安全技术大系丛书 (Broadview图书精品介绍)
- MySQL序列解决方案
- #define的神秘用法
- Rails:使用@user.save(false)来避开model的校验
- 使用PLSQL Developer来实现不同数据库的表结构以及表数据同步
- Windows内存管理
- Invoke and BeginInvoke
- JAVA工程师所需要具备的基本技能
- 编写高质量的代码
- java程序员面试必备的32个要点