mysql clave principal de dos columnas con autoincremento

Tengo varias bases de datos con la misma estructura en la que a veces se copian los datos. Para mantener la integridad de los datos, utilizo dos columnas como clave principal. Uno es una identificación de base de datos, que se vincula a una tabla con información sobre cada base de datos. La otra es una clave de tabla. No es único porque puede tener varias filas con este valor siendo el mismo, pero con valores diferentes en la columna database_id.

Estoy planeando convertir las dos columnas en una clave primaria conjunta. Sin embargo, también quiero establecer la clave de la tabla para el incremento automático, pero en función de la columna database_id.

EG, con esta información:

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

Si agrego datos que incluyen el dabase_id de 1, entonces quiero que table_id se establezca automáticamente en 4. Si el dabase_id se ingresa como 2, entonces quiero que table_id se establezca automáticamente en 3. etc.

¿Cuál es la mejor manera de lograr esto en MySql?

si estás usando myisam

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

Para las tablas MyISAM y BDB, puede especificar AUTO_INCREMENT en una columna secundaria en un índice de varias columnas. En este caso, el valor generado para la columna AUTO_INCREMENT se calcula como MAX (auto_increment_column) + 1 WHERE prefix = given-prefix. Esto es útil cuando desea colocar datos en 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 | +--------+----+---------+ 

Para tu ejemplo:

 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) 

Aquí hay un enfoque al usar innodb, que también será muy eficaz debido al índice compuesto agrupado, solo disponible con 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; 

puede hacer que la clave primaria de dos columnas sea unique y la clave de incremento automático primary .

La solución proporcionada por DTing es excelente y funciona. Pero cuando intenté lo mismo en AWS Aurora, no funcionó y me quejé del siguiente error.

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

De ahí que sugiera una solución basada en json aquí.

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

Tenga la primera clave primaria fuera, y la segunda clave primaria dentro de json y haga que la segunda clave primaria sea 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) ); 

Y la salida es como abajo

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

Si sus claves secundarias son enormes, y tiene miedo de usar json, entonces sugeriría tener un procedimiento almacenado, para verificar MAX (secondary_column) junto con el locking como se muestra a continuación.

 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;