MariaDB and MySQL
MariaDB and MySQL are very similar when it comes to datatypes. However, there are some small differences.
Numeric types
Numeric types sometimes have a "size". This size is not directly a validation. It is most common the size of the number and defines how much of that size is used to store the decimal. Sized never have a size which validates the input. It is always about precision.
INTEGER
Java: Integer, Long
The integer in MariaDB are divided into several sizes. They will require different disk space depending on the size you choose. Choose your integer type based on the maximum and minimum size you expect the values to be.
- TINYINT: Between -128 and 127 - MariaDB | MySQL
- SMALLINT: -32,768 and 32,767 - MariaDB | MySQL
- MEDIUMINT: -8,288,608 and 8,388,607 - MariaDB | MySQL
- INT or INTEGER: -2,147,483,648 and 2,147,483,647 - MariaDB | MySQL
- BIGINT: Every value larger than INT - MariaDB | MySQL
DECIMAL
Java: Double | MariaDB | MySQL
A number with an "exact" fixed point.
DOUBLE
Java: Double | MariaDB | MySQL
A number with a double precision floating point
FLOAT
A number with a single precision floating point
BOOLEAN
Java: Boolean | MariaDB | MySQL
Booleans are stored as tinyint internally.
Text types
We have several string types which we need to choose based on the expected size of the value.
CHAR
Java: String | MariaDB | MySQL
A character is used for strings with the same size. E.g. Country Codes. It has a maximum length of 255. The length needs to be defined on creation. Characters will be padded with spaces if some characters are missing to reach the specified length.
CHAR(length)
TEXT
Java: String
The text type is divided into several sizes. They will disallow values larger than the maximum size.
- TINYTEXT: Up to 255 chars - MariaDB | MySQL
- TEXT: Up to 65,353 chars - MariaDB | MySQL
- MEDIUMTEXT: Up to 16,777,215 chars - MariaDB | MySQL
- LONGTEXT: Up to 4,294,967,295 chars - MariaDB | MySQL
VARCHAR
Java: String | MariaDB | MySQL
The varchar is a string with a variable maximum size. The maximum size here is a bit more complex, since it depends on the encoding of the database. Unlike CHAR is does not add any padding.
Theoretically the maximum size is 65,532 characters. If you use utf8 which requires up to 4 bytes per char you are down to 21,844 characters in the worst case.
One advancement over TEXT is that varchar columns can be fully indexed while TEXT columns are truncated to a specified length.
Enum
Java: String or Enum name | MariaDB | MySQL
The enum type is a special string which adds input validation to the column. Only values defined in the column can be added.
ENUM('value1','value2',...)
JSON
Java: String | MariaDB | MySQL
MySQL JSON is stored as a LONGTEXT column but converted into an internal format for better access.
MariaDB
JSON is an alias for LONGTEXT and adds a check that ensures a valid json syntax on insertion. JSON is a normal string in MariaDB.
SET
Java: String | MySQL only
Similar to an enum a set can only contain predefined input which are separated by ,
.
Given a set like this:
We can have several values like:
''
'a'
'a,b'
'a,c'
- ...
Binary types
BLOB
Java: Anything
The blob type is divided into several sizes. It is used to store any binary data you want.
- TINYBLOB: Up to 255 bytes - MariaDB | MySQL
- BLOB: Up to 65,353 bytes - MariaDB | MySQL
- MEDIUMBLOB: Up to 16,777,215 bytes - MariaDB | MySQL
- LONGBLOB: Up to 4,294,967,295 bytes aka 4GB - MariaDB | MySQL
BINARY
Java: Anything | MariaDB | MySQL
Stores a fixed length binary value.
BINARY(length)
Date and Time
DATE
Java: LocalDate | MariaDB | MySQL
TIME
Java: LocalTime | MariaDB | MySQL
DATETIME
Java: LocalDateTime | MariaDB | MySQL
Combination of DATE and TIME. They will be stored in the current timezone. They will also validate that the time is valid in the current timezone. Some times can be invalid due to daylight saving times also referred as winter- and summertime.
TIMESTAMP
Java: LocalDateTime | MariaDB | MySQL
A timestamp will be converted to UTC on insertion and changed to the sessions timezone on read again.
YEAR
Java: Integer or String | MariaDB | MySQL
A year represents a year in two or four digit format. They are limited in range.
- Four digits (
YEAR(4)
): 1901 -> 2155 and 0000 - Two digits (
YEAR(2)
): 70 -> 69 representing 1970 -> 2069