MySQL 使用 LIKE 进行 SQL 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12176824/
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
SQL UPDATE with LIKE
提问by jumax
I was trying to update approx 20,000 records in a table by using statements like this one,
however, I got the message say 0 row(s) affectedso it didn't work.
我试图通过使用这样的语句来更新表中大约 20,000 条记录，但是，我收到消息说它0 row(s) affected不起作用。
UPDATE nc_files SET title ="Worklog details" WHERE "log_name" LIKE "%PC01%"
The log_namefield has all the file names with mixed file extensions and cases ,e.g.
该log_name字段具有混合文件扩展名和大小写的所有文件名，例如
PC01.TXT | Worklog details
PC02.txt | Project Summary
PC03.DOC| Worklog details
PC04.doc| Project Summary
The reason why I need to use LIKEis that the updated file only have file name without extension, e.g.
我需要使用的原因LIKE是更新后的文件只有文件名而没有扩展名，例如
PC01 | Worklog details
PC02 | Project Summary
How do I update records by using LIKE?
如何使用 更新记录LIKE？
回答by Devart
The log_nameis a field name, remove literal quotes from it -
这log_name是一个字段名称，从中删除文字引号 -
UPDATE nc_files SET title ="Worklog details" WHERE log_name LIKE "%PC01%"
回答by Omesh
this is because your column name log_nameshould not be in 'quotes.
这是因为您的列名log_name不应该用'引号引起来。
"log_name" LIKE "%PC01%"condition will always fail and zero rows will get updated, try this:
"log_name" LIKE "%PC01%"条件总是会失败，零行会被更新，试试这个：
UPDATE nc_files
SET title ="Worklog details"
WHERE log_name LIKE "%PC01%";
回答by pilcrow
By default MySQL allows double quoted names to be understood as eitheridentifiers (like column names) or as string literals.
默认情况下，MySQL允许双引号可以理解为名称或者标识符（如列名）或字符串文字。
This is meant as a convenience, but I find the semantic ambiguity frustrating. MySQL must resolve the ambiguity, and cannot magically always guess the coder's intention, as you discovered.
这是为了方便，但我发现语义歧义令人沮丧。MySQL 必须解决歧义，并且不能像您发现的那样总是神奇地猜测编码器的意图。
-- In default sql_mode under 5.5
--
SELECT "foo" -- "foo" is a *column* if tbl.foo exists, otherwise a string
FROM "tbl" -- Oops! ER_PARSE_ERROR (1064) Can't do this in default mode.
WHERE "foo" = "foo"; -- Both of these are strings
So, the way around it is to force unambiguous interpretation of identifiers:
因此，绕过它的方法是强制对标识符进行明确解释：
- Do not quote simple identifiers
- Use MySQL-specific backticks for quoting
(This is ODBC's SQL_IDENTIFIER_QUOTE_CHAR) - Always override the change the
sql_modeto include ANSI_QUOTES(or a superset of it)
Double quotes are then exclusively for identifiers, single quotes for strings.
- 不要引用简单的标识符
- 使用 MySQL 特定的反引号进行引用
（这是 ODBC 的 SQL_IDENTIFIER_QUOTE_CHAR） - 始终覆盖更改
sql_mode以包含ANSI_QUOTES（或其超集）
然后双引号专门用于标识符，单引号用于字符串。
#3 is my personal favorite, for readability and portability. The problem is it tends to surprise people who only know MySQL, and you have to remember to override the default.
#3 是我个人最喜欢的，可读性和便携性。问题是它往往会让只知道 MySQL 的人感到惊讶，你必须记住覆盖默认值。
回答by DataNetwork
was looking for same but didn't work for me.... but this works perfect:
正在寻找相同的但对我不起作用....但这很完美：
$title = "Worklog details";
$log_name = 'PC01';
$update = $connection -> prepare("UPDATE nc_files
SET title = ?
WHERE log_name = ?");
$update->bind_param('ss',$title,$log_name);
$update->execute();
$update->close();
回答by user7137340
I had a similar trouble. The problem are the quotations marks " I Fixed my code as follow.
我遇到了类似的麻烦。问题是引号“我修正了我的代码如下。
UPDATE Table SET Table.Field = "myreplace" WHERE (((Table.Field) Like '%A-16%'));
UPDATE Table SET Table.Field = "myreplace" WHERE (((Table.Field) Like '%A-16%'));
Regards, Alexwin1982
问候， Alexwin1982
回答by Israr Khan
Try replace keyword
UPDATE nc_filesSET title= REPLACE(title, 'PC01', 'Worklog details') WHERE log_name LIKE '%PC01%'
尝试替换关键字 UPDATE nc_filesSET title= REPLACE( title, 'PC01', 'Worklog details') WHERE log_name LIKE '%PC01%'
回答by Joe G Joseph
"log_name" should not be in quotes
