MYSQL—数字格式问题

Question

我现在正在做一项工作，客户希望用户能够通过产品代码搜索产品。

产品代码的格式如下：123.4567.89

因此，无论用户输入带句点、不带句点或带空格的数字，搜索框都应返回该产品。

因此，以下所有内容都应返回产品：123.4567.89、123456789、123 4567 89。

我当前的查询如下所示：

SELECT      *
FROM        products
WHERE       product_code LIKE '%$search_code%'"

我不知道如何修改它以包括如何修改的所有不同可能性用户将输入这些数字。

预先感谢您的任何帮助。

Answer 1

1. [前端]限制用户可以输入的字符。只允许使用句点和空格。不允许使用任何字母字符（如果您的所有产品 SKU 都是数字）。

2. [中间层] 表单发布后，请仔细检查后端数据是否存在无关字符。如果客户端设法通过前端的验证，您可以在后端捕获它。使用简单的搜索并用您选择的语言进行替换。

3. [数据库/后端]将数据限制为仅限数字并将 SKU 发送到数据库查询后，删除产品表中的所有句点。如果您知道只使用句点来存储 SKU，则只需搜索排除它们即可，例如

<前><代码>选择*
来自产品
WHERE REPLACE(product_code,'.','') = @productCode

避免通配符 %% 搜索，它们的成本很高。

Answer 2

在进行搜索之前，您必须对用户输入的号码进行规范化，即：使其与数据库中存储的号码具有相同的格式。

例如，如果数据库中存储的数字不带句点（如 123456789），则必须预处理用户输入的数字，以删除其中的句点、空格和任何其他字符。

编辑：如果数字与句点一起存储在数据库中，那么您还需要通过删除句点来标准化它们，正如 @HertzaHaeon 在他的答案中指出的那样。

Answer 3

如何删除数据库代码和 searchg 输入中的点和空格，这样就只剩下数字了？像这样的事情：

WHERE REPLACE(product_code, '.', '') LIKE '%formatted_search_code%'

对于搜索输入，您可以使用正则表达式或简单的子字符串替换来删除除数字之外的所有内容。

Answer 4

我认为解决您问题的最佳方法是格式化搜索值，使其与数据库中使用的格式匹配。但如果用户填写完整的产品编号，您只会找到该产品。如果这不是所需的解决方案，并且您希望能够让用户填写产品代码的任何部分，并找到具有包含该代码的代码的所有产品，我认为您应该过滤掉数据库中的期间。

最快的解决方案是在数据库中实际执行此操作。从产品代码中删除点，或添加包含不带点的产品代码的额外字段。当数据集变大时，这将加快查询速度。

如果您不想这样做，您可以随时过滤掉搜索查询中的点：

REPLACE(product_code,'.','') LIKE '%$search_code%'

这会起到作用，但当数据集变大时可能会非常慢。

Answer 5

我一直在处理社会安全号码的问题。我的解决方案是获取输入字符串，去掉非数字字符，确保字符串的长度正确，然后使用 substring 函数将字符串分解，然后将其与分隔符放回一起。如果您使用 PHP，该函数可能如下所示：

<?php
function FormatProductCode($String) {
    $String = preg_replace("/[^0-9]/", "", $String);
    if ($String == "") {
        return null;
    }
    $String = str_pad($String, 9, "0", STR_PAD_LEFT);
    return substr($String, 0, 3) . "." . substr($String, 3, 4) . "." . substr($String, 7, 2);
}
?>

每当您需要将数据输入数据库或比较数据时，请使用此函数。