MariaDB is an open source relational database system with pluggable storage engine capabilities. Having pluggable storage engine capabilities allows for developers to use a single database in a more flexible manner , essentially creating individual tables to suit a specific business need – be it analytics or transaction processing. Databases are containers for objects with a user-defined structure. Some of these objects are containers themselves or assist with the process of making the object easier to understand. One of these object types are tables. Tables are containers with a logical structure in a row-and-column format. Each row represents a record while each column represents a field or property of that record.
15 Types of MariaDB Storage Engines
At the time of writing the following storage engines are supported for use with MariaDB:
- InnoDB – A general purpose transactional storage engine. This is the default storage in the majority of MariaDB deployments.
- ColumnStore – A storage engine for massively parallel architecture where analysis is run over large data sets.
- Aria – A storage engine with a small footprint that allows for easy copying between systems.
- Archive – A storage engine for archiving data.
- Blackhole – Accepts data but does not store it , always returning an empty result.
- Connect – A storage engine which allows for access to different kinds of text files and remote resources.
- CSV – A storage engine which allows for data to be read from and appended to files in a comma separated format.
- Memory – A storage engine which does not write data to disk and is best used for read-only caches of data.
- Mroonga – Provides fast CJK-ready full text searching using column store.
- MyISAM – A storage engine with a small footprint , is largely replaced by Aria.
- MyRocks – A storage engine which allows for greater compression than InnoDB and less write amplification to provide for better Flash Storage performance.
- OQGraph – Allows for the handling of hierarchies and complex graphs.
- S3 – A read only storage engine that stores data in S3 storage.
- Spider – Uses partitioning to provide data sharding through multiple servers
- TokuDB – A transactional storage engine optimised for workloads that do not fit in memory.
How to Create a Basic Table in MariaDB
All commands will be executed using the mysql client utility.
Before creating a table in MariaDB a database or schema must first be present. To see what databases have been created use the SHOW DATABASES or SHOW SCHEMAS command:
|
1 2 3 4 5 6 7 8 9 |
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ |
|
1 2 |
MariaDB [(none)]> CREATE DATABASE EXAMPLES; Query OK, 1 row affected (0.001 sec) |
In this instance there are no user created databases. One can easily be created with the simple CREATE DATABASE command :
Once the database has been created ensure it is set t the current database with the USE command :
|
1 2 3 |
MariaDB [(none)]> use EXAMPLES; Database changed MariaDB [EXAMPLES]> |
At this point a table can be created in the relevant database.
The syntax for creating a table in MariaDB follows these rules :
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,…) [table_options ]… [partition_options]
- CREATE TABLE is always the first word set in the command string
- [OR REPLACE] allows for the consideration that if the table already exists it will be replaced by the new definition.
- [TEMPORARY] specifies if the table should be created temporarily , that is it will only exist for the duration of the current session.
- create_definiton contains all of the column, index or period definitions for the table.
- Each column could be of a specific data type such as character, number, data, time and even binary data.
- Table_options allows for various configuration options for a table. This is the location where items such as the storage engine, compression options , encryption options, tablespaces and other options can be set.
- partition_options allows for various options to be set regarding how a table should be partitioned. Partitioning allows for the logical segregation of a table into smaller parts , increasing the potential speed queries need to run at.
Here is an example of a table that is created to identify a person. A person has a first name , a last name and a date of birth, so three column definitions need to be created for the table. Some things to note about the column definitions :
- A column is specified to have a name , a type and then any properties for that type.
- VARCHAR are variable character types; a limit can be applied to the column definition that essentially limits the maximum number of characters any entry into that column can create.
- DATE data types allow for years , months and day values to be entered.
|
1 2 3 4 5 6 7 8 9 10 11 |
MariaDB [EXAMPLES]> CREATE TABLE Person ( -> FirstName VARCHAR(200), -> LastName VARCHAR(200), -> DateOfBirth DATE -> ); Query OK, 0 rows affected (0.008 sec) |
Running this query will create the Person table with these column definitions.
To see the definition of the table which has been created the DESCRIBE command can be used:
|
1 2 3 4 5 6 7 8 9 |
MariaDB [EXAMPLES]> DESCRIBE Person; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | FirstName | varchar(200) | YES | | NULL | | | LastName | varchar(200) | YES | | NULL | | | DateOfBirth | date | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 3 rows in set (0.003 sec) |
Creating a table with MariaDB is incredibly simple while allowing for a wide range of customization options. The flexibility to create tables with different storage engines allows for greater flexibility as to how structured data will be stored and used.
Learn Next How to Update NodeJS
