You are currently viewing MySQL How To Check Column Data Types in MySQL Database? MySQL Data Types And Columns Data Type – The Ultimate Guide Through Easy 5 ways!
Could You Please Share This Post? I Appreciate It And Thank YOU! :) Have A Nice Day!
5
(2)

There are at least a few ways to get column data types in MySQL database. I will show you 5 the most popular ways in MySQL How To Check Column Data Types and check MySQL data types (columns data type).

MySQL How To Check Column Data Types?

The first way to check data types for MySQL database is query with EXPLAIN, DESCRIBE and SHOW statement. Let’s assume we have the following table:

Prepare TEST Table

For tests I created the TEST_DATA_TYPES table which contains the most used SQL data types. In the next sections we will try to get the information about these types from database using SQL queries.

CREATE TABLE TEST_DATA_TYPES(
    COL_1 INTEGER,
    COL_2 INT,
    COL_3 BIGINT,
    COL_4 DECIMAL(6, 2),
    COL_5 NUMERIC,
    COL_6 DOUBLE,
    COL_7 FLOAT,
    COL_8 TIMESTAMP,
    COL_9 DATETIME,
    COL_10 DATE,
    COL_11 VARCHAR(20),
    COL_12 CHAR(10)
);

Show Data Types – 4 SQL Statements

Now we will try to get information about data types for this table. I will show you the 4 SQL statement which returns the same output as below:

EXPLAIN bigdataetl.test_data_types;

DESCRIBE bigdataetl.test_data_types;

SHOW FIELDS FROM bigdataetl.test_data_types;

SHOW COLUMNS FROM bigdataetl.test_data_types;
MySQL How To Check Column Data Types in MySQL Database? MySQL Data Types And Columns Data Type - The Ultimate Guide Through Easy 5 ways!
MySQL How To Check Column Data Types in MySQL Database?

More Complex SQL Statement – Second Approach

SELECT
COLUMN_NAME, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, 
 NUMERIC_SCALE, EXTRA
FROM
  INFORMATION_SCHEMA.COLUMNS 
WHERE
  TABLE_SCHEMA = 'bigdataetl'
AND
  TABLE_NAME = 'test_data_types';
MySQL How To Check Column Data Types in MySQL Database? MySQL Data Types And Columns Data Type - The Ultimate Guide Through Easy 5 ways!
How to get the mysql table columns data type?

How To Setup MySQL In Docker – MySQL Download?

If you want to use MySQL please use the docker to refer you to this post: How to run MySQL database using Docker-Compose in 3 minutes? There you will find a ready-to-use script that you just need to copy and run.

MySQL Download: You can manually download the MySQL Database using official site. There you will find the full description how to download MySQL and install it on you machine.

MySQL Download
MySQL Download

More Information About Data Types

If you would like to lear more about MySQL data types I strongly encourage you to read the Official page.

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!

BigData-ETL: image 7YOU MIGHT ALSO LIKE

What is the data type of the column?

MySQL Database (as others databases) provides multiple data types. Please find more derails: https://bigdata-etl.com/mysql-how-to-check-column-data-types-in-mysql/

How do I show column data in MySQL?

You can use multiple methods. The easiest one is to use SQL Statement. Please find more derails: https://bigdata-etl.com/mysql-how-to-check-column-data-types-in-mysql/

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 2

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?