> For the complete documentation index, see [llms.txt](https://book.ferro.pro/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://book.ferro.pro/db/mysql.md).

# MySQL & MariaDB

* [Storage Engines](/db/mysql.md#storage-engines)
* [MySQL Cluster](/db/mysql.md#mysql-cluster)
* [JSON](/db/mysql.md#json)
  * [MySQL 5.7+](/db/mysql.md#mysql-57)
  * [MariaDB 10.2+](/db/mysql.md#mariadb-102)

## Storage Engines

<https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html>\
Compare: Table 15.1 Storage Engines Feature Summary

| Feature                               | MyISAM  | Memory      | InnoDB  | Archive | NDB     |
| ------------------------------------- | ------- | ----------- | ------- | ------- | ------- |
| Storage limits                        | 256TB   | RAM         | 64TB    | None    | 384EB   |
| Transactions                          | No      | No          | Yes     | No      | Yes     |
| Locking granularity                   | Table   | Table       | Row     | Row     | Row     |
| MVCC                                  | No      | No          | Yes     | No      | No      |
| Geospatial data type support          | Yes     | No          | Yes     | Yes     | Yes     |
| Geospatial indexing support           | Yes     | No          | Yes\[a] | No      | No      |
| B-tree indexes                        | Yes     | Yes         | Yes     | No      | No      |
| T-tree indexes                        | No      | No          | No      | No      | Yes     |
| Hash indexes                          | No      | Yes         | No\[b]  | No      | Yes     |
| Full-text search indexes              | Yes     | No          | Yes\[c] | No      | No      |
| Clustered indexes                     | No      | No          | Yes     | No      | No      |
| Data caches                           | No      | N/A         | Yes     | No      | Yes     |
| Index caches                          | Yes     | N/A         | Yes     | No      | Yes     |
| Compressed data                       | Yes\[d] | No          | Yes\[e] | Yes     | No      |
| Encrypted data\[f]                    | Yes     | Yes         | Yes     | Yes     | Yes     |
| Cluster database support              | No      | No          | No      | No      | Yes     |
| Replication support\[g]               | Yes     | Limited\[h] | Yes     | Yes     | Yes     |
| Foreign key support                   | No      | No          | Yes     | No      | Yes\[i] |
| Backup / point-in-time recovery\[j]   | Yes     | Yes         | Yes     | Yes     | Yes     |
| Query cache support                   | Yes     | Yes         | Yes     | Yes     | Yes     |
| Update statistics for data dictionary | Yes     | Yes         | Yes     | Yes     | Yes     |

```
CREATE TABLE t1 (i INT) ENGINE = INNODB;

InnoDB: default in MySQL 5.7. 
    transaction-safe (ACID compliant)：commit, rollback, and crash-recovery
    row-level locking (without escalation to coarser granularity locks) 
    Oracle-style consistent nonlocking reads
    clustered indexes to reduce I/O for common queries based on primary keys
    FOREIGN KEY referential-integrity constraints
MyISAM: 
    Table-level locking. 
    often used in read-only or read-mostly workloads.
Merge: 
    logically group a series of identical MyISAM tables and reference them as one object.     
Memory or HEAP:
    Its use cases are decreasing:
        InnoDB with its buffer pool memory area
        NDBCLUSTER provides fast key-value lookups for huge distributed data sets
NDB or NDBCLUSTER: highest possible degree of uptime and availability.
Federated: link separate MySQL servers to create one logical database
CSV: Its tables are really text files with comma-separated values.
Archive: compact, unindexed tables
Blackhole: 
    not store data, Queries always return an empty set. 
    can be used in replication configurations
Example: illustrates how to begin writing new storage engines.
```

## MySQL Cluster

<http://severalnines.com/blog/mysql-docker-introduction-docker-swarm-mode-and-multi-host-networking>\
<https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-ndb-innodb-engines.html>

## JSON

### MySQL 5.7+

<https://dev.mysql.com/doc/refman/5.7/en/json.html>

```
CREATE TABLE t1 (jdoc JSON);
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

SELECT JSON_ARRAY('a', 1, NOW());
SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');

SET @j = JSON_OBJECT('key', 'value');
SELECT @j;

# escape quote character
'{"mascot": "... \\"Sakila\\"."}'
JSON_OBJECT("mascot", "... \"Sakila\".")
JSON_OBJECT('mascot', '... "Sakila".')    # NO_BACKSLASH_ESCAPES

# JSON values is case sensitive
SELECT CAST('null' AS JSON); # `null`, `true`, and `false` always lowercase

SELECT col->"$.mascot" FROM qtest;          # "... \"Sakila\"."
SELECT sentence->>"$.mascot" FROM facts;    # ... "Sakila".

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)
```

### MariaDB 10.2+

<https://mariadb.com/resources/blog/json-mariadb-102>

```
CREATE TABLE products(id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
price DECIMAL(9,2) NOT NULL,
stock INTEGER NOT NULL,
attr VARCHAR(1024),
CHECK (attr IS NULL OR JSON_VALID(attr)));

INSERT INTO products VALUES(NULL, 'Blouse', 17, 15, '{"colour": "white"}');
UPDATE products SET attr = JSON_REPLACE(attr, '$.colour', 'red') WHERE name = 'Blouse';

ALTER TABLE products ADD attr_colour VARCHAR(32) AS (JSON_VALUE(attr, '$.colour'));

CREATE INDEX products_attr_colour_ix ON products(attr_colour);
EXPLAIN SELECT * FROM products WHERE attr_colour = 'white';     # verify index
```