MySQL 5.5 模式匹配LIKE
发表于：2025-11-11 作者：千家信息网编辑
千家信息网最后更新 2025年11月11日，mysql> select * from t_test;+--------+-------------+---------+| deptno | dname | loc |+--------+----
千家信息网最后更新 2025年11月11日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安全错误
数据库的锁怎样保障安全
魔兽世界数据库火焰花
软件开发的分类上位机
中国文化对软件开发的影响
游戏数据库管理员待遇
软件开发的法律纠纷
洪湖手机软件开发
软件开发过程中详细设计任务
女人与女人的软件开发
杭州东信网络技术工作怎么样
怎么查看服务器本地编码
当贝服务器
邯郸荣升网络技术有限公司
查数据库学生表
龙井手机软件开发
学校网络安全等级表
昌平区专业网络技术诚信服务
2020年互联网科技排名
网络安全警报开机
广州市天气预报软件开发
数据库转换单元格
传奇3数据库编辑
丰台ibm服务器回收估价
绍兴科优佰诺网络技术有限公司
20年网络安全宣传周
浪潮服务器怎么配管理口
网站建设服务器管理软件
我是网络安全守护者
服务器机柜选购技巧
出台网络安全方面的法规
数据库死锁应该怎么处理掉
