MySQL 5.5 模式匹配LIKE
发表于：2025-01-24 作者：千家信息网编辑
千家信息网最后更新 2025年01月24日，mysql> select * from t_test;+--------+-------------+---------+| deptno | dname | loc |+--------+----
千家信息网最后更新 2025年01月24日MySQL 5.5 模式匹配LIKEmysql> select * from t_test;
+--------+-------------+---------+
| deptno | dname | loc |
+--------+-------------+---------+
| 10 | Research | Beijing |
| 20 | Maintenance | Huludao |
| 30 | Market | Tianjin |
| 40 | Leader | Qingdao |
+--------+-------------+---------+
4 rows in set (0.00 sec)
mysql> create index idx_test_dname on t_test(dname);
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show keys from t_test\G
*************************** 1. row ***************************
Table: t_test
Non_unique: 1
Key_name: idx_test_dname
Seq_in_index: 1
Column_name: dname
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
%Lea%没有使用索引扫描
mysql> explain select * from t_test where dname like '%Lea%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_test | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Lea%使用索引扫描
mysql> explain select * from t_test where dname like 'Lea%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | t_test | range | idx_test_dname | idx_test_dname | 47 | NULL | 1 | Using where |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
1 row in set (0.12 sec)
mysql> explain select dname from t_test where dname like 'Lea%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | t_test | index | idx_test_dname | idx_test_dname | 47 | NULL | 4 | Using where; Using index |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
+--------+-------------+---------+
| deptno | dname | loc |
+--------+-------------+---------+
| 10 | Research | Beijing |
| 20 | Maintenance | Huludao |
| 30 | Market | Tianjin |
| 40 | Leader | Qingdao |
+--------+-------------+---------+
4 rows in set (0.00 sec)
mysql> create index idx_test_dname on t_test(dname);
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show keys from t_test\G
*************************** 1. row ***************************
Table: t_test
Non_unique: 1
Key_name: idx_test_dname
Seq_in_index: 1
Column_name: dname
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
%Lea%没有使用索引扫描
mysql> explain select * from t_test where dname like '%Lea%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t_test | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
Lea%使用索引扫描
mysql> explain select * from t_test where dname like 'Lea%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
| 1 | SIMPLE | t_test | range | idx_test_dname | idx_test_dname | 47 | NULL | 1 | Using where |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+-------------+
1 row in set (0.12 sec)
mysql> explain select dname from t_test where dname like 'Lea%';
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
| 1 | SIMPLE | t_test | index | idx_test_dname | idx_test_dname | 47 | NULL | 4 | Using where; Using index |
+----+-------------+--------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
索引
模式
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
银行网络安全的意义
游戏养号需要什么配置的服务器
数据库查看表内容的命令是
宇创网络技术
运用什么软件编写数据库系统
网络安全网站设计感裙子
百度基木鱼能用自己服务器吗
软件开发陪聊师
如何管理互联网中的各个服务器
魔兽不是一个服务器可以加公会吗
贝壳找房美国上市网络安全吗
华图在线服务器崩溃
金铲铲之战服务器全部爆满
通信理论与网络技术
变量的数据类型数据库
我的世界怪物学院网络安全
win10安装服务器
棋牌游戏软件开发怎么谈
网络安全你我共建主题班会
棋牌软件开发销售需要的证件
职业规划范文软件开发
广州帝琪ip音响服务器
数据库审计产品型号
广州海绵互联网科技
长沙哪个学校软件开发最好
学过数据库之后的体会
四川语音网络技术分类技术
国家网络安全宣传周总结汇报
关系型数据库性能比较
网络安全与执法论文
