Skip to content

Data Types

Before we move on to actually creating tables, we need to first go through basic data types. Data types are very important aspect of databases as every column that you create in your tables will need to have a specific type, like text or number.

Why do we need to use Data Types?

Data types specify the type of data. For example, a name, email, and occupation are text. Age, level in a game, and the number of login attempts is number. By specifying exact data types, we have some direct benefits:

  • We can be certain that only the type of data that we specify is stored in that certain column (other type of data is rejected)
  • We can even specify things like maximum number of chararacters for columns (like that email can be a maximum of 50 characters)
  • Things like calculations can be done only with specific data (for example calculating the total amount of login attempts)
  • We can even save space in the database by only reserving certain amount of space for certain columns.

Different kinds of Data Types in MariaDB

Data types can be split into 3 commonly used categories: String Data Types, Numeric Data Types and Date and Time Data Types.

Data Types: String

Columns that have the string data type set can contain text like Aleksi, Pori rocks and so on. There are few different string data types available: CHAR and VARCHAR and TEXT, BINARY and VARBINARY, BLOB, ENUM and SET. For the sake of simplicity we will only cover CHAR, VARCHAR and TEXT for now. Read More

CHAR, VARCHAR and TEXT

CHAR and VARCHAR are very commonly used in MariaDB. They store text. CHAR can store text maximum amount of 255 characters and VARCHAR maximum amount of 65,535 characters. The difference between them is also that CHAR always reserves the given amount of space even though the text would be smaller. VARCHAR only reserves the amount of memory that the actual given text is.

When using the CHAR or VARCHAR we need to also specify the maximum length of the text. The actual definition is made in this kind of way: CHAR(70) or VARCHAR(5000). In the first example we defined that maximum of 70 characters can be stored and in second that maximum of 5000 characters can be stored.

There is also the TEXT type available which can be TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. TEXT type is stored little differently in the database than CHAR. TINYTEXT can store up to 255 characters, TEXT maximum of 65,535 characters, MEDIUMTEXT maximum of 16,777,215 characters and LONGTEXT maximum of 4,294,967,29 characters. Unlike CHAR and VARCHAR you do not need to specify the maximum length for any TEXT types, so you only specify TEXT, MEDIUMTEXT and so on.

Which one to use?

Only use CHAR if you need to store something which you know that is always the same length and the data is less than 255 characters long. Great for example for storing country short names (like fi, en, gb...) and so on.

For anything else, you can use VARCHAR. It can support up to 65,535 characters and you can store anything from usernames, occupations, email addresses, passwords to reviews, written articles... in it.

CHAR and VARCHAR are indexes quite well in MariaDB compared to TEXT datatype, so if you want to make faster searches to this kind of data, it is recommended to use CHAR or VARCHAR as opposed to TEXT.

If you need to store much more text than 65,535 characters or text that should not be required to be searched for, you can use the TEXT datatype.

CHAR, VARCHAR and TEXT Lengths

Data Type Maximum Length Example Definition
CHAR 255 CHAR(30)
VARCHAR 65,535 VARCHAR(18100)
TINYTEXT 255 TINYTEXT
TEXT 65,535 TEXT
MEDIUMTEXT 16,777,215 MEDIUMTEXT
LONGTEXT 4,294,967,29 LONGTEXT

SQL Example of CHAR, VARCHAR and TEXT

SQL
CREATE TABLE products(
    name VARCHAR(150),
    alternativeName VARCHAR(20),
    description VARCHAR(1500),
    superLongDescription TEXT
);

Data Types: Numeric

Numeric Data Types can contain, like the name suggests, numbers like 1 or 1661242 or 1.6223. There are a lot of different numeric data types available and we will have a look at two most commonly used ones: Integer Types and Fixed-Point Types. We will also look at the BOOLEAN datatype for storing true or false values. Read More

Integer Types

These data types can store basic numbers like 1 or 8162 or -15, but not floating point numbers like 1.52. The differences again for the datatypes are that they can store different amount of characters. TINYINT can store 255 characters, SMALLINT 65,535, MEDIUMINT 16,777,215, INT 4,294,967,295 and BIGINT 2(64)-1. In SQL syntax you specify integer without maximum limitation like this: MEDIUMINT. Read More

Fixed-Point Types

Fixed-Point Types can store DECIMAL numbers with exact values like salaries or timestamps. For example: 152.92 can be stored.

You define Fixed-Point DECIMAL type by passing two parameters for it. First parameter defines the precision and second the scale. For example: DECIMAL(3,1) can accept values like 12.3 or 81.1 or -10.3. Read More

Numeric Lengths

Data Type Maximum Length Example Definition
TINYINT 255 TINYINT
SMALLINT 65,535 SMALLINT
MEDIUMINT 16,777,215 MEDIUMINT
INT 4,294,967,295 INT
BIGINT 2(64)-1 BIGINT
DECIMAL precision 1-65, scale 0-30 DECIMAL(5,2)

SQL Example of Numerics

SQL
CREATE TABLE prices(
    price DECIMAL(6,2),
    taxPercentage TINYINT,
    discount DECIMAL(4,2),
    predictionPercentageToBuyAgain TINYINT
);

Data Types: Date and Time

This data type can store exact times, days, years, dates and so on. The data types that can be stored are DATE, TIME, DATETIME, TIMESTAMP and YEAR. The most commonly used types of these are the DATETIME and TIMESTAMP formats. Read More

Date Types Comparison

Data Type Example Definition What can it contain?
DATE '2021-03-02' exact day
TIME '11:05:30' exact time
DATETIME '2021-03-02 11:05:30' exact day and exact time
TIMESTAMP '2021-03-02 11:05:30' exact day and exact time
YEAR 2021 exact year

DATETIME and TIMESTAMP can be defined the same way but the difference between them is that the time stored in TIMESTAMP is always converted from current timezone into UTC +0 timezone and back from UTC +0 to current timezone.

SQL Example of Dates

SQL
CREATE TABLE songs(
    composedDay DATE,
    createdAt TIMESTAMP,
    publishedAt TIMESTAMP,
    composerBornYear YEAR
);

Data Types: Boolean

MariaDB does not have an built-in Boolean data type for storing the true or false values, but it uses the TINYINT(1) for storing this kind of data as that integer can be of value 0 or 1. MariaDB also provides BOOLEAN synonym for creating TINYINT(1) and TRUE and FALSE for evaluating or storing the results.

SQL Example of Boolean

SQL
CREATE TABLE users(
    hasLoggedIn BOOLEAN,
    isAdmin BOOLEAN
);

This is the same as:

SQL
CREATE TABLE users(
    hasLoggedIn TINYINT(1),
    isAdmin TINYINT(1)
);

When inserting data, we can use this kind of syntax:

SQL
INSERT INTO users (hasLoggedIn) VALUES (true);

Which is the same as:

SQL
INSERT INTO users (hasLoggedIn) VALUES (1);