加索引的怪异问题

简介: 加索引的怪异问题:数据库的版本是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类型加上索引,会出现如此的问题,太有意思了,希望有官方给与帮助啊。

相关文章
|
5月前
|
移动开发 前端开发 HTML5
有关CSS中排版常见问题(清除默认样式问题 + 元素居中问题 + 元素之间的空白问题 + 行内块的幽灵空白问题)
有关CSS中排版常见问题(清除默认样式问题 + 元素居中问题 + 元素之间的空白问题 + 行内块的幽灵空白问题)
|
前端开发 开发者
CSS必学:元素之间的空白与行内块的幽灵空白问题
CSS空白现象就是非常常见的问题之一。虽然它已经被发现很久,但仍然有许多新手和经验丰富的开发者们忽略了它对页面布局的影响。我们一起来看看吧!
221 4
CSS必学:元素之间的空白与行内块的幽灵空白问题
|
6月前
|
移动开发 前端开发 小程序
CSS 如何完美地去除表格的 “双线”
CSS 如何完美地去除表格的 “双线”
|
前端开发 JavaScript
css控制文本超出省略(单行、两行、多行)
想要控制文本长度? 想要文本超出隐藏? 还想要不用JavaScript实现? 让我们来用css试试吧~
199 0
|
前端开发
css超出部分省略(单行、多行,多种方法实现)
这是一行测试数据,这是一行测试数据,这是二行测试数据,这是一行测试数据,这是三行测试数据,这是四行测试数据
144 0
css超出部分省略(单行、多行,多种方法实现)
|
前端开发
CSS 奇技淫巧 | 巧妙实现文字二次加粗再加边框
CSS 奇技淫巧 | 巧妙实现文字二次加粗再加边框
340 0
CSS 奇技淫巧 | 巧妙实现文字二次加粗再加边框
|
前端开发 JavaScript
CSS 奇技淫巧 | 妙用 drop-shadow 实现线条光影效果
CSS 奇技淫巧 | 妙用 drop-shadow 实现线条光影效果
428 0
CSS 奇技淫巧 | 妙用 drop-shadow 实现线条光影效果
|
前端开发 API
【网页前端】CSS样式表进阶之伪元素
【网页前端】CSS样式表进阶之伪元素
130 0
【网页前端】CSS样式表进阶之伪元素
|
JavaScript 前端开发 C#
JavaScript基础插曲—元素样式,正则表达式,全局模式,提取数组
JavaScript基础插曲—元素样式,正则表达式,全局模式,提取数组
141 0
JavaScript基础插曲—元素样式,正则表达式,全局模式,提取数组
|
Web App开发 前端开发 算法
[重拾CSS]一道面试题来看伪元素、包含块和高度坍塌
前几天某个群友在群里问了一道面试题,就是关于一个自适应的正方形布局的困惑,先贴上代码。我其实很长一段时间没有写 CSS 了,对于里面的一些细节也比较模糊了,因此决定重拾 CSS,来重新捋一捋这题目中的一些知识点。
[重拾CSS]一道面试题来看伪元素、包含块和高度坍塌