php 使用不同列中的名字和姓氏在 MySQL 数据库中搜索全名或名字或姓氏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10046741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Searching full name or first or last name in MySQL database with first and last name in separate columns
提问by G.Thompson
I'm working with an existing database that has first name and last name seperated in the database. I need to create a function that will take one search input and return results. say my database has a structure like....
我正在使用一个现有的数据库，该数据库的名字和姓氏在数据库中分开。我需要创建一个函数来接受一个搜索输入并返回结果。说我的数据库有这样的结构......
nameFirst nameLast
Joe Smith
Joe Jones
Joe Brown
How could I, using MySql, take a search input that is say 'Joe Smith' and just get his row? But if I put just 'Joe' in the search field, return them all? Will I need to explode the string with a space? thanks!
我如何使用 MySql 进行搜索输入，即“Joe Smith”并获得他的行？但是，如果我在搜索字段中只输入“Joe”，是否全部返回？我需要用空格炸开绳子吗？谢谢！
回答by csi
SELECT *
FROM table
WHERE CONCAT( nameFirst, ' ', nameLast ) LIKE '%Joe%'
Be sure to sanitize any user submitted parameters such as "Joe"
确保清理任何用户提交的参数，例如“Joe”
回答by Stefano Borzì
SELECT * FROM table WHERE CONCAT(nameFirst, ' ', nameLast) LIKE 'Joe%';
I suggest to use this if you are sure that your search input start with "Joe" to filter more.
如果您确定您的搜索输入以“Joe”开头以过滤更多内容，我建议使用它。
In my humble opinion in a lot of cases we don't know if the search input is the name or the surname so I suggest to use this:
以我的拙见，在很多情况下我们不知道搜索输入是名字还是姓氏，所以我建议使用这个：
SELECT * FROM table WHERE CONCAT(nameFirst, ' ', nameLast) LIKE 'SEARCH_INPUT%' OR CONCAT(nameLast, ' ', nameFirst) LIKE 'SEARCH_INPUT%';
