How do I make another MySQL auto increment column?

Question

MySQL doesn't support multiple auto increment columns.

CREATE TABLE IF NOT EXISTS `parts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Is there another solution to make the value of column order increase automatically when I insert a new record?

there is also the "rowid" that goes up every inserted line : select rowid from parts where id = somenumber — Louis, Sep 08 '11 at 07:35
http://stackoverflow.com/questions/7085275/multiple-auto-increment-in-mysql — Haim Evgi, Sep 08 '11 at 07:37
So, you want two fields to be both auto_incemented and both store the exact same values in all rows? — ypercubeᵀᴹ, Sep 08 '11 at 08:11

score 7 · Answer 1 · answered Sep 08 '11 at 07:37

7

You can do it from within your application by issuing another query that increases order or you can create a trigger that does that for you. Whatever you do, for the sake of sanity of programming world - don't use reserved words for column names such as order :)

answered Sep 08 '11 at 07:37

N.B.

13,688
3
45
55

22

For the sake of sanity of programming world you got to escape column names with backticks and never ever have any problems with reserved words. – sanmai Sep 08 '11 at 08:24

score 3 · Answer 2 · edited Mar 31 '17 at 07:10

3

Why do you want 2 fields to be auto incremented - they will have the same values anyway so you can just use ID.

If you want to have your invoices/orders to have sequential numbers then you should keep that numbering in separate table and haves separate logic to update those numbers.

edited Mar 31 '17 at 07:10

AsgarAli

2,201
1
20
32

answered Sep 08 '11 at 07:35

Riho

4,523
3
33
48

4

They might be 'offset' from eachother, or possibly OP wants to 'close up holes' in the autoincrements. This article is MSSQL but might be relevant. http://stackoverflow.com/questions/349092/can-a-sql-server-table-have-two-identity-columns – StuartLC Sep 08 '11 at 07:40
MySQL only assigns an auto-incremented id if you don't specify one yourself. Thus you could use it to automatically assign a new value if none was filled in, but still allow the explicit choice of any of the existing values. So they wouldn't have to be the same for each record. – Stijn de Witt Mar 24 '17 at 11:38
For example we need two IDs when implementing temporal tables. On ID will be system and second ID will be used to identify record: 'id', 'country_id' – Eugen Konkov May 18 '18 at 10:47
1

You might want the column to be unique and auto-increment by default but at the same time you might want the be able to freely switch values e.g. the order in a list. If you were to reassign ID's this would obviously be very bad for existing relations. I don't see why you'd want another table for this logic either? – Luke Vincent May 31 '18 at 14:25

HenrikW · Answer 3 · 2011-09-12T11:25:48.483

3

Based on your original table:

CREATE TABLE IF NOT EXISTS `parts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `order` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

How about this single insert query:

INSERT INTO `parts` (`name`, `order`) 
SELECT 'name of new part', IFNULL(MAX(`order`), 0) + 1 FROM parts;

If the claim is true that this is not a safe query, one can simply introduce table locking as follows:

LOCK TABLES `parts` AS t1 WRITE, `parts` WRITE;

INSERT INTO `parts` (`name`, `order`) 
SELECT 'name of new part', IFNULL(MAX(`order`), 0) + 1 FROM `parts` AS t1;

UNLOCK TABLES;

edited Sep 12 '11 at 11:25

answered Sep 08 '11 at 08:26

HenrikW

106
1
4

1

Have not been able to produce any issues with this query; 50 queries in parallel, and still does not cause any duplicates. Is there a source where it says that this kind of query can cause issues? – HenrikW Sep 09 '11 at 08:48
1

@sanmai Added table locking to my answer. Would still like to have an explanation of when this single query - without table locking - would cause issues. – HenrikW Sep 12 '11 at 11:43

Devart · Answer 4 · 2011-09-08T08:44:55.763

I'd suggest you to set only order field as AUTO_INCREMENT; and calculate new value for the id field manually. Here it is an example -

CREATE TABLE IF NOT EXISTS `parts` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `order` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`, `order`)
) ENGINE=myisam DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

-- Add some new rows with manually auto-incremented id:

--    SELECT COALESCE(MAX(id), 0) + 1 INTO @next_id FROM parts;
--    INSERT INTO parts VALUES(@next_id, '', NULL);
--    SELECT COALESCE(MAX(id), 0) + 1 INTO @next_id FROM parts;
--    INSERT INTO parts VALUES(@next_id, '', NULL);

  INSERT INTO parts SELECT COALESCE(MAX(id), 0) + 1, '', NULL FROM parts;
  INSERT INTO parts SELECT COALESCE(MAX(id), 0) + 1, '', NULL FROM parts;

SELECT * FROM parts;
+----+------+-------+
| id | name | order |
+----+------+-------+
|  1 |      |     1 |
|  2 |      |     1 |
+----+------+-------+


-- Add some new rows for specified `id`, the value for `order` field will be set automatically:

INSERT INTO parts VALUES(2, '', NULL);
INSERT INTO parts VALUES(2, '', NULL);

+----+------+-------+
| id | name | order |
+----+------+-------+
|  1 |      |     1 |
|  2 |      |     1 |
|  2 |      |     2 |
|  2 |      |     3 |
+----+------+-------+

Yes, table can be locked. ...I edited my answer, now SELECT and INSERT statements are replaced with one INSERT...SELECT statement; in this case we can do it without locking. — Devart, Sep 08 '11 at 08:43

Ergest Basha · Answer 5 · 2021-08-25T09:43:28.753

For what is worth, if anybody need this again you can use this trigger. It increment order by +1 based on id and it works on multiple insert values

DELIMITER $$
CREATE TRIGGER MyTrigger BEFORE INSERT ON parts
FOR EACH ROW
BEGIN
    IF NEW.`order` < 0 THEN
        SET NEW.`order` = (select id from parts order by id desc limit 1) +1;
    ELSEIF NEW.`order` > 0 THEN
        SET NEW.`order` = (select id from parts order by id desc limit 1) +1;
    ELSEIF NEW.`order` is null THEN
        SET NEW.`order` = (select id from parts order by id desc limit 1) +1;
    END IF;
END $$
DELIMITER ;

score -1 · Answer 6 · answered Sep 28 '18 at 13:30

-1

Don't think too far _ just add the last_id to your order String and thats it :) I solved it this way that I added a string let's say "Order_2018_00" + LAST_INSERT_ID(). So this field is unique for each added set :)

answered Sep 28 '18 at 13:30

loomcomputer

1

sanmai · Answer 7 · 2011-09-09T14:58:46.683

-1

Consider adding a second table for the order column:

CREATE TABLE IF NOT EXISTS `parts_order` (
  `order` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`order`)
) ENGINE=MyISAM;

Example usage:

-- First we insert a new empty row into `parts_order`
INSERT INTO `parts_order` VALUES (NULL);
-- Next we insert a new row into `parts` with last inserted id from `parts_order`
INSERT INTO `parts` SET `name` = "new name", `order` = LAST_INSERT_ID();

This approach does not require transactions nor locking.

edited Sep 09 '11 at 14:58

answered Sep 08 '11 at 07:37

sanmai

29,083
12
64
76

Table is MyISAM, why transaction then? – N.B. Sep 08 '11 at 07:38
If you read carefully, you'll see that the table type is MyISAM. That table type does NOT support transactions. Whether it's "good" or not, transactions **will not work** with MyISAM. – N.B. Sep 08 '11 at 08:08
Okay, I removed that section. – sanmai Sep 08 '11 at 08:20

How do I make another MySQL auto increment column?

7 Answers7

Linked