MySql内存引擎不检查更新外键？ [英] MySql memory engine do not check on update foreign key?

问题描述

为了更好地解释，这里有一个InnoDb中的类似的例子

pre $ CREATE TABLE testInnoDb1（
c1 int（11）PRIMARY KEY
）ENGINE = InnoDB;

CREATE TABLE testInnoDb2（
c1 int（11）PRIMARY KEY，
FOREIGN KEY（c1）REFERENCES testInnoDb1（c1）ON UPDATE CASCADE ON DELETE CASCADE
）ENGINE = InnoDB的;

INSERT INTO testInnoDb1（c1）VALUES（42）;
INSERT INTO testInnoDb2（c1）VALUES（42）;
- INSERT INTO testInnoDb2（c1）VALUES（64）; - 这将失败
- UPDATE testInnoDb2 SET c1 = 128; - 这会失败


与MEMORY相同
$ b $ CREATE TABLE testMem1（
c1 int（11）PRIMARY KEY
）ENGINE = MEMORY; b

  
 
 CREATE TABLE testMem2（
 c1 int（11）PRIMARY KEY，
 FOREIGN KEY（c1）REFERENCES testMem1（c1）ON UPDATE CASCADE ON DELETE CASCADE 
）ENGINE = MEMORY; 
 
 INSERT INTO testMem1（c1）VALUES（42）; 
 INSERT INTO testMem2（c1）VALUES（42）; 
  -  INSERT INTO test2（c1）VALUES（64）; - 这会失败
 UPDATE testMem2 SET c1 = 128; - 这应该失败？ 
  

我的问题是：为什么更新一致性不被内存引擎维护？这是一个错误还是因为任何真正的原因？任何引用到这个已知的错误？

作为额外的信息，我用于开发我的Linux版本库仍然是15.1（5.1）

解决方案

MySQL目前不支持外键约束检查：

lockquote
MySQL 5.6参考手册

13.1.17.3使用FOREIGN KEY约束

外键和其他MySQL语句

重要

对于不支持外键的存储引擎（如MyISAM），MySQL服务器解析并忽略外键规范。

 <$>第15章其他存储引擎c $ c>特性MyISAM内存InnoDB存档NDB 
外键支持否否是否否
  

15.3内存存储引擎

表15.4内存存储引擎特性

pre code外键支持否

PS从15.3开始，InnoDB（有FKs）和MEMORY（没有）：

性能特征

尽管MEMORY表在内存中的处理速度比在繁忙服务器上的InnoDB表速度要快，目的查询，或在读/写的工作量下。

After making a few tests, I found that foreign keys on MEMORY engine miss one of the checks for consistency.

To explain better, here a similar example in InnoDb

CREATE TABLE testInnoDb1 (
    c1 int(11) PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE testInnoDb2 (
    c1 int(11) PRIMARY KEY,
    FOREIGN KEY (c1) REFERENCES testInnoDb1(c1) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO testInnoDb1 (c1) VALUES (42);
INSERT INTO testInnoDb2 (c1) VALUES (42);
-- INSERT INTO testInnoDb2 (c1) VALUES (64); -- This will fail
-- UPDATE testInnoDb2 SET c1=128; -- This will fail

And the same with MEMORY

CREATE TABLE testMem1 (
    c1 int(11) PRIMARY KEY
) ENGINE=MEMORY;

CREATE TABLE testMem2 (
    c1 int(11) PRIMARY KEY,
    FOREIGN KEY (c1) REFERENCES testMem1(c1) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=MEMORY;

INSERT INTO testMem1 (c1) VALUES (42);
INSERT INTO testMem2 (c1) VALUES (42);
-- INSERT INTO test2 (c1) VALUES (64); -- This will fail
UPDATE testMem2 SET c1=128; -- This should fail?

My question is: Why the update consistency is not maintained with the memory engine? Is that a bug or it is for any real reason? Any reference to a known bug for this?

As additional info, I am using for development the repository release on my Linux which is still 15.1 (5.1)

解决方案

MySQL does not currently support foreign key constraint checking:

MySQL 5.6 Reference Manual

13.1.17.3 Using FOREIGN KEY Constraints
Foreign Keys and Other MySQL Statements

Important
For storage engines that do not support foreign keys (such as MyISAM), MySQL Server parses and ignores foreign key specifications.

Chapter 15 Alternative Storage Engines
Table 15.1 Storage Engines Feature Summary

Feature              MyISAM  Memory  InnoDB  Archive  NDB
Foreign key support  No      No      Yes     No       No

15.3 The MEMORY Storage Engine
Table 15.4 MEMORY Storage Engine Features

Foreign key support     No  

PS From 15.3 re InnoDB (which has FKs) vs MEMORY (which does not):

Performance Characteristics
Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload.

这篇关于MySql内存引擎不检查更新外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！