Skip to content

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.

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

Java: Float | MariaDB | MySQL

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.

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:

SET ('a', 'b', 'c', 'd')

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.

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