Datatypes in MySQL - II | INT, DECIMAL, BOOLEAN, CHAR, VARCHAR and TEXT

INTEGER Data type in MySQL

INT data type is used for denoting integer values in MySQL. It can be either 0 positive integer or negative integer. There are various extensions of it for different ranges of integer values. For example - INT, MEDIUM INT, TINY INT, etc.

The below table shows the ranges of each data type used for integer type in MySQL.

Note-

• Integer types are often used as Primary Keys of the table.
• For 0 or NULL value in the AUTO_INCREMENT column, the sequence starts from 1.

INT with display width

The display width can be specified by specifying the width wrapped with parenthesis followed by INT. For example - INT(4).

INT with ZEROFILL attribute

The ZEROFILL attribute in MySQL replaces spaces with zero. 

For example:

Let's create a table sample_table.

CREATE TABLE sample_table(
c1 INT(2) ZEROFILL,
c2 INT(3) ZEROFILL,
c3 INT(4) ZEROFILL
);

It can be seen that ZEROFILL attribute is used in the above query with INT(display_width).

Now, Insert some value in each column.

INSERT INTO sample_table(c1,c2,c3)
VALUES (1,2,3); 

Now, the table will look like this:

It can be seen in the table, c3 has a specified width of 4 and its value is 3. So, the three vacant spaces before it are replaced with 0, and similarly for c1 and c2.

DECIMAL data type in MySQL

The DECIMAL data type is used in MySQL to store exact numeric values. It helps in storing the data with exact precision. For example - In a banking system, is used to store the exact value of money in an account.

The syntax for this is:

column_name DECIMAL(P,D);

In the above syntax:

• P represents the number of significant digits.
• D represents the number of digits after the decimal point.
• The range of P is from 1 to 65. D is always ≤ P.
• The P and D combined mean the maximum number of digits will be P and D will be the number of digits after the decimal point.

For example:

column_name DECIMAL (3,1)

In the above example, the maximum number of digits can be 3 and the number of digits after the decimal point will be 1. So, the range will be from -99.9 to 99.9.

Note - The ZEROFILL can be used with DECIMAL also. It will replace all spaces with 0. Whenever ZEROFILL is used, the unsigned attribute is automatically added to the column.

BOOLEAN data type in MySQL

MySQL uses TINYINT(1) instead of boolean as it doesn't have a built-in boolean type. On the other hand, MySQL provides BOOLEAN or BOOL as the synonyms of TINYINT(1). So, they can be used in queries but the datatype will be TINYINT(1). 

In MySQL 0 is considered false and any non-zero value is considered true. TRUE and FALSE can be used but it eventually evaluates to 1 and 0 respectively.

For example:

Let's create a table named students.

CREATE TABLE students (
roll_no INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
remarks BOOLEAN
);

After the execution of the above statements, a table named demo_table id will be created.

Here, In column remarks, if the value is true it represents the student is pass else fail.

Let's insert some rows in it.

INSERT INTO students(name,remarks)
VALUES('Ashish',true),
      ('Amit',false); 

Now, after execution of the above statements, the table will look like this:

It can be seen that remarks show 1 in place of true and 0 in place of false because BOOLEAN is the synonym of TINYINT(1) and true and false are converted into 1 and 0.

If we query the data type of remarks column it will show TINYINT(1).

Note- Any non-zero value can be inserted into the remarks column, and it will be considered true.

CHAR data type in MySQL

The CHAR data type is used in MySQL for storing fixed-length character types. It is often declared by specifying the maximum length that needs to be stored. For example - CHAR(5). The length can vary from 0 to 255. 

MySQL removes any trailing spaces for the CHAR value. For example - if the stored value is ‘Y   ’, it will be printed as ‘Y’. Trailing spaces are also not considered while comparing or pattern matching.

Let's take an example and create a table named demo_table.

CREATE TABLE demo_table (

name CHAR(7)
);

The table is created after the execution of the above statements.

Let's insert some values in it.

INSERT INTO demo_table(name)
VALUES('Ashish'),
      ('Amit '); 

Now, the table will look like this.

We can get the length of each CHAR value using LENGTH().

For example:

SELECT name,LENGTH(name)
FROM demo_table;

The output of the above query will be:

Note - The leading spaces are not removed only the trailing spaces are removed for a CHAR value in MySQL.

So, the value ‘Y’ and ‘Y  ’ are the same according to MySQL as the trailing spaces will be removed for the later one.

VARCHAR data type in MySQL

The VARCHAR in MySQL stores the variable-length string and the length can be up to 65535. It stores a VARCHAR value as a 1- byte or 2-byte length prefix plus actual data. The length prefix specifies the number of bytes in the VARCHAR value. For less than 255 bytes length prefix is 1 else for greater it is 2.

Unlike the CHAR value, MySQL does not remove trailing spaces for VARCHAR values.

For example:

CREATE TABLE demo_table (
name VARCHAR(7)
);
INSERT INTO demo_table(name)
VALUES ('Ankit ');

Now, the table will look like this:

Now, on querying the LENGTH(name) the output will be 6 as the trailing spaces are not removed for the VARCHAR value.

TEXT data type in MySQL

MySQL provides a TEXT data type too for storing strings which has more features than CHAR and VARCHAR data types. We don't have to specify a length for TEXT type. It is useful for storing long strings. It can take from 1 byte to 4 GB. It is mainly used for storing values like the description or body part of an article or blog.

The TEXT data is stored in the disk instead of a database server. That's why it is slower while querying the data as MySQL has to fetch data from the disk.

MySQL provides four TEXT types:

1. TINY TEXT  - It can store up to 255 Bytes ( 255 characters).
2. TEXT - It can store up to 64KB (65535 characters).
3. MEDIUM TEXT - It can store up to 16MB (16777215 characters).
4. LONG TEXT - It can store up to 4GB (4294967295 characters).