Mysql之模糊查询
前情提要
mysql中的模糊检索方法，总结了有以下几种，下面我们来简单介绍一下
-- 创建表
mysql> create table if not exists wuxia(
-> id int unsigned auto_increment,
-> name varchar(10) not null,
-> primary key (id)
-> );
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| wuxia |
+----------------+
1 row in set (0.00 sec)
-- 插入数据
mysql> insert into wuxia (name) values ('小龙女'),('小高'),('卓东来'),('陆小凤');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
-- 查询表
mysql> select * from wuxia;
+----+-----------+
| id | name |
+----+-----------+
| 13 | 小龙女 |
| 14 | 小高 |
| 15 | 卓东来 |
| 16 | 陆小凤 |
+----+-----------+
4 rows in set (0.00 sec)
1.LOCATE(substr,str) ：
substr 检索条件；str column(字段)；
- 演示结果
mysql> select * from wuxia where locate('龙', name);
+----+-----------+
| id | name |
+----+-----------+
| 4 | 小龙女 |
+----+-----------+
2.INSTR(str,substr)
str column(字段)；substr 检索条件；
- 演示结果
mysql> select * from wuxia where instr(name, '龙');
+----+-----------+
| id | name |
+----+-----------+
| 4 | 小龙女 |
+----+-----------+
1 row in set (0.00 sec)
3.POSITION(substr IN str)
substr 检索条件；str column(字段)；
- 演示结果
mysql> select * from wuxia where position('龙' in name);
+----+-----------+
| id | name |
+----+-----------+
| 4 | 小龙女 |
+----+-----------+
1 row in set (0.00 sec)
4.REGEXP 正则
使用格式为“str REGEXP str_part”，当str字符串中含有str_pat相匹配的字符串时，则返回值1，否则返回0; column regexp '条件'
详细查看：https://www.cnblogs.com/ringbug/p/15703045.html
- 演示结果
mysql> select * from wuxia where name regexp '小';
+----+-----------+
| id | name |
+----+-----------+
| 4 | 小龙女 |
| 5 | 小高 |
| 7 | 陆小凤 |
+----+-----------+
3 rows in set (0.01 sec)
5.LIKE
column like '%条件%',这种类似方式是我们最常见常用的
- 演示结果
mysql> select * from wuxia where name like concat('陆', '%');
+----+-----------+
| id | name |
+----+-----------+
| 7 | 陆小凤 |
+----+-----------+
1 row in set (0.00 sec)
