# 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
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://book.ferro.pro/db/mysql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.