mysql - 如何检查引用的表是否不为空？

Question

例如，我有2张表:

CREATE TABLE IF NOT EXISTS master(
    master_id      INT          NOT NULL AUTO_INCREMENT,
    master_name    VARCHAR(15) NOT NULL,
    PRIMARY KEY(master_id)
) TYPE=InnoDB CHARACTER SET=UTF8;

和

CREATE TABLE IF NOT EXISTS slave(
    slave_id        INT          NOT NULL AUTO_INCREMENT,
    slave_name      VARCHAR(15)  NOT NULL,
    master_id       INT
    PRIMARY KEY (slave_id), 
    FOREIGN KEY (master_id) REFERENCES master(master_id)
) TYPE=InnoDB CHARACTER SET=UTF8; 

如何检查主表是否连接到从表，如果是，我想知道从表是否为空？

Answer 1

您可以通过连接检查两个表是否连接。

  SELECT COUNT(*) as number_of_connections
  FROM master m
  INNER JOIN slave s ON (s.master_id = m.master_id)
UNION ALL
  SELECT COUNT(*) as rows_in_slave
  FROM slave s2
UNION ALL
  SELECT COUNT(*) as rows_in_master
  FROM master m2

这个查询会返回3行，分别告诉你连接数，从表是否为空，主表是否为空。