[Osaka/Yokohama/Tokushima] Looking for infrastructure/server side engineers!

[Deployed by over 500 companies] AWS construction, operation, maintenance, and monitoring services

[Successor to CentOS] AlmaLinux OS server construction/migration service

[For WordPress only] Cloud server “Web Speed”

[Cheap] Website security automatic diagnosis “Quick Scanner”

[Reservation system development] EDISONE customization development service

[Registration of 100 URLs is 0 yen] Website monitoring service “Appmill”

[Compatible with over 200 countries] Global eSIM “Beyond SIM”

[If you are traveling, business trip, or stationed in China] Chinese SIM service “Choco SIM”

[Global exclusive service] Beyond's MSP in North America and China

[YouTube] Beyond official channel “Biyomaru Channel”

[MySQL compatible/derivative] About the features of MariaDB [Seal]

2020.09.14

Server construction/operation

table of contents

1 Main specifications and functions unique to MariaDB (MariaDB 10.3 and later)
2 Recommended features of MariaDB
3 MariaDB precautions (incompatibility with MySQL)
4 summary

This is Ohara from the technical sales department.

I will describe the characteristics of
MariaDB , 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 her second daughter.

*This article is current as of September 2020.

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

MariaDB originally appeared as a compatible/derivative of MySQL, but recently, MariaDB has been updated more quickly, and it has many specifications and functions that are unique to MariaDB and are not found in the original MySQL.

Here we have summarized MariaDB's unique specifications and functions.
* Since updates are repeated between MySQL and MariaDB, there is a good chance that MySQL will reimport MariaDB's specifications and functions in the future.

item	overview
Forced termination of ID	Forcibly terminate the query with the specified ID
Regular expression engine changes	Regular expression enhancements with regular expression engine changes
Obtaining deletion results	When using the DELETE ... RETURNING syntax, if the target of deletion is one table, the deletion result is returned.
Initial value settings for BLOB columns and text columns	Initial values can be set for BLOB columns (huge binary data type) and TEXT columns
Executing dynamic SQL	Forcibly generate SQL from a string like OracleDatabase
system versioning table	Function to record data update date and time and save data history
Oracle compatibility mode	OracleDatabase SQL mode
table value constructor	Ability to generate data combinations on the fly
Application function	Ability to define aggregate functions as stored functions
sequence table	Function to generate sequential numbers
FOR loop	Ability to loop using FOR syntax
Application period table	You can set the time arbitrarily

Recommended features of MariaDB

Earlier I described the unique specifications and functions of MariaDB that MySQL does not have, and I would like to introduce three recommended points.

● System versioning table:

・When changing (updating) DB data, overwriting is the basic method, but if you enable this system versioning function, the history of all changes will be saved. By specifying/specifying the data that remains in the history, you will be able to retrieve the relevant data, making it possible to analyze data at any point in time, audit changes, and compare data at different points in time.

● Oracle compatible mode:

- A feature that enables compatibility with the commercial database Oracle Database. It makes it easy to migrate from Oracle Database, as it can use Oracle Database's own syntax and execute its own stored procedure language (PL/SQL).

● Sequence table:

・Sequential numbers can be handled in a separate table, like the sequence function in PostgresSQL that manages sequential numbers. This makes it easier to check the current sequence number value and reassign sequence numbers.

MariaDB precautions (incompatibility with MySQL)

When comparing MariaDB and MySQL, there are many similar specifications/functions, but they are not compatible in everything, so you need to be careful about the following items.

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

summary

Above, we have introduced the overview and features of MariaDB.

If you normally operate MySQL, you should be able to easily get used to MariaDB, although there are some differences in manners.

If you found this article helpful , please give it a like!

[2025.6.30 Amazon Linux 2 support ended] Amazon Linux server migration solution

The person who wrote this article

About the author

ohara

I started my career in the telecommunications industry as a salesperson in charge of introducing IT products such as NW services, OA equipment, and groupware for corporations.

After that, he worked as a pre-sales engineer for physical servers/hosting services and as a customer engineer for SaaS-type SFA/CRM/BtoB e-commerce at an SIer-based data center business company, before joining his current company, Beyond.

Currently, I am stationed in Shenzhen, China, the Silicon Valley of Asia, and my daily routine is to watch Chinese dramas and billbill.

Qualification: Second class bookkeeping

[DWH] Features and architecture of Snowflake [Big Data] We will provide a “remote work allowance” [Coronavirus countermeasures]