Keskustelu: Ohjelmointikysymykset: MySQL ja uniikin INT-arvon lisäys, kun AUTO_INCREMENT ei ole käytössä

Minulla on MySQL-kanta. Täytyisi pystyä lisäämään uniikkeja INT-arvoja speed-sarakkeeseen, jossa AUTO_INCREMENT ei ole käytettävissä.

CREATE TABLE ki
(
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT
  , comp_id INT(10) UNSIGNED NOT NULL
  , speed INT(4) UNSIGNED NOT NULL DEFAULT 0
  , position INT(4) UNSIGNED NOT NULL DEFAULT 0

  , PRIMARY KEY (id)

  , UNIQUE INDEX (comp_id, speed, position)
  , INDEX (comp_id)

  , FOREIGN KEY (comp_id)
      REFERENCES competitions (id)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
) ENGINE=InnoDB CHARACTER SET latin1 COLLATE latin1_swedish_ci;

Haluan siis lisätä tauluun rivejä, useita. comp_id on aina 1, position aina 0 ja speed täytyisi olla suurin kannasta löytyvä speed-arvo + 1.

Joten jos yritän lisätä speed-arvoksi 3...

INSERT INTO ki (comp_id, speed, position) VALUES (1, 3, 0)

...niin jos arvo 3 on jo taulussa, speed pitäisikin olla 4.

En halua muokata kannassa jo olevia tietueita vaan ainoastaan lisätä uuden.

Mitenkähän tämä onnistuisi? Kuten totesin, speedin täytyy olla aina uniikki.

No, tyylikkäin vaihtoehto olisi tehdä siitä autoincrement, jos sen kerran kuuluu toimia kuten autoincrement..

Tällainen purkkaviritys on mahdollinen, mutta voi aiheuttaa virhetilanteen jos tulee kaksi yhtäaikaista pyyntöä:

INSERT INTO ki (comp_id, speed, position) SELECT 1, max(speed)+1, 0 from ki

Täytyisi myös tuo id-arvo olla uniikki, joten sen takia ei ole speedillä auto_incrementiä.

Pitää löytää joku tapa, ettei tule ongelmaa yhtäaikaisten pyyntöjen kanssa.

Kuulostaa nyt siltä, että yrität luoda kenttää, jonka arvo on aina "kiinteästi" id + delta, missä delta tunnetaan entuudestaan. Voit siis käyttää id-sarakkeen arvoa ja lisätä siihen tuon vakion arvon 'delta'.

Voisit myös harkita apc_inc()-funktion käyttöä.

The Alchemist kirjoitti:

Voit siis käyttää id-sarakkeen arvoa ja lisätä siihen tuon vakion arvon 'delta'.

Jos juuri lisään uuden rivin, niin mistä tiedän tuon lisätyn uuden id:n, johon tuon deltan lisään?

Et oikeastaan tarvitse saraketta tietokantaan, koska voit pitää tuon delta-arvon joko koodissa vakiona tai hakea sen apc-välimuistiin (ns. cache flushin yhteydessä). Jos tarvitset speed-arvoa sql-kyselyissä, niin voit kirjoittaa SELECT foo:n sijaan SELECT id + delta. Koska speed-kentän arvo vaikuttaa olevan suoraan verrannollinen id:hen, niin sorttaukset voi tehdä id-kentän perusteella.

Vaikea sanoa tarkemmin, kun ei ole mitään käsitystä siitä, mitä aiot kyseisellä arvolla edes tehdä. Kuulostaa oudolta, että "nopeus" kasvaisi rajattomasti ja jokaisen uuden tietueen myötä.

Viimeinen vaihtoehto on vaan tehdä update-kysely heti perään...

Juu, nopeus kasvaa aina, kun uusi tietue lisätään.

Oletetaan, että delta on 1. Menisikö lisäys sitten näin vai mistä saan tuon speed-arvon?

INSERT INTO ki (comp_id, speed, position) VALUES (1, id + 1, 0)

Vai tarkoititko, että poistaisin koko speed-sarakkeen? Se ei ole mahdollista.

xxmss kirjoitti:

Menisikö lisäys sitten näin vai mistä saan tuon speed-arvon?

INSERT INTO ki (comp_id, speed, position) VALUES (1, id + 1, 0)

No kokeilitko? Toimiko?

Veikkaan että ei koska VALUES listalle ei voi antaa sarakkeita ja toisaalta autoincrementin arvo on olemassa vasta lisäyksen jälkeen.

Periaatteessa olisi mahdollista tehdä triggeri joka ajetaan aina rivin lisäyksen jälkeen:

CREATE TRIGGER default_speed_assignment AFTER INSERT ON `ki`
FOR EACH ROW BEGIN
SET NEW.speed = NEW.id+1;
END;

Ongelma tässä ratkaisussa on, että jos tuolle speedille on uniikkindeksi (jota tosin ei luonnissa näy ainoastaan comp_id,speed,position yhdistelmä) niin ennen tuon triggerin suorittumista siellä pitäisi kuitenkin olla jokin arvo ja jos kaksi kertaa tulee sama arvo niin lisäys epäonnistuu.

Kun tuohon purkkatarpeeseen nyt ei kuitenkaan ole kuin huonoja ratkaisuja niin ehkä speed sarakkeeseen insertissä kirjoitettava 0 joka päivittyy tuolla triggerillä oikeaksi olisi huonoista paras ratkaisu.

Miten saisin tuota triggeriä muokattua niin, että SET NEW.speed olisikin edellisen speed + 1?

No laitoin jo tuossa eilen kello 20:56 tavan, jolla saat speedin laitettua 1 suuremmaksi kuin siihen asti suurin speed.

Grez kirjoitti:

No laitoin jo tuossa eilen kello 20:56 tavan, jolla saat speedin laitettua 1 suuremmaksi kuin siihen asti suurin speed.

Totesin aiemmin, että täytyy löytää jokin tapa, ettei tule ongelmia yhtäaikaisten pyyntöjen takia.

Paras ratkaisu olisi edelleen korjata koodi sellaiseksi, että voit yhdistää id- ja speed-sarakkeet. Jos on yksinkertaisempaa poistaa id-niminen sarake, niin tee sitten niin päin. Sillähän ei ole mitään väliä, mikä on tunnistimena toimivan sarakkeen nimi. Kun on purkkaan lähdetty, niin jonkinlaisen kompromissin joudut joka tapauksessa tekemään. Kahden sarakkeen tapauksessa tekisin itse tuon aiemmin mainitsemani apc-välimuistin päälle ratkaisun, jossa speed-laskuria ylläpidetään php:n päässä.

Niin periaatteessa 20:56 esittämäni keino lisättynä tarvittaessa taulujen lukituksella täyttäisi vaatimuksesi. Se on kuitenkin tehoton ratkaisu kun tulee ylimääräisiä lukituksia ja sisäkyselyitä (tosin kun speed on indeksoitu niin luulisi jopa MySQLn kykenevän max() operaatioon skannaamatta koko taulua). Todennäköisesti tällä ei ole mitään väliä tapauksessasi. Alchemistin ratkaisu olisi varmaankin tehokkaampi, jos vaan voit hyväksyä että laskuria ylläpidetään PHP:n puolella.

Laskurin ylläpito PHP:n puolella ei todellisuudessa ole mahdollista.

Taulussa voi olla esim. seuraavia arvoja:

id, comp_id, speed, position
1144, 1, 1, 0
4334, 1, 2, 0
5432, 1, 3, 0
6654, 1, 4, 0

Todellisuudessa comp_id voi saada myös muita arvoja kuin 1.

Totta kai se voi saada muita arvoja, muutenhan sekin sarake ja kolmen avaimen unique-indeksi olisi täysin turha. Se ei tosin liity siihen, voitko tallentaa tietoa apc-välimuistiin vai et. Sinun täytynee vain hyväksyä rajoitteet ja valita se ainoa toimiva tapa, joka sinulle on esitetty aikaleimassa 20:56.

Olet vielä jättänyt sanomatta, että myös kolmas sarake eli position voi saada muita arvoja. Olet suunnitellut tietokannan aivan päin helvettiä ja luonut sellaisen vaatimuksen, jota on täysin mahdoton tyydyttää ilman monimutkaista laskentaa.

Taidan poistaa unique indexin ja päivittää arvoja ainoastaan PHP:llä, niin ei tule samoja arvoja moneen kertaan.

Miksi poistaa Unique indeksi, jos kerran pystyt PHP:ssä varmistamaan että tupla-arvoja ei tule?

Ongelma on se, että arvot täytyisi saada lisättyä uniikkeina kantaan, jos minulla on unique index käytössä. PHP:llä saan siis muokattua jo kannassa olevan tiedon uniikiksi, mutta se, miten saan tiedon lisäysvaiheessa uniikiksi, on tässä se suurin ongelma käyttäessäni unique indexiä.

Okei. Millä sä sit lisäät tietoja kantaan, jos et PHP:llä? :o

Sä joudut jo nyt varmistamaan php:n päässä, että jotain niistä kolmesta sarakkeesta muuttaessa unique-ehto ei pauku, niin rivin lisääminen kantaan on vain yksi erikoistapaus koko sopassa.

Jos haluan, että speed-arvot alkavat luvusta 1, olisikohan tämä OK?

INSERT INTO ki (comp_id, speed, position)
VALUES (
   1,
   COALESCE((SELECT MAX(ki2.speed)
     FROM ki AS ki2
     WHERE ki2.comp_id = 1
   ), 1) + 1,
   0
)