MySQL MyISAM and InnoDB differences and selection, MySQL add foreign key

InnoDB:
Support transaction processing, etc.
No lock Read
Support for foreign keys
Support Row lock
Index of type Fulltext not supported
Do not save the table's exact number of rows, scan the table to calculate how many rows
Delete Table is a row of deletes
InnoDB storing data and indexes in a table space
Cross-platform can be copied directly using
InnoDB must contain the index of the Auto_increment type field
Tables are hard to compress

MyISAM:
Transactions are not supported, rollback will result in incomplete rollback, no atomicity
Foreign keys are not supported
Foreign keys are not supported
Support full-Text search
Saves the exact number of rows in the table, without where, directly returning the saved rows
DELETE table, drop table first, then rebuild table
The MyISAM table is stored in three files. The frm file holds the table definition. The data file is MyD (MYData). Index file is myi (myindex) extension
Cross-platform hard to copy directly
MyISAM can make the Auto_increment type field a federated index
Tables can be compressed

Choose:
Because MyISAM is relatively simple, it is better than innodb in efficiency. If the system reads more, write less. Low requirements for atomicity. So MyISAM the best choice. and the MyISAM recovery speed is fast. Can be restored directly with backup coverage.
If the system reads less and writes more, especially when the concurrent writes are high. InnoDB is the first choice.
Both types have their own pros and cons, choose the one that completely depends on your actual class.

to add a foreign key to an already added data table: syntax:Alter TableTable nameAdd constraintfk_idForeign Key(Your foreign key field name)REFERENCESOuter table name (the name of the primary key field for the corresponding table);Alter TableTb_activeAdd constraintfk_idForeign Key(user_id)REFERENCEStb_user (ID)//fk_id is the name of the foreign key/*CREATE TABLE ' tb_active ' (' id ' int (one) not NULL auto_increment, ' title ' varchar () CHARACTER SET UTF8 COLLATE utf8_ Unicode_ci not NULL, ' content ' text CHARACTER SET UTF8 COLLATE utf8_unicode_ci not null, ' user_id ' int (one) not NULL, PRIMA RY key (' ID '), key ' user_id ' (' user_id '), key ' User_id_2 ' (' user_id '), CONSTRAINT ' fk_id ' FOREIGN KEY (' user_id ') referenc ES ' Tb_user ' (' id ')) engine=innodb DEFAULT charset=latin1*/To Remove the foreign key syntax:ALTER TABLE Table-NameDROP FOREIGN KEY Key-an ID;ALTER TABLE' Tb_active 'DROP FOREIGN KEY' fk_id ' auto key update and delete: foreign keys guarantee the integrity of newly inserted records, but what happens if a named table deletes records in a references clause?   What happens in a secondary table that uses the same value as a foreign key? Obviously, those records should also be deleted, otherwise there will be a lot of meaningless orphaned records in the database, and MySQL can be passed to foreignKEY... The references modifier adds an onDELETEor the ON UPDATE clause simplifies the task, which tells the database how to handle orphaned task keyword meanings in this situationCASCADEDelete all records that contain a referential relationship to the deleted key valueSET NULLmodify all records that contain a reference relationship to the deleted key value, replace with a null value (only for fields that have been marked as NOT null)RESTRICTdeny delete requirements until a secondary table using the DELETE key value is manually deleted and no reference is made (this is the default and the safest setting) No ACTION do nothing. Notice thatUPDATEAnd onDelete rule, when you set up MySQL to automate the operation, if the relationship of the key is not set well, it can cause serious data corruption, for example: If a series of tables are through foreign key relationships and onDELETE CASCADEWhen a rule is connected, any change in the main table will cause even only the original deletion of some records that will be contacted without warning, so we have to check the rules before we do the operation and check again after the operation. Add foreign KeyAlter TableLocstockAdd Foreign KeyLOCSTOCK_IBFK2 (Stockid)Referencesproduct (Stockid) Locstock is the table name, LOCSTOCK_IBFK2 is the foreign key name in the first parenthesis to fill out the foreign key column name, product is the table name, the second parenthesis is the column name associated with the write foreign key to delete the foreign keyAlter TableLocstockDrop Foreign KeyLOCSTOCK_IBFK2 See what foreign keys are in the table showCreate TableLocstock[CONSTRAINT Symbol] FOREIGN KEY [ID](Index_col_name, ...) REFERENCEStbl_name (index_col_name, ...) [On DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]      [On UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]All tables must be of type InnoDB, they cannot be temporary tables. In the reference table, there must be an index in which the foreign key columns are listed in the same order as the first column.         If such an index does not exist, it must be created automatically in the reference table.         In the reference table, there must be an index in which the referenced columns are listed in the same order as the first column. • Index prefixes for foreign key columns are not supported.         One consequence of this is that the blob and text columns are not included in a foreign key, because the indexes on these columns must always contain a prefix length. If Constraintsymbol is given, it must be unique in the database. If it is not given, InnoDB automatically creates this name. 

MySQL MyISAM and InnoDB differences and selection, MySQL add foreign key