在 MySQLINSERTINTO 文本中添加换行符

有人能告诉我如何在文本中添加一个新行，我输入一个 MySql 表？

我尝试在我用 INSERT INTO语句输入的行中使用 '\n'，但是 '\n'显示为原样。

事实上，我已经创建了一个表在微软访问与一些数据。MSAccess 使用 '\n'增加了新的线路。我正在将 MSAccess 表数据转换为 MySql。但是当我转换时，'\n'被忽略，当我在 PHP 表单上从 MySql 表中显示它时，所有的文本都显示在一行中。

有人能告诉我 MySQL 如何在文本中添加新行吗? 等待回复，谢谢! ！

330844 次浏览
小开

First of all, if you want it displayed on a PHP form, the medium is HTML and so a new line will be rendered with the <br /> tag. Check the source HTML of the page - you may possibly have the new line rendered just as a line break, in which case your problem is simply one of translating the text for output to a web browser.

小开

in an actual SQL query, you just add a newline

INSERT INTO table (text) VALUES ('hi this is some text
and this is a linefeed.
and another');
小开

In SQL or MySQL you can use the char or chr functions to enter in an ASCII 13 for carriage return line feed, the \n equivilent. But as @David M has stated, you are most likely looking to have the HTML show this break and a br is what will work.

小开

For the record, I wanted to add some line breaks into existing data and I got \n to work ok...

Sample data:

Sentence. Sentence. Sentence

I did:

UPDATE table SET field = REPLACE(field, '. ', '.\r\n')

However, it also worked with just \r and just \n.

小开

If you're OK with a SQL command that spreads across multiple lines, then oedo's suggestion is the easiest:

INSERT INTO mytable (myfield) VALUES ('hi this is some text
and this is a linefeed.
and another');

I just had a situation where it was preferable to have the SQL statement all on one line, so I found that a combination of CONCAT_WS() and CHAR() worked for me.

INSERT INTO mytable (myfield) VALUES (CONCAT_WS(CHAR(10 using utf8), 'hi this is some text', 'and this is a linefeed.', 'and another'));
小开 
INSERT INTO test VALUES('a line\nanother line');

\n just works fine here

小开

You can simply replace all \n with <br/> tag so that when page is displayed then it breaks line.

UPDATE table SET field = REPLACE(field, '\n', '<br/>')
小开

  1. You have to replace \n with <br/> before inset into database.

    $data = str_replace("\n", "<br/>", $data);

    In this case in database table you will see <br/> instead of new line.

    e.g.

    First Line
    Second Line

    will look like:

    First Line<br/>Second Line

  2. Another way to view data with new line. First read data from database. And then replace \n with <br/> e.g. :

    echo $data;
    $data = str_replace("\n", "<br/>", $data);
    echo "<br/><br/>" . $data;

    output:

    First Line Second Line

    First Line
    Second Line


    You will find details about function str_replace() here: http://php.net/manual/en/function.str-replace.php

小开 
INSERT INTO myTable VALUES("First line\r\nSecond line\r\nThird line");
小开

MySQL can record linebreaks just fine in most cases, but the problem is, you need <br /> tags in the actual string for your browser to show the breaks. Since you mentioned PHP, you can use the nl2br() function to convert a linebreak character ("\n") into HTML <br /> tag.

Just use it like this:

<?php
echo nl2br("Hello, World!\n I hate you so much");
?>

Output (in HTML):

Hello, World!<br>I hate you so much

Here's a link to the manual: http://php.net/manual/en/function.nl2br.php

小开

use <pre> tag instead of <p> in html to show your \n in database

小开

Adding to the answer given @DonKirby

INSERT INTO mytable (myfield) VALUES (CONCAT_WS(CHAR(10 using utf8), 'hi this is some text', 'and this is a linefeed.', 'and another'));

is unnecessary

The CHAR() function doesn't accept the full set of utf8 values. It accepts only ASCII values.

See - https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_char

Thus more appropriate would be to use CHAR(10 USING ASCII) in place of CHAR(10 USING utf8)


提交答案