MySQL 执行 UNION ALL` 语句报错- The used SELECT statements have a different number of columns

The used SELECT statements have a different number of columns

前期准备

1、数据库表

（1）unsigned_user 表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for unsigned_user
-- ----------------------------
DROP TABLE IF EXISTS `unsigned_user`;
CREATE TABLE `unsigned_user`  (
  `unsigned_user_id` int NOT NULL AUTO_INCREMENT COMMENT '未签名用户ID',
  `unsigned_user_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '未签名用户编码',
  `unsigned_user_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '未签名用户地址',
  `unsigned_user_action` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '未签名用户行为',
  PRIMARY KEY (`unsigned_user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of unsigned_user
-- ----------------------------
INSERT INTO `unsigned_user` VALUES (1, 'AD78589522', '浙江省杭州市', '已预订');
INSERT INTO `unsigned_user` VALUES (2, 'YG34687345', '四川省德阳市', '未预定');
INSERT INTO `unsigned_user` VALUES (3, 'HG83275685', '陕西省西安市', '已购买');

SET FOREIGN_KEY_CHECKS = 1;

（2）user 表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户编码',
  `user_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户地址',
  `user_pay_level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用户支付等级',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'AD78589522', '浙江省杭州市', 'A');
INSERT INTO `user` VALUES (2, 'YG34687345', '四川省德阳市', 'B');
INSERT INTO `user` VALUES (3, 'HG83275685', '陕西省西安市', 'D');

SET FOREIGN_KEY_CHECKS = 1;

2、SQL 语句

SELECT s.unsigned_user_code as userCode,s.unsigned_user_address as userAddress FROM unsigned_user as s
 
UNION ALL

SELECT u.user_code as userCode,u.user_address as userAddress,u.user_pay_level userPayLevel FROM `user` AS u;

3、报错

> 1222 - The used SELECT statements have a different number of columns

4、报错原因

我们在 SQL 语句中使用了 UNION 连接两张表时，查询字段数量不一致导致的。

5、解决方法

SELECT s.unsigned_user_code as userCode,s.unsigned_user_address as userAddress,null as userPayLevel FROM unsigned_user as s

UNION ALL

SELECT u.user_code as userCode,u.user_address as userAddress,u.user_pay_level userPayLevel FROM `user` AS u;

查询结果：

当然，除了添加 null as userPayLevel，也可以换成 '' as userPayLevel、0 as userPayLevel 等一切常量信息，只要保证两个 SQL 语句的查询字段数量一致即可。