表结构
CREATE TABLE `table_key1` (
`id` int NOT NULL AUTO_INCREMENT ,
`c1` float NOT NULL ,
`c2` double NOT NULL ,
`c3` decimal NOT NULL ,
`c4` date NOT NULL ,
`c5` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP ,
`c6` datetime NOT NULL ,
PRIMARY KEY (`id`)
)
看看float类型的索引长度，NOT NULL和NULL的时候，���别是4和5
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = '3.22';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | table_key1 | ref | c1_index | c1_index | 4 | const | 8 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c1 float;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c1 = '3.22';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | table_key1 | ref | c1_index | c1_index | 5 | const | 8 | Using index condition |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
row in set (0.00 sec)
看看double类型的索引长度，NOT NULL和NULL的时候，分别是8和9
(dg1)root@127.0.0.1 [mytest]> alter table table_key1 add key c2_index (c2);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c2 = '3.22';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key1 | ref | c2_index | c2_index | 8 | const | 1 | NULL |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]> alter table table_key1 modify c2 double;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
(dg1)root@127.0.0.1 [mytest]> desc select * from table_key1 where c2 = '3.22';
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
| 1 | SIMPLE | table_key1 | ref | c2_index | c2_index | 9 | const | 1 | NULL |
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
row in set (0.00 sec)
(dg1)root@127.0.0.1 [mytest]>
3、看看时间类型