MySQL: 多个主键和自增属性

Question

我在设置MySQL表方面还很新，有些比较高级的设置还不太能够掌握。

我有两个作为组合主键的列，一个是日期，另一个是ID。 我希望ID是自动递增整数。 对于每个日期，我想将自动整数重置为0，例如：

|-----------------|
|Date       | ID  |
|-----------------|
|2012-06-18 | 1   |
|2012-06-18 | 2   |
|2012-06-18 | 3   |
|2012-06-19 | 1   |
|2012-06-19 | 2   |
|2012-06-20 | 1   |
|-----------------|

谢谢

- AdmiralJonB

---

自动递增不是那样工作的。因此，您将不得不手动插入这些记录，以便每个日期都有递增的数字。 - Neeraj

看我的回答，它适用于 MyISAM，但不适用于 InnoDB。 - ppsreejith

Answer 1

这应该可以工作。

CREATE TABLE  `answer`(
  `dates` DATE NOT NULL,
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`dates`,`id`)
) ENGINE=MyISAM;

已知会对innoDB造成问题。希望这能帮到你。

编辑：结果

2012-06-19  1
2012-06-19  2
2012-06-19  3
2012-07-19  1
2012-07-19  2
2012-08-19  1

在php myadmin中。

- ppsreejith

Answer 2

对我来说，MySQL可以自动完成您想要的操作。

mysql> CREATE TABLE TestData(Date date not null, ID int unsigned not null auto_increment, PRIMARY KEY(Date, ID));

mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-18";
mysql> INSERT INTO TestData SET Date = "2012-06-19";
mysql> INSERT INTO TestData SET Date = "2012-06-19";
mysql> INSERT INTO TestData SET Date = "2012-06-20";

mysql> select * from TestData;
+------------+----+
| Date       | ID |
+------------+----+
| 2012-06-18 |  1 |
| 2012-06-18 |  2 |
| 2012-06-18 |  3 |
| 2012-06-19 |  1 |
| 2012-06-19 |  2 |
| 2012-06-20 |  1 |
+------------+----+

没有任何魔法涉及到。

- Johan Soderberg

---

创建操作导致以下错误：错误代码：1075。表定义不正确；只能有一个自动列，并且必须定义为键。 - Richard

@RichardKing：哦？对我来说不是这样的，你用的是哪个版本？我在5.1.51-log上测试过，在更早的mysql版本上也使用过它（自mysql 3以来）。表格是MyISAM。 - Johan Soderberg

谢谢。使用 ENGINE=MyISAM 解决了我的问题。 - Killer

@JohanSoderberg 这是解决问题的完美方法，但如何在innoDB引擎中实现呢？我们可以吗？ - Andy Su

Answer 3

你可以创建一个 before insert 触发器。

DELIMITER $$
CREATE TRIGGER `composite_auto_increment` BEFORE INSERT ON `your_table`
FOR EACH ROW
BEGIN
    DECLARE max_id INT(11); -- add the appropriate column length from your table definition
    SELECT ID FROM `your_table` WHERE `Date` = DATE(NOW()) INTO max_id;
    SET NEW.ID = IF(ISNULL(max_id), 1, max_id + 1);
END$$

如果某一天已存在ID，则ID将递增。如果不存在，则设置为1。需要注意的是，在这种情况下，表定义中的ID并不是AUTO_INCREMENT，而是通过触发器实现。

- cypher

---

这是错误的，你在获取最大值之前需要锁定表格。如果不这样做，同时运行的两个触发器将得到相同的标识符。 - mavroprovato

在MyISAM中，插入操作不会并发运行。而在InnoDB中，你可以进行锁定。不过这是一个好的观点。 - cypher

Answer 4

在触发器中： 将SELECT ID FROM your_table WHERE Date = DATE(NOW()) INTO max_id; 改为： SELECT max(ID) FROM your_table WHERE Date = NEW.key_field INTO max_id;

但更好的方法是按键进行锁定。 这对于InnoDB上的并发插入更好。

- huskye