加索引的怪异问题

简介: 加索引的怪异问题:数据库的版本是5.5.20                  linux的版本是CentOS release 6.2 (Final)添加username和email的索引(system@localhost) [baike]> create index idx_use...
加索引的怪异问题:数据库的版本是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类型加上索引,会出现如此的问题,太有意思了,希望有官方给与帮助啊。

目录
打赏
0
0
0
0
10
分享
相关文章
CSS必学:元素之间的空白与行内块的幽灵空白问题
CSS空白现象就是非常常见的问题之一。虽然它已经被发现很久,但仍然有许多新手和经验丰富的开发者们忽略了它对页面布局的影响。我们一起来看看吧!
277 4
CSS必学:元素之间的空白与行内块的幽灵空白问题
📕 重学JavaScript:判断数组中包含哪些值有什么好方法?
你有没有遇到过这样的问题:你想要判断一个数组中包含哪些值,但是却不知道改用什么方法就直接用for循环遍历?🤔
117 0
冒泡 VS 插入 VS 选择——谁更胜一筹?(附排序源码)
排序对于任何一个程序员来说,可能都不会陌生。你学的第一个算法,可能就是排序。大部分编程语言中,也都提供了排序函数。在平常的项目中,我们也经常会用到排序。
90 0
JavaScript基础插曲—元素样式,正则表达式,全局模式,提取数组
JavaScript基础插曲—元素样式,正则表达式,全局模式,提取数组
159 0
JavaScript基础插曲—元素样式,正则表达式,全局模式,提取数组
索引很难么?带你从头到尾捋一遍MySQL索引结构,不信你学不会!(上)
索引很难么?带你从头到尾捋一遍MySQL索引结构,不信你学不会!(上)
索引很难么?带你从头到尾捋一遍MySQL索引结构,不信你学不会!(上)

热门文章

最新文章

AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等