[Mysql 不可忽略的细节] #Mysql

Tip.md

count()

• count(1) 针对每一行，不管是否为null;
• count(*) 针对总数也不管是否为null;
• count(field) 针对该列，会忽略为null的值(空值会计算在内)

DROP TABLE IF EXISTS `counttest`;
CREATE TABLE `counttest` (
  `name` char(1) DEFAULT NULL,
  `age` char(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of counttest
-- ----------------------------
INSERT INTO `counttest` VALUES ('a', '14');
INSERT INTO `counttest` VALUES ('a', '15');
INSERT INTO `counttest` VALUES ('a', '15');
INSERT INTO `counttest` VALUES ('b', null);
INSERT INTO `counttest` VALUES ('b', '16');
INSERT INTO `counttest` VALUES ('c', '17');
INSERT INTO `counttest` VALUES ('d', null);
INSERT INTO `counttest` VALUES ('e', '');

SELECT `name`,count(*),count(1),count(`name`),count(age) from	counttest GROUP BY `name`

Null

• is NULL 只能判断value 为null，而空的则判断不了 SELECT * FROM counttest WHERE age is NULL;

• <> '' 可以过滤掉为空或null的记录？ SELECT * FROM counttest WHERE age <> '';