Skillversitas

TiDB Data Types

TiDB supports the majority of MySQL data types. Here are the available types and their intended uses.

Numeric Types

Type Size Range Use Case
TINYINT 1 byte -128 to 127 Small flags
SMALLINT 2 bytes -32768 to 32767 Small IDs
MEDIUMINT 3 bytes -8388608 to 8388607 Medium counters
INT 4 bytes -2^31 to 2^31-1 General IDs, counters
BIGINT 8 bytes -2^63 to 2^63-1 Large IDs, timestamps
FLOAT 4 bytes -3.4E+38 to 3.4E+38 Low-precision decimals
DOUBLE 8 bytes -1.7E+308 to 1.7E+308 High-precision decimals
DECIMAL(M,D) Variable -10^M to 10^M Money, finance

Example

CREATE TABLE numeric_example (
    tiny_col TINYINT,
    small_col SMALLINT,
    big_col BIGINT,
    float_col FLOAT(10, 2),
    double_col DOUBLE(15, 4),
    decimal_col DECIMAL(12, 2)
);

INSERT INTO numeric_example VALUES
(127, 32767, 9999999999, 12345.67, 123456789.1234, 999999999.99);

String Types

Type Max Length Use Case
CHAR(N) 255 chars Fixed type (country code, status)
VARCHAR(N) 65535 chars Variable text (name, email)
TEXT 65535 chars Long text (description)
MEDIUMTEXT 16M chars Articles, content
LONGTEXT 4G chars Very long content
BINARY(N) 255 bytes Fixed binary data
VARBINARY(N) 65535 bytes Variable binary data
BLOB 65535 bytes Binary files
ENUM - Choice from a fixed list
SET - Combination of multiple options

Example

CREATE TABLE string_example (
    code CHAR(5),
    name VARCHAR(100),
    bio TEXT,
    avatar BLOB,
    role ENUM('admin', 'editor', 'viewer'),
    tags SET('php', 'mysql', 'laravel')
);

INSERT INTO string_example VALUES
('ID001', 'John', 'Web developer', NULL, 'admin', 'php,mysql');

Date and Time Types

Type Format Use Case
DATE YYYY-MM-DD Birth date
TIME HH:MM:SS Time of day
DATETIME YYYY-MM-DD HH:MM:SS Absolute timestamp
TIMESTAMP YYYY-MM-DD HH:MM:SS Timezone-aware timestamp
YEAR YYYY Year

Example

CREATE TABLE date_example (
    born DATE,
    alarm TIME,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO date_example (born, alarm, created) VALUES
('1990-05-15', '08:30:00', '2024-01-15 10:00:00');

JSON Type

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(100),
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES
('Laptop', '{"brand": "Asus", "ram": "16GB", "storage": "512GB SSD"}');

-- Query JSON
SELECT name, attributes->>'$.brand' AS brand
FROM products;

Tips for Choosing Data Types

  • Use INT/BIGINT for primary keys
  • Use DECIMAL for financial data (not FLOAT/DOUBLE)
  • Use VARCHAR for variable-length text
  • Use TIMESTAMP for timezone-aware timestamps
  • Use JSON for semi-structured data

In the next chapter, we will look at constraints in TiDB.