How to create an association table in MySQL

Creating an association table in MySQL is a basic knowledge. The following describes how to create an association table in MySQL. If you are interested in creating an association table in MySQL, take a look.

Creating an association table in MySQL can be understood as a foreign key relationship between two tables, but the two tables must meet three conditions.
1. The two tables must be InnoDB data engines
2. The fields using the foreign key relationship must be indexed)
3. The fields using the foreign key relationship must be similar to the data type.
The following two tables are respectively created to illustrate:

Create table if not exists 'books '(
'Book _ id' smallint (6) not null auto_increment COMMENT 'book number ',
'Book _ name' char (20) not null comment 'title ',
'Book _ pic 'varchar (200) not null comment 'Cover ',
'Book _ author' char (20) not null comment' author ',
'Book _ pub' char (40) not null comment' press ',
'Book _ sort 'char (6) not null comment' category ',
'Book _ owner 'char (6) default null comment 'owner ',
'Book _ borrower 'Char (7) default null comment 'borrower ',
'Book _ borrower_time 'date default null comment 'Lending time ',
Primary key ('book _ id '),
INDEX (book_borrower ))
ENGINE = InnoDB character set utf8 COLLATE utf8_general_ci AUTO_INCREMENT = 5;
 
Create table if not exists 'parts '(
'Part _ id' smallint (6) not null comment 'Member number ',
'Part _ name' varchar (6) not null comment 'Member name ',
'Part _ mail' varchar (50) not null comment 'mailbox ',
'Part _ pass' varchar (20) not null comment 'Password ',
Primary key ('Part _ id '),
Foreign key (part_name) REFERENCES books (book_borrower) on delete cascade on update cascade)
ENGINE = InnoDB character set utf8 COLLATE utf8_general_ci;
Analyze the books and parts tables and create their associations. I used the book_borrower field of the books table to create an index and chose InnoDB as the table engine. In the parts table, the part_name field is a foreign key and is associated with the book_borrower field in the books table. Note that the two fields are both char and varchar strings. On delete cascade means that when the books table has related records deleted, the parts table will also delete the associated records. theoretically, the part_name field of the parts table must be indexed, but the experiment shows that the Association is automatically indexed.

Several common MySQL performance testing tools

Optimization instance for MySQL random Query

MySQL sorting usage

MySQL query cache Variables

How to enable SQL slow Query