将 MyISAM 键插入 INNODB 表

2022-01-18 00:00:00 tags mysql innodb myisam

我正在将此表结构用于类似 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 上的外键错误，即使我插入的数据是正确的(有效的 TAGS 别名和有效的产品 ID)

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.

相关文章