MySQL Data Types

Introduction

In this chapter, we will learn about the various data types available in MySQL. Data types are essential in defining the nature of the data that can be stored in each column of a table. Choosing the appropriate data type for each column ensures data integrity and optimizes storage and performance. We will cover the major categories of data types in MySQL, including numeric, string, date and time, and other types, along with examples and important considerations.

Numeric Data Types

Integer Types

1. TINYINT: A very small integer.

   • Storage: 1 byte
   • Range: -128 to 127 (signed), 0 to 255 (unsigned)

   TINYINT column_name;
   

2. SMALLINT: A small integer.

   • Storage: 2 bytes
   • Range: -32,768 to 32,767 (signed), 0 to 65,535 (unsigned)

   SMALLINT column_name;
   

3. MEDIUMINT: A medium-sized integer.

   • Storage: 3 bytes
   • Range: -8,388,608 to 8,388,607 (signed), 0 to 16,777,215 (unsigned)

   MEDIUMINT column_name;
   

4. INT (INTEGER): A standard integer.

   • Storage: 4 bytes
   • Range: -2,147,483,648 to 2,147,483,647 (signed), 0 to 4,294,967,295 (unsigned)

   INT column_name;
   

5. BIGINT: A large integer.

   • Storage: 8 bytes
   • Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (signed), 0 to 18,446,744,073,709,551,615 (unsigned)

   BIGINT column_name;
   

Floating-Point Types

1. FLOAT: A small (single-precision) floating-point number.

   • Storage: 4 bytes

   FLOAT(column_name);
   

2. DOUBLE: A large (double-precision) floating-point number.

   • Storage: 8 bytes

   DOUBLE(column_name);
   

Fixed-Point Types

1. DECIMAL: A fixed-point number.

   • Storage: Varies (based on the precision and scale)

   DECIMAL(column_name, precision, scale);
   

String Data Types

Character String Types

1. CHAR: A fixed-length string.

   • Storage: 1 byte per character

   CHAR(column_name, length);
   

2. VARCHAR: A variable-length string.

   • Storage: 1 byte per character + 1 or 2 bytes for length

   VARCHAR(column_name, length);
   

Text Types

1. TINYTEXT: A very small text string.

   • Storage: Up to 255 bytes

   TINYTEXT(column_name);
   

2. TEXT: A small text string.

   • Storage: Up to 65,535 bytes

   TEXT(column_name);
   

3. MEDIUMTEXT: A medium-length text string.

   • Storage: Up to 16,777,215 bytes

   MEDIUMTEXT(column_name);
   

4. LONGTEXT: A large text string.

   • Storage: Up to 4,294,967,295 bytes

   LONGTEXT(column_name);
   

Binary Data Types

1. BINARY: A fixed-length binary string.

   • Storage: 1 byte per byte

   BINARY(column_name, length);
   

2. VARBINARY: A variable-length binary string.

   • Storage: 1 byte per byte + 1 or 2 bytes for length

   VARBINARY(column_name, length);
   

BLOB Types

1. TINYBLOB: A very small binary large object.

   • Storage: Up to 255 bytes

   TINYBLOB(column_name);
   

2. BLOB: A small binary large object.

   • Storage: Up to 65,535 bytes

   BLOB(column_name);
   

3. MEDIUMBLOB: A medium-sized binary large object.

   • Storage: Up to 16,777,215 bytes

   MEDIUMBLOB(column_name);
   

4. LONGBLOB: A large binary large object.

   • Storage: Up to 4,294,967,295 bytes

   LONGBLOB(column_name);
   

Date and Time Data Types

1. DATE: A date value.

   • Format: ‘YYYY-MM-DD’

   DATE(column_name);
   

2. DATETIME: A date and time value.

   • Format: ‘YYYY-MM-DD HH:MM:SS’

   DATETIME(column_name);
   

3. TIMESTAMP: A timestamp value.

   • Format: ‘YYYY-MM-DD HH:MM:SS’

   TIMESTAMP(column_name);
   

4. TIME: A time value.

   • Format: ‘HH:MM:SS’

   TIME(column_name);
   

5. YEAR: A year value.

   • Format: ‘YYYY’

   YEAR(column_name);
   

Other Data Types

1. ENUM: An enumeration, a string object with a value chosen from a list of permitted values.

   • Storage: 1 or 2 bytes, depending on the number of enumeration values

   ENUM('value1', 'value2', ..., 'valueN');
   

2. SET: A set, a string object that can have zero or more values, each chosen from a list of permitted values.

   • Storage: 1, 2, 3, 4, or 8 bytes, depending on the number of set members

   SET('value1', 'value2', ..., 'valueN');
   

Important Considerations

• Choose the Appropriate Data Type: Select data types based on the nature of the data to ensure data integrity and optimal performance.
• Storage Requirements: Consider the storage requirements of each data type, especially for large tables.
• Indexing: Some data types may impact the performance of indexes. For example, indexing large text or BLOB columns can be inefficient.
• Data Validation: Use constraints and data types to enforce data validation at the database level.

Conclusion

Understanding MySQL data types is crucial for designing efficient and effective databases. This chapter covered the major categories of data types, provided syntax and examples for each type, and discussed important considerations.