Adding a line break in MySQL INSERT INTO text

问题:

Could someone tell me how to add a new line in a text that I enter in a MySql table?

I tried using the '\n' in the line I entered with INSERT INTO statement but '\n' is shown as it is.

Actually I have created a table in MS Access with some data. MS Access adds new line with '\n'. I am converting MS Access table data into MySql . But when I convert, the '\n' is ignored and all the text is shown in one single line when I display it from MySql table on a PHP form.

Can anyone tell me how MySQL can add a new line in a text? Awaiting response, thanks!!

回答1:

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'));

回答2:

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');

回答3:

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.

回答4:

INSERT INTO test VALUES('a line\nanother line');

\n just works fine here

回答5:

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

回答6:

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

回答7:

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.

回答8:

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

回答9:

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.

回答10:

It sounds to me like you're confusing the contents of the table some particular tool's rendering of those contents.

I presume you're using the command line (shell) client, the mysql prompt both to enter and to display the text in this column. I'm also guessing that you're \n is being stored as a newline and that it's only the way it's being presented that's causing your confusion.

Try using a GUI tool for accessing your MySQL data.

回答11:

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/>')