看看date类型的索引长度，在NOT NULL和NULL的时候，分别是3和4
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = '2015-05-06';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | table_key1 | ref | c4_index | c4_index | 3 | const | 4 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
row in set, 3 warnings (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c4 date;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c4 = '2015-05-06';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | table_key1 | ref | c4_index | c4_index | 4 | const | 4 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
row in set, 3 warnings (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
在timestamp类型的时候索引长度，在NOT NULL 和 NULL的时候，分别是4和5
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c5 = '2015-05-06 11:23:21' ;
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | table_key1 | ref | c5_index | c5_index | 4 | const | 5 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
row in set, 3 warnings (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c5 timestamp ON UPDATE CURRENT_TIMESTAMP ;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]>
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c5 = '2015-05-06 110:23:21';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | table_key1 | ref | c5_index | c5_index | 5 | const | 5 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
row in set, 3 warnings (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
##############################在大家认识里datetime是八个字节的长度，下面就来看看，是不是真的这样
(dg1)root@localhost [mytest]> alter table table_key1 modify c6 datetime not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@localhost [mytest]> desc select * from table_key1 where c6 = '2015-05-06 11:10:36';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key1 | ref | c6_index | c6_index | 5 | const | 1 | NULL |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
row in set (0.00 sec)
(dg1)root@localhost [mytest]> alter table table_key1 modify c6 datetime null;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@localhost [mytest]> desc select * from table_key1 where c6 = '2015-05-06 11:10:36';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key1 | ref | c6_index | c6_index | 6 | const | 1 | NULL |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
row in set (0.00 sec)
颠覆了我们认识，datetime不是8个字节么，下面来看一下MySQL的版本，没错MySQL5.6是datetime长度是5个字节
(dg1)root@localhost [mytest]> \s
--------------
mysql Ver 14.14 Distrib 5.6.22, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3
Current database: mytest
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.22-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: gbk
Db characterset: gbk
Client characterset: gbk
Conn. characterset: gbk
UNIX socket: /opt/app/mysql/mysql3307.socket
Uptime: 4 min 47 sec
Threads: 1 Questions: 19 Slow queries: 0 Opens: 75 Flush tables: 1 Open tables: 64 Queries per second avg: 0.066
--------------
(dg1)root@localhost [mytest]>
小结：在MySQL5.6版本，是否还得使用timestamp类型应该是仁者见仁智者见智的问题了，datetime是五个字节，timestamp范围比较窄（1970-2037年），不排除后续版本会修改其范围值
4.字符类型