字符类别数据缺失引号索引被抑制

发布时间：2022-03-31 22:36:11 所属栏目：MySql教程来源：互联网

导读：字符类型的数据没有使用引号，索引将被抑制，下边看一个案例：查看表结构：点击(此处)折叠或打开 mysql show create table test06 G *************************** 1. row *************************** Table: test06 Create Table: CREATE TABLE `test0

   字符类型的数据没有使用引号，索引将被抑制，下边看一个案例：
查看表结构：
点击(此处)折叠或打开

mysql> show create table test06 G
*************************** 1. row ***************************
       Table: test06
Create Table: CREATE TABLE `test06` (
  `id` bigint(11) NOT NULL DEFAULT '0',
  `u_id` bigint(11) NOT NULL,
  `openid` varchar(100) DEFAULT NULL,
  `unionid` varchar(100) DEFAULT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `create_time` datetime NOT NULL,
  KEY `idx_test03_id` (`id`),
  KEY `idx_test03_name` (`username`),
  KEY `idx_test06_crea_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
username字段不加引号：
点击(此处)折叠或打开

mysql> select * from test06 where username=13499770088;
Empty set, 8208 warnings (5.77 sec)

mysql> explain select * from test06 where username=13499770088;
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys   | key | key_len | ref | rows    | Extra       |
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
| 1 | SIMPLE      | test06 | ALL | idx_test03_name | NULL | NULL    | NULL | 2009559 | Using where |
+----+-------------+--------+------+-----------------+------+---------+------+---------+-------------+
username字段加引号：
点击(此处)折叠或打开

mysql> select * from test06 where username='13499770088';
Empty set (0.07 sec)

mysql> explain select * from test06 where username='13499770088';
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
| 1 | SIMPLE      | test06 | ref | idx_test03_name | idx_test03_name | 302     | const | 1    | Using index condition |
+----+-------------+--------+------+-----------------+-----------------+---------+-------+------+-----------------------+
查询速度明显变快，执行计划走了索引，这样是正常的写法。

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

mysql重创某个用户账号	Linux运维定会的MySQL
MySQL视图存储步骤与	MySQL的server_uuid取