MySQL 如何使用like运算符编写mysql存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15896351/
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
How to write mysql stored procedure using like operator
提问by jayant kumar
Following is my code:
以下是我的代码：
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `employee_with_asset`(IN name VARCHAR(250))
BEGIN
SELECT a.Asset_code,a.name as name1,a.type,a.description,`purchase date`,
`amc availability`,`amc renewal`,`employee Id`,b.Name FROM `asset_details` a,
employee b WHERE b.Name LIKE '%' + @name + '%' and a.`assigned to`=b.`employee Id`;
END
It is showing error near LIKE. How to solve it.
它在 LIKE 附近显示错误。如何解决。
回答by John Woo
the concatenation in mysql is done using CONCAT()
mysql 中的连接是使用 CONCAT()
LIKE CONCAT('%', @name , '%')
FULL STATEMENT
完整声明
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `employee_with_asset`
(
IN _name VARCHAR(250)
)
BEGIN
SELECT a.Asset_code,
a.name as name1,
a.type,
a.description,
`purchase date`,
`amc availability`,
`amc renewal`,
`employee Id`,
b.Name
FROM `asset_details` a
INNER JOIN employee b
ON a.`assigned to` = b.`employee Id`
WHERE b.Name LIKE CONCAT('%', _name , '%');
END $$
DELIMITER ;
回答by Trung Le Nguyen Nhat
The answer of John Woo is right but if you don't use utf8_general_ci collection, you must cast collection after WHERE clause (after each condition if you have multiple conditions) as the same as collection of the column you compare to like this
John Woo 的答案是正确的，但是如果您不使用 utf8_general_ci 集合，则必须在 WHERE 子句之后（如果有多个条件，则在每个条件之后）将集合转换为与您比较的列的集合相同
... WHERE name LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci
or multiple conditions
或多个条件
... WHERE name LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci
OR job LIKE CONCAT('%', @name , '%') COLLATE utf8_unicode_ci ...
回答by user1779283
PHP:
PHP：
$keyword = $_POST['keyword'];
$response = "call user_search('%$keyword%');";
Mysql:
mysql：
....where user.name like @keyword;
