InnoDB Chinese Reference Manual---4 establish InnoDB table

Reference | Reference Manual | Chinese 4 establish InnoDB table
Suppose you have run the MySQL client program as a MySQL test command. In order to create a table in InnoDB format you must specify TYPE = InnoDB in the SQL command:

CREATE TABLE CUSTOMER (a INT, B CHAR (), INDEX (a)) TYPE = InnoDB;

This SQL command will create a table and an index of column A in the InnoDB data file set in My.cnf, while creating a file named Customer.frm in test under the MySQL data directory. Internally, InnoDB will add the entry point of the table "Test/customer" to its own data dictionary (dictionary). This allows you to create a data table with the same name as CUSTOMER in the other MySQL database, which will not conflict with the table in InnoDB.

You can view any InnoDB type table by issuing a View table status command for MySQL to view the remaining space of the InnoDB data file. The table comment section in Show output information displays the remaining space for the data file. Example:

Show TABLE STATUS from test like ' CUSTOMER '

Note: Show InnoDB table statistics are only approximate: they are optimized by SQL. However, the table and index of the reserved sizes in bytes are accurate.

Be particularly careful not to manually add and delete ". frm" files in the InnoDB database: Use the CREATE table and drop TABLE commands. InnoDB has its own internal data Dictionary (database dictionary), if the MySQL ". frm" file and InnoDB internal data dictionary does not "sync" will produce an error.
4.1 How to use transactions in InnoDB through different APIs
By default, MySQL always initiates the automatic submission of each SQL statement you run automatically (autocommit) mode to create each new connection. In order to use transactions, you can use the SQL command SET autocommit = 0 To turn off the autocommit (autocommit) switch, committing transactions and rolling back transactions using commit and ROLLBACK. If you want to keep the autocommit switch open, you can put the transaction between BEGIN and COMMIT or rollback.
heikki@hundin:~/mysql/client> MySQL test Welcome to the MySQL monitor. Commands End With; or \g. Your MySQL Connection ID is 5 to server Version:3.23.50-log Type ' help; '/' \h ' for help. Type ' \c ' to clear the buffer. Mysql> CREATE TABLE CUSTOMER (a INT, B CHAR (), INDEX (a)) TYPE = InnoDB; Query OK, 0 rows Affected (0.00 sec) mysql> BEGIN; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into CUSTOMER VALUES (' Heikki '); Query OK, 1 row Affected (0.00 sec) mysql> COMMIT; Query OK, 0 rows Affected (0.00 sec) mysql> SET autocommit=0; Query OK, 0 rows Affected (0.00 sec) mysql> INSERT into CUSTOMER VALUES (' John '); Query OK, 1 row Affected (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from CUSTOMER; +------+--------+ | A | B | +------+--------+ | 10 | Heikki | +------+--------+ 1 row in Set (0.00 sec) mysql>

　

Through APIs (such as PHP, Perl dbi/dbd, JDBC, ODBC, or MySQL's standard C call interface), send a transaction control statement (such as "COMMIT") to a MySQL server that can be like other SQL statements. For example: "SELECT ..." or "INSERT ...". APIs often contain separate special commit-transaction methods, but MySQL's support for the transaction is relatively young, and they do not work on all versions of APIs.
4.2 Convert MyISAM table to InnoDB type important reminder: MySQL system tables such as ' user ' or ' host ' can not be converted to InnoDB types. The system table must be of type MyISAM.
　

If you want all your new tables to be InnoDB types, starting with MySQL version 3.23.43, you can add the following line to your my.cnf or My.ini file [mysqld]
Default-table-type=innodb

　

InnoDB does not have a special fascicle index to establish an optimization mechanism. It is therefore not supported to first export/import the table and then create the index. The quickest way to do this is to change the table type directly to the InnoDB type or to insert the data directly, that is, to use ALTER TABLE ... Type=innodb or create an empty INNODB table with the same structure, and then use INSERT into ... SELECT * from .... Inserts data.

If you have a unique constraint, start from 3.23.52 to increase the speed of insertion you can turn off uniqueness checking:
SET unique_checks=0;
For large tables, the index buffer is used to merge secondary index records to insert in batches when inserting into InnoDB, which reduces I/O to many disks.
　

In order to better control the insertion process, it is best to insert large tables in batches:

INSERT into newtable SELECT * oldtable WHERE yourkey > Something and Yourkey <=;

After all data has been inserted, you can rename the table name.

During the insert process, make the InnoDB buffer pool larger to reduce disk I/O, but not more than 80 of the physical memory. You should also increase the log file and log buffers.

Note that the table space is not exhausted: the InnoDB table uses more memory than the MyISAM table. If ALTER table runs out of tablespaces, the process will be rolled back and will last for several hours if the disk is not enough. When inserted into InnoDB, the index buffer is used to merge secondary index records to insert in batches, which reduces many disk I/O. This mechanism is not used in the rollback, which will use 30 times times more time than the former.

To exit the runaway rollback, if you don't have any important data in your import table, start with tips from 3.23.53 and 4.0.3 to exit the rollback, check out the 6.1 out of control rollback (stop the runaway rollback).
4.3 FOREIGN KEY constraints
The foreign KEY constraint attribute is supported from the beginning of the 3.23.43b InnoDB. InnoDB table type provides a FOREIGN key constraint for MySQL for the first time to ensure your data integrity.

The definition syntax for InnoDB foreign KEY constraints is as follows:
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES table_name (index_col_name, ...) [on DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] [on UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
Two tables must be of type InnoDB, the foreign key and the referenced key (referenced key) must be the first column in the index. InnoDB are not automatically indexed for foreign keys and referenced keys, they must be created explicitly.
　

foreign keys and corresponding referenced keys must have similar internal data types within InnoDB so that they can be compared without requiring a type conversion. Integer (integer) fields must be the same length as the signed type (signedness). The character type does not need to be consistent. If you specify a SET NULL action, you must determine that the corresponding field in the child table is not defined as not NULL.

If the CREATE table gives a number 1005th error and the error message string prompts for error number (errno) 150, the table creation fails because the FOREIGN key constraint was not properly established. Similarly, if an ALTER table fails and returns error number 150, it means that altered table does not define a foreign key correctly. Starting with 4.0.13, you can view a detailed description of the foreign key error that the server is the last INNODB by using show INNODB STATUS.

Starting with 3.23.50, InnoDB no longer checks foreign key constraints on the foreign key or referenced key that allows NULL values.

Inconsistent with the SQL standard: If in the parent table there are several rows which have the same referenced key value, then InnoDB acts in F Oreign key checks like the ' other parent ' rows with the same key value would not exist. For example, if you have defined a RESTRICT type constraint, and there are a child row with several parent rows, InnoDB Doe s not allow the deletion of any of those parent rows.

Starting with 3.23.50, it is possible to combine the on delete CASCADE or on delete SET NULL clauses with foreign KEY constraints. The appropriate on UPDATE option will be supported from 4.0.8. If the on delete CASCADE is specified, when the row of rows in the primary table is deleted, InnoDB automatically deletes the record of the referenced key value in the child table that is the same as the corresponding foreign key value in the primary table. If on DELETE set NULL is specified, the foreign key corresponding row in the child table is set to a null value.

Inconsistent with the SQL standard: If on update CASCADE or on update SET NULL recurses to update the SAME TABLE it has already updated during th e Cascade, it acts like RESTRICT. This is to prevent infinite loops resulting from cascaded updates. A self-referential on DELETE SET NULL on the other hand, works starting from 4.0.13. A self-referential on DELETE CASCADE has always worked.

Example:
CREATE TABLE parent (id INT not NULL, PRIMARY KEY (ID)) Type=innodb; CREATE TABLE Child (id int, parent_id int, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent (ID) on DELE TE CASCADE) Type=innodb;

　

Starting with 3.23.50, InnoDB allows you to add a foreign key constraint to a table in the following ways:
ALTER TABLE yourtablename ADD [CONSTRAINT symbol] FOREIGN KEY [id] (...) REFERENCES table_name (index_col_name, ...) [on DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}] [on UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
Remember to first establish the necessary indexes, although you can create a self-referential foreign key from the ALTER table for a table.
Starting from 4.0.13, InnoDB support
ALTER TABLE DROP FOREIGN KEY internally_generated_foreign_key_id
You can use show CREATE TABLE to view the internally generated foreign key ID when you need to delete a foreign key.




If you want to import several dumps for a table, and the data is not sorted by a foreign key, starting with 3.23.52 and 4.0.3, you can turn off the foreign key check at the time of import:
SET foreign_key_checks=0;
This allows data to be imported in any order, while increasing the import speed.
　

Starting with 3.23.50, InnoDB parser (parser) allows you to backquotes around table and put the column name into FOREIGN KEY ... REFERENCES ... Clause. Starting with 4.0.5, the InnoDB parser can handle lower_case_table_names that can be set by the My.cnf file.

In a version less than 3.23.50, InnoDB any ALTER TABLE or CREATE INDEX cannot be used on a table that has a foreign key constraint or a referenced key constraint: Any ALTER TABLE deletes the FOREIGN KEY constraint defined in the table. ALTER TABLE can no longer be used to any table, only modified by DROP table and CREATE table. When MySQL executes an ALTER table, the internal processing is done through the RENAME table, which causes the foreign key constraint to confuse the table's references. The CREATE INDEX statement is also processed as ALTER table and cannot be used in tables that are foreign key constraints.

The primary table and child table data are InnoDB when the foreign key check is performed. Nnodb immediately checks the FOREIGN KEY constraint: The check does not wait for a transaction to commit.

InnoDB allows you to drop any table, even if it breaks the foreign key, so the result of the operation is that the constraint is also drop.

InnoDB allows you to undo (drop) Any table, even if it breaks the foreign key constraint of the referenced table. When you undo a table, the constraint is also revoked.

If you re-create an undone table, you must refer to the original definition to establish a consistent foreign key constraint. Must have the right column for me with the type. Must have an index on the reference key. If the above conditions are not met, MySQL will return 1005th error, error message string prompt error number (errno) 150.

Starting with 3.23.50, the following instructions allow InnoDB to return the table's foreign KEY constraint definition
Show CREATE TABLE Yourtablename
You can also dump the full definition of a table into a file by mysqldump, including, of course, the foreign key definition.
　

You can also list the foreign KEY constraints for table T by using the following instructions:
Show TABLE STATUS from yourdatabasename like ' T '
The FOREIGN KEY constraint is listed in the table comment.
　
4.4 Self-adding (auto-increment) is how to work in InnoDB
If the table has an auto-increment column, the InnoDB table processing system includes a special counter in its data dictionary to record the next column value of the self-added column. Self-increasing counters are placed only in main memory, not on disk.
　
InnoDB uses the following rules to initialize the self-increment counter. When the database is started, when the user inserts data into table T or runs Show Table STATUS for the first time, the InnoDB executes
SELECT MAX (Auto-inc-column) from T for UPDATE,
Add a value of 1 to the field and record the self-recording of the table. If the table is empty, the value is assigned to 1. Note that a read lock is added to the table during this initialization (a normal x-locking read), and this lock will continue until the end of the transaction process.
　

InnoDB sets up the counter in the same way for a newly created table.

If you specify a value of 0 for a self-added column, then InnoDB will be treated as having not specified a value for the column and assigning the column to the new value.

When the self-increment counter is initialized, a new, explicitly specified value is inserted in the self-added column, and the value is greater than the current count value, then the counter is set to the new specified value. If the user does not explicitly specify a value for it, InnoDB will increment the counter and assign the new value to the self-added column.

When the InnoDB counter is accessed, a special table-level lock (Auto-inc lock) is used, and the lock remains until the end of the current SQL statement, not the end of the thread. The special lock-release strategy was introduced to improve concurrency performance for inserting data into tables with self added fields. Two transactions cannot establish auto-inc locks on the same table.

Note If transactions are taken from the self-increasing counter, gaps may occur in the order of the columns when the transaction rolls back.

If you specify an invalid value for a self-added field or a value that is larger than the maximum range of defined integer types, then the state of the self-increasing mechanism will not be predictable.
4.5 InnoDB and MySQL replication (replication)
The MySQL replication feature (replication feature) works on the InnoDB table as it does on the MyISAM table type. It is possible to use replication (replication) When a table type in Master is inconsistent with a table type in slave. For example, you can copy changes from a InnoDB table in master to a MyISAM table in a slave.

In order to set up a new slave for master, you must create a copy of the InnoDB tablespace and log file (log files), and also include the InnoDB table corresponding to the. frm file and move the copies to slave. This looks a bit like the following section 7 instructions for moving a InnoDB database. If you can shut down master, you can create a InnoDB table space and log file Cold backup (cold back-up) to build a slave. To build a new slave without shutting down the master database, you can use a InnoDB hot Backup tool that is not free.

One of the minor limitations in InnoDB replication:
The LOAD table from MASTER cannot be used in InnoDB table types. Have workspace 1 dump the table in master and import the dump file (dumpfile) into Slave, or 2, for ALTER TableName TYPE=MYISAM in master, and then use LOAD TABLE tablename from Master, and finally changes the table back to the InnoDB type in master. In previous versions of MySQL-4.0.6, SLAVE STOP was not aware of the boundaries of transactions with several SQL statements. An unfinished transaction will be rolled back and will run the remainder of the other half of the transaction at the next slave START. This will cause replication to fail.
In previous versions of MySQL-4.0.6, slave crashes during a multiple-statement transaction run will cause the same problem as the slave STOP.
In previous versions of MySQL-4.0.11, the statement SET foreign_key_checks=0 replication was not working at all.
Finally, a shorter explanation about MySQL processing transactional replication failures in master. MySQL replication is based on binary log (Binlog), which is used for MySQL to record SQL statements that modify the data. Slave reads Master's binary log (Binlog) and runs the same SQL statement. If a statement fails, for example, because a foreign key constraint is violated, the statement is not recorded in the binary log and therefore is not copied into the slave. If a transaction rolls back, the SQL statements in the transaction will not be recorded in the binary log, and the transaction will not run at all in slave.