MySQL: Autoimcrement ohne Key?

[Mario Steinko]

Hey Leute, ich möchte eine Datenbank erstellen die ca. so aussieht:

id | blog_id | entry_id | ....
------------------------------
1  |       1 |        1 | ....
2  |       1 |        2 | ....
3  |       2 |        1 | ....
4  |       1 |        3 | ....

id is Autoincrement und Key
blog_id referenziert auf die id des blogs
entry_id sollte ein autoincrement pro blog_id sein, also für den jeweiligen Blog immer die nächst höchste Nr. erstellen.

Meine Lösung sieht so aus, is aber recht ineffizient:

$max_entry_id = mysql_query("SELECT MAX(entry_id) FROM entries WHERE blog_id = '".$blog_id."'");  
if(mysql_query("INSERT INTO entries (blog_id, entry_id, ...) VALUES ('".$blog_id."', '".($max_entry_id+1)."', ...)")); {  
  echo "geschafft";  
}

Gibts da ne bessere Lösung?

lg
Mario

[ChrisB]

Hi,

entry_id sollte ein autoincrement pro blog_id sein, also für den jeweiligen Blog immer die nächst höchste Nr. erstellen.
[...]>
Gibts da ne bessere Lösung?

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
"For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. [...]"

Das ist dann allerdings nicht "ohne Key" - ginge so wie ich das verstehe also nur, wenn du auf die auto_increment ID-Spalte zur Identifizierung der Datensätze verzichtest, und diese dann über blog_id und entry_id in Kombination referenzierst.

MfG ChrisB

[Mario Steinko]

Ich brauch leider wegen der Foreign Keys eine InnoDB und die ID bracuh ich weil mein PHP Framework (CakePHP) nur single Keys unterstützt....

Gibts ne Möglichkeit das SQL performanter zu schreiben oder das ganze irgendwie mit TRIGGER oder FUNCTIONS (kenn mich mit MySQL sehr wenig aus).

danke
Mario

[Rouven]

Hello,

Gibts ne Möglichkeit das SQL performanter zu schreiben oder das ganze irgendwie mit TRIGGER oder FUNCTIONS (kenn mich mit MySQL sehr wenig aus).

ja, und zwar genau so wie du es bisher machst, nur technisch eleganter. Deine aktuelle SELECT-MAX-INSERT-Lösung krankt daran, dass es kein Locking gibt, d.h. potenziell tragen zwei parallele Transaktionen parallel die selbe ID ein. Was du machen kannst ist dir einen ON INSERT-Trigger zu setzen, der genau diese ID in einem Schritt generiert. Schematisch sollte die Syntax etwa wie folgt aussehen:

  
CREATE TRIGGER generate_id  
AFTER INSERT ON entries  
FOR EACH ROW BEGIN  
   UPDATE entries e  
   SET entry_id = (SELECT MAX(entry_id) FROM entries WHERE blog_id = e.blog_id) + 1  
   WHERE entries.id = NEW.id  
END  

MfG
Rouven

[Mario Steinko]

Cool, der Trigger sieht recht gut aus, als Startwert für neu eingefügte Zeilen nehm ich dann bei der entry_id 0, oder!?

Warum machst du das denn mit einem UPDATE für AFTER INSERT statt einem BEFORE INSERT und dann die INSERT-Daten ändern? Liegt das an dem potenziellen Locking?

Vielen Dank!
Mario

[Rouven]

Hello,

Warum machst du das denn mit einem UPDATE für AFTER INSERT statt einem BEFORE INSERT und dann die INSERT-Daten ändern? Liegt das an dem potenziellen Locking?

Hmh, hab ich so genau nicht drüber nachgedacht. Was ich nicht wüsste: theoretisch kannst du mit einem INSERT mehrere Datensätze einfügen, ich habe keine Ahnung, ob der Trigger dann INSERT-Trigger -> INSERT -> INSERT-Trigger -> INSERT feuert, denn wenn nicht, und er verarbeitet erst alle TRIGGER, dann bekämen sie alle die selbe MAX(id).

MfG
Rouven

[Mario Steinko]

Es kann pro INSERT nur ein neuer Entry angelegt werden, da die Eingabemaske nur ein insert zulässt ;)

Wäre es also möglich ohne daten von blogs lesen zu müssen?

danke
Mario

[Tom]

Hello,

CREATE TRIGGER generate_id
AFTER INSERT ON entries
FOR EACH ROW BEGIN
   UPDATE entries e
   SET entry_id = (SELECT MAX(entry_id) FROM entries WHERE blog_id = e.blog_id) + 1
   WHERE entries.id = NEW.id
END

  
Ich wüsste gerne, wie Du das zum Laufen bringst.  
Bei MySQL 5.1.33 sollte das eigentlich einen Fehler geben:  
  
ERROR 1442 (HY000): Can't update table '...' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.  
  
In Triggern, die die eigene Tabelle betreffen, kann man nur die Werte ändern.  
Oder aber man kann in \_anderen\_ Tabellen rumfummeln :-)  
  
  
  
  
Liebe Grüße aus dem schönen Oberharz  
  
  
Tom vom Berg  
![](http://selfhtml.bitworks.de/Virencheck.gif)  
  

-- 
Nur selber lernen macht schlau  
<http://bergpost.annerschbarrich.de>

[Rouven]

Hello,

ERROR 1442 (HY000): Can't update table '...' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Drecksding - aber ja, du hast Recht. Kombiniert man allerdings Marios Rückfrage "warum nicht BEFORE INSERT" mit diesem Artikel, dann ist es offenbar (hab gerade kein MySQL 5.1 zur Hand) möglich, DEN GERADE GEÄNDERTEN Datensatz doch nochmal anzufassen, nur keinen anderen.

MfG
Rouven

[Tom]

Hello,

ERROR 1442 (HY000): Can't update table '...' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Drecksding - aber ja, du hast Recht. Kombiniert man allerdings Marios Rückfrage "warum nicht BEFORE INSERT" mit diesem Artikel, dann ist es offenbar (hab gerade kein MySQL 5.1 zur Hand) möglich, DEN GERADE GEÄNDERTEN Datensatz doch nochmal anzufassen, nur keinen anderen.

Bei älteren MySQL-Versionen (ich glaube das war 4.1) konnte man auch beim Update noch ein Subselect auf dieselbe Tabelle und sogar auf dieselbe Spalte vornehmen. Je nachdem, wie man die Datenmenge sortierte, ging es dann (meistens) gut, oder aber es krachte ganz ordentlich.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Ilja]

yo,

diese information hört sich sehr stark nach prozessdaten an und dann würde dir das einmalige eintragen nicht helfen. aber das kann man schnell klären. was passiert den, wenn mal ein datensatz aus der tabelle gelöscht wird und sich dadurch die anzahl der entsprechenden blog_id verändert, bzw wenn sich die blod_id unud somit die referenz ändert. bleiben dann alle werte in der aufzählungsspalte unverändert ? oder mit allgemeineren worte, egal was später passiert, ändert sich der wert niemals in der aufzählungsspalte, der ihr einmal zugeteilt wurde ?

Ilja

[Mario Steinko]

Hi!

Die Wertte bleiben gleich und werden ncith verändert, also wenn es z.B. für die blog_id 2 entry_ids von 1-6 gibt und der entry 4 wird gelöscht so bleibt 5 auch 5 und 6 auch 6...

Sollten die Nr. dynamsich vergeben werden hätte es ja gereicht beim SELECT ein ORDER BY id hinzufügen und das Ergebis in ein Array speichern lassen => Arraykey+1 = entry_id.
In meinem Fall geht dies aber nicht, da sie entry_ids einmal definiert und dann fix sein sollen.

lg
Mario

[Tom]

Hello,

id | blog_id | entry_id | ....

1  |       1 |        1 | ....
2  |       1 |        2 | ....
3  |       2 |        1 | ....
4  |       1 |        3 | ....

Du müsstest eigentlich eine weitere Tabelle führen für die Blogs und die jeweils nächste Entry-ID

ID_TABLE

blog_id | next_entry_id | ....
 autoinc |               |
 ------------------------------
       1 |             4 | ....
       2 |             2 | ....

Und dann wirst Du nicht drum herumkommen, den betroffenen Datensatz in dieser Nachschlagetabelle für die beiden notwendigen Statements zu sperren

Für das Anlegen eines neuen Entrys benötigst Du die die nächste Entry-ID

lock     record in ID_TABLE für blog_id = $blog_id
   select   next_entry_id aus ID_TABLE für blog_id = $blog_id
   update   next_entry_id in ID_TABLE mit next_entry_id+1 für blog_id = $blog_id
   unlock   table ID_TABLE

oder eben für das Anlegen eines neuen Blogs die nächste Nummer besorgen

insert
   select next_entry_id ... for blog_id = last_insert_id()

Da ist mMn keine Sperre notwendig, da der Eintrag einen _neuen_ Datensatz erzeugt, der dann für die next_entry_id den Default 1 haben muss und die blog_id automatisch hochzählt.

In wieweit man bei MySQL inzwischen ein Update über mehrere Tabellen und ein Subselect auf einer dieser beiden kombinieren kann zu einem atomar abgehandelten Statement, weiß ich nicht.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Mario Steinko]

Hi!

Da sieht mir Rouvens Lösung mit nem Trigger doch deutlich effizienter aus...

lg
Mario

[Tom]

Hello,

Da sieht mir Rouvens Lösung mit nem Trigger doch deutlich effizienter aus...

Nur dass die auch nicht ohne Lock funktionieren kann.

In einem Update ... Select dürfen nur Spalten aus _anderen_ als der updateten Tabelle benutzt werden und schon gar nicht dieselbe Spalte, die updated werden soll.

Der Trigger bindet auch nicht das Insert, das Update und das Select-Statement zu einem atomaren Statement. Da musst Du trotzdem selber mit einem LOCK machen. Ein Recordlock sit bei MySQL nicht so prall. Bliebt also nur ein Table Lock.

Außerdem solltest Du bei Schlüsseln (und die Sub-Keys sind ja auch welche) nur mit verlorenen Schlüsseln arbeiten, also einen einmal vergebenen Schlüssel nicht wieder vergeben. Wenn Du aber ermöglichen willst, dass aus der BLOG-Tabelle Einträge gelöscht werden dürfen, dann funktioniet die Schlüsselermittlung mit max() nicht mehr. Dann dürftest Du ja nie den letzten Datensatz eine Subkey-Linie löschen.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Tom]

Hello,

In einem Update ... Select dürfen nur Spalten aus _anderen_ als der updateten Tabelle benutzt werden und schon gar nicht dieselbe Spalte, die updated werden soll.

Das DBMS schmeißt sonst Error 1093
ERROR 1093 (HY000): You can't specify target table '....' for update in FROM clause

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Mario Steinko]

Ok, verstehe. Wie gesagt so Tiefen Einblick in die Materie hab ich leider nicht, ich kann nur die Standard-SQL Sachen und kenn im Groben die Unterschiede zwischen den unterschiedlichen SQL-DB...

Ich hab sowieso bereits eine Tabelle mit dem primary autoincrement Key id für die Tabelle blogs, da füg ich einfach ne Spalte next_entry_id hinzu ;)

Reicht es wenn ich folgendes SQL-Statement beim erstellen eines Entrys ausführe?

mysql_query("lock record in blogs where blog_id = '".$blog_id."'");  
$next_entry_id = mysql_query("select next_entry_id from blogs where blog_id = '".$blog_id."'");  
$next_entry_id = mysql_fetch_array.......  
mysql_query("update next_entry_id in blogs set next_entry_id = '".$next_entry_id+1."' where blog_id = '".$blog_id."'");  
mysql_query("unlock table blogs");  
mysql_query("INSERT INTO entries (entry_id, ...) VALUES ('".$next_entry_id."',...)");

Was passiert wenn während eines locks ein zweiter Zugriff stattfindet, "wartet" der dann oder entsteht dann ein Fehler?

Vielen Dank!
Mario

[Tom]

Hello,

Was passiert wenn während eines locks ein zweiter Zugriff stattfindet, "wartet" der dann oder entsteht dann ein Fehler?

Gute Frage.

Beim Table-Lock sollte der eigentlich solange warten, bis alle angeforderten Locks verfügbar sind. Man muss ja alle auf einmal anmelden.

Bei den "Record-Lock"-Funktionen get_lock() bekommt man eine entsprechenden Rückgabewert und kann dann selber entscheiden, was man will. Die sind auch nur advisory und helfen daher nur, wenn alle Datenbanknutezr (Scripte) sich auch kooperativ verhalten und den Mechanismus benutzen.

Da wäre es dann wieder zweckmäßig, den Zugriff auf die Datenbank ausschließlich über Stored Procedures vorzunehmen, da man dann darin das kooperative Locking konsequent berücksichtigen kann und alle anderen Zugriffsarten einfach unterbinden kann.

Damit wäre das Model dann abgesichert gegen Fehlbedienung.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Mario Steinko]

Hmmm... und was is jetzt das Ergebnis dieser Diskussion, bin noch ned agnz schlau draus geworden, bis jetzt klang alles nach PHP und keinen TRIGGERN, über Stored Prozedures weiß ich jetzt aber gar nix...

Wäre klasse wenn ihr mir Helfen könnt den Code zu erarbeiten...
Soll ich bei meinem PHP Code bleiben, den ich hier gepostet hab:
http://forum.de.selfhtml.org/?t=187468&m=1246278

Oder das ganze mit tored prozedures probiern?

lg
Mario

[Tom]

Hello,

[...] bis jetzt klang alles nach PHP und keinen TRIGGERN, über Stored Prozedures weiß ich jetzt aber gar nix...

also Du solltest aber eine Stored Procedure daraus machen.
Diese kannst Du dann aus dem Trigger heraus aufrufen.
Trigger gibt es immer nur für jede Art einen.
Stored Procedures kann man darin aber soviele aufrufen, wie man braucht.

Wenn Du also auf Wachstum gehen willst, mach es gleich richtig.

http://dev.mysql.com/doc/refman/5.1/de/stored-procedures.html

Lies Dir das Kapitel durch und experimentiere ein wenig in der Konsole.
Dazu musst Du dann nur die kleine Gemeinheit mit den Delimitern beachten. In der Konsole ist das Semikolon das Endezeichen für den Befehl. In Procedures, Funktionen und Triggern benötigt man das aber auch gerade dafür. Damit man nun in der Konsole die mehrzeiligen Proceduren erstellen kann, muss man für die Konsole vorher den Delimiter umstellen und hinterher wieder zurück

Steht aber auch alles in den Beispielen.

Das Locking kann dann in den Prozeduren stattfinden, wo es sich nicht vermeiden lässt.

Überdenke aber vorher Dein Datendesign nochmal. Das scheint mir noch nicht sehr glücklich zu sein..

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Mario Steinko]

Überdenke aber vorher Dein Datendesign nochmal. Das scheint mir noch nicht sehr glücklich zu sein..

In wiefern?

Ich wüsst nicht was ich ändern soll...

lg
Mario

[Tom]

Hello,

Ich wüsst nicht was ich ändern soll...

id | blog_id | entry_id | ....
  ------------------------------
  1  |       1 |        1 | ....
  2  |       1 |        2 | ....
  3  |       2 |        1 | ....
  4  |       1 |        3 | ....

Da ist noch Abhängigkeit vorhanden.

| ..PK = Unique .....|
  | blog_id | entry_id | ....
  ------------------------------
  |       1 |        1 | ....
  |       1 |        2 | ....
  |       2 |        1 | ....
  |       1 |        3 | ....

Würde vollkommen ausreichen, um die Datensätze zu identifizieren.

Oder aber Du musst tatsächlich zerlegen in Kopf und Positonsdatensätze.
Das ist immer dann richtig, wenn der Blog z.B. eine Überschrift, einen Eigentümer, usw. hat.
Dann entsteht die BLOG_ID ja nicht in obiger Tabelle, sondern ist dort nur ein Fremdschlüssel.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Mario Steinko]

Hi!

Ich brauch die id aus dem simplen Grund das mein PHP Framework (CakePHP) nur single Keys unterstützt ;)

lg
Mario

[Tom]

Hello,

Ich brauch die id aus dem simplen Grund das mein PHP Framework (CakePHP) nur single Keys unterstützt ;)

Benötigst Du denn eine (konsequente) Paginierung, also eine fortlaufende Numerierung der entry_id, oder reicht Dir, wenn die bestimmte Reihenfolge eingehalten werden kann?

Dann kannst Du den künstlichen (redundaten) Index auch über

BLOG_ID + MESSAGE_ID aufbauen.

Das bedeutet, dass Du dann mehrere Tabellen mit AutoIndex baust:

populate
      id_populate   = id_blog + id_message
      -----------

blog
      id_blog
      -------

message
      id_message
      ----------

Die lassen sich mit last_insert_id() in gestaffelten Stored Procedures verwalten, ohne dass man irgendwelche Locks benutzen müsste. WEnn für den Blog die Message erstellt wurde, wird deren ID mit der des Blogs zusammengebaut und da diese Kombination immer Unique ist mit ruhigem Gewissen in die Tabelle mit dem Künstlichen Key übernommen.

Für den Zugriff auf einen derartigen zusammengebauten Datensatz kann man sich dann Stored Procedures (also 4 Stück für Select, Update, Insert, Delete) schreiben. Der Kunst-Key muss dann so gestaltet sein, dass er von deinem mangelhaften Framework benutzt und von den stored Procedures bequem zerlegt werden kann.

In der Hüll-Tabelle (Populate) kann dann auch verwaltet werden, ob der Eintrag freigegeben, gesperrt oder bearbeitet werden kann.

Der Direktzugriff auf BLOG und MESSAGE durch die API wird unterbunden.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Mario Steinko]

Ich brauch leider eine fortlaufende kosistente Nummerierung, wenn also Eintrag 3 für Blog 2 gelöscht wird, muss Eintrag 4 Eintrag 4 bleiben und darf nicht zu 3 werden...

lg
Mario

[Tom]

Hello,

Ich brauch leider eine fortlaufende kosistente Nummerierung, wenn also Eintrag 3 für Blog 2 gelöscht wird, muss Eintrag 4 Eintrag 4 bleiben und darf nicht zu 3 werden...

Da hast Du etwas verkehrt verstanden. Benötigst Du zur Ausübung von Kontrolle einen geschlossenen Nummernkreis, also stets ordinale Indexe?

Dann darfst Du auch nichts löschen, sondern bestenfalls als ungültig markieren.

Oder benötigst Du nur einen konsistenten Index, der aber durchaus Lücken bekommen darf, wenn gelöscht wird? Dann ist mein Vorschlag der richtige.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Mario Steinko]

Sry, beim ersten mal hab ich das um ehrlich zu sein ned ganz verstanden :-P

Das wäre genau das was ich brauche!!!
Vor allem da die (simplere) Idee mit dem lock nicht funktioniert da man bei ner InnoDB keine Locks auf records machen kann, hab mich schon durch die mysql docs durchgeackert....

Kannst du mir vielleicht noch ein bisschen helfen wie man so stored prozedures schreibt, da kenn ich mich gar ned aus...

Vielen Dank!!
Mario

[Tom]

Hello,

Kannst du mir vielleicht noch ein bisschen helfen wie man so stored prozedures schreibt, da kenn ich mich gar ned aus...

Du solltest erst einmal ganz einfache nur zum Testen schreiben, um die unterschiedlichen Arten kennen zu lernen (ohne und mit Paramtern, mit Pseudo-Referenzen).

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-views.html

Einfache Aufgabe:
Bau Dir eine Tabelle.
Schreibe Dir eine Procedure, die einen Datensatz sucht und die ID zurückliefert
oder einen neuen anlegt und dessen ID zurückliefert, wenn die Daten noch nicht vorhanden waren
oder einen Fehlerwert zurückliefert, wenn beim Versuch, den neuen Anzulegen, die Daten doch schon vorhanden waren.

Erweiterung der Übung:
Keinen Fehler zurückliefern, sondern die ID des plötzlich doch vorhandenen Datensatzes...

Dann solltest Du langsam auch die Vorgehensweise verstanden haben, die ohne Locks auskommt und trotzdem keine Nebenläufigkeitsprobleme produziert.

Liebe Grüße aus dem schönen Oberharz

Tom vom Berg

[Mario Steinko]

Dann solltest Du langsam auch die Vorgehensweise verstanden haben, die ohne Locks auskommt und trotzdem keine Nebenläufigkeitsprobleme produziert.

Die Vorgehensweise hab ich bereits kappiert, is recht logisch ;)
Aber mit Stored Procedures hab ich noch keine Erfahrung, ich werd mich mal anstrengen ;)

[Rouven]

Hello,

Der Trigger bindet auch nicht das Insert, das Update und das Select-Statement zu einem atomaren Statement.

Du willst mir sagen, Trigger sind bei MySQL nicht Teil der Transaktion (die das Statement zumindest alleinstehend für sich beanspruchen dürfte)?
Drecksding...

MfG
Rouven