MySQL Forums
Forum List  »  Newbie

Re: A sequence solution for those that want it
Posted by: Rick James
Date: May 14, 2009 10:04PM

Here's the most efficient "Sequence" generator I have found for MySQL:

# Table to handle any number of sequences:
CREATE TABLE Sequences (
  name VARCHAR(22) NOT NULL,
  seq INT UNSIGNED NOT NULL,  # (or BIGINT)
  PRIMARY KEY name
);

# Create a Sequence:
INSERT INTO Sequences (name, seq) VALUES (?, 0);
# Drop a Sequence:
DELETE FROM Sequences WHERE name = ?;

# Get a sequence number:
UPDATE Sequences
  SET seq = LAST_INSERT_ID(seq + 1)
  WHERE name = ?;
$seq = $db->LastInsertId();

(Adjust accordingly to you API.)
Let me know if it does not match up to non-MySQL stuff (aside from having different syntax).

Options: ReplyQuote


Subject
Written By
Posted
Re: A sequence solution for those that want it
May 14, 2009 10:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.