Ερώτημα για MySql

Λογισμικό, λειτουργικά συστήματα, προγραμματισμός, hardware, δίκτυα, Internet
Άβαταρ μέλους
shrike
Δημοσιεύσεις: 4110
Εγγραφή: 02 Απρ 2018, 08:39
Phorum.gr user: Isildur
Τοποθεσία: Παρά θῖν' ἁλὸς

Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από shrike » 16 Απρ 2019, 19:40

Προσπαθώ να γράψω ένα mysql select query και δεν μου βγαίνει, μπορεί να βοηθήσει κανείς;

Υπάρχει πίνακας (έστω my_table) που έχει column με unique index (έστω id1) αλλά όχι auto_increment, οπότε οι τιμές του id1 μπορεί να είναι οποιοσδήποτε integer (μοναδικές, βέβαια).

Θέλω να μπορώ να βρω το επόμενο ελεύθερο id, ξεκινώντας από κάποιο σαν βάση (starting point), είτε προς τα πάνω είτε προς τα κάτω.

Παράδειγμα:
Έστω ότι ο id1 έχει τις τιμές 5, 6, 7, 8, 13, 14, 15, 45, 78.
Αν θέλω να βρω τον επόμενο ελεύθερο αριθμό ξεκινώντας από 6, θα πρέπει να φέρει το 9.
Αν θέλω να βρω τον επόμενο ελεύθερο αριθμό ξεκινώντας από 11, θα πρέπει να φέρει το 11.
Αν θέλω να βρω τον επόμενο ελεύθερο αριθμό ξεκινώντας από 123, θα πρέπει να φέρει το 123.
Αν θέλω να βρω τον προηγούμενο ελεύθερο αριθμό ξεκινώντας από 15, θα πρέπει να φέρει το 9.
Αν θέλω να βρω τον προηγούμενο ελεύθερο αριθμό ξεκινώντας από 8, θα πρέπει να φέρει το 4.
κ.ο.κ...

Προσπάθησα με το παρακάτω:

Κώδικας: Επιλογή όλων

SELECT t1.`id1` + 1
FROM `my_table` t1
LEFT JOIN `my_table` t2 ON t1.`id1` + 1 = t2.`id1`
WHERE t2.`id1` IS NULL
ORDER BY t1.`id1` ASC
LIMIT 1
...αλλά δεν παίρνω το επιθυμητό αποτέλεσμα. Κατ' αρχάς δεν μπορώ να ορίσω starting point, αλλά πέρα απ' αυτό, το παραπάνω query βρίσκει πάντα το πρώτο ελεύθερο id μετά από κάποιο υπαρκτό id (λογικό βέβαια, έτσι όπως είναι γραμμένο).

Έχω δοκιμάσει και κάποιες παραλλαγές με MIN(id1) κλπ αντί για LIMIT 1, αλλά το αποτέλεσμα είναι το ίδιο.

Υπάρχει βέβαια λύση με ορισμό μεταβλητής όπως πχ εδώ:

Κώδικας: Επιλογή όλων

SET @var=1;
SELECT IF(@var=`id1`,@var:=`id1`+1,@var) FROM `my_table` ORDER BY `id1`;
SELECT @var;
και στο "@var=1" να δίνω το starting point, αλλά έχω έναν περιορισμό. Πρέπει να γίνει όλο σε μία γραμμή, δεν μπορώ να χρησιμοποιήσω πολλαπλές σειρές (δηλαδή με ';') για κάποιον λόγο.

Μπορεί κανείς να βοηθήσει; Θενκς εκ των προτέρων...


Άβαταρ μέλους
the comet the course the tail
Δημοσιεύσεις: 4774
Εγγραφή: 03 Νοέμ 2018, 00:29
Τοποθεσία: In camera

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από the comet the course the tail » 16 Απρ 2019, 21:42

πρόσεχε τι θα τα κάνεις αυτά τα id που θα βρεις...

race conditions.... :xena:
tears,
the life that was ours
grows sharper and stronger
away and beyond...

Άβαταρ μέλους
klg
Δημοσιεύσεις: 450
Εγγραφή: 15 Οκτ 2018, 12:14
Phorum.gr user: klg

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από klg » 16 Απρ 2019, 23:11

Αυτό προσπαθείς να το κάνεις για να παίρνεις ID για να κάνεις insert καινούργια εγγραφή; Ποιο είναι το πρόβλημα που θες να επιλύσεις;

Άβαταρ μέλους
Dwarven Blacksmith
Δημοσιεύσεις: 12902
Εγγραφή: 31 Μαρ 2018, 18:08
Τοποθεσία: Maiore Patria

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από Dwarven Blacksmith » 16 Απρ 2019, 23:26

Άκυρο, είχα ποστάρει κάτι που έβρισκε την επόμενη υπάρχουσα ID, όχι την ελεύθερη
Don't cry because Rome is over. Smile because it happened.

Άβαταρ μέλους
foscilis
Δημοσιεύσεις: 9602
Εγγραφή: 21 Ιουν 2018, 11:42

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από foscilis » 16 Απρ 2019, 23:52

Νομιζω δε γινεται με ενα statement γιατι δεν εχει κατι το ιδιαιτερο ενα ελευθερο νουμερο αναμεσα στα πιασμενα σε σχεση με ενα οποιοδηποτε ελευθερο.

Πρεπει να φτιαξεις ενα βοηθητικο πινακα Τ με ολους τους ακεραιους απο 0 μεχρι το μεγιστο πιασμενο.

Στο παραδειγμα σου 1,2,3,...78.

Μετα:

SELECT MAX(ID) FROM (SELECT ID FROM T WHERE ID < 6 AND ID NOT IN (SELECT ID FROM MYTABLE) M) T

SELECT MIN (ID) FROM (SELECT ID FROM T WHERE ID > 6 AND ID NOT IN (SELECT ID FROM MYTABLE)M) T


μπορει και να μη θελει aliases. Σε πιο σοβαρες βασεις γινεται πιο ευκολα γιατι υπαρχουν sequence generators.

Άβαταρ μέλους
Dwarven Blacksmith
Δημοσιεύσεις: 12902
Εγγραφή: 31 Μαρ 2018, 18:08
Τοποθεσία: Maiore Patria

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από Dwarven Blacksmith » 17 Απρ 2019, 00:05

Εναλλακτικά φτιάχνεις μια fuction που λουπάρει.
Τη φτιάχνω σε T-SQL που με βολεύει αλλά θα την καταλαβαίνεις σίγουρα.

Κώδικας: Επιλογή όλων

CREATE FUNCTION udf_GetNextFreeID (@id INT)
RETURNS INT
AS
BEGIN
  DECLARE @freeID INT
  
  SET @freeID = @id
  
  WHILE @freeID <= (SELECT MAX(id)+1 FROM my_table)
  BEGIN
    IF @freeID NOT IN (SELECT id FROM my_table)
    BEGIN
      RETURN @freeID
    END
    
    SET @freeID = @freeID +1
  END
  
  RETURN NULL
END
και μετά απλά

Κώδικας: Επιλογή όλων

SELECT dbo.udf_GetNextFreeID(6)
Don't cry because Rome is over. Smile because it happened.

Άβαταρ μέλους
Stalker
Δημοσιεύσεις: 2147
Εγγραφή: 17 Μάιος 2018, 08:36
Phorum.gr user: CTAΛKEP

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από Stalker » 17 Απρ 2019, 00:16

Γιατί, προσπαθείς να κάνεις οικονομία στα primary keys μην τυχόν και πιάσεις το όριο των 9.223.372.036.854.775.807 διαθέσιμων 64μπιτων ακεραίων πριν την ώρα σου;

Το να ψάχνεις να γεμίσεις ενδιάμεσα primary keys φαίνεται εντελώς anti-pattern, το λιγότερο ποιος ξέρει τι references έχουν μείνει σε κλειδιά που κάποτε υπήρχαν και τώρα όχι.

Σε κάθε περίπτωση το πρόβλημα της εύρεσης συνεχόμενων τιμών σε πίνακα λέγεται gaps and islands problem, αν γκουγκλάρεις γι' αυτό όλο και κάτι θα βρεις.

Μια καλή αρχή ίσως να ήταν κάτι σαν

Κώδικας: Επιλογή όλων

 
  select c.id + 1 from TABLE c 
  where c.id + 1 not in 
	(select a.id from TABLE a, TABLE b where a.id = b.id+1)
Με άλλα λόγια να κάνεις join τον πίνακα με τον εαυτό του σε κάθε id με το επόμενό του, και με το not in να παίρνεις το πρώτο id που δεν υπάρχει κάθε φορά σε κάθε gap. Δεν το δοκίμασα, λέω μήπως.

εδιτ: meh, περίπου το ίδιο γράψαμε, απλά μου πήρε ένα λεπτό να το καταλάβω επειδή δεν έχω συνηθίσει mysql. Για τι είναι αυτό το πρόβλημα, για κανένα hackathon ή συνέντευξη; Απλό homework;
If reality is the sum of our perceptions, to acquire more varying points of view is to acquire, literally, more reality

Άβαταρ μέλους
shrike
Δημοσιεύσεις: 4110
Εγγραφή: 02 Απρ 2018, 08:39
Phorum.gr user: Isildur
Τοποθεσία: Παρά θῖν' ἁλὸς

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από shrike » 17 Απρ 2019, 09:14

the comet the course the tail έγραψε:
16 Απρ 2019, 21:42
πρόσεχε τι θα τα κάνεις αυτά τα id που θα βρεις...

race conditions.... :xena:
Αυτό δεν είναι πρόβλημα, γιατί την αναζήτηση και εισαγωγή θα την κάνει ένα workstation μόνο.
Θέλω να πω, δεν υπάρχει περίπτωση να δουλεύει ταυτόχρονα κι άλλος χρήστης (αν κατάλαβα καλά τι εννοείς).
klg έγραψε:
16 Απρ 2019, 23:11
Αυτό προσπαθείς να το κάνεις για να παίρνεις ID για να κάνεις insert καινούργια εγγραφή; Ποιο είναι το πρόβλημα που θες να επιλύσεις;
Ναι, αυτό. Προσπαθώ να βρω ελεύθερα ids για να εισάγω νέα εγγραφή που δεν θα είναι με auto incremented index.


Άβαταρ μέλους
shrike
Δημοσιεύσεις: 4110
Εγγραφή: 02 Απρ 2018, 08:39
Phorum.gr user: Isildur
Τοποθεσία: Παρά θῖν' ἁλὸς

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από shrike » 17 Απρ 2019, 09:23

foscilis έγραψε:
16 Απρ 2019, 23:52
Νομιζω δε γινεται με ενα statement γιατι δεν εχει κατι το ιδιαιτερο ενα ελευθερο νουμερο αναμεσα στα πιασμενα σε σχεση με ενα οποιοδηποτε ελευθερο.

Πρεπει να φτιαξεις ενα βοηθητικο πινακα Τ με ολους τους ακεραιους απο 0 μεχρι το μεγιστο πιασμενο.

Στο παραδειγμα σου 1,2,3,...78.

Μετα:

SELECT MAX(ID) FROM (SELECT ID FROM T WHERE ID < 6 AND ID NOT IN (SELECT ID FROM MYTABLE) M) T

SELECT MIN (ID) FROM (SELECT ID FROM T WHERE ID > 6 AND ID NOT IN (SELECT ID FROM MYTABLE)M) T


μπορει και να μη θελει aliases. Σε πιο σοβαρες βασεις γινεται πιο ευκολα γιατι υπαρχουν sequence generators.
Εκεί έχω καταλήξει κι εγώ, ότι δεν γίνεται σε μια γραμμή ακριβώς γι' αυτόν τον λόγο που λες.

Πίνακα μπορώ να φτιάξω στο πρόγραμμα που διαβάζει την βάση και να τον επεξεργαστώ εκεί αντί για την database (έτσι το κάνω μέχρι τώρα δηλαδή), αλλά προσπαθούσα να βρω λύση με query για λόγους ταχύτητας. Για την ακρίβεια, σε άλλα σημεία του προγράμματος είναι πολύ πιο γρήγορο να φορτώνει τις τιμές σε πίνακα και να γίνεται εκεί η διαδικασία, επειδή ο πίνακας μένει αμετάβλητος. Στο συγκεκριμένο κομάτι του προγράμματος όμως θα πρέπει να φορτώνεται κάθε φορά ο πίνακας, οπότε έψαχνα λύση χωρίς πίνακα.

Θενκς ένιγουέι.


Άβαταρ μέλους
shrike
Δημοσιεύσεις: 4110
Εγγραφή: 02 Απρ 2018, 08:39
Phorum.gr user: Isildur
Τοποθεσία: Παρά θῖν' ἁλὸς

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από shrike » 17 Απρ 2019, 09:25

Dwarven Blacksmith έγραψε:
17 Απρ 2019, 00:05
Εναλλακτικά φτιάχνεις μια fuction που λουπάρει.
Τη φτιάχνω σε T-SQL που με βολεύει αλλά θα την καταλαβαίνεις σίγουρα.
SpoilerShow

Κώδικας: Επιλογή όλων

CREATE FUNCTION udf_GetNextFreeID (@id INT)
RETURNS INT
AS
BEGIN
  DECLARE @freeID INT
  
  SET @freeID = @id
  
  WHILE @freeID <= (SELECT MAX(id)+1 FROM my_table)
  BEGIN
    IF @freeID NOT IN (SELECT id FROM my_table)
    BEGIN
      RETURN @freeID
    END
    
    SET @freeID = @freeID +1
  END
  
  RETURN NULL
END
και μετά απλά

Κώδικας: Επιλογή όλων

SELECT dbo.udf_GetNextFreeID(6)
Μα, αυτό είναι το πρόβλημα ρε συ. Το "και μετά"... πρέπει να γίνει με ένα statement, λόγω ιδιαιτερότητας του προγράμματος.


Άβαταρ μέλους
foscilis
Δημοσιεύσεις: 9602
Εγγραφή: 21 Ιουν 2018, 11:42

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από foscilis » 17 Απρ 2019, 09:43

υπόψη αν τρέχεις MariaDB που είναι συμβατή με MySQL και το default σε debian, υπάρχει ένα sequence data engine με το οποίο διαβάζεις ψευδοπίνακες-ακολουθίες με αυτή τη σύνταξη:

SELECT * FROM seq_100_to_150;

Δες εδώ:

https://www.tutorialspoint.com/mariadb/ ... uences.htm

Οπότε τουλάχιστον γλιτώνεις το κόστος του να αποθηκεύσεις την ακολουθία κάπου.

Και πάλι βέβαια είναι λίγο πολύπλοκο αν τα όρια της ακολουθίας δεν είναι στατικά αλλά πρέπει να τα υπολογίσεις με min και max. Αλλά όχι πάρα πολύ.

Άβαταρ μέλους
shrike
Δημοσιεύσεις: 4110
Εγγραφή: 02 Απρ 2018, 08:39
Phorum.gr user: Isildur
Τοποθεσία: Παρά θῖν' ἁλὸς

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από shrike » 17 Απρ 2019, 09:54

Stalker έγραψε:
17 Απρ 2019, 00:16
Γιατί, προσπαθείς να κάνεις οικονομία στα primary keys μην τυχόν και πιάσεις το όριο των 9.223.372.036.854.775.807 διαθέσιμων 64μπιτων ακεραίων πριν την ώρα σου;
Όχι, δεν κάνω οικονομία. Μάλιστα, ο συγκεκριμένος πίνακας το πολύ-πολύ να πιάσει 15000-20000 rows σε κάποια στιγμή, δε νομίζω παραπάνω.

Υπάρχει το εξής πρόβλημα. Είναι ένα πρόγραμμα κυριολεκτικά αρχαίο, για να καταλάβεις είναι γραμμένο σε visual basic 6, το οποίο όμως για αρκετό καιρό ακόμα δεν μπορώ να ξεφορτωθώ και μου είναι εντελώς απαραίτητο. Μέχρι να μπορέσω να το αντικαταστήσω σε κάποια στιγμή, κάνω διάφορα πασαλείματα και μπαλώματα για να μπορεί να εκτελεί κάποιες λειτουργίες που δεν είχε στο παρελθόν. Στην αρχική του μορφή όλα τα data του τα διαχειριζόταν με random access files, επειδή όμως έπρεπε να το συνδυάσω και με αλλά προγράμματα και να προσθέσω και κάποιες νέες δυνατότητες (έχω τον κώδικα του προγράμματος και εγκατεστημένη vb για να το κάνω, όπως καταλαβαίνεις) έχω κάνει σύνδεση με mysql μέσω connector και σιγά-σιγά έχω μεταφέρει σε tables μιας βάσης κάποια από τα data που είχε σε αρχεία (συν τα tables που δημιούργησα για τις νέες λειτουργίες που λέω, συν τη σύνδεση με κάποια tables από άλλη βάση άλλου προγράμματος). Το πρόβλημα με την σύνδεση μέσω της vb είναι, ότι δεν δέχεται πολλαπλά statements. Εκτελεί σχεδόν τα πάντα άψογα (έχει πχ ένα μικρό θεματάκι με τις ημερομηνίες όταν επιστρέφουν κενές, αλλά αυτό λύνεται) και παραδόξως απίστευτα γρήγορα, αλλά το string που στέλνεις για εκτέλεση θα πρέπει να είναι σε μία γραμμή. Τουλάχιστον δεν βρήκα εγώ τρόπο να το κάνω αλλιώς, αλλά δεν ήθελα να σπαταλήσω και πολύ περισσότερο χρόνο απ' αυτόν που έχω ήδη σπαταλήσει, για να βρω καλύτερο τρόπο σύνδεσης. Πιθανόν να δουλεύει πιο σωστά με καμιά άλλη μορφή database, πχ MsSql, αλλά αυτό προς το παρόν αποκλείεται να το δοκιμάσω, αν πρόκειται να φάω τόσο χρόνο για τέτοιες νέες προσπάθειες προτιμώ να το κάνω για πλήρη αλλαγή του προγράμματος.
Stalker έγραψε:
17 Απρ 2019, 00:16
Το να ψάχνεις να γεμίσεις ενδιάμεσα primary keys φαίνεται εντελώς anti-pattern, το λιγότερο ποιος ξέρει τι references έχουν μείνει σε κλειδιά που κάποτε υπήρχαν και τώρα όχι.
Δυστυχώς, δεν είναι επιλογή μου το πώς θα αριθμούνται οι indexes. Έχει να κάνει με το πώς γινόταν η απόδοση των τιμών τους έως τώρα στο πρόγραμμα, όπου υπάρχουν πολλά κενά και ο χρήστης μπορεί να ορίσει όποιο νέο index θέλει (εφόσον είναι κενό βέβαια), ή μπορεί να ψάξει για το επόμενο κενό κλπ.

Εννοείται ότι δεν θα γέμιζα ποτέ ενδιάμεσα primary keys, όπως λες. Στον πίνακα μάλιστα, το id που λέω είναι μεν unique, αλλά έχω άλλο primary key που είναι και auto increment και που χρησιμοποιώ αποκλειστικά και μόνο για δικά μου functions όπως edit, deactivate κλπ και δεν συμμετέχει καθόλου στην υπόλοιπη λειτουργικότητα του προγράμματος.
Stalker έγραψε:
17 Απρ 2019, 00:16
Σε κάθε περίπτωση το πρόβλημα της εύρεσης συνεχόμενων τιμών σε πίνακα λέγεται gaps and islands problem, αν γκουγκλάρεις γι' αυτό όλο και κάτι θα βρεις.
Ξέρω, έτσι το 'ψαξα κι εγώ. Αλλά λύση μιας γραμμής δεν βρήκα...


Άβαταρ μέλους
shrike
Δημοσιεύσεις: 4110
Εγγραφή: 02 Απρ 2018, 08:39
Phorum.gr user: Isildur
Τοποθεσία: Παρά θῖν' ἁλὸς

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από shrike » 17 Απρ 2019, 09:56

foscilis έγραψε:
17 Απρ 2019, 09:43
υπόψη αν τρέχεις MariaDB που είναι συμβατή με MySQL και το default σε debian, υπάρχει ένα sequence data engine με το οποίο διαβάζεις ψευδοπίνακες-ακολουθίες με αυτή τη σύνταξη:

SELECT * FROM seq_100_to_150;

Δες εδώ:

https://www.tutorialspoint.com/mariadb/ ... uences.htm

Οπότε τουλάχιστον γλιτώνεις το κόστος του να αποθηκεύσεις την ακολουθία κάπου.

Και πάλι βέβαια είναι λίγο πολύπλοκο αν τα όρια της ακολουθίας δεν είναι στατικά αλλά πρέπει να τα υπολογίσεις με min και max. Αλλά όχι πάρα πολύ.
Ρε συ, θα δεις παρακάτω που απαντώ στον Stalker, μιλάμε για σύνδεση μέσω vb6...

Εκεί είναι και το βασικό πρόβλημα, σαν να παίρνεις δυο πλυντήρια και να βάζεις τη σκάφη πάνω για να πλύνεις. :-?


Άβαταρ μέλους
shrike
Δημοσιεύσεις: 4110
Εγγραφή: 02 Απρ 2018, 08:39
Phorum.gr user: Isildur
Τοποθεσία: Παρά θῖν' ἁλὸς

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από shrike » 17 Απρ 2019, 09:57

Thanks πάντως. Ευχαριστώ όσους μπήκαν στον κόπο να βοηθήσουν.


Κι αφού υπάρχουν αρκετοί γνώστες απ' ό,τι βλέπω, μπορεί να σας ξαναπρήξω ξαναρωτήσω σε κάποια στιγμή για άλλο θέμα. :lol:


Άβαταρ μέλους
shrike
Δημοσιεύσεις: 4110
Εγγραφή: 02 Απρ 2018, 08:39
Phorum.gr user: Isildur
Τοποθεσία: Παρά θῖν' ἁλὸς

Re: Ερώτημα για MySql

Μη αναγνωσμένη δημοσίευση από shrike » 17 Απρ 2019, 10:05

foscilis έγραψε:
17 Απρ 2019, 09:43
υπόψη αν τρέχεις MariaDB που είναι συμβατή με MySQL και το default σε debian, υπάρχει ένα sequence data engine με το οποίο διαβάζεις ψευδοπίνακες-ακολουθίες με αυτή τη σύνταξη:

SELECT * FROM seq_100_to_150;

Δες εδώ:

https://www.tutorialspoint.com/mariadb/ ... uences.htm

Οπότε τουλάχιστον γλιτώνεις το κόστος του να αποθηκεύσεις την ακολουθία κάπου.

Και πάλι βέβαια είναι λίγο πολύπλοκο αν τα όρια της ακολουθίας δεν είναι στατικά αλλά πρέπει να τα υπολογίσεις με min και max. Αλλά όχι πάρα πολύ.
Ενδιαφέρον, πάντως. Εκεί που το παλεύω έχει MySql, αλλά έχω τη δυνατότητα να το τρέξω και σε MariaDb σε άλλον server, οπότε θα του ρίξω μια ματιά. Ευχαριστώ.


Απάντηση


  • Παραπλήσια Θέματα
    Απαντήσεις
    Προβολές
    Τελευταία δημοσίευση

Επιστροφή στο “Πληροφορική”

Phorum.com.gr : Αποποίηση Ευθυνών