mysql count(*)和count(1)及count(col)的区别测试

----查看表定义

(product)root@localhost [test]> show create table tab_t3;
Current database: test

+--------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                          |
+--------+---------------------------------------------------------------------------------------------------------------------------------------+
| tab_t3 | CREATE TABLE `tab_t3` (
  `c_varchar` varchar(5) DEFAULT NULL,
  `c_char` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

----查看表tab_t3的记录
(product)root@localhost [test]> select * from tab_t3;

+-----------+--------+
| c_varchar | c_char |
+-----------+--------+
| hello     | hello  |
+-----------+--------+
1 row in set (0.01 sec)

-----插入''值 ，并分别比较count(*)、count(1)和count(col)

(product)root@localhost [test]> insert into tab_t3 values('','');
Query OK, 1 row affected (0.00 sec)

(product)root@localhost [test]> select * from tab_t3;
+-----------+--------+
| c_varchar | c_char |
+-----------+--------+
| hello     | hello  |
|           |        |
+-----------+--------+
2 rows in set (0.00 sec)

(product)root@localhost [test]> select count(*) from tab_t3;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

(product)root@localhost [test]> select count(1) from tab_t3;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

(product)root@localhost [test]> select count(c_varchar) from tab_t3;
+------------------+
| count(c_varchar) |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

-----插入null值 ，并分别比较count(*)、count(1)和count(col)
(product)root@localhost [test]> insert into tab_t3 values(null,null);
Query OK, 1 row affected (0.00 sec)

(product)root@localhost [test]>  select count(c_varchar) from tab_t3;
+------------------+
| count(c_varchar) |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

(product)root@localhost [test]> select count(*) from tab_t3;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

(product)root@localhost [test]>  select count(1) from tab_t3;
+----------+
| count(1) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

 

结论：1.count(*)等于count(1)，而count(col)会忽略null值，而不忽略''值 ;
           2.''值不等于null值，''是空字符串。