Movatterモバイル変換

When you use subtraction between integer values where one is of typeUNSIGNED, the result is unsigned unless theNO_UNSIGNED_SUBTRACTION SQL mode is enabled. SeeSection 14.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 ifM is omitted.

• TINYINT[(M)] [UNSIGNED] [ZEROFILL]

  A very small integer. The signed range is-128 to127. The unsigned range is0 to255.

• BOOL,BOOLEAN

  These types are synonyms forTINYINT(1). A value of zero is considered false. Nonzero values are considered true:

  mysql> SELECT IF(0, 'true', 'false');+------------------------+| IF(0, 'true', 'false') |+------------------------+| false                  |+------------------------+mysql> SELECT IF(1, 'true', 'false');+------------------------+| IF(1, 'true', 'false') |+------------------------+| true                   |+------------------------+mysql> SELECT IF(2, 'true', 'false');+------------------------+| IF(2, 'true', 'false') |+------------------------+| true                   |+------------------------+

  However, the valuesTRUE andFALSE are merely aliases for1 and0, respectively, as shown here:

  mysql> SELECT IF(0 = FALSE, 'true', 'false');+--------------------------------+| IF(0 = FALSE, 'true', 'false') |+--------------------------------+| true                           |+--------------------------------+mysql> SELECT IF(1 = TRUE, 'true', 'false');+-------------------------------+| IF(1 = TRUE, 'true', 'false') |+-------------------------------+| true                          |+-------------------------------+mysql> SELECT IF(2 = TRUE, 'true', 'false');+-------------------------------+| IF(2 = TRUE, 'true', 'false') |+-------------------------------+| false                         |+-------------------------------+mysql> SELECT IF(2 = FALSE, 'true', 'false');+--------------------------------+| IF(2 = FALSE, 'true', 'false') |+--------------------------------+| false                          |+--------------------------------+

  The last two statements display the results shown because2 is equal to neither1 nor0.

• SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

  A small integer. The signed range is-32768 to32767. The unsigned range is0 to65535.

• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

  A medium-sized integer. The signed range is-8388608 to8388607. The unsigned range is0 to16777215.

• INT[(M)] [UNSIGNED] [ZEROFILL]

  A normal-size integer. The signed range is-2147483648 to2147483647. The unsigned range is0 to4294967295.

• INTEGER[(M)] [UNSIGNED] [ZEROFILL]

  This type is a synonym forINT.

• BIGINT[(M)] [UNSIGNED] [ZEROFILL]

  A large integer. The signed range is-9223372036854775808 to9223372036854775807. The unsigned range is0 to18446744073709551615.

  SERIAL is an alias forBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

  Some things you should be aware of with respect toBIGINT columns:

  • All arithmetic is done using signedBIGINT orDOUBLE values, so you should not use unsigned big integers larger than9223372036854775807 (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 aBIGINT value to aDOUBLE.

    MySQL can handleBIGINT in the following cases:

    • When using integers to store large unsigned values in aBIGINT column.

    • InMIN(col_name) orMAX(col_name), wherecol_name refers to aBIGINT column.

    • When using operators (+,-,*, and so on) where both operands are integers.

  • You can always store an exact integer value in aBIGINT 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 useBIGINT 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 than9223372036854775807.

• DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

  A packed“exact” fixed-point number.M is the total number of digits (the precision) andD is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the- sign are not counted inM. IfD is 0, values have no decimal point or fractional part. The maximum number of digits (M) forDECIMAL is 65. The maximum number of supported decimals (D) is 30. IfD is omitted, the default is 0. IfM is omitted, the default is 10. (There is also a limit on how long the text ofDECIMAL literals can be; seeSection 14.25.3, “Expression Handling”.)

  UNSIGNED, if specified, disallows negative values. TheUNSIGNED attribute is deprecated for columns of typeDECIMAL (and any synonyms); you should expect support for it to be removed in a future version of MySQL. Consider using a simpleCHECK constraint instead for such columns.

  All basic calculations (+, -, *, /) withDECIMAL 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 forDECIMAL. TheFIXED 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, and1.175494351E-38 to3.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 andD is the number of digits following the decimal point. IfM andD 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. This syntax is deprecated, and you should expect support for it to be removed in a future version of MySQL.

  UNSIGNED, if specified, disallows negative values. TheUNSIGNED attribute is deprecated for columns of typeFLOAT (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simpleCHECK constraint instead for such columns.

  UsingFLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. SeeSection B.3.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 useFLOAT orDOUBLE for the resulting data type. Ifp is from 0 to 24, the data type becomesFLOAT with noM orD values. Ifp is from 25 to 53, the data type becomesDOUBLE with noM orD values. The range of the resulting column is the same as for the single-precisionFLOAT or double-precisionDOUBLE data types described earlier in this section.

  UNSIGNED, if specified, disallows negative values. TheUNSIGNED attribute is deprecated for columns of typeFLOAT (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simpleCHECK 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, and2.2250738585072014E-308 to1.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 andD is the number of digits following the decimal point. IfM andD 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; and is deprecated. You should expect support for this syntax to be removed in a future version of MySQL.

  UNSIGNED, if specified, disallows negative values. TheUNSIGNED attribute is deprecated for columns of typeDOUBLE (and any synonyms) and you should expect support for it to be removed in a future version of MySQL. Consider using a simpleCHECK constraint instead for such columns.

• DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL],REAL[(M,D)] [UNSIGNED] [ZEROFILL]

  These types are synonyms forDOUBLE. Exception: If theREAL_AS_FLOAT SQL mode is enabled,REAL is a synonym forFLOAT rather thanDOUBLE.