Mysql два столбца первичного ключа с автоматическим приращением

У меня есть несколько баз данных с той же структурой, в которой данные иногда копируются. Чтобы поддерживать целостность данных, я использую два столбца в качестве первичного ключа. Один из них - это идентификатор базы данных, который ссылается на таблицу с информацией о каждой базе данных. Другой - это ключ таблицы. Он не уникален, поскольку он может иметь несколько строк с таким же значением, но разными значениями в столбце database_id.

Я планирую сделать два столбца совместным первичным ключом. Однако я также хочу установить ключ таблицы для автоматического увеличения, но на основе столбца database_id.

EG, с этими данными:

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. и т.д.

Каков наилучший способ достичь этого в MySql.

Ответ 1

если вы используете 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 ГДЕ Префикс = дано-префикс. Это полезно когда вы хотите поместить данные в заказ группы.

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)

Ответ 2

Здесь один подход при использовании innodb, который также будет очень результативным из-за кластерного составного индекса - доступен только с 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;

Ответ 3

вы можете сделать первичный ключ двух столбцов unique и клавишу автоматического увеличения primary.

Ответ 4

Решение, предоставляемое DTing, превосходно и работает. Но когда он пробовал то же самое в AWS Aurora, он не работал и жаловался на ошибку ниже.

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

Отсюда и предлагается решение на основе json.

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

Имейте первый первичный ключ снаружи и второй первичный ключ внутри json и введите второе значение первичного ключа как 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)
  );

И результат выглядит следующим образом:

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

Если ваши вторичные ключи огромны и боятся использовать json, я бы предложил хранить хранимую процедуру, чтобы проверить MAX (secondary_column) вместе с блокировкой, как показано ниже.

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;