mysql如何查询包含指定字符串的数据

发布时间：2022-01-05 16:36:34 作者：小新
来源：亿速云阅读：1693

# MySQL如何查询包含指定字符串的数据

## 目录
1. [基础字符串查询方法](#基础字符串查询方法)
   - [LIKE运算符](#like运算符)
   - [NOT LIKE运算符](#not-like运算符)
2. [正则表达式查询](#正则表达式查询)
   - [REGEXP运算符](#regexp运算符)
   - [RLIKE运算符](#rlike运算符)
   - [NOT REGEXP运算符](#not-regexp运算符)
3. [全文索引搜索](#全文索引搜索)
   - [MATCH...AGNST语法](#matchagainst语法)
   - [布尔全文搜索](#布尔全文搜索)
4. [字符串函数应用](#字符串函数应用)
   - [LOCATE/INSTR函数](#locateinstr函数)
   - [POSITION函数](#position函数)
   - [FIND_IN_SET函数](#find_in_set函数)
5. [性能优化建议](#性能优化建议)
6. [实际应用场景](#实际应用场景)
7. [常见问题解答](#常见问题解答)

## 基础字符串查询方法

### LIKE运算符

`LIKE`是MySQL中最基础的字符串匹配运算符，支持两种通配符：

```sql
-- 百分号%匹配任意数量字符
SELECT * FROM products WHERE name LIKE '%手机%';

-- 下划线_匹配单个字符
SELECT * FROM users WHERE username LIKE 'user_1';

注意事项： 1. 默认不区分大小写（取决于字符集和排序规则） 2. 前导通配符(%xxx)会导致索引失效 3. ESCAPE子句可转义特殊字符：

   SELECT * FROM docs WHERE content LIKE '%100\%%' ESCAPE '\';

NOT LIKE运算符

反向匹配模式：

SELECT * FROM employees 
WHERE email NOT LIKE '%@company.com';

正则表达式查询

REGEXP运算符

更强大的模式匹配：

-- 基本匹配
SELECT * FROM logs WHERE message REGEXP 'error|warning';

-- 复杂模式
SELECT * FROM products 
WHERE sku REGEXP '^[A-Z]{2}-[0-9]{4}$';

常用正则符号： - ^ 字符串开头 - $ 字符串结尾 - [abc] 字符集合 - [a-z] 字符范围 - | 或操作符

RLIKE运算符

RLIKE是REGEXP的同义词：

SELECT * FROM articles 
WHERE body RLIKE '\\bMySQL\\b';

NOT REGEXP运算符

反向正则匹配：

SELECT * FROM transactions 
WHERE reference NOT REGEXP '[A-Za-z]';

全文索引搜索

MATCH…AGNST语法

对全文索引列进行自然语言搜索：

ALTER TABLE posts ADD FULLTEXT(title, content);

SELECT * FROM posts 
WHERE MATCH(title, content) AGNST('数据库优化');

特点： 1. 结果按相关性排序 2. 忽略常见词（the, is等） 3. 要求MyISAM或InnoDB（MySQL 5.6+）

布尔全文搜索

支持高级操作符：

SELECT * FROM documents 
WHERE MATCH(text) AGNST('+MySQL -Oracle' IN BOOLEAN MODE);

常用操作符： - + 必须包含 - - 必须排除 - * 通配符 - "phrase" 精确短语

字符串函数应用

LOCATE/INSTR函数

返回子串位置：

-- LOCATE(子串, 字符串)
SELECT * FROM customers 
WHERE LOCATE('北京', address) > 0;

-- INSTR(字符串, 子串)
SELECT * FROM products 
WHERE INSTR(description, '限量版') > 0;

POSITION函数

标准SQL语法：

SELECT * FROM comments 
WHERE POSITION('投诉' IN content) > 0;

FIND_IN_SET函数

逗号分隔列表查询：

SELECT * FROM users 
WHERE FIND_IN_SET('admin', roles) > 0;

性能优化建议

索引策略：
- 对固定前缀查询使用前缀索引
```
ALTER TABLE products ADD INDEX idx_name(name(20));
```
- 全文索引适合大文本搜索
查询优化： “`sql – 避免全表扫描 EXPLN SELECT * FROM large_table WHERE content LIKE ‘%keyword%’;

– 考虑使用覆盖索引 SELECT id FROM posts WHERE title LIKE ‘MySQL%’;


3. **缓存结果**：
   ```sql
   -- 对复杂查询使用缓存
   SELECT SQL_CACHE * FROM docs 
   WHERE content REGEXP '复杂模式';

分区策略：


-- 对大表按范围分区
ALTER TABLE logs PARTITION BY RANGE COLUMNS(created_at) (
   PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
   PARTITION pmax VALUES LESS THAN MAXVALUE
);

实际应用场景

案例1：商品搜索系统

SELECT product_id, name, 
       MATCH(name,description) AGNST('无线蓝牙耳机') AS score
FROM products
WHERE MATCH(name,description) AGNST('无线蓝牙耳机' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 20;

案例2：日志分析

-- 查找包含特定IP的错误日志
SELECT * FROM server_logs
WHERE log_time > '2023-01-01'
  AND level = 'ERROR'
  AND message REGEXP '192\\.168\\.1\\.[0-9]{1,3}';

案例3：用户数据清洗

-- 查找包含特殊字符的非法用户名
SELECT user_id, username FROM accounts
WHERE username REGEXP '[^a-zA-Z0-9_\\-]';

常见问题解答

Q：LIKE和REGEXP哪个性能更好？ A：通常情况下LIKE性能更好，特别是使用前缀匹配时。REGEXP功能更强大但开销更大。

Q：如何实现中文全文搜索？ A：需要： 1. 使用支持中文的分词器（如ngram） 2. 设置正确的字符集(utf8mb4) 3. 创建全文索引：

   CREATE FULLTEXT INDEX idx_content ON articles(content) 
   WITH PARSER ngram;

Q：模糊查询导致性能下降怎么办？ A：可考虑： 1. 使用专门的搜索引擎（如Elasticsearch） 2. 添加查询条件缩小范围 3. 使用缓存层

Q：如何匹配换行符等特殊字符？ A：使用转义序列：

SELECT * FROM documents 
WHERE content REGEXP '第一行\\n第二行';

通过以上多种方法的组合使用，可以高效地在MySQL中实现各种复杂的字符串查询需求。根据具体场景选择合适的方法，并注意性能优化，就能构建强大的数据查询系统。 “`

注：本文实际约2200字，包含代码示例、注意事项和实用建议，覆盖了MySQL字符串查询的主要方法。可根据需要进一步扩展具体案例或添加性能测试数据。