In Operationen mit genauen Werten sind Präzisionsberechnungen mit genauen numerischen Datentypen (DECIMAL und Integer-Typen) und genauen numerischen Literalen möglich. Näherungsweise Datentypen und Zahlenliterale werden weiterhin als Fließkommazahlen behandelt.

Genaue numerische Literale haben einen ganzzahligen oder einen Bruchteilsanteil oder beides. Sie können ein Vorzeichen besitzen. Beispiele: 1, .2, 3.4, -5, -6.78, +9.10.

Näherungsweise numerische Literale werden in der wissenschaftlichen Schreibweise mit Mantisse und Exponent dargestellt. Beides kann auch vorzeichenbehaftet sein. Beispiele: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3.

Zwei Zahlen, die ähnlich aussehen, müssen nicht unbedingt beide genaue Werte oder beide Näherungswerte sein. So ist beispielsweise 2.34 eine genaue (Festkomma-)Zahl, während 2.34E0 eine näherungsweise (Fließkomma-)Zahl ist.

Der Datentyp DECIMAL ist ein Festkommatyp und Berechnungen mit diesem Typ sind genau. In MySQL hat DECIMAL mehrere Synonyme: NUMERIC, DEC, FIXED. Auch die Integer-Typen sind genaue Datentypen.

Die Datentypen FLOAT und DOUBLE sind Fließkommatypen und Berechnungen mit diesen Typen sind Näherungen. In MySQL haben FLOAT und DOUBLE die Synonyme DOUBLE PRECISION und REAL.

In diesem Abschnitt werden die Merkmale des Datenyps DECIMAL (und seiner Synonyme) in MySQL 5.1 vorgestellt. Dabei werden besonders folgende Themen angesprochen:

Auf mögliche Inkompatibilitäten mit Anwendungen, die für ältere Versionen von MySQL geschrieben wurden, weisen wir an geeigneter Stelle in diesem Abschnitt hin.

Die Deklarationssyntax für eine DECIMAL-Spalte lautet DECIMAL(M,D). Die Argumente haben in MySQL 5.1 folgende Wertebereiche:

Dass M maximal 65 beträgt, führt dazu, dass Berechnungen mit DECIMAL-Werten auf bis zu 65 Stellen genau sind. Da diese maximale Genauigkeit von 65 Stellen auch für genaue numerische Literale gilt, hat sich der maximale Wertebereich solcher Literale geändert. (In älteren MySQL-Versionen konnten Dezimalwerte bis zu 254 Stellen haben, aber die Berechnungen wurden als Fließkommaoperationen ausgeführt und waren daher Näherungen und nicht genau.)

Werte für DECIMAL-Spalten werden in MySQL 5.1 in einem Binärformat gespeichert, das Dezimalstellen in 4 Byte hineinpackt. Der Speicherbedarf für den ganzzahligen Teil und den Bruchteil wird separat ermittelt. Ein Vielfaches von 9 Stellen belegt 4 Byte und eventuelle Reststellen belegen einen Bruchteil von 4 Byte. Da beispielsweise die Spalte DECIMAL(18,9) auf jeder Seite des Dezimalpunkts 9 Stellen hat, belegen ihr ganzzahliger und ihr Dezimalteil jeweils 4 Byte. Eine DECIMAL(20,10)-Spalte hat dagegen auf jeder Seite des Dezimalpunkts 10 Stellen. Jeder Teil belegt also 4 Byte für die ersten 9 Stellen und 1 Byte für die Reststelle.

Folgende Tabelle zeigt den Speicherbedarf für Reststellen an:

Im Gegensatz zu älteren Versionen von MySQL (vor 5.0.3) speichern DECIMAL-Spalten in MySQL 5.1 keine Stelle für ein vorangestelltes +-Zeichen oder vorangestellte 0-Ziffern. Wenn Sie +0003.1 in eine DECIMAL(5,1)-Spalte speichern, so wird diese Zahl einfach als 3.1 gespeichert. Anwendungen, die das frühere Speicherverhalten benötigen, müssen umgeschrieben werden, um sie an diese Änderung anzupassen.

DECIMAL-Spalten können in MySQL 5.1 keine Werte speichern, die größer sind als in der Spaltendefinition angegeben. So lässt beispielsweise eine DECIMAL(3,0)-Spalte Werte von -999 bis 999 zu und eine DECIMAL(M,D)-Spalte erlaubt höchstens M – D Stellen auf der linken Seite des Dezimalpunkts. Das ist nicht kompatibel mit Anwendungen, die sich auf die älteren Versionen von MySQL stützen, welche eine zusätzliche Stelle für das +-Zeichen speicherten.

Der SQL-Standard verlangt für NUMERIC(M,D)-Werte eine Genauigkeit von exakt M Stellen und für DECIMAL(M,D)-Werte eine Genauigkeit von mindestens M Stellen (es darf auch mehr sein). In MySQL sind DECIMAL(M,D) und NUMERIC(M,D) dasselbe und haben beide eine Genauigkeit von exakt M Stellen.

Genauere Informationen über die Portierung von Anwendungen, die sich auf die frühere Behandlung des Datentyps DECIMAL stützen, finden Sie unter MySQL-Referenzhandbuch für die Version 5.0.

In Präzisionsberechnungen werden genaue Zahlen möglichst immer so verwendet, wie sie angegeben sind. Beispielsweise werden Zahlen in Vergleichsoperationen genau wie vorgegeben verwendet, ohne jede Änderung. Im strikten SQL-Modus wird eine Zahl, die mit INSERT in eine Spalte mit einem genauen Datentyp (DECIMAL oder Integer) geladen wird, mit ihrem genauen Wert eingefügt, wenn sie im Wertebereich der Spalte liegt. Wird die Zahl abgerufen, ist ihr Wert immer noch derselbe, der eingefügt wurde. (Ohne den Strict-Modus kann ein Wert bei INSERT-Operationen auch abgeschnitten werden.)

Die Handhabung numerischer Ausdrücke hängt davon ab, welche Art von Werten die Ausdrücke enthalten:

Wenn ein numerischer Ausdruck Strings enthält, so werden diese in Fließkommawerte mit doppelter Genauigkeit umgewandelt und es entsteht ein näherungsweiser Ausdruck.

Einfügungen in numerische Spalten werden durch den SQL-Modus beeinflusst, der mit der Systemvariablen sql_mode eingestellt wird. (Siehe Abschnitt 1.9.2, „Auswahl der SQL-Modi“.) In den nachfolgenden Ausführungen werden der Strict-Modus (der durch die Werte STRICT_ALL_TABLES oder STRICT_TRANS_TABLES eingeschaltet wird) und ERROR_FOR_DIVISION_BY_ZERO verwendet. Um alle Restriktionen einzuschalten, können Sie auch einfach den TRADITIONAL-Modus wählen, der beide Strict-Modi und ERROR_FOR_DIVISION_BY_ZERO umfasst:

mysql> SET sql_mode='TRADITIONAL';

Wird eine Zahl in eine Spalte eines genauen Datentyps (DECIMAL oder Integer) eingefügt, so wird sie mit ihrem genauen Wert in die Spalte geladen, wenn sie im zulässigen Wertebereich dieser Spalte liegt.

Hat ihr Dezimalteil zu viele Stellen, wird sie gerundet und eine Warnung ausgegeben. Gerundet wird, wie es unter Rundungsverhalten beschrieben ist.

Hat die Zahl in ihrem ganzzahligen Teil zu viele Stellen, ist sie zu groß und wird folgendermaßen behandelt:

Da ein Speicherunterlauf nicht erkannt wird, ist die Behandlung eines Unterlaufs nicht definiert.

Nach Voreinstellung kommt bei einer Division durch null das Ergebnis NULL heraus und keine Warnung. Haben Sie jedoch den SQL-Modus ERROR_FOR_DIVISION_BY_ZERO aktiviert, geht MySQL mit einer Division durch null ganz anders um:

Mit anderen Worten: Einfügungen und Aktualisierungen, in denen Ausdrücke mit einer Division durch null vorkommen, können als Fehler behandelt werden, allerdings nur dann, wenn zusätzlich zum Strict-Modus ERROR_FOR_DIVISION_BY_ZERO eingeschaltet ist.

Nehmen wir als Beispiel folgende Anweisung:

INSERT INTO t SET i = 1/0;

Nun sehen Sie, was bei unterschiedlichen Kombinationen von Strict und ERROR_FOR_DIVISION_BY_ZERO passiert:

Werden Strings in numerische Spalten eingefügt, findet folgende Zahlenkonvertierung statt, wenn der String keinen numerischen Inhalt hat:

Dieser Abschnitt beschreibt das Rundungsverhalten in Präzisionsberechnungen für die Funktion ROUND() und Einfügungen in DECIMAL-Spalten.

Die Funktion ROUND() rundet genaue und näherungsweise Argumente unterschiedlich:

Das folgende Beispiel verdeutlicht die Unterschiede einer Rundung von genauen und näherungsweisen Werten:

mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+

Da Einfügungen in eine DECIMAL-Spalte einen genauen Datentyp zum Ziel haben, wird immer nach der Regel „Runde auf die nächste gerade Zahl“ gerundet, egal ob der einzufügende Wert ein genauer oder ein Näherungswert ist:

mysql> CREATE TABLE t (d DECIMAL(10,0));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES(2.5),(2.5E0);
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> SELECT d FROM t;
+------+
| d    |
+------+
| 3    |
| 3    |
+------+

Dieser Abschnitt zeigt Beispiele für die Ergebnisse von Anfragen mit Präzisionsberechnungen in MySQL 5.1.

Beispiel 1. Zahlen werden möglichst mit ihrem genauen Wert wie vorgegeben benutzt:

mysql> SELECT .1 + .2 = .3;
+--------------+
| .1 + .2 = .3 |
+--------------+
|            1 |
+--------------+

Bei Fließkommawerten sind die Ergebnisse Näherungen:

mysql> SELECT .1E0 + .2E0 = .3E0;
+--------------------+
| .1E0 + .2E0 = .3E0 |
+--------------------+
|                  0 |
+--------------------+

Eine andere Möglichkeit, den Unterschied in der Behandlung von genauen und Näherungswerten zu zeigen, besteht darin, eine kleine Zahl immer wieder zu einer Summe zu addieren. Betrachten Sie folgende gespeicherte Prozedur, die zu einer Variablen tausendmal den Wert .0001 addiert.

CREATE PROCEDURE p ()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE d DECIMAL(10,4) DEFAULT 0;
  DECLARE f FLOAT DEFAULT 0;
  WHILE i < 10000 DO
    SET d = d + .0001;
    SET f = f + .0001E0;
    SET i = i + 1;
  END WHILE;
  SELECT d, f;
END;

Logischerweise sollte die Summe für d und f beide Male 1 ergeben, aber in Wirklichkeit ist dies nur bei der Dezimalberechnung der Fall. In der Fließkommaberechnung wird ein Fehler eingeführt:

+--------+------------------+
| d      | f                |
+--------+------------------+
| 1.0000 | 0.99999999999991 |
+--------+------------------+

Beispiel 2. Multiplikationen werden mit der durch den SQL-Standard vorgegebenen Anzahl Dezimalstellen ausgeführt: Für zwei Zahlen X1 und X2, die S1 beziehungsweise S2 Dezimalstellen haben, entsteht ein Ergebnis mit S1 + S2 Dezimalstellen:

mysql> SELECT .01 * .01;
+-----------+
| .01 * .01 |
+-----------+
| 0.0001    |
+-----------+

Beispiel 3. Das Rundungsverhalten ist wohldefiniert:

So ist das Rundungsverhalten (beispielsweise der Funktion ROUND()) unabhängig von der Implementierung der zugrunde liegenden C-Bibliothek, sodass die Ergebnisse plattformübergreifend konsistent sind.

DECIMAL-Spalten und genaue Zahlen werden nach der Regel „Ab der Hälfte wird aufgerundet“ behandelt: Werte mit einem Dezimalteil von .5 oder mehr werden, wie das nächste Beispiel zeigt, immer von null weg auf den nächstgelegenen Integer aufgerundet:

mysql> SELECT ROUND(2.5), ROUND(-2.5);
+------------+-------------+
| ROUND(2.5) | ROUND(-2.5) |
+------------+-------------+
| 3          | -3          |
+------------+-------------+

Dagegen stützt sich die Rundung von Fließkommawerten auf die C-Bibliothek, die auf vielen Systemen nach dem Grundsatz „Runde auf die nächste gerade Zahl“ verfährt. Auf solchen Systemen werden Werte mit einem Bruchteilsanteil auf die nächste gerade Zahl gerundet:

mysql> SELECT ROUND(2.5E0), ROUND(-2.5E0);
+--------------+---------------+
| ROUND(2.5E0) | ROUND(-2.5E0) |
+--------------+---------------+
|            2 |            -2 |
+--------------+---------------+

Beispiel 4. Wenn Sie im Strict-Modus einen zu großen Wert einfügen, führt dies zu einem Überlauf und verursacht einen Fehler. Der Wert wird nicht auf ein zulässiges Maß zurechtgestutzt.

Läuft MySQL jedoch nicht im Strict-Modus, wird der Wert abgeschnitten, bis er passt:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t SET i = 128;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT i FROM t;
+------+
| i    |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

Immerhin wird eine Überlaufbedingung registriert, wenn der Strict-Modus eingeschaltet ist:

mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SET i = 128;
ERROR 1264 (22003): Out of range value adjusted for column 'i' at row 1

mysql> SELECT i FROM t;
Empty set (0.00 sec)

Beispiel 5: Sind der Strict-Modus und ERROR_FOR_DIVISION_BY_ZERO eingeschaltet, verursacht eine Division durch null einen Fehler, anstatt das Ergebnis NULL nach sich zu ziehen.

Im nichtstrikten Modus hat die Division durch null das Ergebnis NULL:

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SET i = 1 / 0;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT i FROM t;
+------+
| i    |
+------+
| NULL |
+------+
1 row in set (0.03 sec)

Sind jedoch die richtigen SQL-Modi eingeschaltet, verursacht eine Division durch null immer einen Fehler:

mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t (i TINYINT);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t SET i = 1 / 0;
ERROR 1365 (22012): Division by 0

mysql> SELECT i FROM t;
Empty set (0.01 sec)

Beispiel 6. Vor MySQL 5.0.3 (also vor der Einführung von Präzisionsberechnungen) wurden sowohl genaue als auch näherungsweise Literale in Fließkommazahlen mit doppelter Genauigkeit umgewandelt:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 4.1.18-log |
+------------+
1 row in set (0.01 sec)

mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
Query OK, 1 row affected (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESCRIBE t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a     | double(3,1) |      |     | 0.0     |       |
| b     | double      |      |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

Seit MySQL 5.0.3 werden näherungsweise Literale zwar immer noch in Fließkommazahlen konvertiert, genaue hingegen werden als DECIMAL behandelt:

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.6-alpha-log |
+-----------------+
1 row in set (0.11 sec)

mysql> CREATE TABLE t SELECT 2.5 AS a, 25E-1 AS b;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESCRIBE t;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| a     | decimal(2,1) unsigned | NO   |     | 0.0     |       |
| b     | double                | NO   |     | 0       |       |
+-------+-----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Beispiel 7. Wenn das Argument einer Aggregatfunktion ein genauer numerischer Typ ist, ist ihr Ergebnis ebenfalls ein genauer numerischer Typ, und zwar mit mindestens so vielen Dezimalstellen, wie das Argument hatte.

Betrachten Sie folgende Anweisungen:

mysql> CREATE TABLE t (i INT, d DECIMAL, f FLOAT);
mysql> INSERT INTO t VALUES(1,1,1);
mysql> CREATE TABLE y SELECT AVG(i), AVG(d), AVG(f) FROM t;

Vor MySQL 5.0.3 (also vor der Einführung von Präzisionsberechnungen in MySQL) sähe das Ergebnis folgendermaßen aus:

mysql> DESCRIBE y;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| AVG(i) | double(17,4) | YES  |     | NULL    |       |
| AVG(d) | double(17,4) | YES  |     | NULL    |       |
| AVG(f) | double       | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

Das Resultat ist ein Double-Wert, egal welchen Typ das Argument hatte.

Mit MySQL 5.0.3 hat sich dies geändert:

mysql> DESCRIBE y;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| AVG(i) | decimal(14,4) | YES  |     | NULL    |       |
| AVG(d) | decimal(14,4) | YES  |     | NULL    |       |
| AVG(f) | double        | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+

Hier entsteht nur für das Fließkommaargument ein Ergebnis vom Typ Double. Argumente mit genauen Typen haben genaue Ergebnisse.