©2020-2024 ioDraw All rights reserved,
Privacy Policy
在使用列级别权限时发现，当表中空值行，列中也有空值时，count(*)和count(id)返回值不一样。
问题重现：在用户拥有某张表的某两列的权限时，select count(*)是否能返回结果时，发现select count(id)的结果与select
count(*)不一样
执行的sql:
CREATE TABLE Test_001( ID INT, ENAME VARCHAR(20), JOB VARCHAR(20), MGR INT,
HIREDATE DATE, SAL INT, COMM INT ); INSERT INTO Test_001 VALUES
(1,'A','CLERK',7902,'1980-12-17',800,NULL),
(2,'B','SALESMAN',7698,'1981-2-20',1600,300),
(3,'C','SALESMAN',7698,'1981-2-22',1250,500),
(4,'D','MANAGER',7839,'1981-4-2',2975,NULL),
(5,'F','SALESMAN',7698,'1981-9-28',1150,1400),
(6,'E','MANAGER',7839,'1981-5-1',2850,NULL),
(7,'G','MANAGER',7839,'1981-6-9',2450,NULL),
(8,'H','PRESIDENT',NULL,'1981-11-17',5000,NULL),
(9,'I','SALESMAN',7698,'1981-9-8',1500,0),
(10,'J','CLERK',7698,'1981-12-8',950,NULL),
(null,null,null,null,null,null,NULL,null); CREATE USER 'user11'@'%' IDENTIFIED
BY '123456'; GRANT select(ID ) ON `test`.`Test_001` TO 'user11'@'%' ;
使用user11登陆后,执行
select count(*) from Test_001;返回11行 select count (ID) from Test_001; 返回10行
当count(*)时, count函数返回的是该表的行数，null值不排除。
当count(列名)时，count函数返回的是该列除了null以外的行数
即count(*)-count(列名)的值为该列的null值个数