Keskustelu: Ohjelmointikysymykset: MySQL, auto_increment ja Next autoindex

Käytössä MySQL / InnoDB

Taulun id-sarake on int(10), not null ja auto increment.

Olen osittain testimielessä yrittänyt tehdä tällaista SQL-koodia, joka lisäisi tauluun käyttäjän tiedot samalla id-numerolla kuin kannassa jo on.

Ja jos käyttäjän voimassa-tieto on muuttunut, silloin käyttäjän tiedot lisättäisiin tauluun käyttäen samaa id-numeroa, jolloin taulun Next autoindex -arvo ei kasvaisi vaan pysyisi ennallaan.

Ideana on se, että Next autoindex saa kasvaa vain siinä tapauksessa, että kantaan lisätään täysin uusi rivi.

INSERT INTO kayttajien_tiedot (id, kayttaja_id, voimassa) VALUES
  ((SELECT id FROM kayttajien_tiedot AS tied WHERE tied.kayttaja_id = 123), 123, 1),
  ((SELECT id FROM kayttajien_tiedot AS tied WHERE tied.kayttaja_id = 234), 234, 0)
  ON DUPLICATE KEY UPDATE id = id, kayttaja_id = VALUES(kayttaja_id), voimassa = VALUES(voimassa)

Jos käyttäjiä on 20 kpl ja yksi heistä on uusi, tällöin kantaan lisätään uusi rivi ihan OK. Ongelma on vain se, että samalla Next autoindex kasvaa parilla kymmenellä, vaikka se saisi kasvaa vain yhdellä.

Tuo id = id on se kohta, mikä tässä askarruttaa. Jos käyttäjää ei ole kannassa, saa id arvon NULL ja tällöin auto_increment hoitaa id:lle uuden arvon. Jos käyttäjä on kannassa, tällöin id:nä pitäisi käyttää käyttäjän jo olemassa olevaa id-numeroa.

Mitenkähän tämän saisi toimimaan?

Oikeastaanhan tuon pitäisi olla id = tied.id, mutta ei toimi...

Juu ei pitäisi kasvaa muulloin, kuin lisättäessä uusi tietue. Voisit miettiä, että mikähän sulla on nyt auto increment seed... Hoituu jälkikäteen: ALTER TABLE talunnimi AUTO_INCREMENT = 1;

neosofta kirjoitti:

Juu ei pitäisi kasvaa muulloin, kuin lisättäessä uusi tietue. Voisit miettiä, että mikähän sulla on nyt auto increment seed... Hoituu jälkikäteen: ALTER TABLE talunnimi AUTO_INCREMENT = 1;

Asiaan liittyy innodb_autoinc_lock_mode, joka on 1.

Täältä voit tutkia lisää:
https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

Havaitsin, että virhe tapahtuukin tuolla insert-lausekkeessa, joten muokkasin sitä. Nyt toimii muuten hyvin, mutta jos käyttäjää ei löydy, lisätään uuden käyttäjän tiedot aina id = 10 kayttajien_tiedot-tauluun.

INSERT INTO kayttajien_tiedot (id, kayttaja_id, voimassa) VALUES
  (COALESCE((SELECT id FROM kayttajien_tiedot AS tied WHERE tied.kayttaja_id = 123 LIMIT 1), 10), 123, 1),
  (COALESCE((SELECT id FROM kayttajien_tiedot AS tied WHERE tied.kayttaja_id = 234 LIMIT 1), 10), 234, 0)
  ON DUPLICATE KEY UPDATE id = id, kayttaja_id = VALUES(kayttaja_id), voimassa = VALUES(voimassa)

Miten saisin tuohon lukujen 10 tilalle koodin, joka tutkii varmasti vapaan id-numeron ja ottaa sen käyttöön kympin sijasta?

Mitä tuo id tuolla tekee? Eikös sen pitänyt olla juurikin se arvo, jonka piti kasvaa auto increment seed arvon mukaan automaattisesti aina kun lisätään uusi tietue. Jos kayttaja_id arvolla on sidos auto increment arvoon tai päinvastoin, niin vituiksi menee.

Lisättävän id-numeron ei ole pakko olla sama kuin Next autoindex -arvo.

xxmss kirjoitti:

Taulun id-sarake on int(10), not null ja auto increment.

WTF? Siihen auto increment numeroon ei tarvitse viitata missään, kanta (engine) hoitelee sen ihan automaattisesti. Nyt jos haluat, että kayttaja_id kasvaa esim. yhdellä aina kun lisäät uuden käyttäjän tietueen niin hae se (max) arvo kyselyllä kayttaja_id kentästä ennen inserttiä ja kasvata sitä sitten aivan oman mielesi mukaan.

Toisaalta esim. asiakasnumerointia ei kannata aloittaa nollasta vaan esim. heti miljoonasta niin saavat kuvan, että bisnes pyörii vakaalla pohjalla 😁

P.S. meinasin jo alkaa sanoilla: Onpa paksupäinen tyyppi...

xxmss kirjoitti:

INSERT INTO kayttajien_tiedot (id, kayttaja_id, voimassa) VALUES
  (COALESCE((SELECT id FROM kayttajien_tiedot AS tied WHERE tied.kayttaja_id = 123 LIMIT 1), 10), 123, 1),
  (COALESCE((SELECT id FROM kayttajien_tiedot AS tied WHERE tied.kayttaja_id = 234 LIMIT 1), 10), 234, 0)
  ON DUPLICATE KEY UPDATE id = id, kayttaja_id = VALUES(kayttaja_id), voimassa = VALUES(voimassa)

Mietihän nyt edes ihan pikkuisen. 🤏

SQL-kyselysi on käytännössä tämä:

INSERT INTO foo (id) SELECT id FROM foo

Mitä ihmettä odotat tapahtuvan?

xxmss kirjoitti:

Lisättävän id-numeron ei ole pakko olla sama kuin Next autoindex -arvo.

Mikä helvetin "next autoindex"? Tuollaista termiä ei ole olemassakaan. Sano nyt edes "autoincrementin seuraava arvo", että olisi pienet mahikset ymmärtää, mistä on kyse.

Oikea vastaus ongelmaan on korjata taulun rakenne. Haluat luonnollisesti käyttää molemmissa tauluissa samaa ID-lukua pääavaimena. Et missään nimessä halua kahta erillistä ID-lukua.

Kun käyttäjätili luodaan, luodaan rivit myös molempiin tauluihin. Missään vaiheessa ei ole tilannetta, että laajennettujen käyttäjätietojen taulussa ei olisi riviä jokaiselle käyttäjälle. Tällöin ei tarvitse myöskään mitään hulluja SQL-kikkareita. Sovelluksen koodissa ei myöskään tällöin tarvitse jatkuvasti ihmetellä, onko taulussa riviä vai puuttuuko se, ja mitä missäkin tapauksessa tulisi tehdä.

Tässä korjattu skeema:

CREATE TABLE users (
  id int,
  username text,

  PRIMARY KEY (id)
);

CREATE TABLE users_data (
  user_id int,
  foo text,
  bar text,

  PRIMARY KEY (user_id),

  FOREIGN KEY (user_id)
    REFERENCES users (id)
);

Jos on ihan pakko tehdä upsert-tyylinen kysely, niin tee se sitten edes oikein.

INSERT INTO users_data (user_id, foo, bar)
VALUES (:uid, :foo, :bar)
ON DUPLICATE KEY
UPDATE foo = VALUES(foo), bar = VALUES(bar)

Fun fact: Ylläoleva kysely toimisi oikein siinäkin tapauksessa, että taulussa users_data on erillinen sarake pääavaimelle.

Haluat saada tauluun juoksevan lukuarvon ilman aukkoja, mutta AUTO_INCREMENT kasvaa myös INSERT-lauseen UPDATE-tapauksissa. Tähän ei varmasti auta tuo kehittämäsi kikkailu, joka on nyt vain ohjannut vastaajat harhaan alkuperäisestä kysymyksestä.

AUTO_INCREMENT tuottaa yksilöivän id:n, mutta se voi jättää aukkoja esimerkiksi juuri tuollaisessa tilanteessa.

Kannattaa luultavasti laittaa tälle juoksevalle lukuarvolle oma sarake, josta sitten MAXilla haetaan suurin. Kun lisäät rivejä yksi kerrallaan, voit tehdä näin:

INSERT INTO kayttajien_tiedot (kayttaja_id, voimassa, juokseva)
VALUES (?, ?, (SELECT IFNULL(MAX(juokseva), 0) + 1 FROM kayttajien_tiedot AS t))
ON DUPLICATE KEY UPDATE voimassa = VALUES(voimassa)

Luonnollisesti UNIQUE KEY kannattaa olla kohdallaan oikeilla sarakkeilla.

Toisaalta kannattaa miettiä, mitä ne aukot id-luvuissa edes haittaavat eli voisiko kuitenkin vain käyttää sitä, mitä AUTO_INCREMENT antaa. Syntyviä aukkoja voi vähentää sillä, että kyselyjä tekevässä koodissa olisi ensin tarkistus, löytyykö rivi ennestään, ja vain tarvittaessa INSERT. Silloin aukko jäisi vain, jos samoja tietoja päivitetään juuri samalla hetkellä.

Kiitos, Metabolix, hyvästä vastauksesta!

Yksi asia mietityttää. Jos vaikkapa sata käyttäjää lisäilee tietoja kantaan lähes yhtäaikaisesti, onko tuo MAX(juokseva) luotettava vai voiko se antaa väärän arvon, jos on kaksi yhtäaikaista komentoa? Eli tulisikin sama juokseva-arvo kahdelle eri käyttäjälle...

Toki on mahdollista että max kyselyt antavat kahdelle eri käyttäjälle saman ID:n ja sitten se joka ehtii ensin lisätä saa sen käyttöön ja toinen saa virheen kun primary key olisi duplikaatti. Voi olla että lukituksia käyttämällä tämän saisi vältettyä.

Nyt kun ymmärsin, että koettu ongelma tosiaan on, että ID-numeroon jää välejä, niin kysymys on oikeastaan että miksi se koetaan ongelmaksi? Kyseessähän on kahden eri taulun sidostaulu, joten miksi siihen ID:hen ei saisi jäädä tyhjiä väliin? Onko pelkona että rapiat 2 miljardia tulee täyteen liian pian?

Jos kuitenkin halutaan pyrkiä välttämään id:n turha kasvu (mutta ei ole välttämätöntä että ei jää yhtäkään väliä) niin tekisin homman niin, että ensin koodin puolella tarkistaisin löytyykö jo pari kannasta ja jos ei, lisäisin käyttäen auto_incrementiä ja "insert on duplicate key update" mekaniikkaa. Tällöin ei voisi tulla max():n ongelmaa, ei tarvitsisi käyttää lukituksia ja väliin jäävä Id tapahtuisi vain mikäli kaksi käyttäjää yrittäisi lisätä samaa paria samaan aikaan.

Toisaalta on mitä todennäköisintä, että injektiosi ei toimi ja voimassa pysyy arvossa: expired