How to Get the Data Type of a Columns in MariaDB

When it comes to managing databases, understanding the types of data stored in each column is crucial. In MariaDB, this knowledge not only helps in organizing data efficiently but also enables more effective querying and analysis.

In this article, we’ll explore How to Get the Data Type of Columns in MariaDB with simple but powerful methods to retrieve column data types in MariaDB.

How to Get the Data Type of Columns?

Understanding the nature of our data is important. MariaDB offers various methods to find the data types of columns within our tables. This knowledge not only helps in database design but also fast data manipulation and ensures data integrity.

To address this issue, We will use below approaches or methods to Get the Data Type of the Columns in MariaDB are as follows:

1. Using DESCRIBE Statement
2. Using SHOW COLUMNS Statement
3. Using INFORMATION_SCHEMA.COLUMNS Table

Let’s set up an environment to Get the Data Type of Columns

To understand How to Get the Data Type of Columns in MariaDB we need a table on which we will perform various operations and queries. Here we will consider a table called customers which contains id, name, email, age, and registered_at as Columns.

 CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  age INT,
  registered_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Output:

Creations of customers table.

Explanation: The query creates a table named as customers with id, name, email, age and registered_at column with the respective data type and constraints.

1. Using DESCRIBE Statement

The DESCRIBE statement is used to get the details about the columns in a table or a view. It is also a default formatted result with field, type, null, key, default and extra columns in the result. The result is same as EXPLAIN statement and SHOW COLUMNS FROM table_name. You can also use DESC instead of DESCRIBE in the statement. You can also get results of a specific column using the column name after the table name.

Syntax:

DESCRIBE table_name [column name];

Example: Retrieving the data type using DESCRIBE statement

The below query uses the DESCRIBE statement to print the details of name column from the customers table.

DESC customers name;

Output:

Using DESCRIBE Statement

Explanation: The query displays the details of name column from the customers table. The details are the column name or field, data type and the respective constraints of the column.

2. Using SHOW COLUMNS Statement

The SHOW COLUMNS Statement is used to retrieve the details about the columns in a table or view. The result is same as EXPLAIN statement and DESCRIBE statement. You can also replace the COLUMNS word to FIELDS and FROM word to IN and get the same results. At the end of the statement you can use the WHERE statement to get specific results with field, type, key etc.

Syntax:

SHOW COLUMNS FROM table_name;

Example: Retrieving the data type using SHOW COLUMNS statement

The below query uses the SHOW COLUMNS Statement to print the details of the email column in the customers table.

SHOW COLUMNS 
FROM customers 
WHERE FIELD='email';

Output:

Using SHOW COLUMNS FROM Statement

Explanation: The query displays the details of email column from the customers table. The details are the column name or field, data type and the respective constraints of the column.

3. Using INFORMATION_SCHEMA.COLUMNS Table

The INFORMATION_SCHEMA.COLUMNS statement allows you to get information about all columns within all tables and view in the database. You can use the SELECT statement to get columns such as TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY etc and use the WHERE statement to get results of a specific database and table with additional conditions that you prefer.

Syntax:

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

Example: Retrieving the data type using INFORMATION_SCHEMA.COLUMNS Table

The below query uses INFORMATION_SCHEMA.COLUMNS to get the column_name, data_type column of table customers.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'customers';

Output:

Using INFORMATION_SCHEMA.COLUMNS Statement

Explanation: The query displays the details of all the columns of the customers table. The result includes the table name, all the columns of the table and respective data type of the columns.

Conclusion

Overall, After reading whole article now you have good understanding about How to Get the Data Type of a Columns in MariaDB . We have go through the various methods using the DESCRIBE statement, SHOW COLUMNS statement, and INFORMATION_SCHEMA.COLUMNS table. Now you can easily retrieve the data types of columns in your MariaDB tables. This knowledge not only enhances your database management skills but also enables you to work with data more efficiently and effectively.