如何在 MySQL 中创建两个自增列? [英] How to create two auto increment columns in MySQL?
问题描述
CREATE TRIGGER test1 AFTER INSERT ON `course_metadata_31`
FOR EACH ROW BEGIN
UPDATE `course_metadata_31`
SET `articleID` = `articleID` + 1
END;
我正在使用这种方法.当我将在 course_metadata_31
中插入新条目时,它也应该增加 articleID
.因为我想要 articleID
作为另一个自增列.
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.
我该怎么办?
推荐答案
不知道为什么你需要两列自动递增的值,没有意义...但如果你坚持 -
你可以在 UDF 中完成或 SP 这样你就有多个列自动递增一个值.
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.
示例 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
);
存储过程
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 ;
致电 SP
CALL autoInc('one');
CALL autoInc('two');
CALL autoInc('three');
查表
SELECT * FROM tests;
+---------+----------+-----------+
| test_id | test_num | test_name |
+---------+----------+-----------+
| 1 | 1 | one |
| 2 | 2 | two |
| 3 | 3 | three |
+---------+----------+-----------+
<小时>
示例 2:用户定义的函数 (UDF)
表格
CREATE TABLE tests (
test_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
test_num INT(10) NULL,
test_name VARCHAR(10) NOT NULL
);
用户自定义函数
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 ;
使用 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');
查表
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.
这篇关于如何在 MySQL 中创建两个自增列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!