Solution to many line breaks and carriage returns in MySQL data

Find the problem

This morning, a client called me and said, "Xiao Chen, I imported hundreds of thousands of data into the database last night, but I couldn't find many of them on the web interface. What the hell is your system? So many bugs! Debo debo de…" (2,000 words omitted) and he habitually criticized our project for being a piece of crap~~

OK, OK, the customer can spray whatever he wants, I will just treat it as having breakfast 🙂🙂🙂🙂 Burp~ Mm, I am really full;

After I arrived at the company, I first tested the error example given by the customer (I couldn't find it), and then checked whether there was a problem with yesterday's data. I found that in the data imported by the customer using Excel yesterday, there were many line breaks and carriage returns such as \r\n.

For example: the data in the database is: "line break\nline break\nline break", and then the following SQL query is used in the interface, then of course it cannot be found!

SELECT * from `LOL` where name = 'newline newline newline';

Newline character: CHAR(10) = "\n"

Carriage return character: CHAR(13) = "\r"

In MySQL, carriage return and line feed characters are both char types. When they are placed in varchar, they are not displayed on plug-ins such as navcat. You need to compare the mysql terminal and navcat to see them (pitfall~)

The navcat plugin displays the following:

The MySQL terminal displays the following:

(The carriage return character \r also caused the data results to be returned directly, and only a part of it was displayed. MySQL, what can I say about you? 😰😰)

mysql> select * from `LOL`;
+----+-------------------------------+-----------+-------+
| id | hero_title | hero_name | price |
+----+-------------------------------+-----------+-------+
| 1 | DBlade Shadow | Talon | 6300 |
| 2 | X-Swift Scout | Teemo | 6300 |
| 8 | newline newline newline| newline| 450 |
Carriage Return | Carriage Return | 450 |
+----+-------------------------------+-----------+-------+
4 rows in set (0.00 sec)

1. How to remove line breaks and carriage returns

If conditions permit, it is recommended to clear the line feed and carriage return characters directly through statements, as follows:

Remove all line breaks in the "hero_title" column (you can do this in either of the following ways):

-- Remove all line breaks in the "hero_title" column (either of the following methods will work)
update `LOL` set hero_title = REPLACE(hero_title, '\n', '');
update `LOL` set hero_title = REPLACE(hero_title, char(10), '');

– Remove all carriage returns from the "hero_title" column (you can do this in either of the following ways)

-- Remove all carriage returns from the "hero_title" column update `LOL` set hero_title = REPLACE(hero_title, '\r', '');
update `LOL` set hero_title = REPLACE(hero_title, char(13), '');

2. How to ignore "line feed and carriage return" in SELECT query

If conditions do not permit, for example, the customer is "very excited" and yells at you: You can't touch Curry's data! …I laughed~

What should I do then? You can filter out line breaks and carriage returns by modifying the SQL, but this will affect the query efficiency to a certain extent, and you will have to change a lot of code, as you know.

Remember what Mr. Lu Xun said: Where there is oppression, there must be resistance!

Here is an example:

-- Ignore line breaks query SELECT * from `LOL` where REPLACE(hero_title, char(10), '') = 'line break line break line break';
-- Ignore the carriage return character query SELECT * from `LOL` where REPLACE(hero_title, char(13), '') = 'Carriage return carriage return carriage return';
-- Ignore line breaks and carriage returns. SELECT * from `LOL` where REPLACE(REPLACE(hero_title, char(13), ''), char(10), '') = 'Carriage return carriage return carriage return';

3. Experience summary

Similar symbols are generally required to be cleared when entering the database. If they must be retained, it is recommended to replace them with other custom identifiers (such as a self-created unique identifier, which I call "big eyes glaring at small eyes: o_O"), and do the replacement in the background program to avoid affecting the indexing efficiency.

Well, I hope this article can help you. Maybe you can mark it and throw it in the face of your colleagues when they encounter this problem.

This is the end of this article about how to solve the problem of many line breaks and carriage returns in MySQL data. For more information about MySQL line breaks and carriage returns, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!