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.
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;
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';
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.
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!