主页 / dba / 问题 / 57731
Accepted
HTF
Asked: 2014-01-29 14:19:10 +0800 CST

MySQL：连接查询

  • 772

我有以下两个表：

mysql> DESC domains;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| id              | int(11)      | NO   | PRI | NULL    | auto_increment |
| name            | varchar(255) | NO   | UNI | NULL    |                |
| master          | varchar(128) | YES  |     | NULL    |                |
| last_check      | int(11)      | YES  |     | NULL    |                |
| type            | varchar(6)   | NO   |     | NULL    |                |
| notified_serial | int(11)      | YES  |     | NULL    |                |
| account         | varchar(40)  | YES  |     | NULL    |                |
+-----------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> DESC records;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(11)   | NO   | PRI | NULL    | auto_increment |
| domain_id   | int(11)      | YES  | MUL | NULL    |                |
| name        | varchar(255) | YES  | MUL | NULL    |                |
| type        | varchar(6)   | YES  |     | NULL    |                |
| content     | varchar(455) | YES  |     | NULL    |                |
| ttl         | int(11)      | YES  |     | NULL    |                |
| prio        | int(11)      | YES  |     | NULL    |                |
| change_date | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

我想加入domains.notified_serial下面的查询，id字段在两个表上都是通用的，请问下面的查询是否是最优的？

mysql> SELECT * FROM records WHERE name IN ('example.com', 'www.example.com') AND type = 'A';
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
| id         | domain_id | name              | type | content         | ttl  | prio | change_date |
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
| 2926120529 |    620750 | example.com     | A      | 192.168.1.100   |  600 |    0 |  1325776553 |
| 2926120595 |    620750 | www.example.com | A      | 192.168.1.100   |  600 |    0 |  1325776553 |
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
4 rows in set (0.00 sec)

编辑：2014 年 1 月 29 日星期三 22:39:41 GMT

mysql> SHOW CREATE TABLE domains\G
*************************** 1. row ***************************
       Table: domains
Create Table: CREATE TABLE `domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `master` varchar(128) DEFAULT NULL,
  `last_check` int(11) DEFAULT NULL,
  `type` varchar(6) NOT NULL,
  `notified_serial` int(11) DEFAULT NULL,
  `account` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=623933 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE records\G
*************************** 1. row ***************************
       Table: records
Create Table: CREATE TABLE `records` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `type` varchar(6) DEFAULT NULL,
  `content` varchar(455) DEFAULT NULL,
  `ttl` int(11) DEFAULT NULL,
  `prio` int(11) DEFAULT NULL,
  `change_date` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `rec_name_index` (`name`),
  KEY `nametype_index` (`name`,`type`),
  KEY `domain_id` (`domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8544581783 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql join

1 个回答

  1. Best Answer

    也许是这样的？

    SELECT r.*,d.notified_serial 
FROM records r
left join domains d
    on d.id  = r.domain_id
WHERE name IN ('example.com', 'www.example.com') AND type = 'A';
    • 0

相关问题