MySQL How To Check Column Data Types in MySQL Database? – The Ultimate Guide Through Easy 5 ways!

You are currently viewing MySQL How To Check Column Data Types in MySQL Database? – The Ultimate Guide Through Easy 5 ways!
Share This Post, Help Others, And Earn My Heartfelt Appreciation! :)
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.

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? - 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? - 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.

If you enjoyed this post please add the comment below and share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!

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.

Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments