我应该在 MySQL 还是 PHP 中执行正则表达式过滤? [英] Should I perform regex filtering in MySQL or PHP?

问题描述

我有一个包含各种字符串数据的 MySQL 列，它是一个 VARCHAR 字段.

I have a MySQL column which holds various string data, it is a VARCHAR field.

该表有超过 10 万条记录，我想通过此字段过滤查询以仅选择此字段以除 1、2、3、4、5、6、7 以外的任何字符开头的记录,8,9.

The table has more than 100k records, and I'd like to filter a query by this field to SELECT only the records in which this field starts with any characters but 1,2,3,4,5,6,7,8,9.

是否更快:

• 在 SQL 查询中写一个 REGEXP，或者
• 只需选择所有记录并通过执行 PHP REGEX 在 PHP 中过滤掉它们?

推荐答案

SQL 查询会更快，毫无疑问.这类东西正是 SQL 的用途.

The SQL query will be faster, hands down. This sort of thing is precisely what SQL is meant to be used for.

澄清以供将来参考:当您需要 DB 返回特定数据集时，您应该让 DB 使用 SQL 查询来构建数据集.然后，您的应用程序代码可以有一个或多个抽象来表示和处理您的业务用例的结果数据集，但它不应该完成数据库引擎的工作.

To clarify for future reference: when you need the DB to return a specific data set, you should let the DB deal with constructing the dataset by using a SQL query. Your application code can then have one or more abstractions that represent and handle the resulting dataset for your business use case, but it should not do the DB engine's job.

TL;DR:从数据库表构建数据集是数据访问层关注的问题，处理与业务实体相关的抽象是应用层关注的问题

TL;DR: building a dataset from DB tables is a Data access layer concern, handling abstractions related to business entities is the application layer concern

这篇关于我应该在 MySQL 还是 PHP 中执行正则表达式过滤?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！