MySQL查询实战：五种方法教你轻松搞定商品编码前缀查询

大家在实际开发当中经常需要根据某些特定条件来筛选数据库中的记录。例如，在商品管理中查找所有以特定数字开头的商品编码（code）。MySQL 提供了多种思路来实现这一需求，每种方法都有其适用场景和优缺点。这里给大家详细介绍五种不同的查询方法，并结合实际示例进行对比分析，帮助你选择最适合的方法。

一、创建表及插入示例数据

首先为了更好地理解各种查询方法的效果，首先创建一个商品表并插入一些测试数据。

创建表

代码语言：javascript代码运行次数：0运行复制

CREATE TABLE goods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    remark TEXT,
    createdate DATETIME DEFAULT CURRENT_TIMESTAMP,
    creator VARCHAR(50)
);

插入示例数据

代码语言：javascript代码运行次数：0运行复制

INSERT INTO goods (name, code, price, remark, creator) VALUES 
('Product A', '2100', 19.99, 'Remark for Product A', 'Alice'),
('Product B', '2150', 24.99, 'Remark for Product B', 'Bob'),
('Product C', '2210', 29.99, 'Remark for Product C', 'Charlie'),
('Product D', '2290', 34.99, 'Remark for Product D', 'David'),
('Product E', '1000', 39.99, 'Remark for Product E', 'Eve'),
('Product F', '1050', 44.99, 'Remark for Product F', 'Frank'),
('Product G', '2300', 49.99, 'Remark for Product G', 'Grace'),
('Product H', '2400', 54.99, 'Remark for Product H', 'Heidi'),
('Product I', '1100', 59.99, 'Remark for Product I', 'Ivan'),
('Product J', '1200', 64.99, 'Remark for Product J', 'Judy');

查询测试数据

代码语言：javascript代码运行次数：0运行复制

SELECT * from goods

二、五种查询方法及其对比

案例将使用code字段来演示如何查询以21、22或10开头的商品。

1. 使用 LIKE 和 OR

代码语言：javascript代码运行次数：0运行复制

SELECT *
FROM goods
WHERE code LIKE '21%' 
   OR code LIKE '22%'
   OR code LIKE '10%';

优点:

• 简单易懂，适合初学者。
• 如果字段上有索引，性能较好。

缺点:

• 需要写多个条件，SQL语句较长。

2. 使用正则表达式 REGEXP

代码语言：javascript代码运行次数：0运行复制

SELECT *
FROM goods
WHERE code REGEXP '^(21|22|10)';

优点:

• SQL语句简洁，易于维护。
• 支持复杂的模式匹配。

缺点:

• 在大型数据集上可能不如LIKE高效。
• 不会使用到索引（除非是全文索引）。

3. 使用 INSTR 函数

代码语言：javascript代码运行次数：0运行复制

SELECT *
FROM goods
WHERE INSTR(code, '21') = 1
   OR INSTR(code, '22') = 1
   OR INSTR(code, '10') = 1;

优点:

• 可以处理一些复杂的情况。

缺点:

• 性能较差，因为需要逐个字符比较。
• 不利于索引利用。

4. 使用 LEFT 函数

代码语言：javascript代码运行次数：0运行复制

SELECT *
FROM goods
WHERE LEFT(code, 2) IN ('21', '22', '10');

优点:

• SQL语句相对简洁。
• 比较直观。

缺点:

• 如果字段上有索引，MySQL可能会忽略索引，导致性能下降。
• 对于长字符串提取前缀可能有额外开销。

5. 使用 BETWEEN 结合数值范围

假设你的列是数值类型：

代码语言：javascript代码运行次数：0运行复制

SELECT *
FROM goods
WHERE (code BETWEEN '2100' AND '2199')
   OR (code BETWEEN '2200' AND '2299')
   OR (code BETWEEN '1000' AND '1099');

优点:

• 如果列是数值类型并且有索引，性能很好。
• 范围查询效率高。

缺点:

• 仅适用于数值类型的列。
• 需要根据具体情况调整范围。

方法对比表

方法	优点	缺点	适用场景
LIKE + OR	易于理解和实现；支持索引	SQL较长	大多数情况
REGEXP	SQL简洁；支持复杂模式匹配	性能一般；不使用索引	小型数据集或需要复杂匹配时
INSTR	可处理复杂情况	性能差；不适合大数据集	特殊需求，如模糊匹配
LEFT	SQL简洁；直观	可能忽略索引	字符串前缀匹配
BETWEEN	性能好；适合数值类型	仅限数值类型；需调整范围	数值类型的数据

性能优化建议

无论选择哪种方法，都可以通过以下方式进行性能优化：

• 索引：确保查询字段上有适当的索引，特别是前缀索引。例如，可以为code字段创建前缀索引： 代码语言：javascript代码运行次数：0运行复制

  CREATE INDEX idx_code_prefix ON goods(code(2));

• 覆盖索引：如果查询只需要访问索引就可以完成（即所有需要的数据都在索引中），那么可以创建一个覆盖索引，进一步提升查询效率。
• EXPLAIN命令：使用EXPLAIN命令分析查询执行计划，找出性能瓶颈并进行针对性优化。

总结

通过本文的介绍，我们了解了五种不同的查询方式来筛选具有特定前缀的商品编码。每种方法都有其独特的优势和局限性。根据实际应用场景和数据量选择最合适的方法，可以帮助我们提高查询效率，简化代码逻辑。

希望这篇文章对你有所帮助！如果你有任何问题或需要进一步的帮助，大家可以在评论区留言。你也可以分享你在实际项目中使用的查询技巧，让大家一起学习和进步！