MYSql 中的 REPLACE 换行符不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13273343/
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
REPLACE new line character in MYSql not working
提问by Pit Digger
I executed following query and for some reason its not replacing new line character in database . It says Rows matched 1 but no change . What can be wrong ?
我执行了以下查询，出于某种原因，它没有替换数据库中的换行符。它说 Rows 匹配 1 但没有变化。有什么问题？
mysql> UPDATE aboutme SET abouttext=REPLACE(abouttext,'\n','') WHERE userid='5099a95cd944b8.22468149';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
回答by Ryan
You can match a newline character using \n, not \\n.
您可以使用\n，而不是来匹配换行符\\n。
Code:
代码：
UPDATE aboutme
SET abouttext=REPLACE(abouttext,'\n','')
WHERE userid='5099a95cd944b8.22468149';
回答by Hammad Khan
If \ndoes not work as in my case, the following worked \r\n
如果\n在我的情况下不起作用，以下工作\r\n
UPDATE aboutme
SET abouttext=REPLACE(abouttext,'\r\n','')
WHERE userid='5099a95cd944b8.22468149';
In my case it has been a web application.
就我而言，它是一个 Web 应用程序。
回答by lauralacarra
You think that it contains \n, but it has \r.
你认为它包含\n，但它有\r。
update [Table] set [column]=replace(convert([column] using utf8) ,'\r','');
In your case:
在你的情况下：
update aboutme set abouttext=replace(convert(abouttext using utf8) ,'\r','');
回答by jcho360
this is what happening
这就是正在发生的事情
mysql> mysql> select * from t1 limit 3;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> update t1 set first_name=replace(first_name,'abc','') where first_name='ed';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 10 Changed: 0 Warnings: 0
mysql> select * from t1 limit 3;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | ED | CHASE | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)
mysql> update t1 set first_name=replace(first_name,'ED','EDD') where first_name='ed';
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> select * from t1 limit 3;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 1 | PENELOPE | GUINESS | 2006-02-15 04:34:33 |
| 2 | NICK | WAHLBERG | 2006-02-15 04:34:33 |
| 3 | EDD | CHASE | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)
What I meant is that the where condition it's working that's why you have 'rows matched: 1' but your replace don't find \\nto replace it, that's why changed: 0so check your table data.
我的意思是它工作的 where 条件这就是为什么你有“行匹配：1”但你的替换没有找到\\n替换它，这就是为什么要changed: 0检查你的表数据。
回答by Do Hoa Vinh
the REPLACE function is case sensitive, i think it belongs MySql server version
REPLACE 函数区分大小写，我认为它属于 MySql 服务器版本
description=REPLACE(description, 'Videosite', 'video.5la.net') is different result with description=REPLACE(description, 'VideoSite', 'video.5la.net')
description=REPLACE(description, 'Videosite', 'video.5la.net') 与 description=REPLACE(description, 'VideoSite', 'video.5la.net') 的结果不同
