MySQL basic day03 storage engine and foreign key MySQL 5.6

• MySQL base day03_ storage engine and foreign key-mysql 5.6

  Conditions for foreign keys:

  1, the table's storage engine is the InnoDB storage engine

  2, the type of the foreign key field in the table is consistent with the field type of the reference table

  3, foreign key field if one of the index types

  Storage engine for MySQL database

  Storage Engine: A part of the MySQL fabric

  MySQL Architecture:

  Consists of 8 parts: Connection pool, SQL interface, parser, optimizer, cache and buffer, storage engine, management tool, physical storage device

  1. Client: MySQL 2. Connector: Nativec API local C language Interface, Jdbcjava Database Connector, ODBC Open database interconnect, very low level oracle,solserver support 3. Connection pool (Conetcionpool): the rear of it is the true function of MySQL 4.SQL interface: The user sends a command via SQL client, received by SQL interface (DML data Manipulation Language: Query, modify, upgrade data, etc. DDL Data Definition Language: Create a new database, new index, delete a user, etc. stored procedure view trigger) 5.Parser: Parser (Query transaction object permissions) 6.optimizer: (Access path, build execution tree) 7.caches&buffers: Caching and caching (most important in optimizations are cache optimizations) 8.pluggablestroage engines Storage Engine (plug-in): A program that transforms a logical structure into a physical structure 9.ManagementServices & Utilites Tools: Backup, Recovery, security, porting, clustering, etc. these tools generally deal with file systems, do not need to deal with mysql-server, and they correspond to commands.

  View the storage engines supported by the current MySQL server: show engines;

  01.mysql> show engines;02.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+03.| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |04.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+05.| PERFORMANCE_SCHEMA | YES     | PerformanceSchema                                             | NO           | NO   | NO         |06.| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |07.| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |08.| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |09.| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |10.| MEMORY             | YES     | Hash based, stored in memory, useful for temporarytables      | NO           | NO   | NO         |11.| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |12.| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreignkeys     | YES          | YES  | YES        |13.| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |14.+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+15.9 rows in set (0.00 sec)16.mysql>

  Specify the storage engine:

  1, specifying the storage engine when the table is built

  CREATE TABLE table name (field list) engine= storage engine name;

  Note: You need to use showengine before you specify, check the supported engines, support column is supported!

  2, build the table after modifying the storage engine used, when the table is not specified when the default:

  ALTER TABLE name engine= storage engine name;

  View the storage engine for a table: (that is, to view the table creation process)

  Show create table table name;

  The file that holds the table structure is the/var/lib/mysql/library name/table name. frm

  The file that holds the table data is the/var/lib/mysql/library name/table name. ibd

  Transactions: A single operation of data in a table, from the beginning to the end of the process is called a transaction;

  Transaction rollback: If the operation is unsuccessful, revert to the previous state;

  InnoDB support transaction rollback;

  Lock mechanism: (resolves multiple users to record concurrent access issues to the database)

  Resulting type: read lock, write lock

  Read lock (Shared lock): does not cause changes in data content;

  Write lock (Mutex/exclusive lock), the degree of locking is the granularity of lock;

  Note: The priority of the write lock is higher than the read lock;

  Lock granularity: Table lock, row lock, page lock

  Table Lock: MyISAM storage engine supports table locks

  Row lock: InnoDB storage engine supports row locks

  Page-level locking: The query's data is stored in the same storage unit as the page (unit default is M)

  Row-level-locking row-level locking (row lock);

  The priority of the write lock is higher than the read lock;

  MyISAM Storage Engine:

  Characteristics:

  Support table lock, do not support row lock;

  does not support transactions;

  Exclusive table space, (each table has a corresponding three related file storage)

  Many tables are used for querying and inserting more operations.

  Related files: Table name. frm (table structure file), table name. MYI (store index), table name. MYD (storage table record)

  INODB Storage Engine:

  Characteristics:

  Support table lock, row lock;

  Support transaction, support foreign key;

  Shared table space; ()

  Related documents:

  Table name. frm (table structure file), table name. IBD (Index and table record file)

  IBDATA1 (rollback supported)

  IB_LOGFILE0, Ib_logfile1

  Set the storage engine supported by the MySQL server by default:

  In the configuration file/etc/my.cnf

  In the Mysqld field, add:

  default-storage-engine= Storage Engine

  Default-storage-engine=myisam (default is InnoDB)

  To view the current lock status:

  That is, check the variable at the beginning of the Table_lock,% as a wildcard character;

  Show status like ' table_lock% ';

  01.mysql> show status like ‘table_lock%‘;02.+-----------------------+-------+03.| Variable_name         | Value |04.+-----------------------+-------+05.| Table_locks_immediate | 70    |06.| Table_locks_waited    | 0     |07.+-----------------------+-------+08.2 rows in set (0.00 sec)09.mysql>

  Conditions for foreign keys:

  1, the table's storage engine is the InnoDB storage engine

  2, the type of the foreign key field in the table is consistent with the field type of the reference table

  3, foreign key field if one of the index types

  Create a syntax format for a foreign key field

  Foreign key (field name) Table name References reference table (field name)

  Synchronizing updates, synchronizing deletes

  Specify synchronous update, delete when building the table:

  On Update CASCADE Synchronous update

  On delete CASCADE Sync Delete

  Sample procedure:

  Create Database engine; Create a database named engine CREATE TABLE Engine.yg (//Creating an YG table in the engine library yg_id Int (3) primary key auto_increment, Name varchar (TEN) NOT NULL ) Engine=innodb; Specifies that the storage engine is InnoDB CREATE TABLE engine.gz (//Creating an YG table in the engine library gz_id Int (3), GZ Float (7,2) NOT NULL default 0.00, Foreign KEY (gz_id) references Engine.yg (yg_id)//Specify the Foreign key field gz_id corresponds to the ge_id of YG On UPDATE cascade ON DELETE CASCADE//Specify synchronous update, sync delete ) Engine=innodb; Use engine; Switch to engine database 1: Requirements: Insert a two-name entry for the YG table named Jim and Tom; Then insert an entry with the name Tom. Desc YG; Insert into YG (name) VALUES (' Jim '); Insert into YG (name) VALUES (' Tom '); Insert into YG (name) VALUES (' Tom '); 2: Requirements: Add the 2nd and 3rd persons to the GZ table with a salary of 10000, and 12000; Desc GZ; INSERT into GZ values (2,10000); INSERT into GZ values (3,12000); 3: Requirements: Add an employee to the YG table named Lucy; The salary of Lucy inserted in GZ table is 4000; Delete the employee information for Lucy in the YG table; Note: No payroll information will be gz_id=4 in GZ table after deletion Use SELECT * from GZ to see if there are also entries for gz_id 4 in GZ. Insert into YG (name) VALUES (' Lucy '); INSERT INTO GZ vlaues (4,4000); Delete from YG where yg_id=4; SELECT * from GZ; 4: Requirements Updated employee information for yg_id 2 in the YG table; Update YG set yd_id=8 where yg_id=2; 5, Requirements: Delete the foreign key in the GZ table; Note: When deleting a foreign key, first use show create Table from GZ; To see the name of the foreign key (note: The field named gz_id,gz_id is not a foreign key name when creating the foreign key). Show create table from GZ; First look at the build table procedure to find the Froeign key field ALTER TABLE GZ drop foreign key gz_ibfk_1; Delete foreign key Show create table from GZ; See the foreign key has been deleted again

  Summary: To create a foreign key, you must first understand the MySQL storage engine, only the InnoDB storage engine supports the creation of foreign keys, in MySQL5.6, the default storage engine is innodb!!!