How do I make another MySQL auto increment column?

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?

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 :)

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;

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.

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 |
+----+------+-------+

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 ;

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.

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 :)