Postgres serial port for MySQL

Question

I am migrating some Postgres SQL to MySQL and am trying to set the initial values ​​of the three columns to specific values. The table is as follows:

CREATE TABLE ITEM  (
ORDID               NUMERIC(4) NOT NULL,
ITEMID              NUMERIC(4) NOT NULL,
PRODID              NUMERIC(6),
ACTUALPRICE         NUMERIC(8,2),
QTY                 NUMERIC(8),
ITEMTOT             NUMERIC(8,2),
CONSTRAINT ITEM_FOREIGN_KEY FOREIGN KEY (ORDID) REFERENCES ORD (ORDID),
CONSTRAINT ITEM_PRIMARY_KEY PRIMARY KEY (ORDID,ITEMID));

The code I'm trying to execute is as follows:

CREATE SEQUENCE ORDID
INCREMENT BY 1
START WITH 622
;

CREATE SEQUENCE PRODID
INCREMENT BY 1
START WITH 200381
;

CREATE SEQUENCE CUSTID
INCREMENT BY 1
START WITH 109
;

However, when I try to start this, I get an error:

SQL query:

CREATE SEQUENCE ORDIDINCREMENT BY 1 START WITH 622 ;


MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SEQUENCE ORDID
INCREMENT BY 1
START WITH 622' at line 1 

I know that there is no direct equivalent to SEQUENCE in MySQL, but I cannot find a reasonable way to achieve the same without it. Any ideas?

Answer 1

MySQL uses AUTO_INCREMENT for this purpose. Instead of creating new types of sequences, you apply them to an existing integer column.

, .

AUTO_INCREMENT, DEFAULT.

, . , , 9999 , .

AUTO_INCREMENT .

, AUTO_INCREMENT . MyISAM .

, PostgreSQL MySQL verbatim.

, MySQL?

---

item.ordid , . item.prodid, , , - . item.itemid AUTO_INCREMENT, . , , , .

, ITEM , ... ? , "".

- :

CREATE TABLE ITEM (
       ITEMID              INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
       PRODID              INTEGER REFERENCES PROD(PRODID),
       ORDID               INTEGER NOT NULL REFERENCES ORD (ORDID),
       ACTUALPRICE         NUMERIC(8,2),
       QTY                 NUMERIC(8),
       ITEMTOT             NUMERIC(8,2),

       UNIQUE(ORDID, ITEMID)
)

CREATE TABLE ORD (
       ORDID               INTEGER PRIMARY KEY AUTO_INCREMENT,
       ...
) AUTO_INCREMENT = 622;

CREATE TABLE PROD (
       PRODID              INTEGER PRIMARY KEY AUTO_INCREMENT,
       ...
) AUTO_INCREMENT = 200381;

AUTO_INCREMENT ALTER TABLE. , , .

ALTER TABLE CUST AUTO_INCREMENT=109;

AUTO_INCREMENT, . AUTO_INCREMENT MAX(column), . , , .

Answer 2

AUTO_INCREMENT :

CREATE TABLE ORDID (id INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT = 622;
CREATE TABLE PRODID (id INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT = 200381;
CREATE TABLE CUSTID (id INT PRIMARY KEY AUTO_INCREMENT) AUTO_INCREMENT = 109;

"". CREATE TABLE:

CREATE TABLE ITEM  (
ORDID               INT NOT NULL,
ITEMID              NUMERIC(4) NOT NULL,
PRODID              NUMERIC(6),
ACTUALPRICE         NUMERIC(8,2),
QTY                 NUMERIC(8),
ITEMTOT             NUMERIC(8,2),
CONSTRAINT ITEM_FOREIGN_KEY FOREIGN KEY (ORDID) REFERENCES ORDID (ID),
CONSTRAINT ITEM_PRIMARY_KEY PRIMARY KEY (ORDID,ITEMID));

INSERT "":

INSERT INTO ordid VALUES (null);
SELECT LAST_INSERT_ID();

Answer 3

AUTO_INCREMENT .

 create table myseq(
   my_id   int auto_increment primary key
 ) auto_increment=100;

ALTER TABLE reset , :

 alter table myseq auto_increment = 100;