Features of MariaDB

Hello everyone! This is Jon from Beyond GTA Inc, and today we will learn about

"MariaDB" which is a MySQL compatible DB derived from MySQL .

By the way, "My" in "MySQL" is the name of the developer's eldest daughter, and "Maria" in "MariaDB" is the name of Widenius' younger daughter.

This article is translated from an original article of Yuya Ohara's which was written as of September in 2020.

Main specifications and functions unique to MariaDB (MariaDB 10.3 or later)

MariaDB originally appeared as a compatibility / derivation of MySQL, but recently MariaDB has been updated faster, and there are many specifications and functions that are unique to MariaDB, which are not in the original MySQL.

Here, it is summarized the specifications and functions unique to MariaDB.

* MySQL ⇔ MariaDB repeats updates on both sides, so there is a good chance that MySQL will reimport MariaDB specifications and functions in the future.

Item	Overview
Regular expression engine changes	Regular expression enhancements by changing the regular expression engine
Get the deletion result	Using the DELETE ... RETURNING syntax, if the delete target is one table, delete result is returned.
Initial value setting for BLOB column and text column	Initial values ​​can be set for BLOB columns (giant binary data type) and TEXT columns
Execution of dynamic SQL	Force SQL to be generated from a string, like Oracle Database
System versioning table	A function to record the data update date and time and save the data history
Oracle compatibility mode	Oracle Database SQL-enabled mode
Table value constructor	Ability to generate data combinations on the spot
Application function	Ability to define an aggregate function as a stored function
Sequence table	Ability to generate serial numbers
FOR loop	Ability to loop using FOR syntax
Application period table	The time can be set arbitrarily

MariaDB Recommended Features

Earlier, as it is mentioned earlier MariaDB's unique specifications and functions that MySQL does not have, but these "three" are recommended points.

● System versioning table:

-When changing (updating) DB data, overwriting is basic, but if this system versioning function is enabled, the history of all changes will be saved. By explicitly / specifying the data that remains in the history, you can retrieve the relevant data and analyze the data at any point in time / audit the changes / compare the data at different points in time.

● Oracle compatibility mode:

-A function that enables compatibility of commercial databases with Oracle Database. You can use Oracle Database's own syntax and execute your own stored procedure language (PL / SQL), which makes it easier to migrate from Oracle Database.

● Sequence table:

-Like the sequence function that manages serial numbers in PostgresSQL, you can handle serial numbers in another table. It is easy to check the value of the current serial number and reassign the serial number.

MariaDB notes (incompatibility with MySQL)

When comparing MariaDB and MySQL, there are many similarities in specifications / functions, but not all are compatible, so you need to pay attention to the following items.

● Differences in functions (such as different JSON and GTID implementations) ● Differences in system variables (different default values) ● Character sets / collations are not supported

Summary

So far, we have introduced the outline and features of MariaDB.

If you normally operate MySQL, it should be easy to attach to MariaDB, although there are differences in manners.