by
SQL Tutorial › Getting Started › SQL Data Types

SQL Data Types

BeginnerLesson 5 of 6914 min read7 queries run on MySQL 8.4

Every column has a data type that decides what values it accepts, how much space it takes and how it sorts and compares. Choosing well prevents bugs such as rounding errors in money or names cut short. This lesson shows the MySQL types you will actually use, and what MySQL 8.4 does when a value does not fit.

Advertisement
In this lesson
  • Pick the right numeric type, and why money needs DECIMAL
  • CHAR vs VARCHAR vs TEXT
  • Store dates and times with DATE, DATETIME and TIMESTAMP
  • What happens when a value is too big or too long

Numeric types

TypeStoresRange / precisionTypical use
TINYINT1 byte-128 to 127flags, small counts
SMALLINT2 bytes-32,768 to 32,767quantities
INT4 bytesabout ±2.1 billionids, counts, salaries in whole rupees
BIGINT8 bytesabout ±9.2 × 10^18very large ids, bytes
DECIMAL(p,s)exactp digits, s after the pointmoney, rates
FLOAT / DOUBLEapproximate7 / 15 significant digitsmeasurements, science

Add UNSIGNED to an integer type to allow only zero and positive numbers and double the upper limit.

DECIMAL vs FLOAT: why money must be exact

FLOAT and DOUBLE store numbers in binary, so most decimal fractions are only approximated. DECIMAL stores them exactly:

MySQL
SELECT 0.1e0 + 0.2e0 AS double_sum,       0.1 + 0.2     AS decimal_sum;
ResultMySQL 8.4
double_sumdecimal_sum
0.300000000000000040.3
1 row in set
How this query works, step by step
  1. 0.1e0 is written in scientific notation, so MySQL treats it as a DOUBLE. The binary sum is 0.30000000000000004.
  2. 0.1 without an exponent is an exact DECIMAL literal, so the sum is exactly 0.3.
  3. A tiny error like this, repeated over thousands of invoices, is why prices and balances use DECIMAL(10,2).

Values that do not fit

MySQL 8.4 runs in strict mode by default: a value that does not fit is an error, not a silent change.

MySQL
CREATE TABLE stock_check (qty TINYINT);INSERT INTO stock_check VALUES (300);
ResultMySQL 8.4
mysql> CREATE TABLE stock_check (qty TINYINT)
Query OK, 0 rows affected
mysql> INSERT INTO stock_check VALUES (300)
ERROR 1264 (22003): Out of range value for column 'qty' at row 1
How this query works, step by step
  1. TINYINT holds -128 to 127.
  2. 300 is out of range, so MySQL rejects the row with error 1264 instead of storing 127.

String types

TypeStoresNotes
CHAR(n)exactly n characterspadded with spaces; good for fixed codes like 'IN'
VARCHAR(n)up to n charactersthe usual choice for names, emails, titles
TEXTup to 64 KBlong descriptions, comments
ENUM('a','b')one value from a listsmall fixed sets such as statuses

Text longer than the column allows is rejected:

MySQL
CREATE TABLE users (username VARCHAR(8));INSERT INTO users VALUES ('sqatools_admin');
ResultMySQL 8.4
mysql> CREATE TABLE users (username VARCHAR(8))
Query OK, 0 rows affected
mysql> INSERT INTO users VALUES ('sqatools_admin')
ERROR 1406 (22001): Data too long for column 'username' at row 1

ENUM accepts only the listed values:

MySQL
CREATE TABLE tickets (id INT, status ENUM('open', 'in_progress', 'closed'));INSERT INTO tickets VALUES (1, 'open');INSERT INTO tickets VALUES (2, 'pending');
ResultMySQL 8.4
mysql> CREATE TABLE tickets (id INT, status ENUM('open', 'in_progress', 'clos…
Query OK, 0 rows affected
mysql> INSERT INTO tickets VALUES (1, 'open')
Query OK, 1 row affected
mysql> INSERT INTO tickets VALUES (2, 'pending')
ERROR 1265 (01000): Data truncated for column 'status' at row 1
How this query works, step by step
  1. The table is created and the first insert succeeds, because 'open' is in the list.
  2. 'pending' is not one of the allowed values, so the second insert fails.
Tip

Many teams prefer a VARCHAR column plus a CHECK constraint or a lookup table instead of ENUM, because adding a value to an ENUM needs an ALTER TABLE.

Date and time types

TypeFormatExample
DATEYYYY-MM-DD'2026-01-31'
TIMEHH:MM:SS'09:30:00'
DATETIMEYYYY-MM-DD HH:MM:SS'2026-01-31 09:30:00'
TIMESTAMPlike DATETIME, stored in UTCconverted to the session time zone
YEARYYYY2026

Date arithmetic understands the calendar:

MySQL
SELECT DATE '2026-01-31' + INTERVAL 1 MONTH AS next_month,       DATE '2024-02-28' + INTERVAL 1 DAY   AS leap_day,       DATEDIFF('2026-12-25', '2026-01-01') AS days_between;
ResultMySQL 8.4
next_monthleap_daydays_between
2026-02-282024-02-29358
1 row in set
How this query works, step by step
  1. One month after 31 January 2026 is 28 February, because February has no 31st.
  2. 2024 is a leap year, so the day after 28 February is the 29th.
  3. DATEDIFF() counts the days between two dates: 358.

Learn more in Date & Time Functions.

BOOLEAN

MySQL has no separate boolean type: BOOLEAN is a synonym for TINYINT(1), TRUE is 1 and FALSE is 0.

SQL
CREATE TABLE features (name VARCHAR(20), enabled BOOLEAN);INSERT INTO features VALUES ('dark_mode', TRUE), ('beta_api', FALSE);SELECT name, enabled FROM features;
ResultMySQL 8.4
mysql> CREATE TABLE features (name VARCHAR(20), enabled BOOLEAN)
Query OK, 0 rows affected
mysql> INSERT INTO features VALUES ('dark_mode', TRUE), ('beta_api', FALSE)
Query OK, 2 rows affected
mysql> SELECT name, enabled FROM features
nameenabled
dark_mode1
beta_api0
2 rows in set

JSON

A JSON column stores validated JSON documents, and you can read values from them in queries:

SQL
CREATE TABLE settings (user_id INT, prefs JSON);INSERT INTO settings VALUES (1, '{"theme": "dark", "font_size": 14}');SELECT user_id,       prefs->>'$.theme'     AS theme,       prefs->>'$.font_size' AS font_sizeFROM settings;
ResultMySQL 8.4
mysql> CREATE TABLE settings (user_id INT, prefs JSON)
Query OK, 0 rows affected
mysql> INSERT INTO settings VALUES (1, '{"theme": "dark", "font_size": 14}')
Query OK, 1 row affected
mysql> SELECT user_id,
user_idthemefont_size
1dark14
1 row in set
How this query works, step by step
  1. MySQL checks that the text is valid JSON before storing it.
  2. ->> extracts a value by path and returns it as plain text: $.theme is the theme key.

How to choose a data type

  • Ids and counts: INT (or BIGINT for very large tables).
  • Money: DECIMAL(10,2), never FLOAT.
  • Names, emails, codes: VARCHAR with a sensible limit.
  • Long text: TEXT.
  • Dates: DATE; date and time: DATETIME; “when was this row changed”: TIMESTAMP.
  • Yes/no: BOOLEAN.
  • Choose the smallest type that safely fits the data: smaller rows mean faster queries and smaller indexes.

Quick quiz

1. Which type should store product prices?

2. What is the difference between CHAR(10) and VARCHAR(10)?

3. In strict mode, what happens when you insert 300 into a TINYINT column?

4. What does BOOLEAN become in MySQL?

Data types FAQ

What are the main data types in SQL?

Numeric (INT, DECIMAL, FLOAT), string (CHAR, VARCHAR, TEXT), date and time (DATE, DATETIME, TIMESTAMP) and others such as BOOLEAN and JSON.

What is the difference between DATETIME and TIMESTAMP in MySQL?

DATETIME stores the date and time exactly as given. TIMESTAMP converts the value to UTC for storage and back to the session time zone when read, and its range ends in 2038.

Should I use VARCHAR(255) for every text column?

No. Choose a limit that matches the data, such as VARCHAR(100) for an email. Realistic limits act as validation and keep indexes smaller.

Why is FLOAT bad for money?

FLOAT and DOUBLE store binary approximations, so values like 0.1 cannot be stored exactly and totals drift. DECIMAL stores exact decimal values.

Leave a Comment