RegEx mysql 8

[Jörg]

Hallo Forum,

in mysql 5.7 habe ich in einer Query folgende RegEx benutzt:

 ...AND User REGEXP '[[:<:]]Joerg[[:>:]]'

Diese nötige RegEx rührt aus deinem Designfehler der DB, den ich aber jetzt unmöglich korrigieren kann.

Jedenfalls bemängelt mysql 8 diesen Ausdruck.

PHP Fatal error:  Uncaught mysqli_sql_exception: Illegal argument to a regular expression. in ...

Was hat sich diesbzgl. geändert bzw. wie hätte mysql 8 den Ausdruck gerne?

Die User sind in dieser Spalte kommabasiert gespeichert. Leider 1-n User in einer Spalte. Und die Abfrage soll in disem Fall alle Vorgänge ermitteln, in der Joerg mit drin ist. Mit % und LIKE ist aber n ichts zu wollen, da es User Peter, sowie z.b. Hans Peter gibt.

Jörg

[Jörg]

Was haltet Ihr von:

and FIND_IN_SET('Joerg', REPLACE(TRIM(BOTH ',' FROM User), ', ', ',')) 

?

Jörg

[Rolf B]

Hallo Jörg,

Frage vorweg: Bist Du absolut sicher, dass Du MYSQL 8 hast und kein Datenmariechen? MYSQL 8 soll teuer sein, hab ich gehört, weswegen die Hoster alle zur Maria geflüchtet sind.

Wenn's MYSQL 8 ist: RTFM.

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multibyte safe. (Prior to MySQL 8.0.4, MySQL used Henry Spencer's implementation of regular expressions, which operates in byte-wise fashion and is not multibyte safe.

und etwas später:

The Spencer library supports word-beginning and word-end boundary markers ([[:<:]] and [[:>:]] notation). ICU does not. For ICU, you can use \b to match word boundaries; double the backslash because MySQL interprets it as the escape character within strings.

Fettsatz von mir. Leider steht im MYSQL Artikel nicht, welche Backslash-Sequenzen ICU unterstützt. \b ist bekannt als Word Boundary Marker, ja, aber ob ICU das ganze PCRE-Spektrum abdeckt - das steht da nicht. Im Zweifelsfall muss man probieren oder die ICU-Regex Doku studieren.

Allerdings wird die Word Boundary-Lösung immer noch Peter in "Rudi,Hans Peter,Otto" finden. D.h. deine Lösung war schon in 5.7 falsch. Du musst anders suchen.

FIND_IN_SET ist meiner Meinung nach keine Lösung. Es erfordert, dass die User-Spalte vom Typ SET ist. Ist sie das? SET Spalten haben eine Einschränkung:

A SET column can have a maximum of 64 distinct members.

Ist das okay für Dich? Ich GLAUBE nämlich, dass das bedeutet, dass es maximal 64 verschiedene Usernamen in der gesamten Tabelle geben darf, weil MYSQL jedem Namen eine Bitposition zuordnet.

Anderes Problem: Was ist mit User-Einträgen wie "Rolf, Joerg , Fritz,Otto,Paul" - also unklare Spacemengen um das Komma herum. Kann das passieren? Haben User durch Fehlbedienung die Möglichkeit, kreativ Spaces einzusetzen?

Eigentlich müsstest Du nach einer Sequenz dieser Art suchen:

• ^ oder ,
• Whitespace
• gesuchter name
• Whitespace
• , oder $

Die Sache mit dem Whitespace ist optional, das hängt von deinen Daten ab. Frage ist dann auch, ob Du nur das Space (\x20) berücksichtigen musst oder auch das &nbsp (\xA0), ein Tab (\x09) oder die Flut von Unicode-Whitespacevarianten. Ich hoffe für Dich, dass Du einfach gar keine Spaces um deine Kommas herum hast.

In dem Fall sähe eine brauchbare Regex meiner Meinung nach so aus:

(^|,)Joerg(,|$)

Man muss das mit dem |-Ausdruck machen, weil die Start- und Ende-Assertions ^ und $ nicht in einem Zeichenklassen-Ausdruck (also eckige Klammern) zulässig sind.

Simple Spaces kannst Du noch so ergänzen:

(^|,) *Joerg *(,|$)

Generisches Whitespace müsste so gehen:

(^|,)[[:blank:]]*Joerg[[:blank:]]*(,|$)

Dass dieses geregexe eine Performance-Katastrophe ist, brauch ich Dir nicht zu sagen. Wenn ich dein Posting so lese, weißt Du das schon... Ich hoffe, deine Query enthält weitere Suchbedingungen, die die zu durchsuchende Menge begrenzen, so dass nicht allzuviele Rows durchforstet werden müssen. Wenn die User-Spalte im Index ist, könnte das aber dazu führen, dass er mit einem Index-Scan arbeiten kann und keinen Table-Scan braucht. Hängt jetzt von der Gesamtquery ab. Mach einen EXPLAIN.

Woher ich das weiß? Hab ich in meinem Leben schonmal MYSQL 8 benutzt? Nö. Kenn ich die ICU Library? Nö. Alles just im Handbuch zusammengelesen. Mag also falsch sein. In dem Fall findest Du den Link für weitere Lektüre zu Beginn meiner Antwort.

Rolf

[Jörg]

Hallo Rolf,

Frage vorweg: Bist Du absolut sicher, dass Du MYSQL 8 hast und kein Datenmariechen? MYSQL 8 soll teuer sein, hab ich gehört, weswegen die Hoster alle zur Maria geflüchtet sind.

Niemals bin ich mir da sicher.
Eher im Gegentum.
Das wird sicher ne Maria sein.

A SET column can have a maximum of 64 distinct members.

Ist das okay für Dich? Ich GLAUBE nämlich, dass das bedeutet, dass es maximal 64 verschiedene Usernamen in der gesamten Tabelle geben darf, weil MYSQL jedem Namen eine Bitposition zuordnet.

Ja, das wäre ok.

Anderes Problem: Was ist mit User-Einträgen wie "Rolf, Joerg , Fritz,Otto,Paul" - also unklare Spacemengen um das Komma herum. Kann das passieren? Haben User durch Fehlbedienung die Möglichkeit, kreativ Spaces einzusetzen?

Nenee, das ist kein Problem.

Dass dieses geregexe eine Performance-Katastrophe ist, brauch ich Dir nicht zu sagen. Wenn ich dein Posting so lese, weißt Du das schon... Ich hoffe, deine Query enthält weitere Suchbedingungen, die die zu durchsuchende Menge begrenzen, so dass nicht allzuviele Rows durchforstet werden müssen. Wenn die User-Spalte im Index ist, könnte das aber dazu führen, dass er mit einem Index-Scan arbeiten kann und keinen Table-Scan braucht. Hängt jetzt von der Gesamtquery ab. Mach einen EXPLAIN.

Wenn ich mal ein bisschen zeit habe, ändere ich das ab.
Scheint mir gar nicht soooo viel Arbeit zu sein.
Hey... ich war das echt (nach sql-Maßstäben) "jung" 😀

Woher ich das weiß? Hab ich in meinem Leben schonmal MYSQL 8 benutzt? Nö. Kenn ich die ICU Library? Nö. Alles just im Handbuch zusammengelesen. Mag also falsch sein. In dem Fall findest Du den Link für weitere Lektüre zu Beginn meiner Antwort.

Danke fürs "Mitlesen", Rolf 😉

Jörg

[Raketenwilli]

Niemals bin ich mir da sicher.

Nun ja... das wird eigentlich direkt nach der Anmeldung mittels MariaDB- oder MySQL-Client angezeigt. Aber MySQL und MariaDB können diese Information auch explizit per Abfrage liefern:

MariaDB [(none)]> select @@version;
+----------------------------------+
| @@version                        |
+----------------------------------+
| 10.6.12-MariaDB-0ubuntu0.22.04.1 |
+----------------------------------+
1 row in set (0,001 sec)

[Jörg]

MariaDB [(none)]> select @@version;
+----------------------------------+
| @@version                        |
+----------------------------------+
| 10.6.12-MariaDB-0ubuntu0.22.04.1 |
+----------------------------------+
1 row in set (0,001 sec)

mysql --version mysql Ver 8.0.34-26 for Linux on x86_64

[Raketenwilli]

MariaDB [(none)]> select @@version;
+----------------------------------+
| @@version                        |
+----------------------------------+
| 10.6.12-MariaDB-0ubuntu0.22.04.1 |
+----------------------------------+
1 row in set (0,001 sec)

== Version des Servers

Diesen Abruf kannst Du verwenden, wenn Du innerhalb eines Skriptes (Python, PHP, NodeJS, ...) die Version des Servers fest stellen willst, um ggf. für den Server optimierte Abfragen verwenden zu können.

Du schreibst nun:

 mysql --version
mysql  Ver 8.0.34-26 for Linux on x86_64

== Version des Clients (Das ist im vorliegenden Fall NICHT das, was Du willst!)

Die korrekten Varianten des Abrufs der Version des Mysql/Mariadb-Servers in einer Shell des Betriebssystems lauten:

mysqld --version
mysqld  Ver 10.6.12-MariaDB-0ubuntu0.22.04.1 for debian-linux-gnu on x86_64 (Ubuntu 22.04)

oder

mariadbd --version
mariadbd  Ver 10.6.12-MariaDB-0ubuntu0.22.04.1 for debian-linux-gnu on x86_64 (Ubuntu 22.04)

Das „d“ macht also den Unterschied.

Client und Server sind verschiedene Programme. Wenn der Client und der Server nicht auf dem selben Rechner sind (und selbst dann) können sich beide stark unterscheiden, sogar Mischungen sind möglich. Client und Server sowohl von Mariadb als auch MySQL sind weitgehend (sic: weit gehend) „kreuzkompatibel“, allerdings scheinen inzwischen bei der Verbindungsaufnahme schon Warnungen auf, dass nicht alle Eigenschaften/Besonderheiten unterstützt werden wenn man den „falschen” Client benutzt. Das beginnt schon bei den Möglichkeiten der Verschlüsselung der Übertragung.

Wie lange in Ubuntu & Co. noch der Client /usr/sbin/mariadb und der Server /usr/sbin/maridbd zu ihren Pendants /usr/sbin/mysql bzw. /usr/sbin/mysqldverlinkt sind vermag ich nicht zu sagen. Nur das die Kompatibilität schwindet weiß ich.

[Raketenwilli]

Hm. Als Ehrenbürger von Ganzgenauheim muss ich mich korrigieren:

Wie lange in Ubuntu & Co. noch der Client /usr/sbin/mariadb und der Server /usr/sbin/maridbd zu ihren Pendants /usr/sbin/mysql bzw. /usr/sbin/mysqld verlinkt sind vermag ich nicht zu sagen.

Korrekt:

Wie lange in Ubuntu & Co. noch der Client /usr/bin/mariadb und der Server /usr/sbin/mariadbd mit (Kompatibiliäts- oder Bequemlichkeits-) Links von den erwarteten Adressen ihrer „Pendants“ /usr/bin/mysql bzw. /usr/sbin/mysqld versehen sind vermag ich nicht zu sagen.

(und so sieht das aus:)

user@host:~$ cd /usr/bin
user@host:/usr/bin$ ls -l mysql mariadb
-rwxr-xr-x 1 root root 4210456 Feb 10  2023 mariadb
lrwxrwxrwx 1 root root       7 Feb 10  2023 mysql -> mariadb

[…]

user@host:/usr/bin$ cd /usr/sbin
user@host:/usr/sbin$ ls -l mysqld mariadbd
-rwxr-xr-x 1 root root 24203200 Feb 10  2023 mariadbd
lrwxrwxrwx 1 root root        8 Feb 10  2023 mysqld -> mariadbd

[Rolf B]

Hallo Jörg,

MariaDB bringt einen Satz Executables mit, die mysql imitieren.

Allerdings gibt die Maria-Version von mysql – zumindest unter Windows – so etwas aus:

mysql  Ver 15.1 Distrib 10.6.8-MariaDB, for Win64 (AMD64), source revision b21…

(Ja, ich hab länger nicht aktualisiert, aber es gibt auch keinen externen Zugriff auf meinen Computer…)

Die beiden Laufenten von Onkel Bing Googlesby sagen mir, dass MariaDB das unter Linux genauso macht.

Heißt also: Du hast MYSQL 8 und MariaDB 10.6 parallel installiert und greifst mit deinem SQL auf MariaDB zu.

Die entsprechende Handbuchseite ist [hier]. Da steht, dass MariaDB die PCRE Regex-Library verwendet.

Regexe haben einen kleinen gemeinsamen Nenner, aber die genauen Fähigkeiten jeder Engine sind unterschiedlich.

Heißt: Mein einfacher Vorschlag (^|,)Joerg(,|$) sollte definitiv funktionieren, die Version mit [[:blank:]] steht im MYSQL Handbuch und sollte auch klappen. Die regex101.com Seite zeigt, dass [[:blank:]] in PCRE, Golang und Rust funktioniert, nicht aber in JavaScript, Java, Python oder .net.

Jamie Zawinski (der, der zusammen mit Brendan Eich Mozilla gründete) wird dieser Spruch zugeschrieben: "Some people, when confronted with a problem, think 'I know, I’ll use regular expressions.' Now they have two problems."^[1]

Yup. At least.

Rolf

---

1. Nicht für die Zitatesammlung vorschlagen. Ist schon drin. ↩︎

[Raketenwilli]

Regexe haben einen kleinen gemeinsamen Nenner, aber die genauen Fähigkeiten jeder Engine sind unterschiedlich.

Für manche noch verwirrender: Diverse Programme - ich nenne mal nur sed und grep, haben die Möglichkeit zwischen Engines bzw. dem Umfang der Fähiggkeiten umzuschalten.

Hintergrund ist:

a) dass die Engines, die mehr können, dann auch (deutlich) langsamer sind.

b) genutzt werden dann regelmäßig andere Libarys (Windows: dlls)

Bei mir sind das u.a.:

• libpcre2-16-0
• libpcre2-32-0
• libpcre2-posix3
• libpcre3

[Auge]

Hallo

Frage vorweg: Bist Du absolut sicher, dass Du MYSQL 8 hast und kein Datenmariechen? MYSQL 8 soll teuer sein, hab ich gehört, weswegen die Hoster alle zur Maria geflüchtet sind.

MySQL 8 dürfte es, wie die bei Oracle erschienenen Vorversionen auch, in einer kostenlosen Variante geben. Sonst würde mein Hoster sie wohl nicht ohne Zusatzkosten anbieten.

Tschö, Auge

[Raketenwilli]

Wie Dir schon Rolf geschrieben hat ist diese Abfrage sehr „teuer“. Zusammen mit der anderen Frage eines „Jörg“ nach einer Empfehlung eines Manages Servers ergibt sich das Bild, dass eine Optimierung angebracht ist.

→ Teile die Benutzer in einer weiteren Tabelle Gruppen zu und trage die Gruppe in die - dann indexierte - Spalte ein - und frage dann nach dieser. Natural Joins oder Left Joins sind recht fix.

[Rolf B]

Hallo Raketenwilli,

ich nehme an, dass es von dem Kaliber noch eine Menge mehr gibt. Ohne ein gründliches Redesign wird das nichts. Aber ich meine mich zu erinnern, dass wir diese Frage mit Jörg schon drölfmal durchhaben…

Rolf