I have a simple 'people' table:
mysql> desc people;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
When I do show create table people; command, it shows me whole command that created my table:
mysql> show create table people;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| people | CREATE TABLE `people` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
In this command, I see a sentence ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci.
Question is: What does it mean? Especially, what does ENGINE=InnoDB mean?
InnoDB is a storage engine in MySQL. As explained in the documentation:
Storage engines are MySQL components that handle the SQL operations for different table types.
InnoDBis the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases. (TheCREATE TABLEstatement in MySQL 8.0 creates InnoDB tables by default.)MySQL Server uses a pluggable storage engine architecture that enables storage engines to be loaded into and unloaded from a running MySQL server.
As of the current state of art, MyISAM, that was the only available engine in the early 5.x days, is being abandonned and InnoDB is the de facto standard: it offers most typical functionalities that can be find in other high-profile databases, such as transactions and row-level locking.
InnoDB it's a storage engin that allow Foriegn Key ,Rollback , commit and ACID Compliant...
CHARSET = is a set of characters that are legal in a string.
uft8mb4 means that each character is stored as a maximum of 4 bytes in the UTF-8 encoding scheme.
ai refers accent insensitivity
ci refers to case insensitivity.
COLLATE = set of rules used to compare characters