Back to: MySQL Tutorials for Beginners and Professionals
Data Types in MySQL with Examples
In this article, I am going to discuss Data Types in MySQL with examples. Please read our previous article where we discussed How to Export and Import MySQL databases using MySQL Workbench. As a developer, it is very important to understand MySQL Data Types and at the end of this article, you will understand everything about the MySQL Data Types with Examples.
What is a data type in MySQL?
Understanding the data types in MySQL is the most important factor in database development. By understanding data types, we can create more professional, normalized, relational, and easy to manage database columns and table structures. Every column in a table is defined with its data type, depending upon the data value it’s going to store. In other words, a data type defines what kind of values a column can store. There are mainly 3 data types:
- Numeric data types
- Character data types
- Temporal data types.
Numeric Data Types:
Numeric data types store only numeric values. Depending upon the values, the numeric data types can be Integer Numeric, Fixed-Point Numbers, and Floating-point Numbers. The Example of numeric data values is price, rating, salaries, etc.
Character Data Types:
Character data types can be store in alphabets, symbols, and also numbers. The Example of character data values are names, description, alphanumeric string, etc.
Temporal Data Types
Temporal data types store date, time, and date-time together. Data type’s name and size can be different in a different database management system.
Integer Numbers (Exact Value) Data Types in MySQL
Integers Numbers are the whole numbers like 1, 2, 3, 4, 5, 6, and so on. Integers can store positive, negative, and zero number values. In SQL standard, integer types are INTEGER OR INT and SMALLINT. In MySQL as an extension to the standard integer types, it supports the integer types like TINYINT, MEDIUMINT, and BIGINT. The following table displays the Integer number data types, their storage size, and the minimum and maximum value it can store.
- TINYINT: TINYINT can store from negative one twenty-eight (-128) to positive 127 for signed numbers. While for unsigned numbers that are the numbers without the negative sign, it can store from 0 to 255 numbers. The TINYINT requires 1-byte storage space.
- SMALLINT: For SMALLINT type, it can store from, negative thirty-two thousand seven hundred and sixty-eight (-32768) to positive thirty-two thousand seven hundred and sixty-seven (32767). For unsigned numbers, SMALLINT can store from 0 to sixty-five thousand five hundred and thirty-five (65535) numbers. The SMALLINT requires 2 bytes of storage space.
- MEDIUMINT: The MEDIUMINT data type can store from, negative eight million, three hundred and eighty-eight thousand, six hundred and eight (-8388608) to positive eight million, three hundred and eighty-eight thousand, six hundred and seven (8388607). For unsigned numbers, MEDIUMINT can store from 0 to sixteen million, seven hundred and seventy-seven thousand, two hundred and fifteen (16777215) numbers. The MEDIUMINT requires 3 bytes of storage space.
- INT: The INT data type can store from, negative over two billion (-2147483648) to positive over two billion (2147483648) numbers. For unsigned numbers, INT can store from 0 to over four billion (4294967295) numbers. The INT requires 4 bytes of storage space.
- BIGINT: In the case of BIGINT data type, it can store from, the given negative value to the given positive value. For unsigned numbers, BIGINT can store from 0 to the given number. The BIGINT requires 8 bytes of storage space.
Fixed-Point Numbers (Exact Value) Data Types in MySQL
Fixed point numbers or exact values are the numbers like 0.01, 10.25, 123.456, and so on. The fixed-point numbers are the values like currency, small calculations, and measurement data with decimal points. The fixed-point numbers can be positive, negative, and zero values with decimals points. Every decimal number has two parts. First is the number of digits to the right of the decimal point. And second is the total number of digits in the value. The decimal point doesn’t count in the total number of digits in the value. For example, the value 10.25 has a total of 4 digits, of which two are to the right of the decimal point.
The DECIMAL and NUMERIC are the data types that store Fixed-point numbers or exact values. The DECIMAL and NUMERIC data types are almost identical with having the same format for storing the data value. NUMERIC is the synonym for DECIMAL. “DECIMAL (5, 2)” OR “NUMERIC (5, 2)” In this format, 5 is the precision that is the total number of digits in the value. And 2 is the scale which is an optional parameter representing the total number of digits to the right of the decimal point. If the scale is not defined the default value for scale is 0.
Here is an example for storing decimal value using a DECIMAL or NUMERIC data type. To store the decimal values like 99.99 in the table column, we will define the data type like, “DECIMAL (4, 2)” Here 4 is precision, and 2 is scale. It means that we can store a total of 4 digits of which we can have 2 digits to the right of the decimal point. The important point to remember here is that according to the data type definition, we cannot store a value like 101.25 because there will be a total of 5 digits in the value, which exceeds the precision 4 defined by us. Similarly, we cannot store a value like 0.456. Though the value contains only 4 digits the digits to the right of the decimal points exceed the scale 2 defined in the data type. So, the value 0.456 will be stored as 0.46 in the data column.
Floating-Point Numbers (Approximate Value) Data Types in MySQL
The Floating-Point Numbers or Approximate Values are either very very big or very very small values, most commonly used in scientific or measurement calculation. The computer hardware architecture causes rounding errors that’s why floating-point numbers are also known as approximate values.
Consider the example with the given value which is a very large number and cannot be stored as INT or BIGINT data type. Example: 9,900,000,000,000,000,000,000,000
To store the given value as a DECIMAL data type value, we will have to define the data type as DECIMAL (25, 0) where 25 is the precision that is the total number of digits. But defining the data type like that will require more storage space. From the given 25 digits value only 2 digits (that is 99) are significant. By using the scientific notation, we can write the same value as (9.9 x 10^24) where 9.9 is called the mantissa and 24 is called the exponent.
Scientific notation is useful to separate the accuracy of the number from its largest or smallness. In floating-point numbers, the precision is applied to mantissa only. Thus, 9.91 x 10(24) is more accurate than 9.9 x 10(24).
The FLOAT, REAL, and DOUBLE PRECISION data types are used to store floating-point number values. The Floating-Point Numbers can be defined using the data types as, FLOAT (M, D) or REAL (M, D), or DOUBLE PRECISION (M, D). M means the total number digits, of which D digits may be after the decimal point. FLOAT (7, 4) can store the value up to 999.9999. if we insert the value, 123.000089 it will be stored as 123.0001 For maximum portability, the Floating-Point Number data types should be defined as FLOAT OR DOUBLE PRECISION with no definition of precision.
CHAR and VARCHAR (String) Data Types in MySQL
The CHAR OR CHARACTER data type stores fixed-width character columns. While defining CHAR data type, it is required to enter column width. Example: CHAR (5), CHAR (50)
If inserted character length is less than the defined column width, the value is positioned to the left and padded with spaces on the right until the character length is equal to the defined column width. For example: “firstname CHAR (20)”,
The given column name will store exactly 20 characters. If we inserted the name ‘John’ the string john will be appended with 16 spaces and stored in the column. In the CHAR data type, the data storage space bytes are exactly similar to the character length defined in the data type. Example: In CHAR (20), the data value ‘John’ will take 20 bytes of storage spaces.
The VARCHAR OR VARYING CHARACTER stores dynamic width character columns. In VARCHAR definition the defined width is the maximum width of the value allowed in the data column. For example, in the columns defined as VARCHAR (30) and VARCHAR (250) the maximum width of the data value allowed in the data column will be 30 and 250 respectively. The inserted data character length will be exactly similar to the data character length itself.
For example, in VARCHAR (250) data column, unlike the CHAR data type which appends extra spaces to match the data column width, the data column will store only 4 characters for the value ‘John’. In VARCHAR data type, the data storage space bytes are the data string length plus one byte. For example, if we are inserting the data value ‘John’ it will take data string length 4 plus 1 byte equal to a total of 5 bytes store space.
NCHAR and NVARCHAR (String) Data Types in MySQL
In SQL standard NCHAR and NVARCHAR mean NATIONAL CHARACTER and NATIONAL VARIABLE CHARACTER. Similar to CHAR and VARCHAR, The NCHAR and VARCHAR data types store fixed-width character columns but it uses larger character set. The NCHAR and VARCHAR use Unicode character sets like UTF-8 for internationalization. The Unicode character sets are required to store foreign languages. In CHAR and VARCHAR 1 byte storage space is required to store each character, while in NCHAR and NVARCHAR it requires 2 bytes of storage space for each character.
CLOB and BLOB (String) Data Types in MySQL
The CLOB and BLOB mean CHARACTER LARGE OBJECT and BINARY LARGE OBJECT respectively. The CLOB and BLOB data type is used to store very large data that cannot be stored in CHAR or VARCHAR. The CLOB and BLOB are used to store the source text of the post in plain text, HTML, or XML format. The CLOB is used to store character data while the BLOB is used to store binary data like images, audio, and video. In MySQL TEXT data type is similar to CLOB.
ENUM
The ENUM data type is a string object whose value is chosen at the time of column creation from a list of permitted values defined. It is a compact data storage that uses numeric indexes to represent string values.
TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT
The TEXT data type is used for storing long-term text strings which can store from 1 byte to 4 GB. TEXT data is not stored in the database server’s memory, MySQL reads it from the disk which makes it slower as compared to CHAR and VARCHAR. It holds up to 64 KB (i.e. 65535) characters.
The TINYTEXT data type is used for a column that requires less than 255 characters. It is used where the data has inconsistent length and does not require sorting. It can store 256 characters which require 1 byte overhead.
The MEDIUMTEXT data type is used for storing quite large data. It can hold up to 10MB of text data (i.e. 16,777.215) characters which require 3 bytes overhead.
The LONGTEXT data type is used to store a lot of large data. It can store up to 4GB of text data which requires 4 bytes overhead.
BOOLEAN Data Types
The BOOLEAN data types are built-in in MySQL whereas MySQL uses TINYINT(1) to represent Boolean values. BOOLEAN or BOOL is the synonyms of TINYINT(1).
DATE, TIME and TIMESTAMP (Temporal Values) Data Types in MySQL
The Temporal data types consist of date, time, and timestamp (have both date and time values). The date value stores the standard 365-day Gregorian calendar. Each database system has its own rules for storing and displaying data values. The date formats differ from one database system to another. The most popular date format is YYYY-MM-DD where 4Y means year, 2M means month, and 2D for the day. MySQL retrieves and displays TIME values in ‘HH:MM:SS’ format. The time value formats may differ from one database system to another. The TIMESTAMP data type can store data as well as time components. We can automatically initialize and update the current date and time by adding “DEFAULT and “ON UPDATE” while defining columns as follows:
DEFAULT CURRENT_TIMESTAMP, and ON UPDATE CURRENT_TIMESTAMP
In the next article, I am going to discuss the SELECT Statement in MySQL with Examples. Here, in this article, I try to explain Data Types in MySQL with Examples and I hope you enjoy these Data Types in MySQL with Examples article.
