Reasons and solutions why MySQL cannot create foreign keys

This article mainly introduces the reasons and solutions for MySQL's inability to create foreign keys, and then gives you timely knowledge about MySQL's inability to create foreign keys and query the properties of foreign keys. Friends who are interested should take a look.

When associating two tables, foreign keys cannot be created. From this blog, we can see that the problem lies in the consistency of the Charset and Collate options in point 6 at the table level and field level. The encoding charset and collate of my two tables are inconsistent, and both tables execute the SQL statement:

alter table 表名 convert to character set utf8;

Perfectly solves the problem;

ps: Let’s take a look at MySQL’s inability to create foreign keys and query the properties of foreign keys

MyISAM and InnoDB explanation

InnoDB and MyISAM are the two most commonly used table types by many people when using MySQL. Both table types have their own advantages and disadvantages, depending on the specific application. The basic difference is: the MyISAM type does not support advanced processing such as transaction processing, but the InnoDB type does. The MyISAM type table emphasizes performance, and its execution times are faster than the InnoDB type, but it does not provide transaction support, while InnoDB provides transaction support and advanced database functions such as foreign keys.

　The following are some details and specific implementation differences:

　1.InnoDB does not support FULLTEXT type indexes.

　◆2.InnoDB does not save the specific number of rows in the table. That is to say, when executing select count(*) from table, InnoDB has to scan the entire table to calculate how many rows there are, but MyISAM only needs a simple Just read out the number of saved lines. Note that when the count(*) statement contains a where condition, the operations of the two tables are the same.

　◆3. For fields of type AUTO_INCREMENT, InnoDB must contain an index with only this field, but in the MyISAM table, a joint index can be established with other fields.

　◆4.When DELETE FROM table, InnoDB will not re-create the table, but will delete it row by row.

　◆5. The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to a MyISAM table, and then change it to an InnoDB table after importing the data. However, for the additional InnoDB used Tables with attributes (such as foreign keys) do not apply.

In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table, for example, update table set num=1 where name like “%aaa%”

The main difference between the two types is that Innodb supports transaction processing, foreign keys and row-level locks. MyISAM does not support it. Therefore, MyISAM is often considered to be only suitable for use in small projects.

From the perspective of users who use MySQL, Innodb and MyISAM are both preferred. If the database platform wants to meet the requirements: 99.9% stability, convenient scalability and high availability, MyISAM is definitely the best choice. First choice.

The reasons are as follows:

1. Most of the projects carried on the platform are projects with more reading and less writing, and the reading performance of MyISAM is much better than Innodb .

　2. MyISAM's index and data are separated, and the index is compressed, which increases the memory usage accordingly. It can load more indexes, and Innodb's index and data are tightly bound. No compression is used, which will cause Innodb to be much larger than MyISAM.

3. It often happens every 1 or 2 months that an application developer accidentally updates a table where written in the wrong range, causing the table to be unable to be used normally. At this time, the superiority of MyISAM is reflected. Just take out the file of the corresponding table from the compressed package copied that day, put it in a database directory, then dump it into sql and import it back to the main database, and fill in the corresponding binlog. If it's Innodb, I'm afraid it can't be so fast. Don't tell me to ask Innodb to regularly use the export xxx.sql mechanism to back up, because the smallest database instance has a data volume of dozens of gigabytes.

4. From the perspective of application logic, select count(*) and order by are the most frequent operations, accounting for more than 60% of the total sql statements, and this operation Innodb actually It can also lock tables. Many people think that Innodb uses row-level locks. That is only valid for where its primary key, and non-primary keys will lock the entire table.

5. There are often many application departments that need me to provide them with data on certain tables on a regular basis. MyISAM is very convenient. Just send them the frm.MYD and MYI files corresponding to that table and let them Just start the database of the corresponding version yourself, but Innodb needs to export xxx.sql, because just giving the file to others will not be able to use it due to the influence of the dictionary data file.

6. Compared with MyISAM for insert write operations, Innodb still cannot reach the write performance of MyISAM. If it is for index-based update operations, although MyISAM may be inferior to Innodb, but with such high concurrent writes, Whether the slave database can catch up is also a problem. It is better to solve it through a multi-instance sub-database and sub-table architecture.

7. If MyISAM is used, the merge engine can greatly speed up the development of the application department. They only need to perform some select count(*) operations on the merge table, which is very suitable for large projects with a total amount of about hundreds of millions. rows A business table of a certain type (such as logs, survey statistics).

　　当然Innodb也不是绝对不用，用事务的项目就用Innodb的。另外，可能有人会说你MyISAM无法抗太多写操作，但是可以通过架构来弥补。

SELECT * FROM information_schema.key_column_usage WHERE table_name='表名' ;
show create table 表名 ;

MySQL学习之外键的图文详解

有关MySQL数据库中的外键约束详解

MySQL 的外键与参照完整性: Part 1_PHP教程

The above is the detailed content of Reasons and solutions why MySQL cannot create foreign keys. For more information, please follow other related articles on the PHP Chinese website!