MySQL Data Types

Introduction

In MySQL, data types define the type of data that can be stored in a column of a table. Choosing the appropriate data type for each column is crucial for efficient storage and retrieval of data. In this article, we will explore the commonly used data types in MySQL and provide examples to illustrate their usage.

Numeric Data Types

MySQL provides various numeric data types to store numbers with different ranges and precision:

• INT: Used for whole numbers (-2147483648 to 2147483647).
• DECIMAL: Used for precise decimal numbers with specified precision and scale (e.g., DECIMAL(10,2) can store numbers like 12345.67).
• FLOAT: Used for approximate floating-point numbers with single precision.
• DOUBLE: Used for approximate floating-point numbers with double precision.

Here's an example that demonstrates the usage of numeric data types:

CREATE TABLE products (
  id INT,
  price DECIMAL(10, 2),
  quantity INT
);

String Data Types

MySQL provides various string data types to store character-based data:

• VARCHAR: Used for variable-length character strings with a specified maximum length.
• CHAR: Used for fixed-length character strings with a specified length.
• TEXT: Used for large text data with variable length.

Here's an example that demonstrates the usage of string data types:

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  address TEXT
);

Date and Time Data Types

MySQL provides several data types to store date and time information:

• DATE: Used for dates in the format 'YYYY-MM-DD'.
• TIME: Used for time values in the format 'HH:MM:SS'.
• DATETIME: Used for date and time values in the format 'YYYY-MM-DD HH:MM:SS'.
• TIMESTAMP: Used for storing the current date and time.

Here's an example that demonstrates the usage of date and time data types:

CREATE TABLE orders (
  id INT,
  order_date DATE,
  order_time TIME,
  created_at DATETIME,
  updated_at TIMESTAMP
);

Conclusion

Understanding the different data types in MySQL is crucial for properly defining the structure of your tables and ensuring efficient storage and retrieval of data. In this article, we explored numeric data types, string data types, and date and time data types in MySQL, providing examples of their usage. By choosing the appropriate data type for each column, you can effectively manage and manipulate your data in MySQL.