MySQL DML操作--------合并查询结果实战
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL DML操作--------合并查询结果实战,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5187字,纯文字阅读大概需要8分钟。
内容图文
![MySQL DML操作--------合并查询结果实战](/upload/InfoBanner/zyjiaocheng/486/781920f66a084e9c9386b0f43e6f1a6b.jpg)
1. 背景
* 全并查询结果是将多个 select 语句的查询结果合并到一起。
* 参与合并的结果集需要字段统一。
* 字段可以用空字符串‘‘代替。
2. 合并查询结果实战 [ users1 and users2 ]
* 查看 users1 表和 users2 表结构
mysql> desc users1;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | | NULL | |
| sex | enum(‘M‘,‘F‘) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> desc users2;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(64) | NO | | NULL | |
| sex | enum(‘M‘,‘F‘) | NO | | NULL | |
| age | int(11) | NO | | NULL | |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
* 查看 users1 表和 users2 表数据
users1和users2表中有相同字段 tom
mysql> select * from users1;
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
| 1 | tom | M | 25 |
| 2 | jak | F | 42 |
+----+------+-----+-----+
2 rows in set (0.00 sec)
mysql> select * from users2;
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | tom | M | 25 |
| 2 | lisea | M | 42 |
+----+-------+-----+-----+
2 rows in set (0.00 sec)
* union 合并并去重
mysql> (select * from users1) union (select * from users2);
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | tom | M | 25 |
| 2 | jak | F | 42 |
| 2 | lisea | M | 42 |
+----+-------+-----+-----+
3 rows in set (0.00 sec)
* union all 只全并不去重
mysql> (select * from users1) union all (select * from users2);
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | tom | M | 25 |
| 2 | jak | F | 42 |
| 1 | tom | M | 25 |
| 2 | lisea | M | 42 |
+----+-------+-----+-----+
4 rows in set (0.01 sec)
* 查看union 性能分析
[ 使用了临时表 ]
mysql> explain (select * from users1) union (select * from users2);
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | users1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
| 2 | UNION | users2 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)
* 查看union all 性能分析
[ 未使用临时表 ]
mysql> explain (select * from users1) union all (select * from users2);
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | PRIMARY | users1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
| 2 | UNION | users2 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
3. union 与 union all 总结
* union 相对于 union all多了一步去重操作,此操作会创建临时表,降低性能。
* 当两边结果集数据相对都确定了唯一性,推荐使用union all。
4. 总结
以需求驱动技术,技术本身没有优略之分,只有业务之分。
MySQL DML操作--------合并查询结果实战
标签:语句 partition 统一 title oat idt lock var lte
本文系统来源:http://lisea.blog.51cto.com/5491873/1943768
内容总结
以上是互联网集市为您收集整理的MySQL DML操作--------合并查询结果实战全部内容,希望文章能够帮你解决MySQL DML操作--------合并查询结果实战所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。