MySQL removes line breaks and carriage returns from fields

UPDATE SMS_PROVINCE set provincename = REPLACE (PROVINCENAME, CHAR (10), ''), CHAR (13 ),'');

Environment: RHEL5 + MySQL

Solution:

UPDATE tablename SET field = REPLACE (field, CHAR (10), "), CHAR (13 ),");

Char (10): line feed

Char (13): Carriage Return

Cause:

You can use either of the following methods to generate an excel report:

1) manually generate

Export The data in the table to generate a CSV file.

Use mysqldump to export data

# Mysqldump-u xxx-p -- tab =/tmp/-- fields-terminated-by = "#" DBName TBName

The tbname.txt file will be generated in the tmpdirectory.

Import the generated txt file in EXCEL

2) directly generate a csv file

Mysqldump-u samu-p-T -- fields-terminated-by = "," -- fields-enclosed-by = ""

-- Lines-terminated-by = "\ n" -- fields-escaped-by = "" test Customer

Or:

Mysqldump-u samu-p -- tab =/tmp/-- fields-terminated-by = "," -- fields-enclosed-by = ""

-- Lines-terminated-by = "\ n" -- fields-escaped-by = "" test Customer

However, no matter which method is used, if a column in the table contains a carriage return or line feed,

The generated CSV file or excel file will split the original line of data into two rows.

Because CSV or excel is imported, the number of data entries is determined by the Data row.

Therefore, you must replace the carriage return or line break in the field before this.