加索引的怪异问题：数据库的版本是5.5.20
linux的版本是CentOS release 6.2 (Final)
添加username和email的索引
(system@localhost) [baike]> create index idx_username on bk_user(username(20));
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> create index idx_email on bk_user(email(30));
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> show index from bk_user;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bk_user | 0 | PRIMARY | 1 | uid | A | 79429 | NULL | NULL | | BTREE | | |
| bk_user | 1 | loginstatus | 1 | loginstatus | A | 3 | NULL | NULL | | BTREE | | |
| bk_user | 1 | idx_username | 1 | username | A | 156 | 20 | NULL | YES | BTREE | | |
| bk_user | 1 | idx_email | 1 | email | A | 156 | 30 | NULL | YES | BTREE | | |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)
(system@localhost) [baike]> desc bk_user;
+----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| uid | bigint(20) | NO | PRI | NULL | |
| useriden | char(17) | YES | | NULL | |
| username | char(64) | YES | MUL | NULL | |
| regdate | int(10) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| email | char(255) | YES | MUL | NULL | |
| status | tinyint(1) | YES | | NULL | |
| image | char(255) | YES | | NULL | |
| bigimage | varchar(255) | NO | | NULL | |
| regip | char(15) | YES | | NULL | |
| lastvisit | int(10) | YES | | NULL | |
| lastip | char(15) | YES | | NULL | |
| loginstatus | tinyint(1) | NO | MUL | 0 | |
| cas_ticket | varchar(255) | NO | | NULL | |
| is_super_admin | tinyint(1) | NO | | 0 | |
| type | tinyint(1) | NO | | 1 | |
| remark | varchar(255) | YES | | NULL | |
| remark_time | int(11) unsigned | NO | | 0 | |
+----------------+------------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
(system@localhost) [baike]> set names gbk;
Query OK, 0 rows affected (0.00 sec)
根据userid查得到
(system@localhost) [baike]> select * from bk_user where uid='300001338703';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | xxx | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | xxx@qq.com | 1 | xx.jpg | xx.jpg | xx | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.00 sec)
根据username和email查询都是空记录
(system@localhost) [baike]> select * From bk_user where username='xxx';
Empty set (0.00 sec)
(system@localhost) [baike]> select * from bk_user where email='xxx@qq.com';
Empty set (0.00 sec)
修改类型为varchar后，数据即可找到
(system@localhost) [baike]> alter table bk_user modify username varchar(64);
Query OK, 86030 rows affected (5.31 sec)
Records: 86030 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> alter table bk_user modify email varchar(255);
Query OK, 86030 rows affected (4.86 sec)
Records: 86030 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> select * From bk_user where username='xxx';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | 刘伽伽 | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | 5549125512@qq.com | 1 | xx/85/46/11300001337475131305460345051_50.jpg | xx/85/46/11300001337475131305460345051_180.jpg | 124.42.60.110 | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.02 sec)
(system@localhost) [baike]> select * from bk_user where email='xxx@qq.com';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | xxx | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | 5549125512@qq.com | 1 | xx/85/46/11300001337475131305460345051_50.jpg | xx/85/46/11300001337475131305460345051_180.jpg | 124.42.60.110 | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.00 sec)
在修改回来为原来的char类型
(system@localhost) [baike]> alter table bk_user modify username char(64);
Query OK, 86030 rows affected (4.99 sec)
Records: 86030 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> alter table bk_user modify email char(255);
Query OK, 86030 rows affected (5.96 sec)
Records: 86030 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> select * From bk_user where username='xxx';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | xxx | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | 5549125512@qq.com | 1 | xx/85/46/11300001337475131305460345051_50.jpg | xx/85/46/11300001337475131305460345051_180.jpg | 124.42.60.110 | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.00 sec)
(system@localhost) [baike]> select * from bk_user where email='xxx@qq.com';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | xxx | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | xxx@qq.com | 1 | xx/85/46/11300001337475131305460345051_50.jpg | xx/85/46/11300001337475131305460345051_180.jpg | 124.42.60.110 | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.00 sec)
虽然问题可以解决，但是出现的问题很诡异，为什么对一个char类型加上索引，会出现如此的问题，太有意思了，希望有官方给与帮助啊。
linux的版本是CentOS release 6.2 (Final)
添加username和email的索引
(system@localhost) [baike]> create index idx_username on bk_user(username(20));
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> create index idx_email on bk_user(email(30));
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> show index from bk_user;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| bk_user | 0 | PRIMARY | 1 | uid | A | 79429 | NULL | NULL | | BTREE | | |
| bk_user | 1 | loginstatus | 1 | loginstatus | A | 3 | NULL | NULL | | BTREE | | |
| bk_user | 1 | idx_username | 1 | username | A | 156 | 20 | NULL | YES | BTREE | | |
| bk_user | 1 | idx_email | 1 | email | A | 156 | 30 | NULL | YES | BTREE | | |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)
(system@localhost) [baike]> desc bk_user;
+----------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| uid | bigint(20) | NO | PRI | NULL | |
| useriden | char(17) | YES | | NULL | |
| username | char(64) | YES | MUL | NULL | |
| regdate | int(10) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| email | char(255) | YES | MUL | NULL | |
| status | tinyint(1) | YES | | NULL | |
| image | char(255) | YES | | NULL | |
| bigimage | varchar(255) | NO | | NULL | |
| regip | char(15) | YES | | NULL | |
| lastvisit | int(10) | YES | | NULL | |
| lastip | char(15) | YES | | NULL | |
| loginstatus | tinyint(1) | NO | MUL | 0 | |
| cas_ticket | varchar(255) | NO | | NULL | |
| is_super_admin | tinyint(1) | NO | | 0 | |
| type | tinyint(1) | NO | | 1 | |
| remark | varchar(255) | YES | | NULL | |
| remark_time | int(11) unsigned | NO | | 0 | |
+----------------+------------------+------+-----+---------+-------+
18 rows in set (0.00 sec)
(system@localhost) [baike]> set names gbk;
Query OK, 0 rows affected (0.00 sec)
根据userid查得到
(system@localhost) [baike]> select * from bk_user where uid='300001338703';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | xxx | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | xxx@qq.com | 1 | xx.jpg | xx.jpg | xx | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.00 sec)
根据username和email查询都是空记录
(system@localhost) [baike]> select * From bk_user where username='xxx';
Empty set (0.00 sec)
(system@localhost) [baike]> select * from bk_user where email='xxx@qq.com';
Empty set (0.00 sec)
修改类型为varchar后，数据即可找到
(system@localhost) [baike]> alter table bk_user modify username varchar(64);
Query OK, 86030 rows affected (5.31 sec)
Records: 86030 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> alter table bk_user modify email varchar(255);
Query OK, 86030 rows affected (4.86 sec)
Records: 86030 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> select * From bk_user where username='xxx';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | 刘伽伽 | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | 5549125512@qq.com | 1 | xx/85/46/11300001337475131305460345051_50.jpg | xx/85/46/11300001337475131305460345051_180.jpg | 124.42.60.110 | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.02 sec)
(system@localhost) [baike]> select * from bk_user where email='xxx@qq.com';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | xxx | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | 5549125512@qq.com | 1 | xx/85/46/11300001337475131305460345051_50.jpg | xx/85/46/11300001337475131305460345051_180.jpg | 124.42.60.110 | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.00 sec)
在修改回来为原来的char类型
(system@localhost) [baike]> alter table bk_user modify username char(64);
Query OK, 86030 rows affected (4.99 sec)
Records: 86030 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> alter table bk_user modify email char(255);
Query OK, 86030 rows affected (5.96 sec)
Records: 86030 Duplicates: 0 Warnings: 0
(system@localhost) [baike]> select * From bk_user where username='xxx';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | xxx | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | 5549125512@qq.com | 1 | xx/85/46/11300001337475131305460345051_50.jpg | xx/85/46/11300001337475131305460345051_180.jpg | 124.42.60.110 | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.00 sec)
(system@localhost) [baike]> select * from bk_user where email='xxx@qq.com';
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| uid | useriden | username | regdate | password | email | status | image | bigimage | regip | lastvisit | lastip | loginstatus | cas_ticket | is_super_admin | type | remark | remark_time |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
| 300001338703 | icVpAUWBwBmZCU0Zx | xxx | 1313054564 | e1ff996065fda37fa5b5813c3ae726be | xxx@qq.com | 1 | xx/85/46/11300001337475131305460345051_50.jpg | xx/85/46/11300001337475131305460345051_180.jpg | 124.42.60.110 | 1342143395 | 124.42.60.110 | 1 | ST-12619-JAaU2hsW7JdhG5YHcBEW-sso | 1 | 4 | NULL | 0 |
+--------------+-------------------+----------+------------+----------------------------------+-------------------+--------+---------------------------------------------------------------------+----------------------------------------------------------------------+---------------+------------+---------------+-------------+-----------------------------------+----------------+------+--------+-------------+
1 row in set (0.00 sec)
虽然问题可以解决，但是出现的问题很诡异，为什么对一个char类型加上索引，会出现如此的问题，太有意思了，希望有官方给与帮助啊。
