Προς το περιεχόμενο

MySQL query με SET column = replace() και μεταβλητά μέρη

philos

Από τον philos
στο Web Design - Development

 Share

Προτεινόμενες αναρτήσεις

philos Crazy Insomniac

philos

Δημοσ.
Δημοσ. (επεξεργασμένο)

Έχω μια βάση δεδομένων forum στην οποία υπάρχει στον πίνακα post το εξής bbcode: 

[MENTION=123]philos[/MENTION]

Θέλω λοιπόν να αντικαταστήσω τα παραπάνω με το εξής: 

[USER=123]@philos[/USER]

(προσοχή στη διαφορά του "@"! )

Καταφέρνω αυτό που θέλω με το εξής query, το οποίο είναι και αρκετά γρήγορο: 

UPDATE `xf_post` SET `message` = replace(message, '[MENTION=', '[USER=')
UPDATE `xf_post` SET `message` = replace(message, '[/MENTION]', '[/USER]')

... όμως δεν επαρκεί για να μπει και το "@".

Καμιά ιδέα για το πως να τροποποιήσω το query ώστε να γίνει σωστά το replace με @  ; Από κανονικές παραστάσεις είμαι άσχετος. :)

Επεξ/σία από philos
tsofras Crazy Insomniac

tsofras

Δημοσ.
Δημοσ.
4 ώρες πριν, philos είπε

Έχω μια βάση δεδομένων forum στην οποία υπάρχει στον πίνακα post το εξής bbcode: 


[MENTION=123]philos[/MENTION]

Θέλω λοιπόν να αντικαταστήσω τα παραπάνω με το εξής: 


[USER=123]@philos[/USER]

(προσοχή στη διαφορά του "@"! )

Καταφέρνω αυτό που θέλω με το εξής query, το οποίο είναι και αρκετά γρήγορο: 


UPDATE `xf_post` SET `message` = replace(message, '[MENTION=', '[USER=')
UPDATE `xf_post` SET `message` = replace(message, '[/MENTION]', '[/USER]')

... όμως δεν επαρκεί για να μπει και το "@".

Καμιά ιδέα για το πως να τροποποιήσω το query ώστε να γίνει σωστά το replace με @  ; Από κανονικές παραστάσεις είμαι άσχετος. :)

Με αυτό εδώ θα κάνεις replace το ] και θα προσθέσει το @ πριν απο το λεκτικό . Απλά έχει την παραδοχή ότι θα κρατήσεις την μορφή που έχεις στο string

  

update xf_post set message = REPLACE(message,
(select result1 from (
SELECT 
  SUBSTR(message, 
    LOCATE(']',message)+1, 
      (CHAR_LENGTH(message) - LOCATE('[',REVERSE(message)) - LOCATE(']',message))) 
 as result1 FROM xf_post  as result2) as r),
(
select concat
 (
 '@',
(select result1 from (
SELECT 
  SUBSTR(message, 
    LOCATE(']',message)+1, 
      (CHAR_LENGTH(message) - LOCATE('[',REVERSE(message)) - LOCATE(']',message))) 
 as result1 FROM xf_post) as r)) 	
)
);

Παιδεύουν λίγο οι παρενθέσεις και τα εσωτερικά queries επειδή δεν υποστηρίζεται αλλιώς το update στον ίδιο πίνακα

Αφού τα έγραψα για να βρώ την λύση πάρε και τα παρακάτω να τα έχεις που σου επιστρέφουν σε 3 κομμάτια , [....] - Λεκτικό - [/....] 

SELECT SUBSTR(message, 1, position("]" in message)) from xf_post;

SELECT 
  SUBSTR(message, 
    LOCATE(']',message)+1, 
      (CHAR_LENGTH(message) - LOCATE('[',REVERSE(message)) - LOCATE(']',message))) 
FROM xf_post;


SELECT SUBSTR(message, (SELECT CHAR_LENGTH(message) - LOCATE('[', REVERSE(message))+1 from xf_post), position("]" in message)) from xf_post;

 

philos Crazy Insomniac

philos

Δημοσ.
  • Μέλος
Δημοσ.

@tsofras ευχαριστώ που ασχολήθηκες!

Λοιπόν, έχω τρεις σημειώσεις:

1. Το query επιστρέφει μήνυμα λάθους "Subquery returns more than 1 row".

2. Είσαι σίγουρος ότι το συγκεκριμένο query αφορά μόνο τον bbcode [ MENTION ] ή [ USER ]; Σαν forum database είναι λογικό να υπάρχουν πολλά bbcodes που τελειώνουν με [ / CLOSE TAG ].

3. Όταν λες " Απλά έχει την παραδοχή ότι θα κρατήσεις την μορφή που έχεις στο string " τι εννοείς; Βασικά ψάχνω για ένα query που θα κάνει αυτό ακριβώς που αναφέρω στο αρχικό μήνυμα:

Μετατροπή κάθε: 

[MENTION=μεταβλητό_μέρος1]μεταβολητό_μέρος2[/MENTION]

σε: 

[USER=μεταβλητό_μέρος1]@μεταβλητό_μέρος2[/USER]

(προσοχή στη διαφορά του "@"! Το τελικό αποτέλεσμα πρέπει να προσθέτει ένα "@" στο μεταβλητό_μέρος2 )

 

Προς το παρόν δεν υπάρχει κανένα μεταβλητό_μέρος2 με παπάκι στην database, όπως φαίνεται από το [ MENTION ] bbcode.

tsofras Crazy Insomniac

tsofras

Δημοσ.
Δημοσ. (επεξεργασμένο)
36 λεπτά πριν, philos είπε

@tsofras ευχαριστώ που ασχολήθηκες!

Λοιπόν, έχω τρεις σημειώσεις:

1. Το query επιστρέφει μήνυμα λάθους "Subquery returns more than 1 row".

2. Είσαι σίγουρος ότι το συγκεκριμένο query αφορά μόνο τον bbcode [ MENTION ] ή [ USER ]; Σαν forum database είναι λογικό να υπάρχουν πολλά bbcodes που τελειώνουν με [ / CLOSE TAG ].

3. Όταν λες " Απλά έχει την παραδοχή ότι θα κρατήσεις την μορφή που έχεις στο string " τι εννοείς; Βασικά ψάχνω για ένα query που θα κάνει αυτό ακριβώς που αναφέρω στο αρχικό μήνυμα:

Μετατροπή κάθε: 


[MENTION=μεταβλητό_μέρος1]μεταβολητό_μέρος2[/MENTION]

σε: 


[USER=μεταβλητό_μέρος1]@μεταβλητό_μέρος2[/USER]

(προσοχή στη διαφορά του "@"! Το τελικό αποτέλεσμα πρέπει να προσθέτει ένα "@" στο μεταβλητό_μέρος2 )

Προς το παρόν δεν υπάρχει κανένα μεταβλητό_μέρος2 με παπάκι στην database, όπως φαίνεται από το [ MENTION ] bbcode.

Εγώ το έτρεξα σε μία εγγραφή και έπαιξε, τώρα αν θες για συγκεκριμένο tag μπορείς να προσθέσεις ένα where που να κοιτάει αν το substring από το 2 έως την πρώτη αγκύλη ( σαν το πρώτο select που σου έβαλα απλά μετακινεις τον index κατά 1) ισούται με το mention ή το user

Όλο το παραπάνω είναι απάντηση όσον αφορά την MySQL και με καμία παραδοχή για forum ή bbcode , γιατί ούτε έχω ασχοληθεί ούτε γνωρίζω πως καταλήγουν στην βάση αυτά που χρειάζεται το φόρουμ.

Το sql αυτό προσθέτει το @ ανάμεσα στα tags , αν θες συγκεκριμένα τότε φτιάξε και το ανάλογο where

Ελπίζω να βοήθησα

Edit: κατάλαβα το πρόβλημα, νόμιζα από το ποστ ότι ήθελες συγκεκριμένες εγγραφές να αλλάξεις οπότε ήξερες το where, αλλά εσύ θες να τρέξει σε όλη τη βάση μία και καλή σωστά? Τότε όντως δεν παίζει αυτό το subquery γιατί θα γυρνάει όλες τις εγγραφές ανάμεσα στις αγκύλες

Αυτό θα το φτιάξεις μία φορά να τρέξει και τέλος?

Επεξ/σία από tsofras
philos Crazy Insomniac

philos

Δημοσ.
  • Μέλος
Δημοσ. (επεξεργασμένο)

Ναι θα τρέξει μόνο μια φορά μέσω phpmyadmin ώστε να "διορθωθεί" όλος ο πίνακας xf_post και συγκεκριμένα το column "message". ;)

Αν δεις το αρχικό μήνυμα, τα συγκεκριμένα queries: 

UPDATE `xf_post` SET `message` = replace(message, '[MENTION=', '[USER=')
UPDATE `xf_post` SET `message` = replace(message, '[/MENTION]', '[/USER]')

...διορθώνουν αυτό που θέλω, με τη διαφορά ότι δεν βάζουν το "@" που χρειάζομαι. Ουσιαστικά ψάχνω ένα query που να βάζει και το "@" στο: 

[USER=μεταβλητό_μέρος1]@μεταβλητό_μέρος2[/USER]

Η τωρινή βάση έχει τα εξής μεταξύ άλλων ως "message" (παράδειγμα) : 

[MENTION=123]philos[/MENTION]

Θέλω λοιπόν να αντικαταστήσω τα παραπάνω με το εξής: 

[USER=123]@philos[/USER]

"philos" είναι τα ψευδώνυμα και 123 τα user ids. Δεν μας ενδιαφέρει τι είναι, απλά να τα αντιμετωπίσουμε σαν μεταβλητά μέρη είναι το θέμα.

Δεν ξέρω πως να "χώσω" το "@" με απλή replace() γιατί μεσολαβούν τα μεταβλητά τμήματα.

Επεξ/σία από philos
tsofras Crazy Insomniac

tsofras

Δημοσ.
Δημοσ. (επεξεργασμένο)
1 ώρα πριν, philos είπε

Ναι θα τρέξει μόνο μια φορά μέσω phpmyadmin ώστε να "διορθωθεί" όλος ο πίνακας xf_post και συγκεκριμένα το column "message". ;)

Αν δεις το αρχικό μήνυμα, τα συγκεκριμένα queries: 


UPDATE `xf_post` SET `message` = replace(message, '[MENTION=', '[USER=')
UPDATE `xf_post` SET `message` = replace(message, '[/MENTION]', '[/USER]')

...διορθώνουν αυτό που θέλω, με τη διαφορά ότι δεν βάζουν το "@" που χρειάζομαι. Ουσιαστικά ψάχνω ένα query που να βάζει και το "@" στο: 


[USER=μεταβλητό_μέρος1]@μεταβλητό_μέρος2[/USER]

Η τωρινή βάση έχει τα εξής μεταξύ άλλων ως "message" (παράδειγμα) : 


[MENTION=123]philos[/MENTION]

Θέλω λοιπόν να αντικαταστήσω τα παραπάνω με το εξής: 


[USER=123]@philos[/USER]

"philos" είναι τα ψευδώνυμα και 123 τα user ids. Δεν μας ενδιαφέρει τι είναι, απλά να τα αντιμετωπίσουμε σαν μεταβλητά μέρη είναι το θέμα.

Δεν ξέρω πως να "χώσω" το "@" με απλή replace() γιατί μεσολαβούν τα μεταβλητά τμήματα.

Οκ το κατάλαβα απλά δεν σκέφτηκα τις πολλές εγγραφές

Επειδή δεν είμαι σε PC τώρα σου έχω μία παπατζα αν θες να δοκιμάσεις

Αν όλες οι εγγραφές έχουν ίδια μορφή , σημαίνει ότι πάντα θα έχεις 2 αγκύλες που κλείνουν σωστά? Οπότε ποτέ πάνω από 2 , και η δεύτερη πάντα στο τέλος.

Κάνεις replace την αγκύλη που κλείνει με ]@ 

Και μετά γράφεις ένα update που αλλάζει τον τελευταίο χαρακτήρα σε αγκύλη

Αλλιώς θα είναι πιο περίπλοκο σε ένα query

Αν θες δοκίμασε το

Edit: Μόλις το δοκίμασα , αν και παπάντζα , για μία φορά που το θέλεις δουλεύει μία χαρά 

-- Update που βάζει μετά απο κάθε αγκύλη του κλείνει το @
update xf_post set message = replace(message,']',']@');

-- Update που τρώει το τελευταίο @
UPDATE xf_post 
SET message = LEFT(message, LENGTH(message) -1) 
WHERE RIGHT(message, 1) = '@';

 

Επεξ/σία από tsofras
k33theod Experienced

k33theod

Δημοσ.
Δημοσ. (επεξεργασμένο)

Δοκίμασε το 

select regexp_replace('[USER=123]philos[/USER]', '(\[USER=\w+\])', '\1@');

Σε postgress που το δοκίμασα δίνει σωστά 

"[USER=123]@philos[/USER]"

επίσης οι πρώτες 2 replace που κάνεις κάντο με μία

όλο μαζί 

UPDATE `xf_post` SET `message` = replace(message, 'MENTION', 'USER')
UPDATE `xf_post` SET `message` = regexp_replace(message, '(\[USER=\w+\])' , '\1@')
Επεξ/σία από k33theod
  • Like 1
  • Thanks 1
tsofras Crazy Insomniac

tsofras

Δημοσ.
Δημοσ.
44 λεπτά πριν, k33theod είπε

Δοκίμασε το 


select regexp_replace('[USER=123]philos[/USER]', '(\[USER=\w+\])', '\1@');

Σε postgress που το δοκίμασα δίνει σωστά 


"[USER=123]@philos[/USER]"

επίσης η πρώτες 2 replace που κάνεις κάντο με μία

όλο μαζί 


UPDATE `xf_post` SET `message` = replace(message, 'MENTION', 'USER')
UPDATE `xf_post` SET `message` = regexp_replace(message, '(\[USER=\w+\])' , '\1@')

Καλή φάση , εγώ δουλεύω σε db2 οπότε μαύρα μεσάνυχτα, σε MySQL 5.7 δεν βρήκα αυτή την συνάρτηση τώρα είδα ότι την έχει στην 8.0

Κομψή λύση , μπράβο μαν

k33theod Experienced

k33theod

Δημοσ.
Δημοσ. (επεξεργασμένο)

Ευχαριστώ tsofras

Εγώ χρησιμοποιώ postgres αλλά είδα ότι την ίδια συνάρτηση υποστηρίζει και η mysql  https://dev.mysql.com/doc/refman/8.0/en/regexp.html.  Μένει μόνο να λειτουργεί και στον philos.

Και με μία γραμμή ίσως δουλευει 🙏 

UPDATE `xf_post` SET `message` = regexp_replace(message, '\[MENTION=(\w+)\](\w+)\[/MENTION\]', '[USER=\1]@\2[/USER]')

 

 

Επεξ/σία από k33theod
philos Crazy Insomniac

philos

Δημοσ.
  • Μέλος
Δημοσ. (επεξεργασμένο)

@k33theod ευχαριστώ πολύ!! Και @tsofras επίσης ευχαριστώ για την ενασχόληση!

Το έχω εδώ και 15 - 20 λεπτά να τρέχει στο localhost / phpmyadmin και ακόμα δεν έχει ολοκληρωθεί / αναφέρει affected rows.

Τρέχω αυτό που είπες γιατί δεν πρόλαβα το νεώτερο: 

UPDATE `xf_post` SET `message` = regexp_replace(message, '(\[USER=\w+\])' , '\1@')

Ο πίνακας να πω ότι έχει 2,7 εκατομμύρια γραμμές και είναι περίπου 3,5GB.

Μήπως αν βάζαμε μια WHERE με LIKE %[USER=% να το κάνει πιο περιορισμένο και πιο γρήγορο;

Επεξ/σία από philos
  • Sad 1
k33theod Experienced

k33theod

Δημοσ.
Δημοσ. (επεξεργασμένο)
23 λεπτά πριν, philos είπε

@k33theod ευχαριστώ πολύ!! Και @tsofras επίσης ευχαριστώ για την ενασχόληση!

Το έχω εδώ και 15 - 20 λεπτά να τρέχει στο localhost / phpmyadmin και ακόμα δεν έχει ολοκληρωθεί / αναφέρει affected rows.

Τρέχω αυτό που είπες γιατί δεν πρόλαβα το νεώτερο: 


UPDATE `xf_post` SET `message` = regexp_replace(message, '(\[USER=\w+\])' , '\1@')

Ο πίνακας να πω ότι έχει 2,7 εκατομμύρια γραμμές και είναι περίπου 3,5GB.

Μήπως αν βάζαμε μια WHERE με LIKE %[USER=% να το κάνει πιο περιορισμένο και πιο γρήγορο;

Έχεις τρέξει πρώτα την σκέτη replace;

Νομίζω μια where like θα χειροτερέψει τα πράγματα.

Επεξ/σία από k33theod
philos Crazy Insomniac

philos

Δημοσ.
  • Μέλος
Δημοσ. (επεξεργασμένο)

Καλησπέρα!! :)

Λοιπόν, εφόσον το query δεν κατάφερε να φτιάξει το πρόβλημα, σκέφτηκα να το διορθώσω διαμεσολαβώντας με php πριν το σύστημα κάνει parse το κείμενο / bbcode. Είναι πιο ασφαλές και μπορώ να κάνω πολλές δοκιμές πολύ γρήγορα.

Αν στην $text είναι το κείμενο, πως πρέπει να διορθωθεί το παρακάτω για να δουλέψει σωστά, ξέρει κάποιος; Χρησιμοποίησα ακριβώς το pattern και το replace που ανέφερε ο k33theod. :) 

$text = preg_replace('\[MENTION=(\w+)\](\w+)\[/MENTION\]', '[USER=\1]@\2[/USER]', $text);

(το παραπάνω σαν php βγάζει error: preg_replace(): Delimiter must not be alphanumeric or backslash).

Δοκίμασα να βάλω $1 και $2 αντί για \1 και \2 αλλά το πρόβλημα δεν λύθηκε.

Thanks εκ των προτέρων για τη βοήθεια (είμαι άσχετος με regular expressions). ;)

Επεξ/σία από philos
k33theod Experienced

k33theod

Δημοσ.
Δημοσ.

Δεν χρειάζεται να τρέξεις όλη τη βάση για να καταλάβεις ότι κάτι δεν πάει καλά μπορείς να τρέξεις ένα μικρό δειγμα 10 εγγραφές πχ ή κάτι στην consola

Από υλοποίηση σε υλοποίηση τα regex έχουν μικρές διαφορές έτσι στην php το pattern πρέπει να περικλείεται μεταξύ '/' αυτό κάνει το slash special character και για να το χρησιμοποιήσεις μέσα στο pattern πρέπει να το κάνεις escape με backslash. To backreference γίνεται για τον παραπάνω λόγο με 2 slash ή ακόμα καλύτερα με dollar sign  https://www.php.net/manual/en/function.preg-replace.php

Όσες περισσότερες πληροφορίες δώσεις στο regex pattern τόσο καλύτερα αποτελέσματα θα έχεις πχ το pattern που έγραψα δεν δουλεύει αν κάπου στο μεταβλητό μέρος έχει ένα κενό. Εάν το match αναμένεται πάντα στην αρχή του string με την προσθήκη ενός caret στην αρχή του pattern μειώνεις το χρόνο ψαξίματος για το worst case από O(n) (δεν υπάρχει match και n το μήκος του string) σε O(1)

Υπάρχει ένα ωραίο tutorial https://www.regular-expressions.info/tutorial.html που αν αφιερώσει κάποιος 2-3 ωρίτσες μπορεί να γράψει αρκετά καλά regexes

σε php τώρα 

<?php
    $text = '[MENTION=metablito_meros1]metablito_meros2[/MENTION]';
    $pattern = '/\[MENTION=(\w+)\](\w+)\[\/MENTION\]/';//έχει τις αλλαγές που σου είπα
    $replacement = '[USER=$1]@$2[/USER]';
    echo preg_replace($pattern, $replacement, $text);
   

?>

 

preg_replace.png

  • Thanks 1

Δημιουργήστε ένα λογαριασμό ή συνδεθείτε για να σχολιάσετε

Πρέπει να είστε μέλος για να αφήσετε σχόλιο

Δημιουργία λογαριασμού

Εγγραφείτε με νέο λογαριασμό στην κοινότητα μας. Είναι πανεύκολο!

Δημιουργία νέου λογαριασμού

Σύνδεση

Έχετε ήδη λογαριασμό; Συνδεθείτε εδώ.

Συνδεθείτε τώρα
 Share
Προς την λίστα θεμάτων
  • Δημιουργία νέου...