CREATE TABLE `user_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='用户表';
INSERT INTO `test`.`user_info`(`id`, `name`, `age`) VALUES (1, 'aaa', 11);
INSERT INTO `test`.`user_info`(`id`, `name`, `age`) VALUES (2, 'bbb', 22);
INSERT INTO `test`.`user_info`(`id`, `name`, `age`) VALUES (3, 'ccc', 33);
数据如下
union操作
select id FROM user_info WHERE age = 11 UNION SELECT id FROM user_info WHERE age = 22
结果
+----+
| id |
+----+
| 1 |
| 2 |
+----+
union all
mysql> select id FROM user_info WHERE age > 11 UNION ALL SELECT id FROM user_info WHERE age > 22;
+----+
| id |
+----+
| 2 |
| 3 |
| 3 |
+----+
结论:union会去除重复,union all不会去除重复的数据;
筛选的列数不一致
mysql> select id,name FROM user_info WHERE age = 11 UNION ALL SELECT id FROM user_info WHERE age = 22;
报错
1222 - The used SELECT statements have a different number of columns
筛选列数起别名
mysql> select id,name name1 FROM user_info WHERE age = 11 UNION ALL SELECT id,name name2 FROM user_info WHERE age = 22;
+----+-------+
| id | name1 |
+----+-------+
| 1 | aaa |
| 2 | bbb |
+----+-------+
2 rows in set (0.02 sec)
结论:起别名以最开始出现的列别名为准。
数据类型不一致进行union 表中id是int的 name 是varchar类型的。
mysql> select id FROM user_info WHERE age = 11 UNION ALL SELECT name FROM user_info WHERE age = 22;
+-----+
| id |
+-----+
| 1 |
| bbb |
+-----+
2 rows in set (0.02 sec)
结论:不同数据类型的可以合并为1列。但业务层返回数据接收处理时可能会出问题。建议数据列最好一致。
总结:union数据会去重,union all 数据不会去重,不同数据类型cloumn可以合并为1列。