mysql - 如何在 MySQL 中创建序列？

Question

我正在尝试在 MySQL 中创建一个序列(作为一个整体，我对 SQL 还很陌生)。我正在使用以下代码，但它会导致错误:

CREATE SEQUENCE ORDID INCREMENT BY 1 START WITH 622;

ORDID 指的是我正在使用的表中的一个字段。如何正确创建序列？

编辑:

据称，MySQL 不使用序列。我现在正在使用以下代码，但这也会导致错误。我该如何修复它们？

CREATE TABLE ORD (
ORDID NUMERIC(4) NOT NULL AUTO_INCREMENT START WITH 622,
//Rest of table code

编辑:

我想我找到了解决办法。对于 phpMyAdmin(我正在使用的)，您可以使用以下代码。

ALTER TABLE ORD AUTO_INCREMENT = 622;

我不知道为什么它更喜欢这个，但如果其他人需要这方面的帮助，那就给你吧。 :)

Answer 1

这是一个解决方案 suggested by the MySQl manual :

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

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. TheSELECT statement retrieves that value. The mysql_insert_id() C APIfunction 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 theutility of using the function this way is that the ID value ismaintained in the server as the last automatically generated value. Itis multi-user safe because multiple clients can issue the UPDATEstatement and get their own sequence value with the SELECT statement(or mysql_insert_id()), without affecting or being affected by otherclients that generate their own sequence values.