11.1.1 Numeric Data Type Syntax

Inhaltsverzeichnis

For integer data types, M indicates the maximum display width. The maximum display width is 255. Display width is unrelated to the range of values a type can store, as described in Section 11.1.6, “Numeric Type Attributes”.

For floating-point and fixed-point data types, M is the total number of digits that can be stored.

As of MySQL 8.0.17, the display width attribute is deprecated for integer data types and support for it will be removed in a future MySQL version.

If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.

As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types and support for it will be removed in a future MySQL version. Consider using an alternative means of producing the effect of this attribute. For example, applications could use the LPAD() function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR columns.

Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.

As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms) and support for it will be removed in a future MySQL version. Consider using a simple CHECK constraint instead for such columns.

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.

Warning

When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled. See Section 12.10, “Cast Functions and Operators”.

BIT[(M)]
A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true:
1. mysql> SELECT IF(0, 'true', 'false');
2. +------------------------+
3. | IF(0, 'true', 'false') |
4. +------------------------+
5. | false |
6. +------------------------+
8. mysql> SELECT IF(1, 'true', 'false');
9. +------------------------+
10. | IF(1, 'true', 'false') |
11. +------------------------+
12. | true |
13. +------------------------+
15. mysql> SELECT IF(2, 'true', 'false');
16. +------------------------+
17. | IF(2, 'true', 'false') |
18. +------------------------+
19. | true |
20. +------------------------+
However, the values TRUE and FALSE are merely aliases for 1 and 0, respectively, as shown here:
1. mysql> SELECT IF(0 = FALSE, 'true', 'false');
2. +--------------------------------+
3. | IF(0 = FALSE, 'true', 'false') |
4. +--------------------------------+
5. | true |
6. +--------------------------------+
8. mysql> SELECT IF(1 = TRUE, 'true', 'false');
9. +-------------------------------+
10. | IF(1 = TRUE, 'true', 'false') |
11. +-------------------------------+
12. | true |
13. +-------------------------------+
15. mysql> SELECT IF(2 = TRUE, 'true', 'false');
16. +-------------------------------+
17. | IF(2 = TRUE, 'true', 'false') |
18. +-------------------------------+
19. | false |
20. +-------------------------------+
22. mysql> SELECT IF(2 = FALSE, 'true', 'false');
23. +--------------------------------+
24. | IF(2 = FALSE, 'true', 'false') |
25. +--------------------------------+
26. | false |
27. +--------------------------------+
The last two statements display the results shown because 2 is equal to neither 1 nor 0.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
This type is a synonym for INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
Some things you should be aware of with respect to BIGINT columns:
- All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE.
  MySQL can handle BIGINT in the following cases:
  - When using integers to store large unsigned values in a BIGINT column.
  - In MIN(col_name) or MAX(col_name), where col_name refers to a BIGINT column.
  - When using operators (+, -, *, and so on) where both operands are integers.
- You can always store an exact integer value in a BIGINT column by storing it using a string. In this case, MySQL performs a string-to-number conversion that involves no intermediate double-precision representation.
- The -, +, and * operators use BIGINT arithmetic when both operands are integer values. This means that if you multiply two big integers (or results from functions that return integers), you may get unexpected results when the result is larger than 9223372036854775807.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the - sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65. The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.
UNSIGNED, if specified, disallows negative values. As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type DECIMAL (and any synonyms) and support for it will be removed in a future MySQL version. Consider using a simple CHECK constraint instead for such columns.
All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
These types are synonyms for DECIMAL. The FIXED synonym is available for compatibility with other database systems.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
A small (single-precision) floating-point number. Permissible values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
FLOAT(M,D) is a nonstandard MySQL extension. As of MySQL 8.0.17, this syntax is deprecated and support for it will be removed in a future MySQL version.
UNSIGNED, if specified, disallows negative values. As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT (and any synonyms) and support for it will be removed in a future MySQL version. Consider using a simple CHECK constraint instead for such columns.
Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. See Section B.4.4.7, “Solving Problems with No Matching Rows”.
FLOAT(p) [UNSIGNED] [ZEROFILL]
A floating-point number. p represents the precision in bits, but MySQL uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT with no M or D values. If p is from 25 to 53, the data type becomes DOUBLE with no M or D values. The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.
UNSIGNED, if specified, disallows negative values. As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT (and any synonyms) and support for it will be removed in a future MySQL version. Consider using a simple CHECK constraint instead for such columns.
FLOAT(p) syntax is provided for ODBC compatibility.
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
A normal-size (double-precision) floating-point number. Permissible values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
DOUBLE(M,D) is a nonstandard MySQL extension. As of MySQL 8.0.17, this syntax is deprecated and support for it will be removed in a future MySQL version.
UNSIGNED, if specified, disallows negative values. As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type DOUBLE (and any synonyms) and support for it will be removed in a future MySQL version. Consider using a simple CHECK constraint instead for such columns.
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]
These types are synonyms for DOUBLE. Exception: If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE.

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-numeric-type-syntax.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut