MySQL 5.5 模式匹配LIKE
发表于：2026-11-03 作者：千家信息网编辑
千家信息网最后更新 2026年11月03日，mysql> select * from t_test;+--------+-------------+---------+| deptno | dname | loc |+--------+----
千家信息网最后更新 2026年11月03日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安全错误
数据库的锁怎样保障安全
咖啡网
南京欣网互联网络科技有限公司
湖南永感互联网科技有限公司
互联网科技与金融科技
有马互联网科技
互联网科技革命
赫美联合互联网科技
大白互联网科技
人人互联网科技是培训
丰台科技园 互联网公司
华中科技有哪些互联网大佬
互联网科技产品
互联网之后的科技是什么
宜昌元轩互联网科技有限公司
银行科技岗属于互联网吗
珠海东科互联网科技有限公司
互联网科技新闻
云南互联网科技学费
恒生科技指数 中概互联网
互联网科技创新方面的资料
互联网与科技
深圳市人人互联网科技有限公司
互联网和科技技术
中资互联网科技
齐家互联网科技有限公司
互联网金融科技委员会
互联网科技公司招聘
博兴的互联网电子科技公司
张召忠谈中国互联网科技
山东亿川互联网科技有限责任公司
