如何从mysql中的数据行中删除换行符?

我可以遍历 php 脚本中的所有行并执行

UPDATE mytable SET title = "'.trim($row['title']).'" where id = "'.$row['id'].'";

和trim可以去掉\n

但我只是想知道是否可以在一个查询中完成相同的事情?

 update mytable SET title = TRIM(title, '\n') where 1=1

能用吗?然后我就可以执行这个查询而不需要循环！

谢谢

(PS:我可以测试一下，但表很大，不想弄乱数据，所以想想你之前是否测试过这样的东西)

解决方案

你的语法错误:

update mytable SET title = TRIM(TRAILING '\n' FROM title)

添加:

如果换行符在字段的开头:

update mytable SET title = TRIM(LEADING '\n' FROM title)

I can loop through all of the rows in a php script and do

UPDATE mytable SET title = "'.trim($row['title']).'" where id = "'.$row['id'].'";

and trim can remove \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)

解决方案

your syntax is wrong:

update mytable SET title = TRIM(TRAILING '\n' FROM title)

Addition:

If the newline character is at the start of the field:

update mytable SET title = TRIM(LEADING '\n' FROM title)