mysql外键不起作用

Question

我有两个表:

CREATE TABLE IF NOT EXISTS treaties(
id INT NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
PRIMARY KEY(id)
)ENGINE=InnoDB;

和

CREATE TABLE IF NOT EXISTS items(
id INT NOT NULL AUTO_INCREMENT,
treaty INT NOT NULL,
item varchar(20),
PRIMARY KEY(id),
FOREIGN KEY (treaty) REFERENCES treaties(id)
      ON UPDATE RESTRICT
      ON DELETE RESTRICT
)ENGINE=InnoDB;

之后，我在每个表中插入了几行，但值 treaties.id 和 items.treaty 是相同的。当我运行时

EXPLAIN SELECT * 
FROM `items` 
JOIN `treaties` ON `items`.`treaty` = `treaties`.`id` 
WHERE 1

我得到:

id | select_type | table   | type | possible_keys | key | key_len | ref | rows | Extra
1  | SIMPLE      | treaties| ALL  | PRIMARY       | NULL|   NULL  | NULL|   3  |
1  | SIMPLE      | items   | ALL  | treaty        | NULL|   NULL  | NULL|   4  | Using where; Using join buffer

我认为如果我在 items.treaty 和 treaties.id 之间有外键，则必须使用此键并且类型不能是 ALL。怎么了？

请帮帮我!

谢谢!

Answer 1

如 the manual 中所述:

The output from EXPLAIN shows ALL in the type column when MySQL uses a table scan to resolve a query. This usually happens under the following conditions:

[...]

• The table is so small that it is faster to perform a table scan than to bother with a key lookup. This is common for tables with fewer than 10 rows and a short row length. Don't worry in this case.