What are the Table Types in MySQL?

I want to gain some more knowledge about the table types that are used in MySQL. I am having just basic knowledge about the MySQL, but still not used the table types in coding. We have given an assignment regarding the table types in MySQL. After reading many posts in this forum, I decided to post it here, since you members answer the query in details along with necessary information. So please tell me what are the Table Types in MySQL? I am expecting some quicker replies from your side.

Although the above issues we have not alluded to, MySQL can use different types of tables such as:

• ISAM
• MyISAM
• InnoDB

ISAM tables are the older format. Sizes are limited to not more than 4 gigs and not to copy tables between machines with different operating system. MySAM tables are the result of developments in the past because it solved the problem posed earlier and is the default format of MySQL from version 3.23. InnoDB type tables have a different structure than MyISAM, since they use one file per table in view of the usual three in the previous types. Incorporate a couple of important advantages, allowing to define transactions and referential integrity rules.

The table creation does not present any additional difficulty. The process is identical to the usual tables simply by adding Type = InnoDB after closing the parentheses of the statement table creation. Once created, InnoDB tables behave "in order to use-just like we have been used in previous pages. No need for any modification in the syntax. It is therefore entirely valid and all commented on: registration, changes, questions and downs. One of the risks involved in managing data bases that can occur is a disruption of the process while upgrading one or more tables. Take for example the recovery of our payroll. Requires two simultaneous notes: ll charge the account of the Paying Agency and the payment in our bank account. If by chance the process was interrupted in the middle of the two operations could be the fact that payment is recorded without the assets were to enter into our account. Transactions avoid situations such as records of data are recorded on a provisional and not final until you take a statement confirming that these entries are final. To this end, MySQL has three statements: BEGIN, COMMIT and ROLLBACK.

Quote:

---

Transactions avoid situations such as records of data are recorded on a provisional and not final until you take a statement confirming that these entries are final. To this end, MySQL has three statements: BEGIN, COMMIT and ROLLBACK.

---

There are three statements to manage transactions. They are:

mysql_query ("BEGIN", $ c)

Its implementation requires that $ c enable connection to the server database and instructs MySQL starting a transaction. All sentences to run from it are provisional and are not effectively implemented until a sentence completion.

mysql_query ("ROLLBACK", $ c)

By this statement warned MySQL transaction is complete but should not be paid any changes included in it.

mysql_query ("COMMIT", $ c)

This statement warns MySQL has completed the transaction and should implement all the changes included in it. When using AUTO_INCREMENT fields in InnoDB tables counters are increasing when adding records (even temporarily) so if the inclusion is aborted with a ROLLBACK that counter keeps increasing and insertions that leave equity. For example. If we start with an empty table and make a transaction in two registers (No. 1 and No. 2 in auto-increment) and end with ROLLBACK, but not inserted in a later insertion auto-increment the counter value starting from 2. MySQL announces that as of version 5.0.3 will include a new sentence to allow AUTO_INCREMENT fields to renumber.

Referential integrity must always be set between two tables. One has to behave as master table (usually called the parent table and the other would be the linked table or child table. The following are essentials :

• As the main table has a primary index (PRIMARY KEY)
• That the linked table has an index (no need to be either sole or primary) type associated with fields identical to those used to index the table. Ie, it can be just INDEX.
• If you look at the source code of the example you have right you can see we use the identity card number (only for students) as part of linking the personal data table that includes directions.

MySQL Sentences to be modify or delete fields used to link tables require a special parameter (CONSTRAINT)-may be different in each of the tables, which is necessary to know. The way to visualize it is to execute the statement: SHOW CREATE TABLE table name returns results in an associative array with two indexes. One of them, called Table, which contains the name of the table and the other, "Create Table, which contains the structure that has been created but including the parameter table CONSTRAINT followed by its value. That value is precisely what we need to make changes in the associated fields of the linked tables. Knowing the value of the previous parameter the process of deleting the current link requires the following syntax:

• ALTER TABLE table name parameter DROP FOREIGN KEY

When it comes to adding a new link to a main table we will use the following statement:

• ALTER TABLE table name ADD [CONSTRAINT parameter] FOREIGN KEY REFERENCES parameter primary table (primary key)

CONSTRAIT parameter (enclosed in square brackets above) is optional and only should separate lists in the event that there is already a prior relationship that table.

It can store your data in a way that best fits the requirements of your application.

MySQL supports various of table types or storage engines to allow you to optimize your database. The table types are available in MySQL are:

* MyISAM tables, the default table type, which employs table locking, making it ideal for high volume select, low volume insert/update environments, such as most websites.
* InnoDB tables, which supports transactions and referential integrity
* ISAM tables, the old MySQL table type, replaced by MyISAM in most instances now.
* BDB, which are also transaction safe.
* MERGE tables, a table type for a collection of MyISAM table types, allowing them to be used as one.
* HEAP table, Creating a HEAP table is simply a matter of specifying the table type as HEAP