mysql 两列主键自动递增 [英] mysql two column primary key with auto-increment

问题描述

我有多个结构相同的数据库，有时会在其中复制数据.为了保持数据完整性，我使用两列作为主键.一个是数据库 ID，它链接到一个表，其中包含有关每个数据库的信息.另一个是表键.它不是唯一的，因为它可能有多个行，该值相同，但 database_id 列中的值不同.

I have multiple databases with the same structure in which data is sometimes copied across. In order to maintain data integrity I am using two columns as the primary key. One is a database id, which links to a table with info about each database. The other is a table key. It is not unique because it may have multiple rows with this value being the same, but different values in the database_id column.

我打算将这两列变成一个联合主键.但是我也想将表键设置为自动递增 - 但基于 database_id 列.

I am planning on making the two columns into a joint primary key. However I also want to set the table key to auto increment - but based on the database_id column.

EG，有了这个数据:

table_id   database_id     other_columns
1          1
2          1
3          1
1          2
2          2

如果我添加的数据包含 1 的 dabase_id，那么我希望 table_id 自动设置为 4.如果 dabase_id 输入为 2，那么我希望 table_id 自动设置为 3.等

If I am adding data that includes the dabase_id of 1 then I want table_id to be automatically set to 4. If the dabase_id is entered as 2 then I want table_id to be automatically set to 3. etc.

在 MySql 中实现这一目标的最佳方法是什么.

What is the best way of achieving this in MySql.

推荐答案

如果你使用的是 myisam

if you are using myisam

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

对于 MyISAM 和 BDB 表，您可以在辅助节点上指定 AUTO_INCREMENT多列索引中的列.在在这种情况下，生成的值AUTO_INCREMENT 列计算为MAX(auto_increment_column) + 1 WHERE前缀=给定的前缀.这很有用当您想将数据放入有序组.

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

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)

这篇关于mysql 两列主键自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！