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.
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