MySQL supports various data types that allow you to store different types of information in your database tables. Here are some of the commonly used MySQL data types:
Numeric Types:
INT: Integer type for storing whole numbers.
TINYINT, SMALLINT, MEDIUMINT, BIGINT: Various integer types with different storage sizes.
FLOAT, DOUBLE: Floating-point types for storing decimal numbers.
String Types:
CHAR: Fixed-length character string.
VARCHAR: Variable-length character string.
TEXT: Variable-length text string for larger amounts of text.
Date and Time Types:
DATE: Date value in the format 'YYYY-MM-DD'.
TIME: Time value in the format 'HH:MM:SS'.
DATETIME: Combination of date and time in the format 'YYYY-MM-DD HH:MM:SS'.
TIMESTAMP: Similar to DATETIME but with automatic updating to the current timestamp.
Binary Types:
BINARY: Fixed-length binary data.
VARBINARY: Variable-length binary data.
BLOB: Binary large object for storing large amounts of binary data.
Spatial Types:
GEOMETRY, POINT, LINESTRING, POLYGON: Spatial data types for storing geometric shapes and spatial data.
JSON Type:
JSON: Introduced in MySQL 5.7, this data type allows for storage and manipulation of JSON data.
Enumeration and Set Types:
ENUM: A string object with a value chosen from a predefined list of values.
SET: A string object that can have zero or more values chosen from a predefined list of values.
Numeric Types:
MySQL provides several numeric data types to represent different kinds of numerical values. Here are some of the commonly used numeric types in MySQL:
INT:
Description: Integer type for storing whole numbers.
Range: Signed INT can store values from -2,147,483,648 to 2,147,483,647, and unsigned INT can store values from 0 to 4,294,967,295.
TINYINT:
Description: Small integer type.
Range: Signed TINYINT can store values from -128 to 127, and unsigned TINYINT can store values from 0 to 255.
SMALLINT:
Description: Medium-sized integer type.
Range: Signed SMALLINT can store values from -32,768 to 32,767, and unsigned SMALLINT can store values from 0 to 65,535.
MEDIUMINT:
Description: Medium-sized integer type.
Range: Signed MEDIUMINT can store values from -8,388,608 to 8,388,607, and unsigned MEDIUMINT can store values from 0 to 16,777,215.
BIGINT:
Description: Large integer type.
Range: Signed BIGINT can store values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, and unsigned BIGINT can store values from 0 to 18,446,744,073,709,551,615.
FLOAT:
Description: Single-precision floating-point type.
Range: Approximately -3.4E+38 to 3.4E+38. The actual range depends on the system.
DOUBLE:
Description: Double-precision floating-point type.
Range: Approximately -1.8E+308 to 1.8E+308. The actual range depends on the system.
DECIMAL:
Description: Fixed-point decimal type.
Syntax: DECIMAL(M, D), where M is the total number of digits, and D is the number of digits after the decimal point.
String Types:
MySQL provides various string data types to store character-based information. Here are some of the commonly used string types:
CHAR:
Description: Fixed-length character string.
Syntax: CHAR(N), where N is the fixed length.
Example: CHAR(10) can store a string of exactly 10 characters.
VARCHAR:
Description: Variable-length character string.
Syntax: VARCHAR(N), where N is the maximum length.
Example: VARCHAR(255) can store a string with a maximum length of 255 characters.
TEXT:
Description: Variable-length text string for larger amounts of text.
Syntax: TEXT.
Example: TEXT can store large amounts of text, suitable for paragraphs or documents.
BINARY:
Description: Fixed-length binary data.
Syntax: BINARY(N), where N is the fixed length.
Example: BINARY(10) can store binary data with a fixed length of 10 bytes.
VARBINARY:
Description: Variable-length binary data.
Syntax: VARBINARY(N), where N is the maximum length.
Example: VARBINARY(255) can store variable-length binary data with a maximum length of 255 bytes.
BLOB:
Description: Binary large object for storing large amounts of binary data.
Syntax: BLOB.
Example: BLOB can store large binary data, such as images or audio files.
ENUM:
Description: A string object with a value chosen from a predefined list of values.
Syntax: ENUM('value1', 'value2', ...).
Example: ENUM('Red', 'Green', 'Blue') can store one of the specified color values.
SET:
Description: A string object that can have zero or more values chosen from a predefined list of values.
Syntax: SET('value1', 'value2', ...).
Example: SET('Read', 'Write', 'Execute') can store a combination of permissions.
Binary Types:
MySQL provides binary data types to store binary data, which can be used to store various types of binary information, such as images, audio files, or any raw binary data. Here are some commonly used binary types in MySQL:
BINARY:
Description: Fixed-length binary data.
Syntax: BINARY(N), where N is the fixed length.
Example: BINARY(10) can store binary data with a fixed length of 10 bytes.
VARBINARY:
Description: Variable-length binary data.
Syntax: VARBINARY(N), where N is the maximum length.
Example: VARBINARY(255) can store variable-length binary data with a maximum length of 255 bytes.
BLOB (Binary Large Object):
Description: Stores large amounts of binary data, such as images or audio files.
Syntax: BLOB.
Example: BLOB can store variable-length binary data for large objects.
These binary types allow you to store binary information efficiently. The choice between BINARY and VARBINARY depends on whether the length of the binary data is fixed or variable. BLOB is typically used for storing large amounts of binary data.
When dealing with binary data, it's crucial to consider the size of the data and choose the appropriate type accordingly. Additionally, be mindful of the application's requirements for reading and writing binary data and consider factors such as performance and storage.
Spatial Types:
Spatial types in MySQL are designed to handle geometric and geographic data, enabling the storage and retrieval of information related to spatial coordinates and shapes. Here are some commonly used spatial types in MySQL:
GEOMETRY:
Description: The base spatial data type that can represent any type of geometry. It can store points, lines, polygons, and other geometric shapes.
Example: POINT(1, 1) represents a point with coordinates (1, 1).
POINT:
Description: Represents a single point in a two-dimensional space.
Example: POINT(2.5, 3.7) represents a point with coordinates (2.5, 3.7).
LINESTRING:
Description: Represents a sequence of points that form a line.
Example: LINESTRING(0 0, 1 1, 2 1, 2 2) represents a line with three connected segments.
POLYGON:
Description: Represents a closed area defined by a sequence of points.
Example: POLYGON((0 0, 0 1, 1 1, 1 0, 0 0)) represents a square.
MULTIPOINT:
Description: Represents a collection of points.
Example: MULTIPOINT(0 0, 1 1, 2 2) represents three individual points.
MULTILINESTRING:
Description: Represents a collection of LineStrings.
Example: MULTILINESTRING((0 0, 1 1), (2 2, 3 3)) represents two separate line segments.
MULTIPOLYGON:
Description: Represents a collection of Polygons.
Example: MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)), ((2 2, 2 3, 3 3, 3 2, 2 2))) represents two separate polygons.
GEOMETRYCOLLECTION:
Description: Represents a collection of different geometry types.
Example: GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3)) represents a collection containing a point and a line.
JSON Type:
The JSON data type in MySQL allows for the storage and manipulation of JSON (JavaScript Object Notation) data. JSON is a lightweight data interchange format that is easy for humans to read and write, and easy for machines to parse and generate. Here are the key features of the JSON data type in MySQL:
JSON:
Description: A data type specifically designed to store JSON data.
Example: {"name": "John", "age": 30, "city": "New York"} is an example of JSON data.
JSON Functions and Operators:
MySQL provides a set of functions and operators to work with JSON data. Some common ones include:
JSON_EXTRACT(): Extracts a value from a JSON document based on a JSON path expression.
JSON_UNQUOTE(): Removes quotation marks from a JSON string.
-> and ->> operators: Used to navigate and extract values from JSON objects.
Indexing for JSON Columns:
MySQL supports indexing for JSON columns. This allows for efficient searches and retrieval of specific elements within JSON documents.
JSON Schema Validation:
MySQL supports validation of JSON documents against a specified JSON schema. This ensures that the stored JSON data adheres to a predefined structure.
JSON Aggregation Functions:
MySQL provides aggregation functions specifically designed for JSON data, such as JSON_ARRAYAGG() and JSON_OBJECTAGG(), which aggregate values into JSON arrays or objects.
Modifying JSON Data:
JSON data can be modified using functions like JSON_SET(), JSON_INSERT(), and JSON_REPLACE(), allowing you to update or add elements to existing JSON documents.
Enumeration and Set Types:
MySQL provides two special types, ENUM and SET, that allow you to define a predefined set of values for a column. These types are useful when you want to constrain the possible values a column can take. Here's an overview of ENUM and SET types:
ENUM:
Description: ENUM, short for enumeration, is a string object that can have only one value chosen from a predefined list of values.
Syntax: ENUM('value1', 'value2', ...).
Example: ENUM('Red', 'Green', 'Blue') can store one of the specified color values.
Usage: ENUM is suitable when you have a fixed set of values that a column can take, and you want to restrict it to only those values.
SET:
Description: SET is a string object that can have zero or more values chosen from a predefined list of values.
Syntax: SET('value1', 'value2', ...).
Example: SET('Read', 'Write', 'Execute') can store a combination of permissions.
Usage: SET is appropriate when a column can have multiple values simultaneously, and you want to represent a set of options.
Important considerations:
Both ENUM and SET types are stored internally as integers, representing the position of the value in the predefined list.
When choosing between ENUM and SET, consider the nature of your data and whether multiple values are applicable.
It's usually recommended to use ENUM or SET when the number of possible values is relatively small.
Example Usage:
SQL
CREATE TABLE user (
id INT PRIMARY KEY,
username VARCHAR(50),
status ENUM('Active', 'Inactive', 'Blocked') DEFAULT 'Active',
permissions SET('Read', 'Write', 'Execute') DEFAULT ''
);
In this example, the status column can only take one of the values 'Active', 'Inactive', or 'Blocked'. The permissions column, on the other hand, can have zero or more of the values 'Read', 'Write', and 'Execute'.