Data Type	Description
INT	Integer value without decimal points.
VARCHAR(n)	A variable-length character string, where n is the maximum length of the string.
CHAR(n)	A fixed-length character string with a finite length of n.
TEXT	Large text data.
DATE	Represents time in YYYY/MM/DD format.
TIME	Represents time in HH:MM:SS format.
FLOAT	Number with a decimal point (single-precision floating point).
DOUBLE	A floating number with higher precision than a floating point number (double-precision floating point).

Methods to Check Data Types of Columns

1. Using INFORMATION_SCHEMA.COLUMNS

To check the data type of a column in MysQL we use the “INFORMATION_SCHEMA.COLUMNS” statement.

Syntax:

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = ‘database_name' AND TABLE_NAME = 'table_name'; 

where INFORMATION_SCHEMA.COLUMNS is the view that contains the meta data about the column.

• SELECT DATA_TYPE: It indicates that we want to select DATA_TYPE column from the INFORMATION_SCHEMA view.
• FROM INFORMATION_SCHEMA.COLUMNS: It specifies the source of the data, here we know that the view SELECT DATA_TYPE contains the metadata.
• WHERE TABLE_SCHEMA = ‘database_name’ AND TABLE_NAME = ‘table_name’; : This line filters which database and table we are going to apply this concept.

2. Using SHOW COLUMNS

The SHOW COLUMNS statement provides detailed information about columns, including datatypes.

Syntax:

SHOW COLUMNS FROM `table_name` FROM `database_name`;

Example 1:

Let us consider a database named company, which has a table named employees with columns employee_id, employee_name, joining_date as in given below image. We have to show the datatypes of each column of the table employee.

To show the datatypes of each column we can use the query:

SHOW COLUMNS FROM `employees` FROM `company`; 

Where, company is database name and employees is table name where we are applying the query.

Output:

Now it is clear that

• employee_id has the datatype INT
• employee_name has the datatype VARCHAR
• joining_date has the datatype DATE

Note: In the output we can see two additional columns NULL, KEY, DEFAULT and EXTRA. 

While using SHOW COLUMNS it will show additional characteristics of the columns like NULL, KEY, DEFAULT and EXTRA.

Example 2:

Consider the the table customers with columns customer_id, customer_name, email, registration_date, active_status

To show the datatypes of each column we can use the query:

SHOW COLUMNS FROM `customers` FROM `company`;

Where, company is database name and customers are table name where we are applying the query.

Output:

Now it is clear that

• customer_id has the datatype INT
• customer_id has the datatype VARCHAR
• email has the datatype VARCHAR
• registration_date has the datatype DATE
• active_status has the datatype ENUM(‘Active’,’Inactive’)

Note: In the output we can see two additional columns NULL, KEY, DEFAULT and EXTRA. 

While using SHOW COLUMNS it will show additional characteristics of the columns like NULL, KEY, DEFAULT and EXTRA .

Conclusion

Through understanding about the MySQL column datatypes, it helps to achieve effective database management. Like we seen above, the use of SHOW COLUMNS and the query INFORMATION_SCHAMA.COLUMNS helps to retrieve the datatypes of each column in a table of a database effectively.

It is helpful for designing database, to ensure data integrity, and optimizing query processing. The people who usually interact with database like database administers, developers are really benefitted so they can make decisions on schema design, data validation and query optimization through analyzing datatypes, nullability and other characters we have seen above.

FAQs on How to Get the Datatype of Table Columns in MySQL

Why is it important to know the data type of a column in a MySQL table?

Understanding the data types of table columns is crucial for maintaining data integrity, preventing errors, and optimizing database performance. It helps in designing databases, ensuring proper data validation, and optimizing queries.

What additional information does SHOW COLUMNS provide?

In addition to data types, SHOW COLUMNS provides information about whether a column can be NULL, any key constraints (e.g., primary key), default values, and extra attributes (e.g., auto-increment).

What is the INFORMATION_SCHEMA in MySQL?

The INFORMATION_SCHEMA is a system database in MySQL that provides access to metadata about databases, tables, columns, and other objects. It contains several read-only views that can be queried to retrieve information about the database objects.