MySQL Data Types

08/03/2021

Contents

In this article, you will learn about MySQL data types.

Understanding the different data types available in MySQL

MySQL supports a variety of data types to represent different kinds of data. Understanding the different data types available in MySQL is essential for creating tables and managing data. Here are the most common MySQL data types with examples:

Numeric data types

MySQL supports several numeric data types, including:

  • INT: an integer value between -2147483648 and 2147483647.
  • TINYINT: a small integer value between -128 and 127.
  • SMALLINT: an integer value between -32768 and 32767.
  • BIGINT: a large integer value between -9223372036854775808 and 9223372036854775807.
  • FLOAT: a floating-point value with a precision of 7 digits.
  • DOUBLE: a floating-point value with a precision of 15-16 digits.

Here’s an example of how to create a table with numeric data types:

CREATE TABLE my_table (
  id INT(11) NOT NULL AUTO_INCREMENT,
  price FLOAT(6, 2) NOT NULL,
  quantity INT(11) NOT NULL,
  PRIMARY KEY (id)
);

This command creates a table called “my_table” with three columns: “id”, “price”, and “quantity”. The “id” column is an integer that will auto-increment for each new record. The “price” column is a floating-point value with a precision of 6 digits and 2 decimal places. The “quantity” column is an integer.

String data types

MySQL supports several string data types, including:

  • VARCHAR: a variable-length string with a maximum length of 65,535 characters.
  • CHAR: a fixed-length string with a maximum length of 255 characters.
  • TEXT: a large string with a maximum length of 65,535 characters.
  • BLOB: a binary large object with a maximum length of 65,535 bytes.

Here’s an example of how to create a table with string data types:

CREATE TABLE my_table (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  description TEXT,
  image BLOB,
  PRIMARY KEY (id)
);

This command creates a table called “my_table” with four columns: “id”, “name”, “description”, and “image”. The “id” column is an integer that will auto-increment for each new record. The “name” column is a variable-length string with a maximum length of 50 characters. The “description” column is a large string, and the “image” column is a binary large object.

Date and time data types

MySQL supports several date and time data types, including:

  • DATE: a date value in the format “YYYY-MM-DD”.
  • TIME: a time value in the format “HH:MM:SS”.
  • DATETIME: a combination of date and time values in the format “YYYY-MM-DD HH:MM:SS”.
  • TIMESTAMP: a timestamp value in the format “YYYY-MM-DD HH:MM:SS”.
  • YEAR: a year value in the format “YYYY”.

Here’s an example of how to create a table with date and time data types:

CREATE TABLE my_table (
  id INT(11) NOT NULL AUTO_INCREMENT,
  date DATE,
  time TIME,
  datetime DATETIME,
  timestamp TIMESTAMP,
  year YEAR,
  PRIMARY KEY (id)
);

This command creates a table called “my_table” with five columns: “id”, “date”, “time”, “datetime”, and “timestamp”, and “year”. The “id” column is an integer that will auto-increment for each new record. The “date” column is a date value, the “time” column is a time value, the “datetime” column is a combination of date and time values, the “timestamp” column is a timestamp value, and the “year” column is a year value.

Boolean data type

MySQL supports a boolean data type called “BOOLEAN” or “BOOL”. The BOOLEAN data type can be used to represent true/false values.

Here’s an example of how to create a table with a boolean data type:

CREATE TABLE my_table (
  id INT(11) NOT NULL AUTO_INCREMENT,
  is_active BOOLEAN,
  PRIMARY KEY (id)
);

This command creates a table called “my_table” with two columns: “id” and “is_active”. The “id” column is an integer that will auto-increment for each new record. The “is_active” column is a boolean value.

#MySQL