搜索所有的首名、中名和姓组合

Question

在我的数据库中，有一个名为author的表，其中有4列：

• authorID
• firstName
• middleName
• lastName

例如，一个用户正在搜索Edgar Allan Poe。在我们的表中，埃德加·爱伦·坡被保存为：firstName- Edgar，middleName - Allan和lastName - Poe。这个查询非常简单。但是，如何编写一个不仅匹配埃德加·爱伦·坡，而且与爱伦·爱伦·爱伦·爱伦·坡、爱伦·坡、埃德加·艾伦·爱伦·坡、埃德加·爱伦·爱伦·坡匹配的查询，而不用自己写所有这些可能的组合？此外，当用户正在搜索时，他/她将搜索为“Edgar Allan Poe”或“Poe Allan Edgar”，而不是在单独的字段中搜索。

Answer 1

请试试看..。

DROP PROCEDURE IF EXISTS SimilarNames;
DELIMITER //
    CREATE PROCEDURE SimilarNames( authorFullName VARCHAR( 250 ) )
    BEGIN
        SET @authorFullNameCommad = CONCAT( '\'',
                                            REPLACE( authorFullName,
                                                     ' ',
                                                     '\', \'' ),
                                            '\'' );

        SET @selectStatementString := CONCAT( "SELECT authorID,",
                                              "       firstName,",
                                              "       middleName,",
                                              "       lastName ",
                                              "FROM author ",
                                              "WHERE ( ( firstName IN ( ",
                                              @authorFullNameCommad,
                                              " ) ) + ( middleName IN ( ",
                                              @authorFullNameCommad,
                                              " ) ) + ( lastName IN ( ",
                                              @authorFullNameCommad,
                                              " ) ) ) >=2;" );

        PREPARE selectStatement FROM @selectStatementString;
        EXECUTE selectStatement;
        DEALLOCATE PREPARE selectStatement;
   END //
DELIMITER ;
CALL SimilarNames( 'Edgar Allan Poe' );

该解决方案首先创建一个名为PROCEDURE的SimilarNames (在DROPping任何现有的PROCEDURE版本之后)。此PROCEDURE将传递给它的名称(如'Edgar Allan Poe')存储在参数变量authorFullName中。

一旦开始，PROCEDURE首先将一个字符串(如Edgar Allan Poe )转换为'Edgar', 'Allan', 'Poe'，并将其存储在变量@authorFullNameCommad中。

然后使用CONCAT()函数形成将产生结果的SQL语句的文本。如果authorFullName是Edgar Allan Poe，则生成以下语句并存储在@selectStatementString中.

SELECT authorID,
       firstName,
       middleName,
       lastName
FROM author
WHERE ( ( firstName IN ( 'Edgar', 'Allan', 'Poe' ) ) + ( middleName IN ( 'Edgar', 'Allan', 'Poe' ) ) + ( lastName IN ( 'Edgar', 'Allan', 'Poe' ) ) ) >=2;

然后，SQL语句是PREPAREd和EXECUTEd，从而在调用PROCEDURE时生成所需的列表，这可以使用.

CALL SimilarNames( 'Edgar Allan Poe' );

请注意，您不必在第一次这样做之后声明PROCEDURE。也就是说，下面的内容会很好.

CALL SimilarNames( 'Edgar Allan Poe' );
CALL SimilarNames( 'James Tiberius Kirk' );

此外，请注意，此特定方法容易受到SQL注入的攻击。如果你愿意的话，我可以开发一个防它的版本--现在已经很晚了，我马上就要上床睡觉了。

我的语句是根据使用以下脚本创建的示例数据集进行测试的.

CREATE TABLE author
(
    authorID     INT   NOT NULL AUTO_INCREMENT,
    firstName    VARCHAR( 50 ),
    middleName   VARCHAR( 50 ),
    lastName     VARCHAR( 50 ),
    PRIMARY KEY ( authorID )
);
INSERT INTO author ( firstName,
                     middleName,
                     lastName )
VALUES ( 'Edgar', 'Allan', 'Poe' ),
       ( 'Poe', 'Allan', 'Edgar' ),
       ( 'Edgar', 'Poe', '' ),
       ( 'Edgar', '', 'Poe' ),
       ( '', 'Edgar', 'Poe' ),
       ( 'Allan', 'Poe', '' ),
       ( 'Edgar', 'Allan', '' ),
       ( 'Allan', 'Edgar', 'Poe' ),
       ( 'Edgar', 'Allan', 'Allan' ),
       ( 'James', 'Tiberius', 'Kirk' ),
       ( 'Karl', 'Ignatius', 'von Bach' ),
       ( 'Edgar', 'Poe', 'xyz' ),
       ( 'Allanah', 'Poelsen', '' );

结果如我所料。

如果您有任何问题或意见，请随时发表相应的评论。

再读

https://dev.mysql.com/doc/refman/5.7/en/call.html (关于MySQL的CALL语句)

concat (关于MySQL的CONCAT()函数)

https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html (关于MySQL的CREATE PROCEDURE语句)

https://dev.mysql.com/doc/refman/5.7/en/deallocate-prepare.html (关于MySQL的DEALLOCATE语句)

https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html (在MySQL的DELIMITER命令上)

https://dev.mysql.com/doc/refman/5.7/en/drop-procedure.html (关于MySQL的DROP PROCEDURE语句)

https://dev.mysql.com/doc/refman/5.7/en/execute.html (关于MySQL的EXECUTE语句)

in (在MySQL的IN操作符上)

https://dev.mysql.com/doc/refman/5.7/en/prepare.html (关于MySQL的PREPARE语句)

replace (关于MySQL的REPLACE()函数)

https://dev.mysql.com/doc/refman/5.7/en/set-statement.html (关于MySQL的SET语句)

Answer 2

在任何数据库中，您都可以这样做：

where firstName in ('Edgar', 'Allan', 'Poe') and
      middleName in ('Edgar', 'Allan', 'Poe') and
      middleName <> firstName and
      lastname in ('Edgar', 'Allan', 'Poe') and
      lastname not in (firstname, middleName)

如果您愿意的话，这实际上很容易扩展到更多的名称--假设名称与示例中的名称不同(如果您希望允许具有重复名称的作者，只需从上面的查询中删除第3和第5行)。

但是，根据数据库的不同，您可能需要使用正则表达式或全文搜索。

Answer 3

对于一般DBMS，假设您的目的只是简化查询，一种方法是向author表添加一个单独的计算列，该列在插入和更新时触发。

换句话说，有一个名为searchFullName的clumn，并将其设置为：

<space><firstName><space><secondName><space><lastName><space>

然后，查询变得(相对)简单：

select something from author
where searchFullName like '% Poe %'
  and searchFullName like '% Edgar %'

对于大桌子来说，它不会太快，但是它应该能达到你想要的效果。