MySQL 如何在mysql中创建两个自增列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22824439/
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 to create two auto increment column in mysql?
提问by John
CREATE TRIGGER test1 AFTER INSERT ON `course_metadata_31`
FOR EACH ROW BEGIN
update `course_metadata_31`set `articleID`=`articleID`+1
END;
I am using this approach. When I will insert new entry in course_metadata_31 it should increment articleID too. Because I want articleID as another auto increment column. what should I do?
我正在使用这种方法。当我将在 course_metadata_31 中插入新条目时，它也应该增加 articleID。因为我想要 articleID 作为另一个自动增量列。我该怎么办？
回答by Thielicious
No idea why you need two columns auto incrementing values, there is no point... but if you insist -
You can accomplish it in a UDF or SP this way you have multiple columns auto incrementing a value.
不知道为什么你需要两列自动递增值，没有意义......但是如果你坚持 -
你可以在 UDF 或 SP 中完成它，这样你就有多个列自动递增一个值。
EXAMPLE #1: STORED PROCEDURE (SP)
Table
示例 #1：存储过程 (SP)
表
CREATE TABLE tests (
test_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
test_num INT(10) NULL,
test_name VARCHAR(10) NOT NULL
);
Stored Procedure
存储过程
DELIMITER $$
CREATE PROCEDURE autoInc (name VARCHAR(10))
BEGIN
DECLARE getCount INT(10);
SET getCount = (
SELECT COUNT(test_num)
FROM tests) + 1;
INSERT INTO tests (test_num, test_name)
VALUES (getCount, name);
END$$
DELIMITER ;
Call the SP
呼叫 SP
CALL autoInc('one');
CALL autoInc('two');
CALL autoInc('three');
Look up the table
查表
SELECT * FROM tests;
+---------+----------+-----------+
| test_id | test_num | test_name |
+---------+----------+-----------+
| 1 | 1 | one |
| 2 | 2 | two |
| 3 | 3 | three |
+---------+----------+-----------+
EXAMPLE #2: USER-DEFINED FUNCTION (UDF)示例 2：用户定义的函数 (UDF)
Table桌子
CREATE TABLE tests (
test_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
test_num INT(10) NULL,
test_name VARCHAR(10) NOT NULL
);
User-defined Function
用户自定义函数
DELIMITER $$
CREATE FUNCTION autoInc ()
RETURNS INT(10)
BEGIN
DECLARE getCount INT(10);
SET getCount = (
SELECT COUNT(test_num)
FROM tests) + 1;
RETURN getCount;
END$$
DELIMITER ;
Insert using the UDF
使用 UDF 插入
INSERT INTO tests (test_num, test_name) VALUES (autoInc(), 'one');
INSERT INTO tests (test_num, test_name) VALUES (autoInc(), 'two');
INSERT INTO tests (test_num, test_name) VALUES (autoInc(), 'three');
Look up the table
查表
SELECT * FROM tests;
+---------+----------+-----------+
| test_id | test_num | test_name |
+---------+----------+-----------+
| 1 | 1 | one |
| 2 | 2 | two |
| 3 | 3 | three |
+---------+----------+-----------+
These have been tested and verified. I'd personally use the function, it's more flexible.
这些已经过测试和验证。我个人会使用该功能，它更灵活。
回答by QuentinB
If you have two auto_increment columns they would be the same, so there is no point having two auto_increment columns.
如果您有两个 auto_increment 列，它们将是相同的，因此有两个 auto_increment 列没有意义。
回答by Shivendra Nath Misra
My use case was to have to AI columns, one as a PK and another one to shuffle the order of items.
我的用例是必须使用 AI 列，一个作为 PK，另一个用于调整项目的顺序。
I thought of making a MySQL trigger to initialise the second AI column with the value of the first column so that I can shuffle it later. But turns out the after insert triggert in MySQL did not allow updation
我想制作一个 MySQL 触发器，用第一列的值初始化第二个 AI 列，以便我稍后可以对其进行洗牌。但事实证明 MySQL 中的 after insert triggert 不允许更新
I accomplished it very easily using Sequelize Hooks
我使用Sequelize Hooks很容易地完成了它
You can use something similar to the following if you are working with Sequelize:
如果您正在使用 Sequelize，您可以使用类似于以下内容的内容：
db.define('songs', { <your_model_definition> }, {
hooks: {
afterCreate: async (item, {}) => {
//id here is the PK which is Auto Increment (AI)
//order is the second key I want as AI
await item.update({ order: item.id});
console.log("afterCreate", item);
}
}
})
回答by spencer7593
Q:What should I do?
问：我该怎么办？
A:Review the reasons you think you need a second AUTO_INCREMENT columns, carefully consider what it is you are trying to achieve.
答：查看您认为需要第二个 AUTO_INCREMENT 列的原因，仔细考虑您要实现的目标。
Come up with an alternate design that doesn't require you to add two AUTO_INCREMENTcolumns to a MySQL table.
想出一个替代设计，不需要您向AUTO_INCREMENTMySQL 表添加两列。
If you do really need to have a second column with "auto increment" type behavior, one way to get that is to add a second dummy table with an auto_increment column, and use a BEFORE INSERT trigger to do an insert into the dummy table, and retrieve the id value that was inserted.
如果您确实需要具有“自动增量”类型行为的第二列，获得它的一种方法是添加第二个带有 auto_increment 列的虚拟表，并使用 BEFORE INSERT 触发器插入虚拟表，并检索插入的 id 值。
Something like this:
像这样的东西：
CREATE TABLE course_metadata_31_ai
( i INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
);
DELIMITER $$
CREATE TRIGGER course_metadata_31_bi
BEFORE INSERT ON course_metadata_31
FOR EACH ROW
BEGIN
DECLARE lii INT;
IF ( NEW.article_id IS NULL OR NEW.article_id < 1 ) THEN
-- set article_id to auto_increment from dummy table
INSERT INTO course_metadata_31_ai (i) VALUES (NULL);
SELECT LAST_INSERT_ID() INTO lii;
SET NEW.article_id = lii;
-- DELETE FROM course_metadata_31_ai WHERE i < lii;
ELSE
-- set auto_increment col in dummy table to match a larger article_id
UPDATE course_metadata_31_ai t
JOIN ( SELECT MAX(r.i) AS i
FROM course_metadata_31_ai r
) s
ON s.i = t.i
SET t.i = GREATEST(t.i,NEW.article_id);
END IF;
END;
$$
DELIMITER ;
NOTE
笔记
You wouldn't necessarily have to delete rows from the dummy table, you wouldn't have to do it in the trigger, but there's no point in keeping them. You'd probably only really need to keep the row that has the largest auto_increment value, just as a prevention against the AUTO_INCREMENT from inadvertently being set lower with an ALTER TABLE statement.)
您不必从虚拟表中删除行，也不必在触发器中执行此操作，但保留它们没有意义。您可能只需要保留具有最大 auto_increment 值的行，就像防止 AUTO_INCREMENT 被 ALTER TABLE 语句无意中设置得较低一样。）
The IF ELSEin the trigger body is designed to emulate auto_increment behavior... if a value is supplied for article_id, use that, AND if it's larger than the current max value of auto_increment column in the dummy table, update that row in the dummy table to have the larger value (so the next insert that needs an auto_increment value will get the next higher value).
将IF ELSE在触发体旨在模拟AUTO_INCREMENT行为......如果一个值是为article_id的供应，使用，如果它是比虚表AUTO_INCREMENT列，更新的当前最大值更大的该行中的虚表来具有更大的值（因此需要 auto_increment 值的下一个插入将获得下一个更高的值）。
回答by Glynne Turner
in the phpMyAdmin set the column property to AI its a tick box on the table structure. you dont even need to pass the details to the database
在 phpMyAdmin 中将列属性设置为 AI 其表结构上的复选框。您甚至不需要将详细信息传递给数据库
