Blog - MySQL Bitmask Anwendungsbeispiel

Üblicherweise verwendet man pro Datenfeld eine eigene Spalte in der Datenbank. Mit der Zeit kann es dazu kommen, dass die Tabelle damit sehr unübersichtlich wird. Für einfache JA/NEIN Felder, also Boolean empfiehlt es sich eine Bitmask zu verwenden. Bei Immobilien gibt es einige Attribute, die eine Immobilie eben hat oder nicht hat, z. B. Balkon, Lift oder Garage. Wir haben die Tabelle ads in der unsere Immobilien gespeichert werden.

CREATE TABLE `ads` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `balcony` tinyint(3) unsigned NOT NULL DEFAULT 0,
 `elevator` tinyint(3) unsigned NOT NULL DEFAULT 0,
 `garage` tinyint(3) unsigned NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`)
);

Nun erweiterte man die Tabelle ads um die Spalte tags.

ALTER TABLE `ads` ADD `tags` INT UNSIGNED NOT NULL DEFAULT '0';

Wichtig dabei, dass der Defaultwert = 0 ist, d.h. zu Beginn ist kein Tag gesetzt. Weiters erzeugen wir eine Tabelle tags in der wir den Eigenschaften Werte zuweisen.

CREATE TABLE `tags` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(50) CHARACTER SET utf8 NOT NULL,
 `value` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`)
);

Hier legen nun auch unsere Eigenschaften an. Jede Eigenschaft entspricht einem Bit in der Bitmask, daher wird in value immer eine 2^x Wert verwendet.

INSERT INTO `tags` (`id`, `name`, `value`) VALUES
(1, 'facilities_balcony', 1),
(2, 'facilities_elevator', 2),
(3, 'facilities_garage', 4);

Hat nun eine Immobilie z. B. einen Balkon, dann muss nur noch das Bit gesetzt werden.

UPDATE `ads`
SET tags = tags | 1
WHERE `title` LIKE '%balkon%';

Dasselbe gilt für Lift oder Garage:

UPDATE `ads`
SET tags = tags | 2
WHERE `title` LIKE '%lift%';
UPDATE `ads`
SET tags = tags | 4
WHERE `title` LIKE '%garage%';

Nun können die überflüssigen Spalten in der ads Tabelle gelöscht werden.

ALTER TABLE `ads`
  DROP `balcony`,
  DROP `elevator`,
  DROP `garage`;

Wie können nun Immobilien mit dem Tag / der Eigenschaft Lift selektiert werden?

SELECT *
FROM `ads`
WHERE tags & 2 = 2;

Weiters kann nun auch Immobilien gefiltert werden, die einen Balkon und einen Lift haben. Dafür muss man nur die 2 value-Spalten addieren. In unseren Fall ist dies 1 = Balkon und 2 = Lift, damit ist 1+2=3.

SELECT *
FROM `ads`
WHERE tags & 3 = 3;

Falls man Immobilien filtern will, die entweder einen Balkon oder einen Lift haben, dann kann folgende Abfrage verwendet werden.

SELECT *
FROM `ads`
WHERE (tags & 1 = 1 OR tags & 2 = 2);

Wenn nun z. B. eine Abfrage mal falsch formuliert wurde oder sich die Kriterien für z. B. Garage geändert hat, dann kann man ein spezifisches Bit ganz leicht zurücksetzen.

UPDATE `ads`
SET tags = tags & ~3;

Dies ist ein einfaches Beispiel, in der Wirklichkeit geht es darum vielen Spalten (mehr als 3) zu ersetzen und damit die Tabelle übersichtlicher zu machen.