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,具有以下数据:
EG, With this data:
table_id database_id other_columns
1 1
2 1
3 1
1 2
2 2
如果我要添加包含dabase_id为1的数据,那么我希望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屋!