MySQL - Data Types

In MySQL, a datatype is a specific type of data that can be stored in a database table column. Each column in a MySQL table must have a defined datatype that determines the kind of data that can be stored in that column.

Numeric Datatype

  1. TINYINT: This datatype stores integer values between -128 and 127, or 0 and 255 if unsigned. It requires one byte of storage.
  2. SMALLINT: This datatype stores integer values between -32,768 and 32,767, or 0 and 65,535 if unsigned. It requires two bytes of storage.
  3. MEDIUMINT: This datatype stores integer values between -8,388,608 and 8,388,607, or 0 and 16,777,215 if unsigned. It requires three bytes of storage.
  4. INT: This datatype stores integer values between -2,147,483,648 and 2,147,483,647, or 0 and 4,294,967,295 if unsigned. It requires four bytes of storage.
  5. BIGINT: This datatype stores integer values between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807, or 0 and 18,446,744,073,709,551,615 if unsigned. It requires eight bytes of storage.
  6. FLOAT: This datatype stores floating-point numbers with single precision, up to 7 digits. It requires four bytes of storage.
  7. DOUBLE: This datatype stores floating-point numbers with double precision, up to 15 digits. It requires eight bytes of storage.
  8. DECIMAL: This datatype stores decimal numbers with fixed precision and scale. The precision can be up to 65 digits, and the scale can be up to 30 digits. It requires storage based on the precision and scale values.

Date & Time Datatypes

  1. DATE: This datatype stores date values in the format 'YYYY-MM-DD'. It can store dates from January 1, 1000 to December 31, 9999. It requires three bytes of storage.
  2. TIME: This datatype stores time values in the format 'HH:MM:SS'. It can store times from '-838:59:59' to '838:59:59'. It requires three bytes of storage.
  3. DATETIME: This datatype stores both date and time values in the format 'YYYY-MM-DD HH:MM:SS'. It can store values from January 1, 1000 to December 31, 9999. It requires eight bytes of storage.
  4. TIMESTAMP: This datatype stores both date and time values in the format 'YYYY-MM-DD HH:MM:SS'. It can store values from January 1, 1970 to December 31, 2037, with a precision of one second. It requires four bytes of storage.
  5. YEAR: This datatype stores year values in a two-digit or four-digit format. If a two-digit format is used, values from 70 to 69 represent years from 1970 to 2069, while values from 00 to 69 represent years from 2000 to 2069. If a four-digit format is used, values from 1901 to 2155 can be stored. It requires one byte of storage.

String Datatypes

  1. CHAR: This datatype stores fixed-length character strings. The length of the string is specified when the table is created, and it cannot be changed later. The maximum length is 255 characters. If the string is shorter than the specified length, it is padded with spaces. It requires storage space equal to the specified length.
  2. VARCHAR: This datatype stores variable-length character strings. The length of the string can be specified when the table is created, up to a maximum of 65,535 characters. If the string is shorter than the specified length, it only requires storage space equal to the actual length of the string. VARCHAR is often a better choice than CHAR for columns where the length of the data can vary widely.
  3. TINYTEXT: This datatype stores variable-length character strings with a maximum length of 255 characters. It requires one byte of storage for the length of the string, plus the actual length of the string.
  4. TEXT: This datatype stores variable-length character strings with a maximum length of 65,535 characters. It requires two bytes of storage for the length of the string, plus the actual length of the string.
  5. MEDIUMTEXT: This datatype stores variable-length character strings with a maximum length of 16,777,215 characters. It requires three bytes of storage for the length of the string, plus the actual length of the string.
  6. LONGTEXT: This datatype stores variable-length character strings with a maximum length of 4,294,967,295 characters. It requires four bytes of storage for the length of the string, plus the actual length of the string.
  7. BINARY: This datatype stores fixed-length binary data, such as images or audio files. The length of the data is specified when the table is created, and it cannot be changed later. It requires storage space equal to the specified length.
  8. VARBINARY: This datatype stores variable-length binary data. The length of the data can be specified when the table is created, up to a maximum of 65,535 bytes. If the data is shorter than the specified length, it only requires storage space equal to the actual length of the data.
  9. BLOB: This datatype stores variable-length binary data with a maximum length of 65,535 bytes. It requires two bytes of storage for the length of the data, plus the actual length of the data.
  10. LONGBLOB: This datatype stores variable-length binary data with a maximum length of 4,294,967,295 bytes. It requires four bytes of storage for the length of the data, plus the actual length of the data.

Other Datatypes

  1. ENUM: ENUM is a string data type that allows you to specify a list of permissible values for a column. When you create a column with ENUM data type, you specify the list of allowed values in the column definition. The values must be enclosed in single quotes and separated by commas. For example, you could use ENUM to create a column called gender that can only have values 'male' or 'female',
  2. BOOLEAN: MySQL does not have a built-in BOOLEAN data type. Instead, it uses the TINYINT data type to represent boolean values, where 0 represents false and 1 represents true. Alternatively, you can use the ENUM data type with two values, such as 'Y' and 'N', to represent boolean values.
  3. BINARY: BINARY is a fixed-length binary data type that stores binary data such as images or audio files. The length of the data is specified when the table is created, and it cannot be changed later.