DataTypes

MariaDB is a highly popular open-source database service that stores and organizes data efficiently. It is crucial to understand the different MariaDB data types when working with this database system. Knowing the correct data type for each data entity results in more efficient storage, faster searches, and better overall performance. This chapter will guide you through understanding and using the range of data types available in MariaDB, and we will provide code snippets to demonstrate their uses.

1. Numeric DataTypes:

   Numeric data types in MariaDB are used to store numeric values. They can be categorized into exact-value (INTEGER, SMALLINT, DECIMAL, etc.) and approximate value types (FLOAT, DOUBLE).

• INTEGER: It allows us to store integer numbers. Depending on the maximum number that we want to store, different types of integers are used. For example:

CREATE TABLE employee (id SMALLINT, salary INT);

Here, the 'id' column uses SMALLINT, which is a smaller range of integers, and 'salary' uses INT for a larger integer.

• DECIMAL: It is used when we need to store exact numerical data. It's useful for storing things like currency data, where precision matters.

CREATE TABLE product (id INT, price DECIMAL(6,2));

Here, 'price' uses DECIMAL(6,2), where 6 is the precision (total number of digits), and 2 is the scale (number of digits after the decimal point).

• FLOAT & DOUBLE: They are used to store large decimal or floating-point numbers. DOUBLE stores larger ranges of floating-point numbers than FLOAT.

CREATE TABLE measurements (length FLOAT, width FLOAT, height DOUBLE);

Here, 'length' and 'width' use FLOAT and 'height' uses DOUBLE.

2. String DataTypes:

   String data types are used to save text-based data. Some the commonly used string types are CHAR, VARCHAR, BINARY, TEXT, and BLOB.

• CHAR & VARCHAR: CHAR length is fixed to the length that you declare when you create it. Whereas VARCHAR length is variable and can hold up to the maximum length specified during creation.

CREATE TABLE person (name CHAR(50), address VARCHAR(100));

Here, 'name' saves fixed-length character strings of length 50 while 'address' saves variable-length strings, holding up to 100 characters.

• BINARY & BLOB: BINARY type is used to store binary data such as images, audio files. Similar to CHAR/VARCHAR difference, the BLOB type allows for storage of larger amounts of binary data.

CREATE TABLE files (file_name VARCHAR(100), file_data BLOB);

Here the 'file_data' field is of type BLOB which can store a file in binary format.

• TEXT: TEXT data type is used to store long strings. MariaDB provides several types of TEXT fields such as TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT differing by their maximum length.

CREATE TABLE articles (id INT, content TEXT);

In this example, 'content' can hold a long string which may contain an entire article.

3. Date and Time DataTypes:

   Date and Time data types are used to store date, time, year, or combinations of date and time.

• DATE: Stores date in the format 'YYYY-MM-DD'.

• TIME: Stores time in the format 'HH:MM:SS'.

• YEAR: Stores year in 2 digit or 4 digit format.

• DATETIME/TIMESTAMP: Both are used to hold date and time together. The main difference is the range and default values.

CREATE TABLE event (event_date DATE, event_time TIME, year YEAR(4), event_start DATETIME, event_end TIMESTAMP);

The above statement will create a table with date and time fields.

4. Spatial DataTypes:

   MariaDB also supports spatial data types like POINT, LINESTRING, and POLYGON. These data types are useful when working with geospatial data.

CREATE TABLE location (place_name VARCHAR(50), location_point POINT);

In this statement, 'location_point' is a POINT data type that can save a location's longitude and latitude.

To sum up, MariaDB offers a wide range of data types. The key is to understand the nature of your data and select the right choice which provides efficient storage and improved querying performance. Besides the above mentioned, MariaDB also has other data types like ENUM, SET, JSON, etc. As you continue to work more with MariaDB, you will gradually come to understand when and where to use these different data types.