Select/Join auf Bitmaske

[niels$]

Hallo zusammen,

Ich habe in einer Konfigurationstabelle (nur 1 Record) ein String-Feld, in dem die verfügbaren Sprachen auf einem 8-Bit-Wort über die einzelnen Bits aktiviert werden.
Hier ein Beispiel:
Bit 0 = Deutsch
Bit 1 = Englisch
Bit 3 = Französisch
Hat der Wert nun also 00000101 sind Französisch und Deutsch aktiv.

In einer 2. Tabelle sind alle Sprachen auf den Dezimalwert des entsprechenden Flags gelistet:
1 = Deutsch
2 = Englisch
4 = Französisch

Nun will ich alle Werte aus Tabelle 2 bekommen, zu denen das entsprechende Bit in Tabelle 1 auf 1 steht.

Niels

[dibo33]

Ich habe in einer Konfigurationstabelle (nur 1 Record) ein String-Feld, in dem die verfügbaren Sprachen auf einem 8-Bit-Wort über die einzelnen Bits aktiviert werden.

Sehr schlecht, nutze ein Bit-Feld
http://dev.mysql.com/doc/refman/5.1/...ld-values.html

Nun will ich alle Werte aus Tabelle 2 bekommen, zu denen das entsprechende Bit in Tabelle 1 auf 1 steht.

Damit ist das dann ganz einfach mit den Bit-Funktionen machbar
http://dev.mysql.com/doc/refman/5.1/...functions.html

WHERE bitfeld & langid = langid

[niels$]

OK, danke erstmal, aber:

Sehr schlecht, nutze ein Bit-Feld
http://dev.mysql.com/doc/refman/5.1/...ld-values.html

Leider ist die DB so Fakt, da eine weitere Anwendung drauf zugreift, welche nur String & Integer kennt. Ich müsste mal testen, wie sie ein Bit-Feld verdaut.

Niels

[dibo33]

Kein Problem, die automatische Typumwandlung von MySQL sorgt schon dafür.

Ansonsten kannst du es auf Binary casten

WHERE CAST(bitfeld AS BINARY(8)) & langid = langid

[niels$]

Jo, Danke klappt super.

select * from menu where menu = 'spr' and CAST((select Sprachen from konfig where id=1)  AS BINARY(8)) & menuflag

Sogar wenn der String kürzer ist, wird es sauber genommen.
Da kommt doch aber glatt die Zusatzfrage zum Thema Bitwerte:
Ich habe da noch weitere 8_Bit-Werte, die auch kürzer sein könnten. Diese benötige ich aber Bitweise mundgerecht zerlegt. Da habe ich jetzt mit meinem Basiswissen SQL ein furchtbar langen Query gebaut. Der geht sicher auch mit den nun dazugelernten Möglichkeiten viel kürzer?

select 
Case when (length(StringFeld)<1) then '0' else mid(REVERSE(StringFeld),1,1) end as StringFeld0,
Case when (length(StringFeld)<2) then '0' else mid(REVERSE(StringFeld),2,1) end as StringFeld1,
Case when (length(StringFeld)<3) then '0' else mid(REVERSE(StringFeld),3,1) end as StringFeld2,
Case when (length(StringFeld)<4) then '0' else mid(REVERSE(StringFeld),4,1) end as StringFeld3,
Case when (length(StringFeld)<5) then '0' else mid(REVERSE(StringFeld),5,1) end as StringFeld4,
Case when (length(StringFeld)<6) then '0' else mid(REVERSE(StringFeld),6,1) end as StringFeld5,
Case when (length(StringFeld)<7) then '0' else mid(REVERSE(StringFeld),7,1) end as StringFeld6,
Case when (length(StringFeld)<8) then '0' else mid(REVERSE(StringFeld),8,1) end as StringFeld7
from tabelle where BedingungsFeld = Bedingung

Mein Versuch war leider nicht erfolgreich:

select 
CAST((stringfeld)  AS BINARY(8)) & 1 as stringfeld0,
CAST((stringfeld)  AS BINARY(8)) & 2 as stringfeld1,
CAST((stringfeld)  AS BINARY(8)) & 4 as stringfeld2,
CAST((stringfeld)  AS BINARY(8)) & 8 as stringfeld3,
CAST((stringfeld)  AS BINARY(8)) & 16 as stringfeld4,
CAST((stringfeld)  AS BINARY(8)) & 32 as stringfeld5,
CAST((stringfeld)  AS BINARY(8)) & 64 as stringfeld6,
CAST((stringfeld)  AS BINARY(8)) & 128 as stringfeld7
from tabelle where BedingungsFeld = Bedingung

Niels

[dibo33]

Shiften ist das Zauberwort

select 
(CAST((stringfeld)  AS BINARY(8)) & 1) as stringfeld0,
(CAST((stringfeld)  AS BINARY(8)) & 2) >> 1 as stringfeld1,
(CAST((stringfeld)  AS BINARY(8)) & 4) >> 2 as stringfeld2,
(CAST((stringfeld)  AS BINARY(8)) & 8) >> 3 as stringfeld3,
(CAST((stringfeld)  AS BINARY(8)) & 16) >> 4 as stringfeld4,
(CAST((stringfeld)  AS BINARY(8)) & 32) >> 5 as stringfeld5,
(CAST((stringfeld)  AS BINARY(8)) & 64) >> 6 as stringfeld6,
(CAST((stringfeld)  AS BINARY(8)) & 128) >> 7 as stringfeld7
from tabelle where BedingungsFeld = Bedingung

[niels$]

Genialer Kram

Doch leider funktioniert es nicht. Ohne das Shiften bekomme ich zwar Werte, aber immer den Maskenwert der Stelle, falls sie 1 ist. Mit Shiften gibt es einen Syntaxfehler.

Danke
Niels

[dibo33]

Mit Shiften gibt es einen Syntaxfehler.

Der da lautet?

Edit
Wenn du die Bitüberprüfung/-berechnung öfter benötigst, lege dir eine Stored Function an

CREATE FUNCTION is_bit(value char(8), bitnr tinyint)
RETURNS tinyint
RETURN (CAST(value AS BINARY(8)) & (1 << bitnr)) >> bitnr

Dann kannst du es einfacher abfragen

SELECT 
    is_bit(bitfeld, 0) bit0,
    is_bit(bitfeld, 1) bit1,
    is_bit(bitfeld, 2) bit2,
    is_bit(bitfeld, 3) bit3,
    is_bit(bitfeld, 4) bit4,
    is_bit(bitfeld, 5) bit5,
    is_bit(bitfeld, 6) bit6,
    is_bit(bitfeld, 7) bit7

Nochmal Edit:
Ich würde für das Feld auch kein VARCHAR nutzen sondern UNSIGNED TINYINT

[niels$]

Der da lautet?

Keiner mehr, da saß das Problem wohl eher vor der Tastatur. Aber da kommt nicht das gewünschte Ergebnis raus:

0000 0000 -> 0000 0000
0000 0001 -> 0000 0010
0000 0010 -> 0000 1010
0000 0100 -> 0110 0100

Edit
Wenn du die Bitüberprüfung/-berechnung öfter benötigst, lege dir eine Stored Function an

CREATE FUNCTION is_bit(value char(8), bitnr tinyint)
RETURNS tinyint
RETURN (CAST(value AS BINARY(8)) & (1 << bitnr)) >> bitnr

Dann kannst du es einfacher abfragen

Da habe ich schon drüber nachgedacht, aber die Anwendung an der ich gerade bastel, sollte sich so schlank wie möglich integrieren, da sie auf vielen Systemen laufen soll. Wird da die Aktualisierung der DB vergessen, geht die Anwendung nicht. Die Alternative wäre in der Anwendung zu prüfen ob die Function da ist und sie bei Bedarf anzulegen, aber das ist ja auch erst mal wieder Aufwand.

Nochmal Edit:
Ich würde für das Feld auch kein VARCHAR nutzen sondern UNSIGNED TINYINT

Jein! Dann wird aus 0000 0100 nur 100. Das macht die Sache in der DB nicht übersichtlicher. Zudem müsste die Hauptanwendung massiv geändert werden, was ich auch erfolgreich vermeiden kann.

Niels

[dibo33]

Jein! Dann wird aus 0000 0100 nur 100.

Dann wird aus 0000 0100 4

Auf BINARY casten geht auch nicht wie gedacht, mache es komplett anders

SELECT
    MID(LPAD(bitfeld, 8, '0'), 8, 1) bit0,
    MID(LPAD(bitfeld, 8, '0'), 7, 1) bit1,
    MID(LPAD(bitfeld, 8, '0'), 6, 1) bit2,
    MID(LPAD(bitfeld, 8, '0'), 5, 1) bit3,
    MID(LPAD(bitfeld, 8, '0'), 4, 1) bit4,
    MID(LPAD(bitfeld, 8, '0'), 3, 1) bit5,
    MID(LPAD(bitfeld, 8, '0'), 2, 1) bit6,
    MID(LPAD(bitfeld, 8, '0'), 1, 1) bit7

[niels$]

Dann wird aus 0000 0100 4

Da hast Du natülich Recht. Das wäre in der Hauptanwendung sogar sehr gut umsetzbar, da man hier die Werte mit einer Maske auswerten könnte, würde aber viele Leute voll aus der Bahn werfen, da teilweise Einstellung über das MySQL-Frontend vorgenommen werden. Dann muss man nicht nur Stellen zählen, sondern Rechnen!

Auf BINARY casten geht auch nicht wie gedacht, mache es komplett anders

SELECT
    MID(LPAD(bitfeld, 8, '0'), 8, 1) bit0,
      :
      :
    MID(LPAD(bitfeld, 8, '0'), 1, 1) bit7

Funktioniert einfandfrei und nach Studium des entsrechenden Teils der MySQL-Doku verstehe ich es sogar.

Niels

[dibo33]

Dann muss man nicht nur Stellen zählen, sondern Rechnen!

Nicht zwangsläufig, du kannst auch direkt Bitwerte übergeben

INSERT INTO Tabelle (bitfeld) VALUES(b'101')

Schreibt dir 5 ins Feld