Overview of MySQL Data Types

Numeric Types

• Numeric types cover different categories: Integer, Floating-point, and Fixed-point types.
• Integer types vary in length, allowing storage of signed and unsigned values with specific ranges.
• TINYINT: 1 byte; range from -128 to 127 (signed) and 0 to 255 (unsigned).
• SMALLINT: 2 bytes; range from -32,768 to 32,767 (signed) and 0 to 65,535 (unsigned).
• MEDIUMINT: 3 bytes; range from -8,388,608 to 8,388,607 (signed) and 0 to 16,777,215 (unsigned).
• INT: 4 bytes; range from -2,147,483,648 to 2,147,483,647 (signed) and 0 to 4,294,967,295 (unsigned).
• BIGINT: 8 bytes; range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (signed) and 0 to 18,446,744,073,709,551,615 (unsigned).

Floating-Point Types

• FLOAT: 4 bytes; signed values range from -1.175494351E-38 to 3.402823466E+38. Unsigned values range from 0 to 3.402823466E+38.
• DOUBLE: 8 bytes; signed values are from -2.22507385850720E-308 to 1.7976931348623E+308. Unsigned values range from 0 to 1.7976931348623E+308.

Fixed-Point Types

• DECIMAL(M,D) can store values with M total digits and D decimal places; ranges from -999.99 to 999.99.

Bit Value Types

• BIT(N) data type stores N-bit values, where N can range from 1 to 64.
• Bit values can be specified using the b 'value' syntax.

Date and Time Types

• DATETIME: Combined date and time format (YYYY-MM-DD HH:MM:SS) with range '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
• DATE: Stores only the date (YYYY-MM-DD) with range '1000-01-01' to '9999-12-31'.
• TIMESTAMP: Converts values from timezone to UTC; range is '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
• TIME: Represents time in 'HH:MM:SS' format; range from '-838:59:59' to '838:59:59'.

Year Type

• YEAR stores year values in a 1-byte format; can be declared as YEAR(2) or YEAR(4).
• 4-digit representation ranges from '1901' to '2155'; 1 or 2 digits convert to years within specific ranges.

String Types

• CHAR: Fixed-length string that pads with spaces; range from 0 to 255 characters.
• VARCHAR: Variable-length string whose size can be from 0 to 65,535 characters in versions 5.0.3 and later.

Binary and Varbinary Types

• BINARY: Stores fixed-length binary strings; range from 0 to 255 bytes.
• VARBINARY: Stores variable-length binary strings; similar size limitations as VARCHAR.

BLOB and TEXT Types

• BLOB: A large binary object with different categories based on size: TINYBLOB (255), MEDIUMBLOB (16,777,215), LONGBLOB (4,294,967,295).
• TEXT: Treated as character strings; same size categories as BLOB.

Miscellaneous Types

• ENUM: A string object where value is selected from predefined list during table creation.
• SET: A string where zero or more comma-separated values can be stored, also selected from a predefined list.

This quiz provides an overview of various MySQL data types, including numeric types, integer types, and their respective value ranges. Learn about different data types available in MySQL and how they can be effectively utilized in database design.