Shulou(Shulou.com)06/01 Report--

This article mainly tells you how to solve the problem that MySQL is unable to create foreign keys. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that the detailed methods of how to solve the problem that MySQL can not create foreign keys can bring you some practical help.

A foreign key cannot be created when associating two tables, and as you can see from this blog, 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 table name convert to character set utf8

Solve the problem perfectly

Ps: let's take a look at the properties of foreign keys that MySQL cannot create and query.

MyISAM and InnoDB explanation

InnoDB and MyISAM are the two most commonly used table types for many people when using MySQL. These two table types have their own advantages and disadvantages, depending on the specific application. The basic difference is that the MyISAM type does not support advanced processing such as transactions, while the InnoDB type does. Tables of type MyISAM emphasize performance and perform several times faster than type InnoDB, but do not provide transaction support, while InnoDB provides advanced database functions such as transaction support and foreign keys.

Here are some of the details and differences in implementation:

◆ 1.InnoDB does not support indexes of type FULLTEXT.

The specific number of rows of the table is not saved in ◆ 2.InnoDB, that is, when select count (*) from table is executed, InnoDB scans the entire table to calculate how many rows there are, but MyISAM can simply read out the number of saved rows. Note that when the count (*) statement contains the where condition, the operation of the two tables is the same.

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

When ◆ 4.DELETE FROM table, InnoDB does not re-establish the table, but deletes it row by row.

The ◆ 5.LOAD TABLE FROM MASTER operation does not work for InnoDB, and the solution is to change the InnoDB table to MyISAM table first, and then to InnoDB table after importing data, but it does not apply to tables that use additional InnoDB features, such as foreign keys.

In addition, the row lock of the InnoDB table is not absolute. If the MySQL cannot determine the range to scan when executing a SQL statement, the InnoDB table will also lock the whole table, such as update table set num=1 where name like "% aaa%"

The main difference between the two types is that Innodb supports transactions with foreign keys and row-level locks. But MyISAM does not support it. So it is easy to think that MyISAM is only suitable for small projects.

From the point of view of users using MySQL, both Innodb and MyISAM are preferred. If the database platform is to meet the requirements of 99.9% stability, convenient scalability and high availability, MyISAM is definitely the first choice.

The reasons are as follows:

1. Most of the projects carried on the platform are projects that read more and write less, and the read performance of MyISAM is much better than that of Innodb.

2. The index of MyISAM is separated from the data, and the index is compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is that indexes and data are tightly bundled, and no compression is used, resulting in Innodb being larger than MyISAM.

3. Often every 2 months, application developers accidentally update a table where to write the wrong scope, resulting in the table can not be used properly, this time the superiority of MyISAM is reflected, casually take out the files of the corresponding table from the compressed package copied on the same day, casually put them in a database directory, and then dump into sql and then import back to the main database, and add the corresponding binlog. If it is Innodb, I am afraid it is impossible to have such a fast speed. Don't tell me to let Innodb back up regularly with the export xxx.sql mechanism, because the smallest database instance basically has dozens of gigabytes of data.

4. In terms of contact application logic, select count (*) and order by are the most frequent, accounting for more than 60% of the total sql statements. In fact, this operation Innodb will also lock the table. Many people think that Innodb is a row-level lock, but where is valid for its primary key, and non-primary keys will lock the whole table.

5, there are often many application departments need me to give them some table data on a regular basis, MyISAM is very convenient, as long as send them the corresponding table frm.MYD,MYI file, let them start in the corresponding version of the database on the line, and Innodb needs to export xxx.sql, because only to other people's files, affected by dictionary data files, the other party is unable to use.

6. If compared with MyISAM and insert write operation, Innodb can not achieve the write performance of MyISAM. For index-based update operation, although MyISAM may be inferior to Innodb, it is also a problem whether the write with high concurrency can catch up with the database. It is better to solve the problem through multi-instance sub-database sub-table architecture.

7. If MyISAM is used, the merge engine can greatly accelerate the development speed of the application department. As long as they do some select count (*) operations on this merge table, it is very suitable for a certain type of rows business table (such as logs, surveys and statistics) with a total amount of hundreds of millions of large projects.

Of course, Innodb is not absolutely unnecessary, using transactional items to use Innodb. In addition, some people may say that your MyISAM can't resist too many writes, but you can make up for it through architecture.

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

How to solve the MySQL can not create foreign keys detailed method to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.