#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

MySQL Data Types: Complete Guide with Examples

Add ₹0.10 and ₹0.20 in a FLOAT column and MySQL will tell you the total is not equal to ₹0.30. That is not a bug. It is what the type was designed to do, and it is the clearest example of why data types deserve a few minutes of thought before you create a table. A type decides what values a column accepts, how much space they take, how they sort and compare, and which mistakes MySQL will refuse to store.

Every result below comes from MySQL 8.0 in its default strict mode (STRICT_TRANS_TABLES is part of the default sql_mode).

Numbers: whole numbers

TypeBytesSigned rangeUnsigned range
TINYINT1-128 to 1270 to 255
SMALLINT2-32,768 to 32,7670 to 65,535
MEDIUMINT3about ±8.3 million0 to 16,777,215
INT4about ±2.1 billion0 to 4,294,967,295
BIGINT8about ±9.2 × 10180 to about 1.8 × 1019
CREATE TABLE t_int (tiny TINYINT, utiny TINYINT UNSIGNED);
INSERT INTO t_int (tiny)  VALUES (128);
INSERT INTO t_int (utiny) VALUES (-1);
ERROR 1264 (22003): Out of range value for column 'tiny' at row 1
ERROR 1264 (22003): Out of range value for column 'utiny' at row 1

In strict mode an out-of-range value is rejected, not cut down to the limit. The number in INT(11) is only a display width and is deprecated in 8.0; it does not limit the value.

Numbers: money and decimals

CREATE TABLE money (f FLOAT, d DOUBLE, dec2 DECIMAL(10,2));
INSERT INTO money VALUES (0.1, 0.1, 0.1), (0.2, 0.2, 0.2);

SELECT SUM(f) AS float_sum, SUM(d) AS double_sum, SUM(dec2) AS decimal_sum FROM money;
SELECT SUM(f) = 0.3 AS float_ok, SUM(d) = 0.3 AS double_ok, SUM(dec2) = 0.3 AS decimal_ok FROM money;
float_sumdouble_sumdecimal_sum
0.300000004470348360.300000000000000040.30
float_okdouble_okdecimal_ok
001

FLOAT and DOUBLE store binary approximations. They are fine for sensor readings or scientific values, and wrong for prices, balances and GST amounts. DECIMAL(p, s) stores exact values. It holds p digits in total, s of them after the point, so DECIMAL(10,2) reaches 99,999,999.99.

Text

CREATE TABLE t_str (code CHAR(5), name VARCHAR(5));
INSERT INTO t_str VALUES ('ab  ', 'ab  ');
SELECT CONCAT('[',code,']') AS char_val, CONCAT('[',name,']') AS varchar_val,
       LENGTH(code), LENGTH(name)
FROM t_str;
char_valvarchar_valLENGTH(code)LENGTH(name)
[ab][ab ]24

CHAR(n) pads with spaces on disk and strips trailing spaces when read back. VARCHAR(n) keeps exactly what you stored. Too-long values are rejected with ERROR 1406 (22001): Data too long for column 'code'. Use CHAR only for truly fixed-length codes, such as two-letter state codes. The full comparison is in CHAR vs VARCHAR.

Dates and times

TypeHoldsRange
DATEa day1000-01-01 to 9999-12-31
DATETIMEday + time, stored as written1000-01-01 00:00:00 to 9999-12-31 23:59:59
TIMESTAMPday + time, stored as UTC1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC
TIMEa time or a duration-838:59:59 to 838:59:59
YEARa year1901 to 2155
CREATE TABLE t_time (dt DATETIME, ts TIMESTAMP);
SET time_zone = '+00:00';
INSERT INTO t_time VALUES ('2026-11-16 10:00:00', '2026-11-16 10:00:00');
SET time_zone = '+05:30';
SELECT dt, ts FROM t_time;
dtts
2026-11-16 10:00:002026-11-16 15:30:00

Both columns received the same text. DATETIME gives it back unchanged. TIMESTAMP converts from the session time zone to UTC when storing, and back when reading, so the same instant now shows in IST. That is useful for "when did this happen" columns in apps used across time zones. The 2038 limit is real, though: inserting '2039-01-01 00:00:00' into the TIMESTAMP column fails with ERROR 1292. For dates far in the future (loan end dates, expiry dates) use DATETIME. The functions for working with these types are on MySQL date and time functions.

ENUM, BOOLEAN and JSON

CREATE TABLE t_enum (size ENUM('S','M','L'));
INSERT INTO t_enum VALUES ('M'), ('S'), ('L');
SELECT size FROM t_enum ORDER BY size;
INSERT INTO t_enum VALUES ('XL');
size
S
M
L
ERROR 1265 (01000): Data truncated for column 'size' at row 1

An ENUM sorts by its position in the list, not alphabetically. Adding a new value later means an ALTER TABLE. A small lookup table with a foreign key is often easier to maintain.

BOOLEAN is only an alias for TINYINT(1). TRUE is stored as 1 and FALSE as 0, and nothing stops you inserting 5. Add a CHECK (flag IN (0,1)) if that matters. JSON stores validated JSON documents and is covered in the JSON data type tutorial.

Choosing quickly

DataSensible type
Surrogate IDINT UNSIGNED AUTO_INCREMENT; BIGINT if billions of rows are possible
Price, salary, balanceDECIMAL(12,2) or as precision needs
Quantity, age, ratingSMALLINT / TINYINT UNSIGNED
Name, email, cityVARCHAR sized to realistic data
Phone, PIN codeVARCHAR(15), CHAR(6)
Date of birthDATE
Created / updated timeDATETIME or TIMESTAMP (watch the 2038 limit)
Article body, commentsTEXT
Yes/no flagBOOLEAN with a CHECK if needed

Smaller types are not only about saving disk space. Indexes and the buffer pool hold more rows in the same memory, so the right type helps performance as well. For how these choices fit into a full table definition, see how to create a table in MySQL.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →