将MyISAM密钥插入到INNODB表中 [英] Insert MyISAM key into a INNODB table

问题描述

我正在将此表格结构用于TAGs系统之类的毒素"

I'm using this table structure for a 'toxi' like TAGs system

table TAGS

+--------+-------------------+
| alias  | isactive          |  varchar(55), tinyint(1)
+--------+-------------------+
| party  | 1                 |

Engine: MyISAM (because I use some 'autocomplete' using this table (field:alias) for 
a %xxx% search

table TAGREL

+-------------+-------------------+
| tags_alias  | productID         |  varchar(55), int(11)
+-------------+-------------------+
|   party     | 15                |

Engine: InnoDB (i dont need full search here)
This TAGREL table uses tags.alias as FK (on update cascade, on delete cascade)  and 
product id as FK (on update no action, on delete cascade)

我的意思是，整个想法是，当我更新某些标签名称(或删除它)甚至删除产品时，TAGREL上的关系也会自动更新.

I mean, the whole idea is that, when I update some tag name (or erase it) or even delete a product, the relation on the TAGREL is auto updated.

但是我什至无法将记录添加到TAGREL表中，即使我插入的数据正确(有效的TAGS别名和有效的产品ID)，它也表示表TAGS错误的外键

But I cant even ADD a record to TAGREL table, it says a foreign key on table TAGS error, even if the data I'm inserting is correct (a valid TAGS alias and a valid product ID)

我无法在MySQL上执行此类操作吗? 唯一的解决方案(因为我需要在TAGS表上进行全面搜索)是每当我更新一些标签或擦除产品时手动更新tagrel?

I can't do this kind of thing on MySQL? The only solution (as I NEED the full search on the TAGS table) is to manually update the tagrel whenever I update some tag OR erase a product?

谢谢.

推荐答案

您不能在引用MyISAM表的InnoDB表中创建外键.

You cannot create foreign keys in InnoDB tables referencing MyISAM tables.

外键定义必须满足以下条件:

Foreign keys definitions are subject to the following conditions:

两个表都必须是InnoDB表，并且不能是TEMPORARY表.

Both tables must be InnoDB tables and they must not be TEMPORARY tables.

http://dev.mysql. com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

因此，是的，如果不更改存储引擎，则必须从应用程序代码中手动执行约束.

So yes, you will have to manually enforce the constraints from your application code if you do not change the storage engine.

LIKE '%XXX%'搜索不是全文搜索；除非您实际上指定了全文索引并且正在使用全文匹配功能，否则您无需使用MyISAM引擎.

LIKE '%XXX%' searches are not fulltext; unless you actually specified a fulltext index and are using fulltext matching functions, you do not need to use the MyISAM engine.

这篇关于将MyISAM密钥插入到INNODB表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！