1 查看字体集
mysql> show variables like '%char%'; +--------------------------+----------------------------------------------------------------+ | Variable_name | Value | +--------------------------+----------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+----------------------------------------------------------------+ 8 rows in set (0.00 sec)
2.创建测试表
mysql> create table w2 (id int,a char(5),b varchar(5), c char(5) not null, d varchar(5) not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into w2 values(1,'1','1','1','1'); Query OK, 1 row affected (0.01 sec) mysql> insert into w2 values(2,'2','2','2','2'); Query OK, 1 row affected (0.00 sec) mysql> insert into w2 values(3,'3','3','3','3'); Query OK, 1 row affected (0.00 sec) mysql> insert into w2 values(4,'4','4','4','4'); rt into w2 values(10,'10','10','10','10');Query OK, 1 row affected (0.01 sec) mysql> insert into w2 values(5,'5','5','5','5'); Query OK, 1 row affected (0.00 sec) mysql> insert into w2 values(6,'6','6','6','6'); Query OK, 1 row affected (0.00 sec) mysql> insert into w2 values(7,'7','7','7','7'); Query OK, 1 row affected (0.00 sec) mysql> insert into w2 values(8,'8','8','8','8'); Query OK, 1 row affected (0.00 sec) mysql> insert into w2 values(9,'9','9','9','9'); Query OK, 1 row affected (0.00 sec) mysql> insert into w2 values(10,'10','10','10','10'); Query OK, 1 row affected (0.00 sec)
3.测试结果
mysql> show create table w2\G *************************** 1. row *************************** Table: w2 Create Table: CREATE TABLE `w2` ( `id` int(11) DEFAULT NULL, `a` char(5) DEFAULT NULL, `b` varchar(5) DEFAULT NULL, `c` char(5) NOT NULL, `d` varchar(5) NOT NULL, KEY `w1` (`id`), KEY `w2` (`a`), KEY `w3` (`b`), KEY `w4` (`c`), KEY `w5` (`d`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from w2 where id=1; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w2 | NULL | ref | w1 | w1 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from w2 where a='1'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w2 | NULL | ref | w2 | w2 | 16 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from w2 where b='1'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w2 | NULL | ref | w3 | w3 | 18 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from w2 where c='1'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w2 | NULL | ref | w4 | w4 | 15 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from w2 where d='1'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w2 | NULL | ref | w5 | w5 | 17 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
结论
varchar 是可变要加2,NULL是为空要加1
公式:
key_len = 定义长度*5 + NULL(1)+可变(2)