如何在MySQL数据库中存储换行?

I'm going around and around in circles in my mind trying to decide how best I should store newlines in a MySQL database.

我在脑子里不停地转着圈圈，试图决定如何最好地在MySQL数据库中存储新行。

A bit of background; the user is asked to complete a textarea. This has to stored in a MySQL database before being read back out and the content included in an HTML email.

一点背景;用户被要求完成一个文本区域。这必须存储在一个MySQL数据库中，然后再读回来，以及包含在HTML电子邮件中的内容。

Should I store them as:

我是否应该将它们存储为:

1) String literal - surely this is dangerous and bad practice

1)字符串字面意义——当然这是危险和糟糕的做法

2) As a string with \r\n in - when I read this back out of the database its read as 4 characters so nl2br() fails to correctly replace them.

2)作为一个带\r\n的字符串—当我从数据库中读取时，它的读取为4个字符，因此nl2br()无法正确地替换它们。

3) As HTML   - as it has to be html entity encoded to be stored it ends up being stored as   so when it gets to the email   is printed rather than an actual newline. Passing it through html_entity_decode() would decode other characters that need to be encoded.

3)作为HTML
-因为它必须是HTML实体编码才能被存储，所以它最终被存储为 所以当它到达电子邮件
是打印而不是实际的换行。通过html_entity_decode()传递它将解码需要编码的其他字符。

Any help grately appreciated.

炉篦感激任何的帮助。

3 个解决方案

#1

Store it as it is but escape first. This is your option 1. When you need to present this data apply whatever function you need to format it. If you need to show it in HTML use nl2br() and htmlentities() functions. That'll work for mail also.

先把它保存起来，然后再逃走。这是你的选择1。当您需要显示此数据时，应用您需要的任何函数来格式化它。如果需要在HTML中显示，请使用nl2br()和htmlentities()函数。这也适用于邮件。

If you have a text area data like this,

如果你有这样的文本区域数据，

$text="Hello Sir,
I am Robert'); Drop table students; --
.....
Yours most obedient pupil
.....";

Just store it as it is after you escape it, or use a prepared statement.

只需在转义后将其存储为原样，或者使用一个准备好的语句。

$text = $mysqli->real_escape_string($text);

#2

use the javascript string replace function str.replace(/\n/g,'\\n')

使用javascript字符串替换函数str.replace(/\n/g，'\ n')

to transform all newlines to escaped literals in the textarea while forming the url or post string.

在形成url或post字符串时，将所有新行转换为文本区域中的转义文字。

Retrieving the same using PHP's json_encode() and displaying it through a textarea works well for me.

使用PHP的json_encode()检索相同的内容，并通过文本区域显示它，对我来说效果很好。

#3

It depends on application type (how you want to display your text).

它取决于应用程序类型(如何显示文本)。

If this is HTML e-mail editor - i think html format will be better.

如果这是HTML电子邮件编辑器——我认为HTML格式会更好。

In most CMS (content management systems) HTML format is used (joomla, wordpress, prestashop etc.), because you can just read this from database and send to browser.

在大多数CMS(内容管理系统)中使用HTML格式(joomla, wordpress, prestashop等)，因为你可以从数据库中读取这个格式，然后发送到浏览器。

Besides - you probably may need other HTML tags anyway (like  for bold or <center>).

此外，您可能还需要其他的HTML标记(比如的粗体或 <中心> )。

I think using \r\n format is better for non-web applications, when data is displayed on windows form controls.

当数据显示在windows窗体控件上时，我认为使用\r\n格式对非web应用程序更好。

One more thing - you may store them in both ways: -   for HTML view - \r\n for HTML source (to add some newline's and make more readable html source code)

还有一件事——您可以以两种方式存储它们:-
用于HTML视图- \r\n用于HTML源代码(添加一些换行符，使HTML源代码更具可读性)

By more readable html i mean this:

我说的更可读的html是:

<center>
This is header
</center>
<p>
This is paragraph.
<br>
Second line.
</p>

Instead of this:

而不是:

<center>This is header</center><p>This is paragraph.<br>Second line.</p>

#1