I’m exploring Sakila database. After
CREATE TABLE, I saw this syntax. Could any one help to let me know what is this and what it is for? how and when to use it? (I didn’t see this in my SQL course)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
This is non-standard MySQL syntax. After the brackets that enclose the list of columns and indexes, MySQL allows table options.
ENGINE specifies the storage engine to associate to the table. A storage engine is a special plugin that implements all the functionalities related to a table: writing rows and indexes, reading rows and indexes, caches, repair, etc. InnoDB is the default storage engine (unless you change the default_storage_engine variable) and it is the one that performs best in most situations. Also, it is the only transactional storage engine that is shipped with MySQL (not distributed by third-parties). Here you can see the other engines. MariaDB and Percona Server have additional engines.
DEFAULT CHARSET specifies the table character set, in this case UTF8. Note that, technically speaking, there is no such thing as a table character set. Each text column has an associated character set, and it could be different for each column. The table character set is a default: whenever you don’t specify a character set for a column, the table character set applies.
See the manual for more details about
CREATE TABLE syntax.