如何在mysql中创建两个自动增量列? [英] How to create two auto increment column 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. what should I do?

推荐答案

不知道为什么需要两列自动递增值，没有意义...但是，如果您坚持-
您可以在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)


表格

EXAMPLE #1: STORED PROCEDURE (SP)


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

存储过程

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 ;

致电SP

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

---

示例2:用户定义的功能(UDF)


表格

---

EXAMPLE #2: USER-DEFINED FUNCTION (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 ;

使用UDF插入

Insert using the 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.

这篇关于如何在mysql中创建两个自动增量列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！