Skip to main content

Step-by-Step MariaDB

Step-by-Step MariaDB

Setting up a database:

  1. Create a database
  2. Create a user
  3. Create a table
  4. Create a column
  5. Create indexes
  6. Add foreign keys

Using the database:

  1. INSERT
  2. SELECT
  3. UPDATE
  4. DELETE

Create a Database

MariaDB Statement

CREATE DATABASE IF NOT EXISTS db_name COLLATE utf8mb4_unicode_520_ci;

Where db_name is the desired name of the database.

phpMyAdmin

Notes

We use collation utf8mb4_unicode_ci to set the default character set (UTF8) and the collation (unicode - case insensitive), i.e. the set of rules that regulates how our string types will be sorted. It is probably the most demanding set, speed and memory wise, but with today's hardware and internalization needs, there are no excuses for using anything else unless there is a very specific need.

Unicode Version

The latest unicode version should be used.

utf8mb4_unicode_ci is based on unicode version 4.0.0. As other versions appear, the version number is specified in the collation, if changes are meaningful relating to collation. At the time of this writing, only utf8mb4_unicode_520_ci exists, for version 5.2.0.

NO PAD Collations

There are no reasons to use NOPAD version for a collation unless there is a very specific need for it.

You may encounter collations such as utf8mb4_unicode_520_nopad_ci. There are 2 ways of comparing strings which are padded with unequal length of trailing spaces:

  • PAD SPACE: When 2 strings are equal except for the number of trailing spaces, the shorter one is effectively extended to the length of the longer by adding trailing spaces, thus setting both equal. This is historically default behavior in MariaDB ( and MySQL ).
  • NO PAD: When 2 strings are equal except for the number of trailing spaces, no spaces are added, thus the shorter string is considered less than the longer one.

References

MariaDB

MySQL

Others