MySQL and MariaDB are two of the most widely used open-source databases. One of the big factors of MySQL’s popularity had in large part been due to WordPress which runs more than a third of all websites around the world. WordPress is a content management system that uses MySQL as a database.
MySQL is an open-source project that is owned by Oracle. Oracle had acquired MySQL as part of acquiring Sun Microsystems. The creators of MySQL had been worried that Oracle would simply kill the MySQL product as it was a direct competitor to their own enterprise solution. As part of that, MariaDB was created by the creators of MySQL as a fork off of the code. The database structure and indexes of MariaDB are the same as MySQL which allows organizations to switch between the two databases without any change in code. This has been a big point of MariaDB to be able to simply replace MySQL with MariaDB at any time including for WordPress.
Even though MySQL is still being updated, MariaDB also is constantly updated to ensure it is compatible with MySQL. This means that all aspects of the data, table definitions, APIs, protocols, file names, binaries, paths, ports, connectors, and more end up being the same.
As both databases are constantly being updated, it can be helpful to follow the differences and incompatibilities based on the version as you will see here:
There are some important differences between ANSI SQL and MySQL/MariaDB to be aware of. One such example is if you access a column from a table to be updated, in ANSI SQL uses the original values vs in MySQL/MariaDB, the update uses the current value in the column in the order that they are set in. For example, if we have the following statement:
UPDATE employee <br> SET pay = pay + 1, new_pay = pay;<br>
If the pay was originally set at 0, in ANSI SQL, the pay value will take the current value of pay and set it to both criteria meaning that pay will be set to 1 as it was set as pay = pay + 1. Since the original value was 0, the pay is now 0 + 1 which is equal to 1. The value of new_pay would be set to 0 as that was the original value of pay.
In MySQL and MariaDB though, the value would be different in this case. With the same statement, pay would be set to be the same where the pay will be set to 1 as it was set as pay = pay + 1. Since the original value was 0, the pay is now 0 + 1 which is equal to 1. However, the new_pay when it is being set to pay uses the updated value. The new_pay is set to 1 instead of 0 in ANSI SQL. This may seem minor but this could have drastic issues if assumptions were made one way or another.
Another key difference is the way that comments in the SQL are defined. In ANSI SQL, /* and */ are used for a block of comments. In MySQL and MariaDB the two dashes are used like --. This can be a bit confusing when you see this in a statement especially when you are setting a value. For example, if we run in MySQL/MariaDB:
UPDATE employee <br> SET pay = pay + 1 – 100;<br>
It would be the same as if you run just the following:
UPDATE employee <br> SET pay = pay + 1;<br>
Anything that is after the – is considered a comment and is thrown out.
Source: Authored by Vincent Tran