Yes. A table can have multiple indexes. Here is a table that includes two index definitions. The first creates an index on the id column and requires each id to be unique. The second index definition creates a two-column index on the last_name, and first_name columns:

MySQL does not perform any check on the table structure when you add the IF NOT EXISTS clause. If a table with the given name exists but has a structure different from the one you've defined in the CREATE TABLE statement, MySQL will issue no warning.

How many AUTO_INCREMENT column is allowed per table and what is the other restriction?

Only one AUTO_INCREMENT column is allowed per table, and it must be indexed (for example, by making it a primary key).

Does MySQL allows the use of function as default value?

No. Default value is specified at table creation time. MySQL does not allow the use of function as default value.

How can we specify default value?

Many database developers use DEFAULT values instead of NULL, especially in columns that will be used in calculation or data groupings.

However, the resulting copies differ in the amount of information retained from the original table structure. The CREATE TABLE … SELECT statement copies the column name and data type from the original table, but does not retain the PRIMARY KEY index information or AUTO_INCREMENT column attribute information. The new table also use the default storage engine, rather than the storage engine utilized by table t.

Some table attributes are not copied, even when using CREATE TABLE … LIKE. The most notable examples are:

If the original table is a MyISAM table for which the DATA DIRECTORY or INDEX DIRECTORY table options are specified, those options are not copied to the new table. The data and index files for the new table will reside in the database directory for the chosen database.

Foreign key definition in the original table are not copied to the new table. If you wish to retain the foreign key definitions, they must be re-specified with ALTER TABLE after creating the copy.