MySQL Learning--storage engine (INNODB)

The InnoDB engine provides transactional security with commit, rollback, and crash resiliency, but the contrast MyISAM engine is less efficient and consumes more disk space and preserves data and indexes

1. Auto-Grow column

The auto-grow column of the InnoDB table can be entered manually, but if the inserted value is empty or 0, the actual insert will be the auto-grow value,

　　

You can force the initial value of the autogrow column to be set by the ALTER table table name auto_increment = N statement, which starts with 1, but the default value of the coercion is kept in memory, and if the value is restarted before the database is used, the mandatory default value is lost. You need to restart after the database is started.

The auto-Grow column for the InnoDB table must be an index, and if it is a composite index, it must be the first column of the combined index, but for the MyISAM table auto-Grow column can be another column of a composite index, so that when the record is inserted, the autogrow column is incremented by the preceding columns of the combined index, For example, to create a new MyISAM table, the autogrow column D1 is the second column of a composite index, and for some records to be inserted, you can find that the autogrow column is incremented after the first column of the combined index D2 sorted by

　　

2. FOREIGN KEY constraints

MySQL supports foreign keys only InnoDB, when creating foreign keys, requires that the parent table has a corresponding index, the child table when creating foreign keys will also automatically create the corresponding index

Example:

Two tables, country is the parent table, COUNTY_ID is the primary key index, the city table is a child table, the county_id field is a foreign key, corresponding to the county table's primary key county_id

　　　　

　　

When you create an index, you can specify the corresponding action on the child table when the parent table is deleted, including Restrict,cascade,set NULL, and no action, where restrict and no action are the same, Refers to the restriction that cannot be updated in the case of a Word table associated with records, Cascade means that when the parent table is updated or deleted, the corresponding record of the Word table is updated or deleted, and set NULL indicates that the corresponding field of the child table is set NULL when the parent table is updated or deleted.

When creating the table above, the foreign key of the Word table is specified to be on the delete RESTRICT on UPDATE CASCADE, when the main table deletes the record, if the child table has corresponding records not allowed to be deleted, when the main table is updated, if the child table has corresponding records, the child table corresponds to the update

　　

When a table is created with a foreign key reference by another table, the index or primary key of the table is prohibited from being deleted, and when the primary table is updated, the corresponding child table is updated.

，

You can close the foreign key by set foreign_key_checks =%, or restore it.

MySQL Learning--storage engine (INNODB)