What are the Data Type in MySQL? And With Code Example

Data Type in MySQL

MySQL efficiently stores diverse data types in tables. Database designers must choose the right data type for each column to optimize storage, performance, and data integrity. MySQL uses integer, string, and date/time data types.

Numeric Data Types

Numbers are stored in numeric data types. Depending on the kind and range of numbers you need to store, MySQL provides a number of options.

• Integer Types: Whole numbers without a decimal point are stored in integer types. A range of integer sizes are available in MySQL:
  • TINYINT: An extremely tiny number. It can be unsigned (range 0 to 255) or signed (range -128 to 127). can define a width of up to four numbers. TinyINT is synonymous with BOOLEAN and BOOL (1).
  • SMALLINT: A tiny integer is called a SMALLINT. Up to five numbers. Signature -32768–32767, unsigned 0–65535. Available signed or unsigned.
  • MEDIUMINT: An integer of medium size. nine digits maximum. Range -8388608-8388607, 0 to 16777215. Both signed and unmarked versions are possible.
  • INT (or INTEGER): A conventional integer. Ranges from -2147483648 to 2147483647, or 0 to 4294967295. A width of 11 digits is possible. Positive and negative integers can be signed or unsigned. By eliminating negative numbers, the UNSIGNED specification raises the maximum positive range. If negative IDs are unnecessary, AUTO_INCREMENT columns should be UNSIGNED. MySQL 8.0.17 deprecates UNSIGNED for FLOAT, DOUBLE, and DECIMAL.
  • BIGINT: A big number. Unsigned (0 to 18446744073709551615) or signed (-9223372036854775808 to 9223372036854775807). can choose a width of up to 20 or 11 digits.
• Fixed-Point Types (DECIMAL/NUMERIC): These types are used to hold precise numerical values. They are advised for maintaining precise accuracy, like for monetary amounts.
  • DECIMAL(M,D) (or NUMERIC(M,D)): Store real numbers with constant decimal points. The number of significant digits (precision) is M, while the number following the decimal point is D. The DECIMAL(5,2) can hold -999.99 to 999.99. Although D may default to 0, it is usually necessary to define M and D. Up to 65 digits can be stored by this kind. DECIMAL and NUMERIC are synonymous terms.
• Floating-Point Types (FLOAT, DOUBLE): Approximate numerical data types are represented by floating-point types (FLOAT, DOUBLE).
  • FLOAT(M,D): A tiny number with a floating decimal point is denoted by FLOAT(M,D). The precision is approximately seven decimal places. Display length is denoted by M, and decimals by D.
  • DOUBLE(M,D): A big number with a floating decimal point is called a DOUBLE(M,D). About 16 decimal places make up precision. Display length is denoted by M, and decimals by D. DOUBLE is synonymous with REAL and DOUBLE PRECISION. It might be difficult to compare floating-point values for exact equality because of their approximate nature.

String Data Types

Text or character sequences can be stored in string data types.

• CHAR(Size): A fixed-length string is stored in CHAR(Size). The maximum character count is 255, and the size is indicated in parenthesis. To attain the desired length, the saved string is right-padded with spaces if it is less than the designated size. Regardless of the actual length of the text, the column takes up a certain amount of space. It uses precisely 4*n bytes when CHAR(n) is used with a CHARACTER SET such as utf8mb4. If no size is supplied, the default is 1. CHAR should be avoided unless a set length is absolutely required.
• VARCHAR(Size): A variable-length string can be stored in VARCHAR(Size). Parentheses indicate the maximum size. Previously, the maximum length was 255 characters, however from MySQL 5.0.3, it can be up to 65532 characters. Creating a VARCHAR field requires you to specify a length. Due to VARCHAR’s dynamic memory allocation, each value only requires the amount of space required plus one or more bytes. Values that are longer than the designated size n will be truncated. In certain operations, VARCHAR can be 50% slower than CHAR. It is said that using VARCHAR(255) is a typical but possibly wasteful default.
• TEXT Types (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT): Larger text blocks where a precise length isn’t necessarily known are handled by the TEXT Types (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT).
  • TINYTEXT: A maximum of 255 characters.
  • TEXT: The maximum character count for text is 65,535 characters.
  • MEDIUMTEXT: A maximum of 16,777,215 characters.
  • LONGTEXT: The maximum character count for LONGTEXT is 4,294,967,295 characters. When using TEXT types, you don’t give a length. By default, comparisons are not case-sensitive, and TEXT values are non-binary strings. Because of the way temporary tables are managed, using TEXT fields in sophisticated queries may cause them to lag.
• BLOB Types (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB): Binary Large Objects are used to store binary data, such as pictures. Comparable to text kinds in terms of size groupings. For comparisons and sorting, BLOBs are case sensitive.

Date and Time Data Types

Temporal information is stored using these data types.

• DATE: Holds a date value. The format is YYYY-MM-DD. ‘1000-01-01’ to ‘9999-12-31’ is the supported range.
• TIME: Holds a time value. The format is HH:MM:SS. ‘-838:59:59’ to ‘838:59:59’ is the supported range.
• DATETIME: Holds a string of dates and times. YYYY-MM-DD HH:MM:SS is the format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. Version 5.6.4 allows fractional seconds.
• TIMESTAMP: Holds a timestamp value. The number of seconds since the Unix epoch (‘1970-01-01 00:00:00’ UTC) is utilized to store TIMESTAMP values. YYYY-MM-DD HH:MM:SS is the format. The range that is supported is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. TIMESTAMP values are converted by MySQL from the current time zone to UTC for storage and back to the current time zone for retrieval. Understanding that this type will expire in 2038 is crucial. Version 5.6.4 allows fractional seconds.
• YEAR: Holds a value for a year. It can be kept in a four-digit or two-digit format. The four-digit format normally falls between 1901 and 2155. The years 1970–2069 are represented by values 70–69 in the two-digit format. By default, 4 is the length.

Code Example (CREATE TABLE using various Data Types):

The CREATE TABLE statement is used to define the structure of a new table in a MySQL database. This involves specifying column names, their data types, and any constraints.

-- Example showing various data types in a table creation
CREATE TABLE ProductDetails (
    ProductID INTEGER UNSIGNED PRIMARY KEY, -- Unsigned INT for non-negative unique ID
    ProductName VARCHAR(100) NOT NULL,    -- Variable-length string, cannot be NULL
    ProductCode CHAR(10),                 -- Fixed-length string
    Price DECIMAL(10, 2) NOT NULL,        -- Exact numeric for currency
    StockQuantity SMALLINT DEFAULT 0,     -- Small integer with a default value
    ManufacturingDate DATE,               -- Stores date only
    LastUpdateTime DATETIME,              -- Stores date and time
    YearOfRelease YEAR,                   -- Stores a year
    IsAvailable TINYINT(1),               -- Often used for boolean (synonym for BOOLEAN)
    Description TEXT                      -- For longer text descriptions
    -- Other constraints like UNIQUE, CHECK, FOREIGN KEY would be added here
);

Along with some common constraints like PRIMARY KEY and NOT NULL, which are frequently included in CREATE TABLE statements this example shows the syntax for defining columns with various numeric (INTEGER UNSIGNED, DECIMAL, SMALLINT, TINYINT), string (VARCHAR, CHAR, TEXT), and date/time (DATE, DATETIME, YEAR) data types as discussed.

Writing effective MySQL queries and creating effective database designs require an understanding of these basic data types.