How do I create a sequence in MySQL?

by

This is a solution suggested by the MySQl manual:

If expr is given as an argument to LAST_INSERT_ID(), the value of the
argument is returned by the function and is remembered as the next
value to be returned by LAST_INSERT_ID(). This can be used to simulate
sequences:

Create a table to hold the sequence counter and initialize it:

    mysql> CREATE TABLE sequence (id INT NOT NULL);
    mysql> INSERT INTO sequence VALUES (0);

Use the table to generate sequence numbers like this:

    mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
    mysql> SELECT LAST_INSERT_ID();

The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The
SELECT statement retrieves that value. The mysql_insert_id() C API
function can also be used to get the value. See Section 23.8.7.37,
“mysql_insert_id()”.

You can generate sequences without calling LAST_INSERT_ID(), but the
utility of using the function this way is that the ID value is
maintained in the server as the last automatically generated value. It
is multi-user safe because multiple clients can issue the UPDATE
statement and get their own sequence value with the SELECT statement
(or mysql_insert_id()), without affecting or being affected by other
clients that generate their own sequence values.

More Related Contents:

  1. SQL Query needed for fetch value from Two identical table but not fully identical table
  2. How to create a MySQL hierarchical recursive query?
  3. Error related to only_full_group_by when executing a query in MySql
  4. SQL split values to multiple rows
  5. How to reset AUTO_INCREMENT in MySQL
  6. How to declare a variable in MySQL?
  7. MySQL Query GROUP BY day / month / year
  8. MySQL Cannot Add Foreign Key Constraint
  9. Grouping into interval of 5 minutes within a time range
  10. SQL counting all rows instead of counting individual rows
  11. How do I select an entire row which has the largest ID in the table?
  12. How do I use on delete cascade in mysql?
  13. Adding a leading zero to some values in column in MySQL
  14. Using SQL to determine word count stats of a text field
  15. Update one MySQL table with values from another
  16. MySQL/MariaDB – order by inside subquery
  17. How to transpose mysql table rows into columns
  18. View’s SELECT contains a subquery in the FROM clause
  19. MySQL Group By and Sum total value of other column
  20. MySQL incorrect key file for tmp table when making multiple joins
  21. Does the order of conditions in a WHERE clause affect MySQL performance?
  22. Difference between these two joining table approaches?
  23. MySQL Stored Procedures not working with SELECT (basic question)
  24. MySQL query, MAX() + GROUP BY
  25. ERROR 1148: The used command is not allowed with this MySQL version
  26. MySQL export into outfile : CSV escaping chars
  27. Simple Random Samples from a MySQL Sql database
  28. ALTER table – adding AUTOINCREMENT in MySQL
  29. MySQL Select minimum/maximum among two (or more) given values
  30. mySQL SELECT upcoming birthdays

Leave a Comment