Boopathi Rajan

There are mainly five types of tables present in MySQL. Out of all these database engines, the default database engine used in MySQL is MyISAM. Refer below to know the five types of tables:

• MyISAM
• Heap
• Merge
• INNO DB
• ISAM

SIX triggers are allowed in MySQL table. They are as follows:

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE and
• AFTER DELETE

Following objects are created using CREATE statement:

• DATABASE
• EVENT
• FUNCTION
• INDEX
• PROCEDURE
• TABLE
• TRIGGER
• USER
• VIEW

A BLOB is a binary large object that can hold a variable amount of data. There are four types of BLOB

• TINYBLOB
• BLOB
• MEDIUMBLOB and
• LONGBLOB

They all differ only in the maximum length of the values they can hold.

A TEXT is a case-insensitive BLOB. The four TEXT types

• TINYTEXT
• TEXT
• MEDIUMTEXT and
• LONGTEXT

They all correspond to the four BLOB types and have the same maximum lengths and storage requirements.

The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive for BLOB values and case-insensitive for TEXT values.

Every row of a table is identified uniquely by primary key. There is only one primary key for a table.

Primary Key is also a candidate key. By common convention, candidate key can be designated as primary and which can be used for any foreign key references.

TIMESTAMP column is updated with Zero when the table is created.  UPDATE CURRENT_TIMESTAMP modifier updates the timestamp field to current time whenever there is a change in other fields of the table.

Following are the differences between CHAR and VARCHAR:

• CHAR and VARCHAR types differ in storage and retrieval
• CHAR column length is fixed to the length that is declared while creating table. The length value ranges from 1 and 255
• When CHAR values are stored then they are right padded using spaces to specific length. Trailing spaces are removed when CHAR values are retrieved.