Felix: Datenbank sortieren (MySQL 5.0.67)

Hi

Die Einträge in meiner Tabelle hatte ich bis jetzt immer eine Spalte, die den Zeitpunkt des hinzufügens enthält. Wenn ich Daten abgefragt hatte, habe ich nach dieser Spalte sortiert.
Nun würde ich gerne die Einträge nachträglich umsortieren können, die Spalte mit dem Zeitpunkt aber beibehalten. Ich hatte gedacht, eine zusätzliche Spalte 'index' o.ä. einzufügen, und sie bei jedem neuen Eintrag zu erhöhen. Beim umsortieren ändere ich den Wert der betreffenden Zeilen.
Da ich mich mit Datenbanken nicht so gut auskenne, wollte ich fragen: Wie würdet ihr das machen? Wie würde ein entsprechndes INSERT aussehen?
Ich wüsste auch nicht, was die einfachste ;öglichkeit wäre, einen Wert beim einfügen um 1 zu erhöhen. auto-increment geht ja nur einmal pro Tabele, und die IDs sollte man (denke ich) nicht nachträglich ändern

Bisher also etwa so:

  
CREATE TABLE `whatever` (  
  `id` int(5) NOT NULL auto_increment,  
  `erstellt` timestamp NOT NULL default CURRENT_TIMESTAMP,  
  [...]  
  PRIMARY KEY  (`id`)  
)  
  
SELECT [...] FROM whatever ORDER BY erstellt ASC  

mfg
Felix

  1. Moin

    Warum willst du die Tabelle ummodeln? Warum sortierst du nicht bei der Ausgabe entsprechend der Anforderung?

    Gruß Bobby

    --
    -> Für jedes Problem gibt es eine Lösung, die einfach, sauber und falsch ist! <-
    ### Henry L. Mencken ###
    -> Nicht das Problem macht die Schwierigkeiten, sondern unsere Sichtweise! <-
    ## Viktor Frankl ###
    ie:{ br:> fl:{ va:} ls:< fo:) rl:( n4:( de:> ss:) ch:? js:( mo:} sh:) zu:)
    1. Hi

      Warum willst du die Tabelle ummodeln? Warum sortierst du nicht bei der Ausgabe entsprechend der Anforderung?

      Ich ändere ja nicht alle Zeilen gleichzeitig. Ich will z.B. dass ein spezieller (vorher ausgewählter) Eintrag um eins nach unten rutscht.

      mfg
      Felix

      1. Moin

        Dann wäre eine Spalte mit einem Sortierwert sinnvoll. Diesen kannst du mit dem nächsthöheren oder nächst niedrigeren tauschen.

        Als Sinnvoll erachte ich (ich weiß nicht ob es andere ebenso sehen) einen Timestringwert der beim Einfügen automatisch gesetzt wird. Da hättest du deinen gewissen Automatismus.

        Gruß Bobby

        --
        -> Für jedes Problem gibt es eine Lösung, die einfach, sauber und falsch ist! <-
        ### Henry L. Mencken ###
        -> Nicht das Problem macht die Schwierigkeiten, sondern unsere Sichtweise! <-
        ## Viktor Frankl ###
        ie:{ br:> fl:{ va:} ls:< fo:) rl:( n4:( de:> ss:) ch:? js:( mo:} sh:) zu:)
        1. Hi

          Erstmal noch danke für die schnellen Antworten

          Dann wäre eine Spalte mit einem Sortierwert sinnvoll. Diesen kannst du mit dem nächsthöheren oder nächst niedrigeren tauschen.

          Das war ja auch meine Idee, ich wollte nur mal bei Leuten nachfragen, die es besser wissen als ich :-)

          Als Sinnvoll erachte ich (ich weiß nicht ob es andere ebenso sehen) einen Timestringwert der beim Einfügen automatisch gesetzt wird. Da hättest du deinen gewissen Automatismus.

          So geht's natürlich auch, ich hatte an einen Integer gedacht. Kann ich bei zwei Spalten 'default CURRENT_TIMESTAMP' benutzen? Ich meine da was im Kopf zu haben, dass das nicht ginge, aber ich kann mich auch irren.

          mfg
          Felix

          1. Moin

            So geht's natürlich auch, ich hatte an einen Integer gedacht. Kann ich bei zwei Spalten 'default CURRENT_TIMESTAMP' benutzen? Ich meine da was im Kopf zu haben, dass das nicht ginge, aber ich kann mich auch irren.

            ARRRRGH... Du hast recht. aktuellen Timestamp kann man nur einer Spalte zuweisen. Ich persönlich benutze für den Sortiertimestamp den PHP-Timestamp. und lasse diesen beim INSERT eines Datensatzes eintragen.

            Gruß Bobby

            --
            -> Für jedes Problem gibt es eine Lösung, die einfach, sauber und falsch ist! <-
            ### Henry L. Mencken ###
            -> Nicht das Problem macht die Schwierigkeiten, sondern unsere Sichtweise! <-
            ## Viktor Frankl ###
            ie:{ br:> fl:{ va:} ls:< fo:) rl:( n4:( de:> ss:) ch:? js:( mo:} sh:) zu:)
            1. Hi

              ARRRRGH... Du hast recht. aktuellen Timestamp kann man nur einer Spalte zuweisen. Ich persönlich benutze für den Sortiertimestamp den PHP-Timestamp. und lasse diesen beim INSERT eines Datensatzes eintragen.

              Ist es sinnvoll, die Sortier-Spalte UNIQUE zu setzen? Oder kann ich anders garantieren, dass die Werte wirklich eindeutig sind?

              mfg
              Felix

              1. Moin

                Ist es sinnvoll, die Sortier-Spalte UNIQUE zu setzen? Oder kann ich anders garantieren, dass die Werte wirklich eindeutig sind?

                Ich würde es als sinnvoll erachten.

                Gruß Bobby

                --
                -> Für jedes Problem gibt es eine Lösung, die einfach, sauber und falsch ist! <-
                ### Henry L. Mencken ###
                -> Nicht das Problem macht die Schwierigkeiten, sondern unsere Sichtweise! <-
                ## Viktor Frankl ###
                ie:{ br:> fl:{ va:} ls:< fo:) rl:( n4:( de:> ss:) ch:? js:( mo:} sh:) zu:)
              2. Hi!

                Ist es sinnvoll, die Sortier-Spalte UNIQUE zu setzen? Oder kann ich anders garantieren, dass die Werte wirklich eindeutig sind?

                Du könntest dann etwas Schwierigkeiten beim Umsortieren bekommen, denn auch da dürfen gleiche Werte nicht zweimal auftreten. Den Index vor dem Löschen zu ändern und nachher wieder hinzuzufügen, könnte eine Lösung sein. (Aufwand überprüfen!)

                Ansonsten ist für das automatische Ändern von Zeileninhalt jenseits von AUTO_INCREMENT und CURRENT_TIMESTAMP ein Trigger verwendbar.

                Lo!

                1. Hi

                  Du könntest dann etwas Schwierigkeiten beim Umsortieren bekommen, denn auch da dürfen gleiche Werte nicht zweimal auftreten. Den Index vor dem Löschen zu ändern und nachher wieder hinzuzufügen, könnte eine Lösung sein. (Aufwand überprüfen!)

                  Daran  hätte ich jetzt gar nicht gedacht. Danke für den Hinweis.

                  Ansonsten ist für das automatische Ändern von Zeileninhalt jenseits von AUTO_INCREMENT und CURRENT_TIMESTAMP ein Trigger verwendbar.

                  Bis vor einer Minute wusste ich noch nicht, was das ist, geschweige denn dass es dass gibt ;-)
                  Meinst du das als Alternative zum UNIQUE oder als Ergänzung?

                  Kann ich (mit PHP) zwei Querys gleichzeitig absetzen, auch wenn nach dem ersten der UNIQUE kurzzeitig verletzt werden würde?

                  mfg
                  Felix

                  1. Hi!

                    Ansonsten ist für das automatische Ändern von Zeileninhalt jenseits von AUTO_INCREMENT und CURRENT_TIMESTAMP ein Trigger verwendbar.
                    Bis vor einer Minute wusste ich noch nicht, was das ist, geschweige denn dass es dass gibt ;-)
                    Meinst du das als Alternative zum UNIQUE oder als Ergänzung?

                    Ein Trigger ist ein Stück SQL-Code, der durch ein Ereignis ausgelöst wird. Diese Eregnisse können das Erzeugen, Ändern oder Löschen von Datensätzen sein.

                    Kann ich (mit PHP) zwei Querys gleichzeitig absetzen, auch wenn nach dem ersten der UNIQUE kurzzeitig verletzt werden würde?

                    Nein, auch Transactions dürften das nicht erlauben. Ein Statement kann die Datenhaltung nicht in inkonsistentem Zustand hinterlassen und hoffen, dass das nächste Statement das wieder aufräumt.

                    Und mehrere Statemens in einem Rutsch gehen nur mit der mysqli-Extension und mysqli_multi_query(). Aber ob du darüber oder mit einzelnen mysql_query()-Aufrufen die drei (oder mehr) Statements (Index weg, ändern, Index hinzu) ausführst, du benötigst einen Mechanismus, der nebenläufige Prozesse warten lässt, während du die Änderung vornimmst: Table-Locking.

                    Lo!

                    1. Hi

                      Ein Trigger ist ein Stück SQL-Code, der durch ein Ereignis ausgelöst wird. Diese Eregnisse können das Erzeugen, Ändern oder Löschen von Datensätzen sein.

                      Ja, das habe ich auch herausgefunden. Ich weiß nur ehrlich gesagt nicht, inwiefern mir das helfen soll.

                      Aber ob du darüber oder mit einzelnen mysql_query()-Aufrufen die drei (oder mehr) Statements (Index weg, ändern, Index hinzu) ausführst, du benötigst einen Mechanismus, der nebenläufige Prozesse warten lässt, während du die Änderung vornimmst: Table-Locking.

                      Gut, das werd ich dann auch noch machen und einzelne Querys senden.
                      Wenn ich einen READ-Lock setze, heißt das, dass immer noch aus der Tabelle gelesen werden kann?

                      Momentan bau' ich nur die Struktur ein, erst wenn das läuft kommt der eigentliche Mechanismus zum Umsortieren dazu.
                      Euch beiden vielen Dank!

                      mfg
                      Felix

                      1. Hi!

                        Ein Trigger ist ein Stück SQL-Code, der durch ein Ereignis ausgelöst wird. Diese Eregnisse können das Erzeugen, Ändern oder Löschen von Datensätzen sein.
                        Ja, das habe ich auch herausgefunden. Ich weiß nur ehrlich gesagt nicht, inwiefern mir das helfen soll.

                        Na, du wolltest doch etwas haben, was automatisch Werte anhand einer bestimmten Regel generieren kann (hochzählen = MAX()+1) und dafür weder auto_increment noch current_timestamp verwenden kannst.

                        Lo!

                        1. Hi

                          Na, du wolltest doch etwas haben, was automatisch Werte anhand einer bestimmten Regel generieren kann (hochzählen = MAX()+1) und dafür weder auto_increment noch current_timestamp verwenden kannst.

                          Ja, das schon, aber... ich... mhmm... OK, du hattest Recht, soweit hatte ich nicht gedacht.

                          mfg
                          Felix

                        2. Moin

                          Kannst du zu Triggern mal eine Beispiel zeigen oder ein Link mit weiterführenden Infos? Danke schonmal.

                          Gruß Bobby

                          --
                          -> Für jedes Problem gibt es eine Lösung, die einfach, sauber und falsch ist! <-
                          ### Henry L. Mencken ###
                          -> Nicht das Problem macht die Schwierigkeiten, sondern unsere Sichtweise! <-
                          ## Viktor Frankl ###
                          ie:{ br:> fl:{ va:} ls:< fo:) rl:( n4:( de:> ss:) ch:? js:( mo:} sh:) zu:)
                          1. Hi!

                            Kannst du zu Triggern mal eine Beispiel zeigen oder ein Link mit weiterführenden Infos? Danke schonmal.

                            Ja, erste Anlaufstelle zu speziellen Fragen des verwendeten Systems sollte dessen Dokumentation sein. Diese zu finden sollte nicht das Problem sein. Sich darin zurechtzufinden kann schon eher problematisch werden. Zwei Strategien fallen mit dabei ein. Die herkömmliche ist, sich das Werk zu schnappen und darin zu blättern, um die gesuchte Information zu finden. Dazu gibt es Hilfsmittel wie Inhalts- und Stichwortverzeichnis. Die moderne Strategie wäre, eine Suchmaschine zu verwenden. Die gibt es meist zur jeweiligen Dokumentation dazu oder man verwendet eine generische Suchmaschine.

                            Wenn du nun eine oder beide Strategien anwendest, zu welchen Ergebnissen kommst du dann?

                            Lo!

                            1. Moin

                              Wenn du nun eine oder beide Strategien anwendest, zu welchen Ergebnissen kommst du dann?

                              Das war nun wirklich unnötig. Ich weiß mit einer Documentation umzugehen. Aber weiterführende Links zu posten ist kein Verbrechen. Dies tue ich hier zuweilen auch wenn es darum geht bestimmte Funktionen direkt zu verlinken. Also mal bitte ein kleines Stück runterkommen. Ich möchte selbst lesen und hatte lediglich um eine Quelle gebeten. Sorry für die Frage.

                              Gruß Bobby

                              --
                              -> Für jedes Problem gibt es eine Lösung, die einfach, sauber und falsch ist! <-
                              ### Henry L. Mencken ###
                              -> Nicht das Problem macht die Schwierigkeiten, sondern unsere Sichtweise! <-
                              ## Viktor Frankl ###
                              ie:{ br:> fl:{ va:} ls:< fo:) rl:( n4:( de:> ss:) ch:? js:( mo:} sh:) zu:)
                              1. Hi!

                                Wenn du nun eine oder beide Strategien anwendest, zu welchen Ergebnissen kommst du dann?
                                Das war nun wirklich unnötig. Ich weiß mit einer Documentation umzugehen. Aber weiterführende Links zu posten ist kein Verbrechen.

                                Tut mir leid, wenn ich dich nun enttäuschen muss, aber mehr kann ich dir nicht bieten. Mit der Dokumentation umgehen kannst du, wie du sagst, und andere Quellen habe ich nicht im Angebot.

                                Lo!

                                1. Moin

                                  Tut mir leid, wenn ich dich nun enttäuschen muss, aber mehr kann ich dir nicht bieten. Mit der Dokumentation umgehen kannst du, wie du sagst, und andere Quellen habe ich nicht im Angebot.

                                  aber ist es denn so schwer den Link zum entsprechenden Abschnitt in der Documentation zu posten? Nunja. Sei es drum.

                                  Gruß Bobby

                                  --
                                  -> Für jedes Problem gibt es eine Lösung, die einfach, sauber und falsch ist! <-
                                  ### Henry L. Mencken ###
                                  -> Nicht das Problem macht die Schwierigkeiten, sondern unsere Sichtweise! <-
                                  ## Viktor Frankl ###
                                  ie:{ br:> fl:{ va:} ls:< fo:) rl:( n4:( de:> ss:) ch:? js:( mo:} sh:) zu:)
                                  1. Hi!

                                    aber ist es denn so schwer den Link zum entsprechenden Abschnitt in der Documentation zu posten? Nunja. Sei es drum.

                                    Nein schwer ist das nicht, im Gegenteil. Es ist viel zu einfach jemandem einen Fisch hinzuwerfen. Deswegen beschrieb ich stattdessen den Gebrauch der Angel. Einen Link anzuklicken schult auch nicht im eigenständigen Umgang mit dem Handbuch. Und wer das nicht erlernen möchte hat ja immer noch die Suchmaschinen, mit denen sich in diesem Fall Information zum Thema ebenso leicht finden lässt.

                                    Lo!

                        3. Hi

                          Also ich hab jetzt beschlossen, statt des Timestamps doch besser 'nen INT zu nehmen und mit Triggern zu arbeiten.
                          Als Trigger hab ich.
                          CREATE TRIGGER triggername BEFORE INSERT ON test FOR EACH ROW SET NEW.spalte = max(spalte)+1;
                          Das nimmt er auch an, aber bei INSERT krieg ich dann "ERROR 1111 (HY000): Invalid use of group function". Wie kann ich den Trigger sonst definieren?

                          mfg
                          Felix

                          1. Hi

                            Mit etwas herumprobieren bin ich doch noch auf die Lösung gekommen:

                              
                            CREATE TRIGGER triggername BEFORE INSERT ON tabelle  
                             FOR EACH ROW SET NEW.spalte = (SELECT IF(MAX(spalte),MAX(spalte),0)+1 FROM tabelle);  
                            
                            

                            mfg
                            Felix

                            1. Hi!

                              CREATE TRIGGER triggername BEFORE INSERT ON tabelle

                              FOR EACH ROW SET NEW.spalte = (SELECT IF(MAX(spalte),MAX(spalte),0)+1 FROM tabelle);

                                
                              Wenn du das so schreibst, so scheint mit, dass du auch nicht-nummerische Werte bekamst. [Ein wenig probiert.] Das ist der Fall, wenn die Tabelle leer ist, dann kommt NULL beim MAX() raus. Allerdings nähme ich COALESCE(MAX(spalte), 0) statt des IF(), dann muss da nicht zweimal "gemaxt" werden.  
                                
                                
                              Lo!
                              
                              1. Hi

                                Wenn du das so schreibst, so scheint mit, dass du auch nicht-nummerische Werte bekamst. [Ein wenig probiert.] Das ist der Fall, wenn die Tabelle leer ist, dann kommt NULL beim MAX() raus.

                                Genau das war mein Problem.

                                Allerdings nähme ich COALESCE(MAX(spalte), 0) statt des IF(), dann muss da nicht zweimal "gemaxt" werden.

                                Danke für den Hinweis, die Funktion kannte ich nicht.

                                mfg
                                Felix

                          2. Hi!

                            CREATE TRIGGER triggername BEFORE INSERT ON test FOR EACH ROW SET NEW.spalte = max(spalte)+1;
                            Das nimmt er auch an, aber bei INSERT krieg ich dann "ERROR 1111 (HY000): Invalid use of group function". Wie kann ich den Trigger sonst definieren?

                            Nimm eine Subquery:

                            CREATE TRIGGER triggername BEFORE INSERT ON test FOR EACH ROW SET NEW.spalte = (SELECT max(spalte) FROM test) +1;

                            Vermutlich ist das in der Art aber nicht thread-safe. Wenn du gleichzeitige Zugriffe erwartest, kann es möglicherweise passieren, dass Prozess B das selbe Ergebnis aus der Subquery bekommt, wenn sie zwischen der Subquery und dem Einfügen von Prozess A abgearbeitet wird.

                            Dass ich vermute, liegt daran, dass ich keine Aussage fand, ob der INSERT-Vorgang zusammen mit dem Trigger einen atomaren Vorgang bilden. Wenn du also nebenläufige Einfügevorgänge erwartest, kann ich nur anbieten, vor dem Insert die Tabelle zu sperren und nachher wieder aufzuheben. Leider lässt sich das nicht in einer Stored Procedure kapseln, denn darin sind LOCK/UNLOCK TABLE nicht möglich.

                            Lo!

                            1. Hi

                              Nimm eine Subquery:

                              CREATE TRIGGER triggername BEFORE INSERT ON test FOR EACH ROW SET NEW.spalte = (SELECT max(spalte) FROM test) +1;

                              Wie ich bereits geschrieben hatte, bin ich allein drauf gekommen. Allerdings muss noch ein IF() rein, falls die Tabelle leer ist, sonst werden nur NULLen eingefügt. Trotzdem danke!

                              Vermutlich ist das in der Art aber nicht thread-safe. Wenn du gleichzeitige Zugriffe erwartest, kann es möglicherweise passieren, dass Prozess B das selbe Ergebnis aus der Subquery bekommt, wenn sie zwischen der Subquery und dem Einfügen von Prozess A abgearbeitet wird.

                              Wie wahrscheinlich ist es denn, dass so etwas passieren könnte?
                              Ist das auch möglich, wenn alle Zugriffe über PHP kommen? Ich benutze mysql_pconnect()

                              Dass ich vermute, liegt daran, dass ich keine Aussage fand, ob der INSERT-Vorgang zusammen mit dem Trigger einen atomaren Vorgang bilden. Wenn du also nebenläufige Einfügevorgänge erwartest, kann ich nur anbieten, vor dem Insert die Tabelle zu sperren und nachher wieder aufzuheben. Leider lässt sich das nicht in einer Stored Procedure kapseln, denn darin sind LOCK/UNLOCK TABLE nicht möglich.

                              Gut, jetzt bin ich wieder etwas überfordert... Wenn ich vor dem INSERT ein LOCK und danach wieder ein UNLOCK absetze, reicht das dann?

                              mfg
                              Felix

                              1. Hi!

                                CREATE TRIGGER triggername BEFORE INSERT ON test FOR EACH ROW SET NEW.spalte = (SELECT max(spalte) FROM test) +1;
                                Wie ich bereits geschrieben hatte, bin ich allein drauf gekommen.

                                Ja, unsere Antworten überschitten sich gegenseitig.

                                Allerdings muss noch ein IF() rein, falls die Tabelle leer ist, sonst werden nur NULLen eingefügt. Trotzdem danke!

                                Oder ein COALESCE().

                                Wie wahrscheinlich ist es denn, dass so etwas passieren könnte?
                                Ist das auch möglich, wenn alle Zugriffe über PHP kommen?

                                Die Frage ist nicht ob PHP oder nicht, sondern ob mehrere Anwender gleichzeitig zugreifen werden. Das ist regelmäßig bei einer Webanwendung der Fall, die nicht nur für einen einzigen Anwender konzipiert ist.

                                Ich benutze mysql_pconnect()

                                Du kennst alle Besonderheiten und Fallstricke der p-Variante?

                                Gut, jetzt bin ich wieder etwas überfordert... Wenn ich vor dem INSERT ein LOCK und danach wieder ein UNLOCK absetze, reicht das dann?

                                Ja, das sollte meines Erachtens nach reichen.

                                Lo!

                                1. Hi

                                  Du kennst alle Besonderheiten und Fallstricke der p-Variante?

                                  Gut dass du gefragt hast, ich habe gerade mal im Forum gesucht. Sven hat ja schon mehrfach darüber geschrieben. Ich nehme an du meinst Apache-Threads vs. DB-Verbindungen?

                                  Gut, jetzt bin ich wieder etwas überfordert... Wenn ich vor dem INSERT ein LOCK und danach wieder ein UNLOCK absetze, reicht das dann?

                                  Ja, das sollte meines Erachtens nach reichen.

                                  Gut, dann baue ich das noch ein.

                                  mfg
                                  Felix

                                  1. Hi!

                                    Du kennst alle Besonderheiten und Fallstricke der p-Variante?
                                    Gut dass du gefragt hast, ich habe gerade mal im Forum gesucht. Sven hat ja schon mehrfach darüber geschrieben. Ich nehme an du meinst Apache-Threads vs. DB-Verbindungen?

                                    Ja. Der Verbindungsaufbau geht normalerweise sehr schnell vonstatten. Wenn du nicht gerade ein Hochlastsystem betreibst, dann wirst du vermutlich keine Nachteile erfahren wenn du die Nicht-P-Variante nimmst. Im Gegenteil, die zu beachtenden Besonderheiten bei persistenten Datenbankverbindungen fallen da ja weg. (Ein weiteres Szenario wäre, wenn der MySQL-Server am anderen Ende der Welt stünde. Dann merkt man die Roundtrips zum Verbindungsaufbau und Kodierungsaushandeln recht deutlich. Doch das versuchte ich eher mit einem Replikationsszenario zu entschärfen.)

                                    Lo!

                                    1. Hi

                                      Ne, das wird nicht der Fall sein. Im Produktiveinsatz wird das Skript wahrscheinlich auf dem selben Server laufen wie MySQL, oder zumindest nicht weit weg. Das ganze läuft nur im Intranet und ich gehe von 4-10 User gleichzeitg aus.
                                      Habs auch schon entfernt.

                                      mfg
                                      Felix