MySQL Types
From Pigbert Wiki
reference@MySql.com (http://dev.mysql.com/doc/refman/5.0/en/data-types.html)
Int Types
ref (http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html)
- TINYINT[(M)] [UNSIGNED] [ZEROFILL]
- A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
- Synonyms:
- BIT= TINYINT(1)
- BOOL, BOOLEAN = TINYINT(1) A value of zero is considered false. Non-zero values are considered true.
- TRUE =1"
- FALSE =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/INTEGER [(M)] [UNSIGNED] [ZEROFILL]
- A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
- 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
- SERIAL DEFAULT VALUE
- in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE.
Floating Point Types
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 allowed by the hardware.
- FLOAT [(M,D)] [UNSIGNED] [ZEROFILL]
- Single-precision floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. Accurate to approximately 7 decimal places.
- DOUBLE [(M,D)] [UNSIGNED] [ZEROFILL]
- Double-precision floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. Accurate to approximately 15 decimal places.
- Synonyms:
- DOUBLE PRECISION
- REAL (If the REAL_AS_FLOAT SQL mode is enabled, REAL is a synonym for FLOAT rather than DOUBLE)
- DECIMAL [(M[,D])] [UNSIGNED] [ZEROFILL]
- A packed “exact” fixed-point number. 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. All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.
String Types
- CHAR(0..255) ref (http://dev.mysql.com/doc/refman/5.0/en/char.html):
- The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.
- VARCHAR(0..65535) ref (http://dev.mysql.com/doc/refman/5.0/en/char.html):
- VARCHAR values are stored as a one-byte or two-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes. VARCHAR values are not padded when they are stored.
- BINARY(0..255) and VARBINARY(0..65536) ref (http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html):
- The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than non-binary strings. That is, they contain byte strings rather than character strings. This means that they have no character set, and sorting and comparison are based on the numeric values of the bytes in the values.
