count() in mysql
it’s a little different from oracle in mysql when deal with NULL.
mysql> use test;
Database changed
mysql> create table diffcount(id char(8));
Query OK, 0 rows affected (0.01 sec)mysql> insert into diffcount values(’hello’);
Query OK, 1 row affected (0.00 sec)mysql> insert into diffcount values(’world’);
Query OK, 1 row affected (0.00 sec)mysql> insert into diffcount values(null);
Query OK, 1 row affected (0.00 sec)mysql> insert into diffcount values(’count’);
Query OK, 1 row affected (0.00 sec)mysql> show create table diffcount;
+———–+———————————————————————————————–+
| Table | Create Table |
+———–+———————————————————————————————–+
| diffcount | CREATE TABLE `diffcount` (
`id` char(8) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+———–+———————————————————————————————–+
1 row in set (0.00 sec)mysql> select * from diffcount;
+——-+
| id |
+——-+
| hello |
| world |
| NULL |
| count |
+——-+
4 rows in set (0.00 sec)mysql> select count(*) from diffcount;
+———-+
| count(*) |
+———-+
| 4 |
+———-+
1 row in set (0.00 sec)mysql> select count(id) from diffcount;
+———–+
| count(id) |
+———–+
| 3 |
+———–+
1 row in set (0.00 sec)
