Sometimes we need to get the datatype of table columns in MySQL when working on a database. Knowing the data type of the column ensures the data integrity of the table.
If we insert a string instead of an integer value that will affect the working of the database and will show an error. So, in this article, we will discuss how to check the data type of a table column in MySQL.
Common Data Types Used in MySQL
Some common data types and their description is given in the table below.
| 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). |
Check Data Type of a Column
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.
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.
employees table
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:
output from table employee with datatypes of each column
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
customers table
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 is table name where we are applying the query.
Output:
output from table customers with datatypes of each column
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.