mysql两列唯一_mysql两列主键，具有自动增量

小编典典

如果您使用的是myisam

对于MyISAM和BDB表，可以在多列索引的第二列上指定AUTO_INCREMENT。在这种情况下，将为AUTO_INCREMENT列生成的值计算为MAX(auto_increment_column)+

1 WHERE prefix = given-prefix。当您要将数据放入有序组中时，这很有用。

CREATE TABLE animals (

grp ENUM('fish','mammal','bird') NOT NULL,

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (grp,id)

) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES

('mammal','dog'),('mammal','cat'),

('bird','penguin'),('fish','lax'),('mammal','whale'),

('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+

| grp | id | name |

+--------+----+---------+

| fish | 1 | lax |

| mammal | 1 | dog |

| mammal | 2 | cat |

| mammal | 3 | whale |

| bird | 1 | penguin |

| bird | 2 | ostrich |

+--------+----+---------+

例如：

mysql> CREATE TABLE mytable (

-> table_id MEDIUMINT NOT NULL AUTO_INCREMENT,

-> database_id MEDIUMINT NOT NULL,

-> other_column CHAR(30) NOT NULL,

-> PRIMARY KEY (database_id,table_id)

-> ) ENGINE=MyISAM;

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (database_id, other_column) VALUES

-> (1,'Foo'),(1,'Bar'),(2,'Baz'),(1,'Bam'),(2,'Zam'),(3,'Zoo');

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM mytable ORDER BY database_id,table_id;

+----------+-------------+--------------+

| table_id | database_id | other_column |

+----------+-------------+--------------+

| 1 | 1 | Foo |

| 2 | 1 | Bar |

| 3 | 1 | Bam |

| 1 | 2 | Baz |

| 2 | 2 | Zam |

| 1 | 3 | Zoo |

+----------+-------------+--------------+

6 rows in set (0.00 sec)

2020-05-17