Posts

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.