Home Database Mysql Tutorial MySQL实现类似Oracle的序列_MySQL

MySQL实现类似Oracle的序列_MySQL

Jun 01, 2016 pm 01:28 PM
oracle

bitsCN.com

MySQL实现类似Oracle的序列

 

Oracle一般使用序列(Sequence)来处理主键字段,而MySQL则提供了自增长(increment)来实现类似的目的;

但在实际使用过程中发现,MySQL的自增长有诸多的弊端:不能控制步长、开始索引、是否循环等;若需要迁移数据库,则对于主键这块,也是个头大的问题。

本文记录了一个模拟Oracle序列的方案,重点是想法,代码其次。

Oracle序列的使用,无非是使用.nextval和.currval伪列,基本想法是:1、MySQL中新建表,用于存储序列名称和值;2、创建函数,用于获取序列表中的值;

具体如下:

表结构为

 

 

[sql] 

表结构为:  

drop table if exists sequence;  create table sequence (      seq_name        VARCHAR(50) NOT NULL, -- 序列名称      current_val     INT         NOT NULL, --当前值      increment_val   INT         NOT NULL    DEFAULT 1, --步长(跨度)      PRIMARY KEY (seq_name)  );  
Copy after login

实现currval的模拟方案

[sql] create function currval(v_seq_name VARCHAR(50))  returns integer  begin      declare value integer;      set value = 0;      select current_value into value      from sequence      where seq_name = v_seq_name;      return value;  end;  
Copy after login

[sql]

函数使用为:select currval('MovieSeq');

实现nextval的模拟方案

[sql] create function nextval (v_seq_name VARCHAR(50))  return integer  begin    update sequence    set current_val = current_val + increment_val    where seq_name = v_seq_name;    return currval(v_seq_name);  end;  
Copy after login

[sql]

函数使用为:select nextval('MovieSeq');

增加设置值的函数

[sql] create function setval(v_seq_name VARCHAR(50), v_new_val INTEGER)  returns integer  begin    update sequence    set current_val = v_new_val    where seq_name = v_seq_name;  return currval(seq_name);  
Copy after login

 

 

同理,可以增加对步长操作的函数,在此不再叙述。

bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Function to calculate the number of days between two dates in oracle Function to calculate the number of days between two dates in oracle May 08, 2024 pm 07:45 PM

Function to calculate the number of days between two dates in oracle

How long will Oracle database logs be kept? How long will Oracle database logs be kept? May 10, 2024 am 03:27 AM

How long will Oracle database logs be kept?

The order of the oracle database startup steps is The order of the oracle database startup steps is May 10, 2024 am 01:48 AM

The order of the oracle database startup steps is

How to use interval in oracle How to use interval in oracle May 08, 2024 pm 07:54 PM

How to use interval in oracle

Oracle database server hardware configuration requirements Oracle database server hardware configuration requirements May 10, 2024 am 04:00 AM

Oracle database server hardware configuration requirements

How to see the number of occurrences of a certain character in Oracle How to see the number of occurrences of a certain character in Oracle May 09, 2024 pm 09:33 PM

How to see the number of occurrences of a certain character in Oracle

How much memory does oracle require? How much memory does oracle require? May 10, 2024 am 04:12 AM

How much memory does oracle require?

How to determine whether two strings are contained in oracle How to determine whether two strings are contained in oracle May 08, 2024 pm 07:00 PM

How to determine whether two strings are contained in oracle

See all articles