本文介绍了MyISAM Engine表关系(MySQL)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我使用的主机仅支持MySQL的MyISAM表引擎.我正在尝试使用php和MySQL创建CMS,但是在解决如何在表之间创建关系时遇到了问题.例如,该系统的功能之一就是能够为文章/博客帖子分配标签,类似于堆栈溢出如何在其问题上添加标签.

I’m using a host which only supports MyISAM tables engines for MySQL. I’m trying to create a CMS using php and MySQL, however, I’m having issues working out how to create relationships between the tables. For example, one of the features within this system is being able to assign tags to an article/blogpost, similar to how stack overflow has tags on their questions.

我的问题是,由于无法将表更改为使用InnoDB,因此如何在两个表之间形成关系?我无法使用外键,因为MyISAM不支持或至少不强制使用它们.

My question is, as I cannot change my tables to use InnoDB, how can I form a relationship between the two tables? I am unable to use foreign keys as they are not supported in MyISAM, or at least not enforced.

到目前为止,我在搜索时发现的所有内容都是通过确保每次更新多个表来通过PHP对其进行跟踪,但是在MySQL方面必须有一种方法来实现这一点.

So far, all I've found when searching is keeping track of it through PHP by ensuring that I update multiple tables at a time, but there must be a way of doing this on the MySQL side.

下面是文章"和标签"表的示例.

Below are examples of the Article and Tag tables.


    +---------------------------+  +---------------------------+
    |         Article           |  |            Tags           |
    +---------------------------+  +---------------------------+
    | articleID    int(11)      |  | tagID         int(11)     |
    | title        varchar(150) |  | tagString     varchar(15) |
    | description  varchar(150) |  +---------------------------+
    | author       varchar(30)  |
    | content      text         |
    | created      datetime     |
    | edited       datetime     |
    +---------------------------+

我在此站点上找到了大量相关问题,但是其中大多数是InnoDB,我不能这样做,因为我的主机不支持它.

I’ve found loads of related questions on this site, but most of them InnoDB, which I cannot do as my host does not support it.

我找到了一种解决方案.我添加了另一个名为ArticleTags的表

I've found a solution (kind of). I've added another table called ArticleTags


    +---------------------------+
    |        ArticleTags        |
    +---------------------------+
    | articleID    int(11)      |
    | tagID        int(11)      |
    +---------------------------+

此查询返回正确的结果,但是我不确定这是否有点破解,或者是否有更好的方法.

This query returns the correct result, but I'm not sure if it's a bit of a hack, or if there is a better way to do it.


SELECT `tagString`
FROM `Tags`
WHERE id
IN (
     SELECT `tagID`
     FROM `ArticleTags`
     WHERE `articleID` = :id
   )
ORDER BY  `Tags`.`tagString`

有人可以告诉我这是否正确吗?

Can someone tell me if this this right?

推荐答案

尝试触发:

  • Enforcing Foreign Keys Programmatically in MySQL
  • Emulating Cascading Operations From InnoDB to MyISAM Tables

创建父表:

CREATE TABLE myisam_parent
(
 mparent_id INT NOT NULL,
 PRIMARY KEY (mparent_id)
) ENGINE=MYISAM;

创建子表:

CREATE TABLE myisam_child
(
 mparent_id INT NOT NULL,
 mchild_id INT NOT NULL,
 PRIMARY KEY (mparent_id, mchild_id)
) ENGINE = MYISAM;

创建触发器(使用DELIMITER):

Create trigger (with DELIMITER):

DELIMITER $$
CREATE TRIGGER insert_myisam_child
BEFORE INSERT ON myisam_child
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM myisam_parent WHERE mparent_id=new.mparent_id)=0 THEN
        INSERT error_msg VALUES ('Foreign Key Constraint Violated!');//Custom error
    END IF;
END;$$
DELIMITER ;

测试案例:

尝试插入(在myisam_parent中创建3行,在myisam_child中创建6行):

Test case:

Try insert (create 3 lines in myisam_parent and 6 lines in myisam_child):

INSERT INTO myisam_parent VALUES (1), (2), (3);
INSERT INTO myisam_child VALUES (1,1), (1,2), (2,1), (2,2), (2,3), (3,1);

尝试插入:

INSERT INTO myisam_child VALUES (7, 1);

返回此错误:


注意:


Note:

此示例适用于INSERT,适用于具有DELETE和UPDATE读取链接的触发器"(在问题开头)

This example is for INSERT, for "triggers" with DELETE and UPDATE read link (at the beginning the question)

这篇关于MyISAM Engine表关系(MySQL)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 03:13
查看更多