MySQL 如何从mysql中的数据行中删除换行符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1504962/
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 remove new line characters from data rows in mysql?
提问by TigerTiger
I can loop through all of the rows in a php script and do
我可以遍历 php 脚本中的所有行并执行
UPDATE mytable SET title = "'.trim($row['title']).'" where id = "'.$row['id'].'";
UPDATE mytable SET title = "'.trim($row['title']).'" where id = "'.$row['id'].'";
and trim can remove \n
和修剪可以删除\n
But I was just wondering if something same could be done in one query?
但我只是想知道是否可以在一个查询中完成相同的事情？
update mytable SET title = TRIM(title, '\n') where 1=1
will it work? I can then just execute this query without requiring to loop through!
它会起作用吗？然后我可以执行这个查询而不需要循环！
thanks
谢谢
(PS: I could test it but table is quite large and dont want to mess with data, so just thought if you have tested something like this before)
（PS：我可以测试，但表很大，不想弄乱数据，所以想想你之前是否测试过这样的东西）
回答by longneck
your syntax is wrong:
你的语法错误：
update mytable SET title = TRIM(TRAILING '\n' FROM title)
回答by ?ukasz Rysiak
UPDATE test SET log = REPLACE(REPLACE(log, '\r', ''), '\n', '');
worked for me.
为我工作。
while its similar, it'll also get rid of \r\n
虽然它相似，但它也会摆脱\r\n
回答by Geo
1) Replace all new lineand tabcharacters with spaces.
1)用空格替换所有新行和制表符。
2) Remove all leading and trailingspaces.
2) 删除所有前导和尾随空格。
UPDATE mytable SET `title` = TRIM(REPLACE(REPLACE(REPLACE(`title`, '\n', ' '), '\r', ' '), '\t', ' '));
回答by Phaneendra Kasalanati
update mytable set title=trim(replace(REPLACE(title,CHAR(13),''),CHAR(10),''));
update mytable set title=trim(replace(REPLACE(title,CHAR(13),''),CHAR(10),''));
Above is working for fine.
以上工作正常。
回答by Ulysnep
Removes trailing returns when importing from Excel. When you execute this, you may receive an error that there is no WHERE; ignore and execute.
从 Excel 导入时删除尾随返回。当您执行此操作时，您可能会收到没有 WHERE 的错误；忽略并执行。
UPDATE table_name SET col_name = TRIM(TRAILING '\r' FROM col_name)
回答by Risse
UPDATE mytable SET title=TRIM(REPLACE(REPLACE(title, "\n", ""), "\t", ""));
回答by Darshan Montgomery
My 2 cents.
我的 2 美分。
To get rid of my \n's I needed to do a \\n. Hope that helps someone.
为了摆脱我的\n，我需要做一个\\n。希望能帮助某人。
update mytable SET title = TRIM(TRAILING '\n' FROM title)
回答by Lue Inubashiri
For new line characters
对于换行符
UPDATE table_name SET field_name = TRIM(TRAILING '\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r\n' FROM field_name);
For all white space characters
对于所有空白字符
UPDATE table_name SET field_name = TRIM(field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\r\n' FROM field_name);
UPDATE table_name SET field_name = TRIM(TRAILING '\t' FROM field_name);
Read more: MySQL TRIM Function
阅读更多： MySQL TRIM 函数
回答by jay_mziray
Playing with above answers, this one works for me
玩上面的答案，这个对我有用
REPLACE(REPLACE(column_name , '\n', ''), '\r', '')
