mysql twee kolom met de primaire sleutel met auto-increment

Ik heb meerdere databases met dezelfde structuur waarin de gegevens is soms gekopieerd over. Om data-integriteit te behouden ben ik met twee kolommen als de primaire sleutel. De ene is een database-id die is gekoppeld aan een tabel met informatie over elke database. De andere is een tabel-toets. Het is niet uniek omdat het mogelijk meerdere rijen met deze waarde is hetzelfde, maar met verschillende waarden in de database_id kolom.

Ik ben van plan op het maken van de twee kolommen in een gezamenlijke primaire sleutel. Maar ik wil ook de tafel-toets om auto increment – maar op basis van de database_id kolom.

BV, Met deze gegevens:

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

Als ik het toevoegen van gegevens met de dabase_id van 1 dan wil ik table_id worden automatisch ingesteld op 4. Als de dabase_id is ingevoerd als 2 dan wil ik table_id worden automatisch ingesteld op 3. enz.

Wat is de beste manier om dit te bereiken in MySql.

 

4 Replies
  1. 42

    als u myisam

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

    Voor MyISAM en BDB tabellen kunt u
    geef AUTO_INCREMENT op een secundaire
    kolom in een multi-kolom index. In
    dit geval, de gegenereerde waarde voor de
    AUTO_INCREMENT kolom is berekend als
    MAX(auto_increment_column) + 1 WAAR
    prefix=gegeven-prefix. Dit is handig
    wanneer u de gegevens in besteld
    groepen.

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

    Voor uw voorbeeld:

    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)
    • Waarschuwing! Dit veroorzaakt replicatie problemen, zie dev.mysql.com/doc/refman/5.1/en/… An INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT column that is not the first column of this composite key is not safe [..]
    • Snelle opmerking dat InnoDB (en eventueel andere motoren) ondersteunen dit van MySQL 5.1.
    • Dat lijkt niet waar te zijn – dit werkt overigens niet in MySQL 5.5 met InnoDB. Dus, niet over een secundaire AUTO_INCREMENT met transacties…
    • Vreemd, ik zag het in de documentatie. Ik heb het probleem van het niet werken op mijn productie server (MySQL 5.5.34). Ik eindigde met mijn verzoek het werk doen door met behulp van de query uit de database gedaan zou hebben intern (MAX(auto_increment_column) + 1 WHERE prefix=given-prefix).
    • het moet vast in 5.1.65 per officiële documenten.
    • het werkt niet in InnoDB, tenzij u de volgorde wijzigen van de primaire sleutel (id,grp) in plaats van (grp,id), blijkbaar is de autoincrement kolom moet het eerst gaan.
    • Voor zover ik weet, de functie zal nooit aansprakelijk gesteld worden in InnoDB. Ik zie het niet in 8.0. Stel je stem in bugs.mysql.com .

  2. 21

    Hier is een aanpak bij het gebruik van innodb die ook zeer performante door de geclusterde composite index – alleen beschikbaar met 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;
    • Sorry, moet ik heb opgegeven dat ik met behulp van MyISAM. Upvoted je toch.
    • leesmij-bestand… mysqlperformanceblog.com/2011/03/18/…
    • deze aanpak vereist van de aanvraag synchronisatie in geval van meerdere threads bijwerken van dezelfde DB in een keer, toch?
  3. 0

    De oplossing van DTing is uitstekend en werken. Maar toen probeerde hetzelfde AWS Aurora, het werkte niet en klagen de onderstaande fout.

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

    Vandaar suggereren json-gebaseerde oplossing hier.

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

    Hebben de eerste primaire sleutel buiten, en tweede primaire sleutel in de json en het maken van tweede primaire sleutel waarde als 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)
      );

    En de uitvoer is zoals hieronder

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

    Als uw secundaire sleutels zijn enorm, en bang zijn van het gebruik van json, dan zou ik adviseren om opgeslagen procedure, om te controleren voor MAX(secondary_column) samen met sluiting onderaan.

    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;
    • Ik zie niet in hoe dit betrekking heeft op de vraag.
    • Alles wat de gebruiker wil, is sequentie begint gebaseerd op twee kolommen. Het is niet mogelijk in Innodb engine. Daarom suggereert json manier om het te doen.
    • Zo zou deze sql-query ‘ s uitvoeren op InnoDB?
    • Ja.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *