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)



1 Answer 1


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.

  • Hi Federico, thanks a lot! In simple language, what does that do? and why do we need that? Nov 9, 2020 at 6:39
  • If you don't know the answers to these questions, you simply don't need to do that. :) ENGINE can be used to get better performance in some edge cases. DEFAULT CHARSET can be used to store strings in a smaller character set than UTF8, again for performance reasons. mysqldump adds that syntax just to be sure that the tables are recreated always in the same name, even on servers with strange configuration. Nov 9, 2020 at 10:44

Not the answer you're looking for? Browse other questions tagged or ask your own question.