MySQL TRIM 函数是否不修剪换行符或回车符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/281724/
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
Does the MySQL TRIM function not trim line breaks or carriage returns?
提问by Thomas Owens
From my experiments, it does not appear to do so. If this is indeed true, what is the best method for removing line breaks? I'm currently experimenting with the parameters that TRIM accepts of the character to remove, starting with trimming \nand \r.
从我的实验来看，它似乎没有这样做。如果确实如此，那么删除换行符的最佳方法是什么？我目前正在试验 TRIM 接受要删除的字符的参数，从修剪\n和\r.
采纳答案by Peter Crabtree
Trim()in MySQL only removes spaces.
Trim()在 MySQL 中只删除空格。
I don't believe there is a built-in way to remove all kinds of trailing and leading whitespace in MySQL, unless you repeatedly use Trim().
我不相信有一种内置的方法可以删除 MySQL 中的各种尾随和前导空格，除非您反复使用Trim().
I suggest you use another language to clean up your current data and simply make sure your inputs are sanitized from now on.
我建议您使用另一种语言来清理您当前的数据，并确保从现在开始对您的输入进行消毒。
回答by
My line breaks were in the middle of the string, and I didn't have control over the source data. The following mysql command worked for me:
我的换行符位于字符串的中间，我无法控制源数据。以下 mysql 命令对我有用：
REPLACE(FIELD,'\r\n',' ')
回答by Lucas
Yes, Trim()will work in MySQL. You have two choices.
是的，Trim()将在 MySQL 中工作。你有两个选择。
1) select it out:
1）选择它：
select trim(BOTH '\n' from [field_name]) as field
If that doesn't work, try '\r', if that doesn't work, try '\n\r'.
如果这不起作用，请尝试'\r'，如果这不起作用，请尝试'\n\r'。
2) replace the bad data in your table with an update...
2）用更新替换表中的坏数据...
update [table_name] set [field_name] = trim(BOTH '\n' from [field_name])
I recommend a select first to determine which line break you have (\r or \n).
我建议首先选择来确定您有哪个换行符（\r 或 \n）。
回答by rjmunro
The standard MySQL trimfunction is not like trim functions in any other languages I know as it only removes exact string matches, rather than any characters in the string. This stored function is more like a normal trim you'd find in PHP, or strip in python etc.
标准 MySQLtrim函数不像我知道的任何其他语言中的修剪函数，因为它只删除精确的字符串匹配，而不是字符串中的任何字符。这个存储的函数更像是你在 PHP 中找到的普通修剪，或者在 python 等中删除。
CREATE FUNCTION `multiTrim`(string varchar(1023),remove varchar(63)) RETURNS varchar(1023) CHARSET utf8
BEGIN
-- Remove trailing chars
WHILE length(string)>0 and remove LIKE concat('%',substring(string,-1),'%') DO
set string = substring(string,1,length(string)-1);
END WHILE;
-- Remove leading chars
WHILE length(string)>0 and remove LIKE concat('%',left(string,1),'%') DO
set string = substring(string,2);
END WHILE;
RETURN string;
END;
You should then be able to do:
然后你应该能够做到：
select multiTrim(string,"\r\n\t ");
and it should remove all newlines, tabs and spaces.
它应该删除所有换行符、制表符和空格。
回答by rjmunro
select trim(both '\r\n' from FIELDNAME) from TABLE;should work if select trim(both '\n' from FIELDNAME) from TABLE;doesn't work.
select trim(both '\r\n' from FIELDNAME) from TABLE;如果select trim(both '\n' from FIELDNAME) from TABLE;不起作用，应该起作用。
回答by Peter Crabtree
i could only get it to work by making the char;
我只能通过制作来让它工作char；
trim(both char(13) from fieldname)
回答by LeppyR64
select trim(both '\n' from FIELDNAME) from TABLE;
回答by Ilya
The answers above, when combined, work. A full example of replacing linebreaks at the beginning and end of the field looks like this:
上面的答案结合起来就行了。在字段的开头和结尾替换换行符的完整示例如下所示：
UPDATE table SET field=REPLACE(field, field, TRIM(BOTH '\r\n' FROM field))
回答by Punit Raizada
I faced the same issue with one of the fields. There is no perfect solution. In my case i was lucky that the length of the field was supposed to be 6. So i used a query like
我在其中一个领域遇到了同样的问题。没有完美的解决方案。在我的例子中，我很幸运字段的长度应该是 6。所以我使用了一个类似的查询
update events set eventuniqueid = substring(eventuniqueid, 1, 6) where length(eventuniqueid) = 7;
You will just have to choose the best option based on your need. The replace '\n' and '\r\n' did not work for me and just ended up wasting my time.
您只需根据需要选择最佳选项。替换 '\n' 和 '\r\n' 对我不起作用，最终浪费了我的时间。
回答by boardtc
REPLACE(FIELD,'\r\n',' ')works perfectly on MySql 5.1 database
REPLACE(FIELD,'\r\n',' ')在 MySql 5.1 数据库上完美运行
