MySQL自动生成以下数字

Question

我的关系表中有4列：

1. ID
2. 粘航向id
3. 与盖子相关的课程独一无二的id
4. course_based_id -解释如下

在另一个名为lessons和courses的表中，有许多具有独特id的课程和课程。这张表是，他们之间的表。从根本上说，它把独特的课程标识分配给了独特的课程标识。但是..。有必要建立以课程为基础的课程id。我的意思是，例如，让我们从屏幕截图中查看第一行和第二行。

​

​

第一课是第二课，第三课是第二节课.诸若此类。所以，如果我插入cid 2，lid 128，它必须是第三课，当然是第二课。

现在，问题是，我想在插入时自动生成这个数字：

$q="INSERT INTO `courses-lessons` (`cid`, `lid`) VALUES (?,?)";

但不知道该怎么做。一些MySQL函数？或者内部查询什么的？

Answer 1

如果您使用MyISAM引擎来存储此表，则可以相对轻松地完成此操作:您只需这样定义字段：

cid INT NOT NULL,
course_based_id INT NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (cid, course_based_id)

然后，任何INSERT都将自动为基于course_based_id的course_based_id分配一个新的(+1)值。这里有一个说明这个概念的小提琴。

您可以在MySQL文档中找到有关它的更多信息。

不幸的是，InnoDB引擎中没有这样的机制。因此，也许您可以使用以下例程(这是一个伪代码，有一些细微差别)：

1) lock table for writing; 
2) @x = SELECT MAX(course_based_id) WHERE cid = %cid_to_be_inserted%;
3) INSERT (..., course_based_id) VALUES (..., @x + 1);
4) unlock table;

我建议检查这个答案；它是针对PostgreSQL的，而不是MySQL的，但是详细描述了一个可能的实现。

更新：这里是实现该行为的实际查询集的示例：

CREATE TABLE tx
  (id INT NOT NULL, cid INT NOT NULL, lid INT NOT NULL,
   course_based_id INT NOT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY (cid, course_based_id)
);

LOCK TABLE tx WRITE, tx AS t READ;

INSERT INTO tx (id, cid, lid, course_based_id)
  SELECT 2, 2, 1, 
         1 + COALESCE(
               (SELECT MAX(course_based_id) 
                  FROM tx AS t 
                 WHERE cid = 2), 
             0);

 INSERT INTO tx (id, cid, lid, course_based_id)
   SELECT 3, 2, 3, 
          1 + COALESCE(
                (SELECT MAX(course_based_id) 
                  FROM tx AS t WHERE cid = 2),
              0);

 UNLOCK TABLES;

正如您所看到的，没有直接计算下一个course_based_id值，但是我们在这里得到了序列。

Answer 2

如果您想要引用完整性(Foreign )，您使用的是InnoDB，所以(很好) MyISAM技巧不起作用。

您可以做的是在插入到表中时生成这些course_based_id。

编写一个不太复杂的过程来执行插入，并且不允许任何其他代码插入到此表中。您还需要一个用于更新的过程(如果您介意这个course_based_id中有空白的话，也需要一个用于删除的过程)。

因此，与其说：

INSERT INTO courses_lessons 
  (cid, lid) 
VALUES 
  ( ?cid, ?lid ) ;

您将被包含在存储过程中，以捕获任何错误(副本等)：

INSERT INTO courses_lessons 
  (cid, lid, course_based_id) 
SELECT 
  ?cid, ?lid, 
  1 + COALESCE( ( SELECT MAX(course_based_id) 
                  FROM courses_lessons 
                  WHERE cid = ?cid
                ), 0)
FROM
  dual ;

对于这个表，您实际上不需要id列。(cid, lid)上的主键将足以识别行。您还需要在(cid, course_based_id)上添加一个唯一的键

ALTER TABLE courses_lessons 
  ADD CONSTRAINT UQ_cid_course_based_id
    UNIQUE cid_course_based_id_UQ
      (cid, course_based_id) ;