玉面飞龙的BLOG

count() in mysql

February 9th, 2009 | Categories: Boring | Tags: 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)