MySQL資料中有很多換行符和回車符!!該咋辦?

2020-10-25 15:00:51

  今天一大早客戶給我打電話:「小陳兒,昨晚我往資料庫導了幾十萬條資料,然後在web介面很多都搜不到,你們系統做的啥玩意兒啊?這麼多BUG!得啵得啵得…」(省略2000字)又習慣性的噴了一遍我們這個專案做的辣雞~~
  得得得,客戶隨便噴,我就當吃了個早飯了🙂🙂🙂嗝兒~嗯,真飽;

對了,沒時間廢話,等米下鍋的朋友請點選目錄直接跳轉到方法操作區,請拿好飛機票;

  我到公司後先測試一下客戶給我的錯誤範例(果然搜不到),然後排查一下昨天的資料是否有問題,發現昨天客戶用excel匯入的資料中,存在很多\r\n這種換行符和回車符。

如:庫中資料為:「換行符\n換行符\n換行符」,然後在介面通過如下SQL查詢,那自然是搜不到咯!

SELECT * from `LOL` where name = '換行符換行符換行符';
  • 換行符:CHAR(10) = "\n"

  • 回車符: CHAR(13) = "\r"

  在MySQL中,回車符、換行符都屬於char型別,放到varchar中時,在navcat等外掛上是不顯示的,需要在mysql終端和navcat對比才能看出來(坑啊~)

navcat外掛顯示如下:
在這裡插入圖片描述
MySQL終端顯示如下:
(回車符\r還導致資料結果直接返回了,只顯示了一部分,MySQL你讓我說你啥好😰😰)

mysql> select * from `LOL`;
+----+-------------------------------+-----------+-------+
| id | hero_title                    | hero_name | price |
+----+-------------------------------+-----------+-------+
|  1 | D刀鋒之影                     | 泰隆      |  6300 |
|  2 | X迅捷斥候                     | 提莫      |  6300 |
|  8 | 換行符
換行符
換行符          | 換行符    |   450 |
回車符          | 回車符    |   450 |
+----+-------------------------------+-----------+-------+
4 rows in set (0.00 sec)

1、如何去掉換行符、回車符

如果條件允許,建議直接通過語句清除掉換行符和回車符,如下:

去除"hero_title"列中所有換行符 (通過以下兩種方式都可以)

-- 去除"hero_title"列中所有換行符 (通過以下兩種方式都可以)
update `LOL` set hero_title =  REPLACE(hero_title, '\n', '');
update `LOL` set hero_title =  REPLACE(hero_title, char(10), '');

– 去除"hero_title"列中所有回車符(通過以下兩種方式都可以)

-- 去除"hero_title"列中所有回車符
update `LOL` set hero_title =  REPLACE(hero_title, '\r', '');
update `LOL` set hero_title =  REPLACE(hero_title, char(13), '');

2、SELECT如何忽略"換行符、回車符"進行查詢

  如果條件不允許,比如客戶「很激動」並向你吼道:不能動庫裡的資料!…我笑了~

  那怎麼辦呢?可以通過修改SQL來過濾掉換行符、回車符,但一定程度上會影響查詢效率,而且要改很多程式碼,你懂得。

  銘記魯迅先生說的:哪裡有壓迫,哪裡就得有反抗!
在這裡插入圖片描述

範例如下:

-- 忽略掉換行符查詢
SELECT * from `LOL` where REPLACE(hero_title, char(10), '')  = '換行符換行符換行符';
-- 忽略掉回車符查詢
SELECT * from `LOL` where REPLACE(hero_title, char(13), '')  = '回車符回車符回車符';
-- 忽略掉換行符 & 回車符查詢
SELECT * from `LOL` where REPLACE(REPLACE(hero_title, char(13), ''), char(10), '')  = '回車符回車符回車符';

3、經驗總結

  類似的符號一般要求入庫時就清理掉,如果必須保留也建議用其他自定義標識代替(如自創的唯一標識,我叫它大眼瞪小眼: o_O),後臺程式中做替換處理,免得影響到索引效率。
  好了,希望這篇文章能幫到你,或許你可以mark一下,當同事遇到該問題時,甩她臉上~

附、一張有故事的照片(六)

在這裡插入圖片描述

1月28日
正是武漢疫情最嚴重的時候
支援湖北醫療隊隊員呂俊馬上就要出征了
他的未婚妻知道丈夫這一去會很久,很苦
在臨行前的最後一刻
他們相擁在一起
而那眼淚也止不住的落下

_陳哈哈 CSDN認證部落格專家 MySQL江湖人
北漂5年的程式設計師,多個國家網際網路安全專案核心開發人員。一個純粹的MySQL愛好者,維護著非著名公眾號「MySQL江湖路」。曾任央視知名欄目《百家講壇》熱心觀眾。