MySQL Data Types (1).pdf

Overview of MySQL data types Prepared by: Shirley D. Moraga Numeric Types: Integer Types Type Length in Minimum Value Maximum Minimum Maximum Bytes (Signed) Value Value Value...

Overview of MySQL data types Prepared by: Shirley D. Moraga Numeric Types: Integer Types Type Length in Minimum Value Maximum Minimum Maximum Bytes (Signed) Value Value Value (Signed) (Unsigned) (Unsigned) TINYINT 1 -128 127 0 255 SMALLINT 2 -32768 32767 0 65535 MEDIUMINT 3 -8388608 8388607 to 0 16777215 INT 4 -2147483648 2147483647 0 4294967295 BIGINT 8 -9223372036854775808 92233720368 0 184467440737 54775807 09551615  Floating-Point Types Type Length Minimum Value Maximum Value Minimum Value Maximum in (Signed) (Signed) (Unsigned) Value Bytes (Unsigned) FLOAT 4 - -1.175494351E-38 1.175494351 3.402823466 3.402823466E+38 E-38 E+38 DOUBLE 8 - -2.22507385850720 0, and 1.797693134 1.7976931348623 14E- 308 2.225073858 862315 157E+ 308 50720 7E+ 308 14E- 308 Fixed-Point Types ► In standard SQL the syntax DECIMAL(5,2) (where 5 is the precision and 2 is the scale.) be able to store any value with five digits and two decimals. Therefore the value range will be from -999.99 to 999.99. Bit Value Types ► The BIT data type is used to store bit-field values. A type of BIT(N) enables storage of N-bit values. N can range from 1 to 64. ► To specify bit values, b 'value' notation can be used. ‘value’ is a binary value written using zeros and ones. For example, b'111' and b'10000000' represent 7 and 128, respectively. Date and Time Types DATETIME, DATE, and TIMESTAMP Types Description Display Range Format DATETIME Use when you need values YYYY-MM-DD HH:MM:SS '1000-01-01 00:00: containing both date and time 00' to '9999-12-31 information. 23:59:59'. DATE Use when you need only date YYYY-MM-DD '1000-01-01' to information. '9999-12-31'. TIMESTAMP Values are converted from the YYYY-MMDD HH:MM:SS '1970-01-01 00:00: current timezone to UTC while 01' UTC to '2038-01- storing, and converted back from 19 03:14:07' UTC UTC to the current time zone when retrieved. Time Type ► MySQL fetches and displays TIME values in 'HH:MM:SS' format or 'HHH:MM:SS' format The range of. TIME values from '-838:59:59' to '838:59:59'. ► The TIME values in MySQL can be recognized in different formats, some of which can include a trailing fractional seconds part in up to 6 digits microseconds precision. The range for TIME values is '-838:59:59.000000' to '838:59:59.000000'. Year Type ► The YEAR type is a 1-byte type used to represent year values. It can be declared as YEAR (2) or YEAR(4) to specify a display width of two or four characters. If no width is given the default is four characters. String length Range 4-digit string '1901' to '2155'. 4-digit number 1901 to 2155. 1- or 2-digit '0' to '99'. Values in the ranges '0' to '69' and '70' to '99' are string converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999. 1- or 2-digit 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to number YEAR values in the ranges 2001 to 2069 and 1970 to 1999. String Types CHAR and VARCHAR Types ►The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. They also differ in maximum length and in whether trailing spaces are retained. Types Description Display Format Range in characters CHAR Contains non-binary strings. Length is Trailing spaces are The length can be any value fixed as you declare while creating a removed. from 0 to 255. table. When stored, they are right padded with spaces to the specified length. VARCHAR Contains non-binary strings. Columns As stored. A value from 0 to 255 are variable-length strings. before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. BINARY and VARBINARY Types ► The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. Types Description Range in bytes BINARY Contains binary strings. 0 to 255 VARBINARY Contains binary strings. A value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.  BLOB and TEXT Types LONGBLOB Maximum length of 4294967295 characters Types Description Categories Range BLOB Large binary object that containing a TINYBLOB Maximum length of 255 variable amount of data. Values are characters. treated as binary strings. You don't need MEDIUMBLOB Maximum length of 16777215 to specify length while creating a characters. column. LONGBLOB Maximum length of 4294967295 characters TEXT Values are treated as character strings TINYBLOB Maximum length of 255 having a character set. characters. MEDIUMBLOB Maximum length of 16777215 characters. Miscellaneous Type ► ENUM Types A string object whose value is chosen from a list of values given at the time of table creation. For example - CREATE TABLE length ( length ENUM('small', 'medium', 'large') ); ► Set Types A string object having zero or more comma separated values (maximum 64). Values are chosen from a list of values given at the time of table creation.