对于mysql中数据为NULL引发的一些问题和思考

vlambda
2021-02-12

对于mysql中数据为NULL引发的一些问题和思考

首先创建测试表

CREATE TABLE `temp_person` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL, `phone` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

填充测试数据

INSERT INTO `mz_order`.`temp_person`(`id`, `name`, `phone`) VALUES (1, '张三', '159xxx001');INSERT INTO `mz_order`.`temp_person`(`id`, `name`, `phone`) VALUES (2, '李四', '159xxx002');INSERT INTO `mz_order`.`temp_person`(`id`, `name`, `phone`) VALUES (3, '王五', '159xxx003');INSERT INTO `mz_order`.`temp_person`(`id`, `name`, `phone`) VALUES (4, '马六', '159xxx004');INSERT INTO `mz_order`.`temp_person`(`id`, `name`, `phone`) VALUES (5, '王一', '159xxx005');INSERT INTO `mz_order`.`temp_person`(`id`, `name`, `phone`) VALUES (6, '王二', '159xxx006');INSERT INTO `mz_order`.`temp_person`(`id`, `name`, `phone`) VALUES (7, '', '159xxx007');INSERT INTO `mz_order`.`temp_person`(`id`, `name`, `phone`) VALUES (8, NULL, '159xxx008');

查一下数据

问题1：count数据

select count(1),count(id),count(`name`),count(phone) from temp_person;

执行结果如下：

对于mysql中数据为NULL引发的一些问题和思考

结论：当某列存在null值时，例如name列可能会有空值，使用count(name)会丢失数据。

解决方案：当需要使用count时，可以使用count(1)或count(主键)进行统计。

问题2：distinct 数据

SELECT count( DISTINCT `name`, phone ), count( DISTINCT phone, `name` ) FROM temp_person;

执行结果如下：

对于mysql中数据为NULL引发的一些问题和思考

第二列也不存重复值，实际列为8，但查询结果为7。

结论：当使用count(distinct 列1,列2)查询时，如果其中一列（不分顺序）为null，那么即使另一列有值，查询结果也会丢失。

问题3：select数据

查询非等于 <>或!=，会导致数据为Null值的结果丢失。

SELECT *FROM temp_person where `name`<>'张三'

例如：查询出名字不等于'张三'的数据

执行结果如下：

对于mysql中数据为NULL引发的一些问题和思考

结论：使用非等于，会使数据为Null的数据丢失。

解决方法：只需要在查询结果后面加上为Null值的结果即可。执行sql如下：

SELECT *FROM temp_person where `name`!='张三' or isnull(`name`)

对于mysql中数据为NULL引发的一些问题和思考

问题4：空指针异常

如果某列存在null值，可能会导致sum(column)的返回结果为null，而非0.

改造测试表，增加列age。

ALTER TABLE `mz_order`.`temp_person` ADD COLUMN `age` smallint(6) NULL COMMENT '年龄' AFTER `phone`;

手动赋值

对于mysql中数据为NULL引发的一些问题和思考

开始查询

SELECT sum( age ) FROM temp_person WHERE id >6

对于mysql中数据为NULL引发的一些问题和思考

结论：当查询的数据为null时，使用sum函数会返回null而不是0，就可能会导致程序空指针。

解决方法：增加判空即可

SELECT ifnull(sum( age ) ,0)FROM temp_person WHERE id >6

对于mysql中数据为NULL引发的一些问题和思考

问题5：查询如果某列为空给默认值

场景：比如说传输数据，需要name不为空的数据传输过去。如果name为空，则给一个默认值。

例如：

SELECT `name` as '原始数据', ifnull( NAME, '默认' ) AS name1, IF( NAME IS NULL, '默认', NAME ) AS name2 , if(name='','默认',name) as name3FROM temp_person;

可以发现，列出的3种方式对于null或空都会存在空值，那么如何避免呢？

解决方法：

SELECT `name` AS '原始数据', IFNULL( IF ( `name` = '', '默认', `name` ), '默认' ) AS name1 FROM temp_person;

vlambda博客
学习文章列表