chave primária de duas colunas mysql com incremento automático

Eu tenho vários bancos de dados com a mesma estrutura na qual os dados às vezes são copiados. Para manter a integridade dos dados, estou usando duas colunas como chave primária. Um é um id do database, que liga a uma tabela com informações sobre cada database. O outro é uma chave de tabela. Não é exclusivo porque pode ter várias linhas com este valor sendo o mesmo, mas valores diferentes na coluna database_id.

Estou planejando fazer as duas colunas em uma chave primária conjunta. No entanto, eu também quero definir a chave da tabela para auto incremento – mas com base na coluna database_id.

EG, com esses dados:

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

Se eu estou adicionando dados que incluem o dabase_id de 1, então eu quero que table_id seja automaticamente definido como 4. Se dabase_id for inserido como 2, então eu quero que table_id seja automaticamente definido como 3. etc.

Qual é a melhor maneira de conseguir isso no MySql.

se você estiver usando myisam

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

Para tabelas MyISAM e BDB você pode especificar AUTO_INCREMENT em uma coluna secundária em um índice de múltiplas colunas. Nesse caso, o valor gerado para a coluna AUTO_INCREMENT é calculado como MAX (auto_increment_column) + 1 WHERE prefix = given-prefix. Isso é útil quando você deseja colocar dados em grupos ordenados.

 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 | +--------+----+---------+ 

Por exemplo:

 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) 

Aqui está uma abordagem ao usar o innodb, que também terá um desempenho muito bom, devido ao índice composto agrupado – disponível apenas com o innodb …

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

 drop table if exists db; create table db ( db_id smallint unsigned not null auto_increment primary key, next_table_id int unsigned not null default 0 )engine=innodb; drop table if exists tables; create table tables ( db_id smallint unsigned not null, table_id int unsigned not null default 0, primary key (db_id, table_id) -- composite clustered index )engine=innodb; delimiter # create trigger tables_before_ins_trig before insert on tables for each row begin declare v_id int unsigned default 0; select next_table_id + 1 into v_id from db where db_id = new.db_id; set new.table_id = v_id; update db set next_table_id = v_id where db_id = new.db_id; end# delimiter ; insert into db (next_table_id) values (null),(null),(null); insert into tables (db_id) values (1),(1),(2),(1),(3),(2); select * from db; select * from tables; 

você pode tornar a chave primária de duas colunas unique e a chave primary incremento automático .

A solução fornecida pela DTing é excelente e funcional. Mas quando tentou o mesmo no AWS Aurora, não funcionou e queixou-se do erro abaixo.

 Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key 

Daí sugerindo solução baseada em json aqui.

 CREATE TABLE DB_TABLE_XREF ( db VARCHAR(36) NOT NULL, tables JSON, PRIMARY KEY (db) ) 

Ter a primeira chave primária fora e a segunda chave primária dentro do json e fazer o segundo valor de chave primária como auto_incr_sequence.

 INSERT INTO `DB_TABLE_XREF` (`db`, `tables`) VALUES ('account_db', '{"user_info": 1, "seq" : 1}') ON DUPLICATE KEY UPDATE `tables` = JSON_SET(`tables`, '$."user_info"', IFNULL(`tables` -> '$."user_info"', `tables` -> '$."seq"' + 1), '$."seq"', IFNULL(`tables` -> '$."user_info"', `tables` -> '$."seq"' + 1) ); 

E a saída é como abaixo

 account_db {"user_info" : 1, "user_details" : 2, "seq" : 2} product_db {"product1" : 1, "product2" : 2, "product3" : 3, "seq" : 3} 

Se suas chaves secundárias são enormes, e com medo de usar json, então eu sugiro ter procedimento armazenado, para verificar MAX (secondary_column) junto com o bloqueio como abaixo.

 SELECT table_id INTO t_id FROM DB_TABLE_XREF WHERE database = db_name AND table = table_name; IF t_id = 0 THEN SELECT GET_LOCK(db_name, 10) INTO acq_lock; -- CALL debug_msg(TRUE, "Acquiring lock"); IF acq_lock = 1 THEN SELECT table_id INTO t_id FROM DB_TABLE_XREF WHERE database_id = db_name AND table = table_name; -- double check lock IF t_id = 0 THEN SELECT IFNULL((SELECT MAX(table_id) FROM (SELECT table_id FROM DB_TABLE_XREF WHERE database = db_name) AS something), 0) + 1 into t_id; INSERT INTO DB_TABLE_XREF VALUES (db_name, table_name, t_id); END IF; ELSE -- CALL debug_msg(TRUE, "Failed to acquire lock"); END IF; COMMIT;