MySQL Data Types: Reference Table and Java Mapping - Quiz
Total: 8 questions
1. What data types does MySQL have, and which type should you choose for common columns of a Java application?
What data types does MySQL have, and which type should you choose for common columns of a Java application?
A MySQL data type is a property of a column that defines which values it can hold, how many bytes they take and how they are compared. The groups are: integers (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT), fractional numbers (DECIMAL, FLOAT, DOUBLE), strings (CHAR, VARCHAR, the TEXT family), binary (BINARY, VARBINARY, BLOB), date and time (DATE, DATETIME, TIMESTAMP, TIME, YEAR) and special types (ENUM, SET, JSON, BIT). BOOLEAN is not a separate type but a synonym of TINYINT(1).
Typical choices:
- id —
BIGINT AUTO_INCREMENT; - price, balance —
DECIMAL(12,2); - flag —
BOOLEAN NOT NULL DEFAULT FALSE; - age, quantity — the smallest type that surely fits, such as
TINYINT UNSIGNED; - login —
VARCHAR(50)UNIQUE, email —VARCHAR(254); - password — never the password itself, only a hash:
CHAR(60)for bcrypt; - description, review —
TEXT; - birth date —
DATE, record creation time —DATETIMEorTIMESTAMPwithDEFAULT CURRENT_TIMESTAMP; - phone number —
VARCHAR(20), not a number: it can start with+or a zero, and 14155552671 does not fit intoINT.
Conversely, numbers you count or compare do not belong in VARCHAR — strings sort character by character ('10' comes before '9') and 'abc' is accepted as a quantity.
2. How do MySQL integer types differ, what does the UNSIGNED attribute do, and what does the number in INT(11) mean?
How do MySQL integer types differ, what does the UNSIGNED attribute do, and what does the number in INT(11) mean?
The five integer types differ only in size and therefore in range:
| Type | Bytes | SIGNED (default) | UNSIGNED |
|---|---|---|---|
TINYINT | 1 | -128 to 127 | 0 to 255 |
SMALLINT | 2 | -32,768 to 32,767 | 0 to 65,535 |
MEDIUMINT | 3 | -8,388,608 to 8,388,607 | 0 to 16,777,215 |
INT | 4 | -2,147,483,648 to 2,147,483,647 | 0 to 4,294,967,295 |
BIGINT | 8 | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 0 to 18,446,744,073,709,551,615 |
UNSIGNED forbids negative values and shifts the range into positive numbers; the size in bytes stays the same. In strict mode an out-of-range value is rejected with ERROR 1264; without strict mode MySQL stores the nearest bound and raises a warning. UNSIGNED on DECIMAL, FLOAT and DOUBLE is deprecated since MySQL 8.0.17 — use CHECK (price >= 0) instead.
INT suits most tables, but for the id of a table that may outgrow 2,147,483,647 rows (orders, events, logs) choose BIGINT from the start: switching a live table later means rebuilding it.
The number in INT(11) is neither the size nor a limit but the deprecated display width, which only affected output with ZEROFILL. INT(5) and INT(11) store the same values. Display width and ZEROFILL are deprecated since MySQL 8.0.17, and since 8.0.19 SHOW CREATE TABLE prints just int; the exception is TINYINT(1), the marker of a boolean column.
3. Does MySQL have a BOOLEAN type, and how should you work with flag columns?
Does MySQL have a BOOLEAN type, and how should you work with flag columns?
MySQL has no separate boolean type: BOOLEAN and BOOL are synonyms of TINYINT(1), and SHOW CREATE TABLE prints tinyint(1). TRUE equals 1, FALSE equals 0, and in a condition any non-zero value is true. The column can hold any number from -128 to 127.
That is why WHERE is_active = TRUE (that is, = 1) skips rows holding 2, 5 and so on. Check flags with WHERE is_active or WHERE is_active <> 0, and to allow only 0 and 1 add CHECK (is_active IN (0, 1)) (MySQL enforces CHECK constraints since 8.0.16).
A typical declaration is is_active BOOLEAN NOT NULL DEFAULT FALSE. Connector/J returns TINYINT(1) to Java as a Boolean by default (tinyInt1isBit=true), and the field is read with rs.getBoolean().
4. How does DECIMAL differ from FLOAT and DOUBLE, and which MySQL type should store money?
How does DECIMAL differ from FLOAT and DOUBLE, and which MySQL type should store money?
DECIMAL(M,D) (synonym NUMERIC) is an exact fixed-point type: M is the total number of digits (up to 65), D the digits after the point (up to 30). DECIMAL(10,2) stores -99,999,999.99 to 99,999,999.99; extra decimal places are rounded (10.555 becomes 10.56), and an integer part that is too large triggers ERROR 1264. DECIMAL without parameters means DECIMAL(10,0). Its Java counterpart is BigDecimal.
FLOAT (4 bytes, about 7 significant digits) and DOUBLE (8 bytes, 15–17 digits) store a binary approximation, like float and double in Java. So 0.1e0 + 0.2e0 = 0.3e0 returns 0, while 0.1 + 0.2 = 0.3 for DECIMAL returns 1. The FLOAT(M,D) syntax is deprecated since MySQL 8.0.17.
Store money in DECIMAL, for example DECIMAL(12,2), and read it with getBigDecimal(): in DOUBLE fractions of a cent pile up in totals and reports. The alternative is BIGINT in minor units (cents): also exact, but it needs conversion everywhere in the code and care with currencies that have three decimal places. Keep DOUBLE for coordinates and scientific or statistical calculations.
5. What is the difference between CHAR, VARCHAR and TEXT in MySQL, how many characters fit into VARCHAR, and when should you choose TEXT?
What is the difference between CHAR, VARCHAR and TEXT in MySQL, how many characters fit into VARCHAR, and when should you choose TEXT?
CHAR(M)— fixed length up to 255 characters: short values are padded with spaces, which are removed on read. For values of the same length: a country code, a bcrypt hashCHAR(60).VARCHAR(M)— variable length, M is set in characters. The limit is 65,535 bytes for the whole table row, so in utf8mb4 (up to 4 bytes per character) no more than 16,383 characters. In strict mode a longer string is rejected:ERROR 1406: Data too long.TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT— 255 bytes, 65,535 bytes (about 64 KB), about 16 MB and about 4 GB. These limits are in bytes.
The default character set in MySQL 8 is utf8mb4: ASCII letters take 1 byte, accented Latin letters 2, Chinese characters 3, emoji 4. VARCHAR(10) fits 10 characters of any kind, but TEXT fits 65,535 ASCII letters and only about 21,845 Chinese characters. LENGTH shows bytes, CHAR_LENGTH shows characters. In MySQL utf8 is an alias of the deprecated utf8mb3 (up to 3 bytes), so emoji cannot be saved there (ERROR 1366: Incorrect string value).
VARCHAR or TEXT: if a value has a sensible length limit (login, email, title), use VARCHAR; if it can be of any size (a review, an article), use TEXT. A VARCHAR gets a normal index and UNIQUE, a TEXT only a prefix index (INDEX (about(100))); TEXT does not allow DEFAULT 'text', since MySQL 8.0.13 only an expression DEFAULT (''). The habit of writing VARCHAR(255) comes from MySQL before 5.0.3; choose the length from the data.
6. What is the difference between DATETIME and TIMESTAMP in MySQL, and which one should you choose?
What is the difference between DATETIME and TIMESTAMP in MySQL, and which one should you choose?
Both types store a date with time in the YYYY-MM-DD HH:MM:SS format, but they differ in time zone handling and range:
DATETIME | TIMESTAMP | |
|---|---|---|
| Time zone | Keeps the value exactly as written | Converts from the session time_zone to UTC on write and back on read |
| Range | 1000-01-01 00:00:00 to 9999-12-31 23:59:59 | 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC |
| Size | 5 bytes (+0–3 for fractional seconds) | 4 bytes (+0–3) |
| Java type | LocalDateTime | Instant or LocalDateTime |
TIMESTAMP suits the moment of an event (when an order was placed) when users and servers live in different time zones. But it ends on January 19, 2038 and does not accept dates before 1970: inserting '1965-05-10' fails with ERROR 1292: Incorrect datetime value. For birth dates, contract terms and anything beyond 2038, use DATE or DATETIME. Fractional seconds go in parentheses: DATETIME(3) for milliseconds, DATETIME(6) for microseconds.
Do not store dates in a VARCHAR such as '11/16/2026': ORDER BY compares them as strings, and BETWEEN on dates does not work.
7. What do the special MySQL types ENUM, SET, JSON and BIT store, and when are they used?
What do the special MySQL types ENUM, SET, JSON and BIT store, and when are they used?
ENUM— one value from a given list (up to 65,535 options), stored as the option number in 1–2 bytes:status ENUM('NEW','PAID','SHIPPED'). In Java — aStringor a Javaenum.SET— any combination of values from a list (up to 64 options):days SET('MON','TUE','WED'). Java receives a string such as"MON,WED".JSON— a JSON document in binary form; invalid JSON is rejected on insert. In Java — aStringparsed, for example, with Jackson.BIT(M)— 1 to 64 bits.BIT(1)is read as aBoolean, the rest asbyte[].
The drawback of ENUM: the list can be changed only with ALTER TABLE. So for statuses that keep growing many teams prefer a VARCHAR(20) validated in code or a separate lookup table.
8. Which Java types do MySQL types map to when you read data through JDBC?
Which Java types do MySQL types map to when you read data through JDBC?
| MySQL type | Java type (ResultSet method) |
|---|---|
BOOLEAN = TINYINT(1) | boolean (getBoolean) |
TINYINT / SMALLINT / INT | byte / short / int |
BIGINT, INT UNSIGNED | long (getLong) |
BIGINT UNSIGNED | BigInteger |
DECIMAL | BigDecimal (getBigDecimal) |
FLOAT / DOUBLE | float / double |
CHAR, VARCHAR, TEXT, ENUM, JSON | String (getString) |
BINARY, BLOB | byte[] (getBytes) |
DATE / DATETIME / TIME | LocalDate / LocalDateTime / LocalTime (getObject(col, LocalDate.class)) |
TIMESTAMP | Instant or LocalDateTime |
Java has no unsigned integers, hence long and BigInteger for the UNSIGNED types. MySQL has no integer LONG type: for a Java long use BIGINT (the keyword LONG creates a MEDIUMTEXT column).
A trap: getInt(), getLong() and getBoolean() return primitives, so for a NULL they give 0 or false. For nullable columns use getObject(col, Integer.class) or call rs.wasNull().