如何制作另一个 MySQL 自动增量列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7344587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I make another MySQL auto increment column?
提问by web lover
MySQL doesn't support multiple auto increment columns.
MySQL 不支持多个自增列。
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 orderincrease automatically when I insert a new record?
order插入新记录时，是否有另一种解决方案可以使列的值自动增加？
回答by N.B.
You can do it from within your application by issuing another query that increases orderor 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:)
您可以通过发出另一个增加的查询在您的应用程序中执行此操作，order或者您可以创建一个为您执行此操作的触发器。无论你做什么，为了编程世界的理智 - 不要对列名使用保留字，例如order:)
回答by Riho
Why do you want 2 fields to be auto incremented - they will have the same values anyway so you can just use ID.
为什么您希望 2 个字段自动递增 - 无论如何它们将具有相同的值，因此您可以只使用 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.
如果您想让您的发票/订单具有序列号，那么您应该将该编号保留在单独的表中，并具有单独的逻辑来更新这些编号。
回答by HenrikW
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;
回答by Devart
I'd suggest you to set only orderfield as AUTO_INCREMENT; and calculate new value for the idfield manually. Here it is an example -
我建议您只将order字段设置为 AUTO_INCREMENT；并id手动计算该字段的新值。这是一个例子 -
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 |
+----+------+-------+
回答by sanmai
Consider adding a second table for the ordercolumn:
考虑为该order列添加第二个表：
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.
这种方法不需要事务也不需要锁定。
回答by loomcomputer
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 :)
不要想得太远 _ 只需将 last_id 添加到您的订单字符串中，就是这样:) 我是这样解决的，我添加了一个字符串，比如说“Order_2018_00”+ LAST_INSERT_ID()。所以这个字段对于每个添加的集合都是唯一的 :)
