前言

在開始今天的博客內(nèi)容之前，正在看博客的您先來看看以下這兩條sql，如果您剛好還有MySQL的環(huán)境。不妨先猜測(cè)一下它輸出的內(nèi)容，然后看看是否有什么區(qū)別？第一條sql如下：

select length('G30L3B01') as l1;

接著再來看另一條sql，sql腳本如下：

select length('G30L3 B01') as l2;

各位不妨猜測(cè)一下，上面兩條sql語句的執(zhí)行結(jié)果l1和l2分別是多少？是不是在你的預(yù)料之中。 這里不賣關(guān)子了，相信執(zhí)行過sql的朋友一定看到了結(jié)果。沒有數(shù)據(jù)庫客戶端的朋友來看我的執(zhí)行結(jié)果。在給出結(jié)果之前，首先把基礎(chǔ)環(huán)境介紹一下。這里的服務(wù)器用的的個(gè)人的Windows 7 專業(yè)版開發(fā)機(jī)，數(shù)據(jù)庫服務(wù)用的是MySQL5.7，打開的查詢服務(wù)窗口所在的數(shù)據(jù)庫編碼是UTF-8。

上面這個(gè)結(jié)果有沒有超出你的預(yù)想，這兩個(gè)字符串“看”起來似乎是一模一樣的。這里的“看”我打了引號(hào)，至于原因，后面會(huì)講到。本文即以上述場(chǎng)景為例，講解在MySQL中，為什么會(huì)有這種“看”起來一致，但實(shí)際上不一樣的問題，通過現(xiàn)象找本質(zhì)，通過一步一步的排查，找到問題的根源，最后在尋根溯源后，找解決的辦法；從應(yīng)用代碼編程的角度和底層數(shù)據(jù)庫的角度來解決上述問題。如果您現(xiàn)在也遇到了這種“看”起來不正常的值，不妨一起交流一下。

一、問題的由來

首先依然要介紹一下上述問題的出現(xiàn)場(chǎng)景，以便于其它的朋友在此情此景下，有更大的印象。因此本節(jié)首先將對(duì)問題的場(chǎng)景進(jìn)行詳細(xì)的描述。這里打算從兩個(gè)方面講述，第一個(gè)方面是講述需求背景，即在什么情況下作這個(gè)事。第二個(gè)是講述數(shù)據(jù)背景，把相關(guān)的表設(shè)計(jì)也說明一下。

1、需求背景

事情發(fā)生的背景是這樣的，客戶要求我們做一個(gè)功能，他們會(huì)提供一個(gè)數(shù)據(jù)的Excel模板，然后我們需要將這些數(shù)據(jù)批量導(dǎo)入到數(shù)據(jù)庫中。這個(gè)需求咋一看起來，是一個(gè)非常簡(jiǎn)單的需求啊。讀取Excel的依賴庫，一大把。解析Excel的數(shù)據(jù)，然后批量插入到數(shù)據(jù)庫中，對(duì)于MybatisPlus或者其它的ORM工具都是非常簡(jiǎn)單的事。當(dāng)時(shí)我們選擇的是阿里巴巴開源的讀取Excel的組件。感興趣的朋友可以自己上github搜索一下。然后也基于組件也已經(jīng)將Excel沒個(gè)表格都讀取到了內(nèi)存中，然后調(diào)用MP的批量插入方法，意外的是在進(jìn)行批量插入的時(shí)候數(shù)據(jù)庫報(bào)錯(cuò)了。下面還是將數(shù)據(jù)庫的表先做一個(gè)介紹。

2、數(shù)據(jù)表結(jié)構(gòu)

為了實(shí)現(xiàn)將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，根據(jù)面向?qū)ο蟮脑瓌t，我們將Excel表格中的每個(gè)單元格都設(shè)計(jì)成了一個(gè)字段，然后對(duì)字段的數(shù)據(jù)類型進(jìn)行了設(shè)計(jì)，同時(shí)包括數(shù)據(jù)長(zhǎng)度。其中有一個(gè)項(xiàng)是輸入數(shù)據(jù)的編碼，然后用戶是有編碼規(guī)則的，每一個(gè)數(shù)據(jù)都有一個(gè)對(duì)應(yīng)的編碼規(guī)則。我們簡(jiǎn)單的看了他們的編碼規(guī)則，得知其長(zhǎng)度大致為8位，因此我們?cè)跀?shù)據(jù)庫中設(shè)計(jì)成了varchar(8)；這么設(shè)計(jì)其實(shí)是中規(guī)中矩的，非常合理。然而在上面的批量入庫過程中就一直報(bào)對(duì)應(yīng)的這個(gè)字段too long。這個(gè)異?？雌饋砗芷婀郑?yàn)槲覀內(nèi)斯と?ldquo;看”的時(shí)候，這個(gè)字符串的長(zhǎng)度確實(shí)是8。于是陷入了沉思。

二、定位問題

既然在開發(fā)過程當(dāng)中出現(xiàn)了問題，那么如何解決問題呢？在解決問題之前，首先要定位問題，只有正確的定位問題，才能對(duì)癥下藥，問題才能迎刃而解。因此本節(jié)將重點(diǎn)講解怎么定位問題。

1、初步的問題

當(dāng)看到上面問題的時(shí)候，第一感覺是懵的，剛開始都有點(diǎn)不敢相信自己的眼睛。因?yàn)槿庋鄹?ldquo;看”不出來這個(gè)字符串居然不是8位。最開始懷疑的是在字符串的前后可能存在空格，因此導(dǎo)致了其長(zhǎng)度超出了8位。為此我們將數(shù)據(jù)進(jìn)行去空，使用sql進(jìn)行去空如下：

SELECT length('G30L3B01') AS VisibleLength,
       length(replace('G30L3 B01', '?', '')) AS CleanLength;

然后發(fā)現(xiàn)還是不對(duì)，經(jīng)過替換后其長(zhǎng)度還是11，說明還是超長(zhǎng)了。 

2、編碼是否有問題

在進(jìn)行空字符過濾之后還是沒有解決問題。于是換了一個(gè)方向，想著有沒有可能是字符集的問題。其實(shí)大家可以看看UTF-8的字符集規(guī)范，字符集雖然會(huì)有一定的影響，但是這里存儲(chǔ)的都是英文和數(shù)字，其長(zhǎng)度均是標(biāo)準(zhǔn)的1，因此不存在字符的問題。問題一下子沒有了方向，不知道往哪個(gè)方面去排查。

3、依然回到字符本身

在第一次嘗試了空字符替換無果，又排除了字符編碼的問題后，再一次將目光投入到字符本身。這一次的想法是，雖然空字符本文替換后，長(zhǎng)度還是11，但是這并不說明其內(nèi)容一定是空格。有沒有什么其它的東西在搗亂呢。有時(shí)候找問題就是這樣，反反復(fù)復(fù)。

三、深入字符本身

上面從幾個(gè)方面分析了可能存在的問題，尤其是第三點(diǎn)，我們從最開的字符又回到了起點(diǎn)。那么這次采用什么分析思路呢？本小節(jié)深入道來。

1、回歸本質(zhì)

為了來看看這個(gè)數(shù)據(jù)到底是什么？我們將字符串轉(zhuǎn)為十六進(jìn)制的字符串，通過比較原始字符串來看看區(qū)別。在MySQL中可以使用HEX(str)實(shí)現(xiàn)轉(zhuǎn)換。相關(guān)的轉(zhuǎn)換SQL如下所示：

SELECT HEX('G30L3B01') AS hex_representation,HEX('G30L3 B01') as n2;

hex_representation	n2
4733304C33423031	4733304C33EFBBBF423031

不知道眼尖的你發(fā)現(xiàn)了問題沒有，上面這條SQL執(zhí)行完之后發(fā)現(xiàn)，其目標(biāo)字符串似乎不一樣啊。后面的字符串，也就是有問題的字符串其長(zhǎng)度真的超長(zhǎng)了。下面仔細(xì)對(duì)比這兩個(gè)字符串。

4733304C33        423031    
4733304C33 EFBBBF 423031

為了方便展示，我把相同的部分進(jìn)行對(duì)比，我們發(fā)現(xiàn)，前面的字符經(jīng)過十六進(jìn)制的轉(zhuǎn)換后，一共16個(gè)字節(jié)，8位。是符合我們的預(yù)期的，而下面的十六進(jìn)制字符則多了6字節(jié)，一共22字節(jié)，記11位。這也就是為什么這兩個(gè)字符串不一樣的原因。那么這個(gè)多出來的EFBBBF又是什么呢？由于這是轉(zhuǎn)成了十六進(jìn)制的表示，我們需要將其轉(zhuǎn)為十進(jìn)制。將十六進(jìn)制轉(zhuǎn)十進(jìn)制，如果不想計(jì)算的朋友可以在網(wǎng)頁中直接搜索：

當(dāng)然你也可以在MySQL中進(jìn)行進(jìn)制轉(zhuǎn)換，轉(zhuǎn)換方法如下：

-- CONVERT(CONV('EFBBBF', 16, 10), UNSIGNED) 是在mysql中實(shí)現(xiàn)將16進(jìn)制轉(zhuǎn)10進(jìn)制數(shù)據(jù)
select length(char(15711167)),hex(char(15711167)),CONVERT(CONV('EFBBBF', 16, 10), UNSIGNED);

執(zhí)行之后，可以看到：

length(char(15711167))	hex(char(15711167))	CONVERT(CONV('EFBBBF', 16, 10), UNSIGNED)
3	EFBBBF	15711167

到這里，為什么兩個(gè)“看”起來一模一樣的字符串，實(shí)際上不一樣呢？根源就在這里，在字符串的中間位置插入了不可見字符。就是這不可見字符EFBBBF導(dǎo)致我們做數(shù)據(jù)插入時(shí)報(bào)too long。

2、數(shù)據(jù)庫解決之道

在明確了以上的問題所在之后，我們就可以根據(jù)實(shí)際情況來進(jìn)行調(diào)整。只要將這種不可見字符替換掉即可。實(shí)現(xiàn)的方式也很簡(jiǎn)單，sql如下：

select length(REPLACE('G30L3 B01', CHAR(15711167),''))  t;

t
8

通過replace函數(shù)就實(shí)現(xiàn)了不可見字符EFBBBF的去除，這樣再調(diào)用Insert語句，就不會(huì)報(bào)too long的問題。

3、代碼層解決

除了在數(shù)據(jù)庫層來解決問題，還可以在什么地方解決呢？在編碼層解決是否可行。答案是肯定的。下面我們來詳細(xì)介紹一下代碼層的處理辦法，以java語言為例。其實(shí)原理是一樣的，都是要將不可見字符進(jìn)行替換掉。

/*
* 字節(jié)數(shù)組轉(zhuǎn)16進(jìn)制字符串
*/
public static String bytesToHexString(byte[] bArr) {
	if (bArr == null) {
		return null;
	}
	StringBuffer sb = new StringBuffer(bArr.length);
	String sTmp;
	for (int i = 0; i < bArr.length; i++) {
		sTmp = Integer.toHexString(0xFF & bArr[i]);
		if (sTmp.length() < 2)
			sb.append(0);
		sb.append(sTmp);
	}
	return sb.toString();
}

// 轉(zhuǎn)化十六進(jìn)制編碼為字符串
public static String toStringHex(String s) {
	byte[] baKeyword = new byte[s.length() / 2];
	try {
		for (int i = 0; i < baKeyword.length; i++) {
	baKeyword[i] = (byte) (0xff & Integer.parseInt(s.substring(i * 2, i * 2 + 2), 16));
		}
		s = new String(baKeyword, "utf-8");// UTF-16le:Not
	} catch (Exception e1) {
			e1.printStackTrace();
	}
	return s;
}

下面給出測(cè)試代碼，您可以實(shí)際測(cè)試以下結(jié)果，看是否與我的預(yù)期一樣：

public static void main(String[] args) {
		System.out.println("G8014Z03".substring(0, 2));
		System.out.println(bytesToHexString("G30L3 B01".getBytes()).toUpperCase()); // 長(zhǎng)
		System.out.println(bytesToHexString("G30L3B01".getBytes()).toUpperCase()); // 短
		System.out.println("G30L3 B01".length()); // 長(zhǎng)
		System.out.println("G30L3B01".length()); // 短
		System.out.println("進(jìn)制轉(zhuǎn)換======================================================");
		System.out.println(toStringHex("4733304C33423031"));
		System.out.println(toStringHex("4733304C33423031").length());
		System.out.println(toStringHex("4733304C33EFBBBF423031"));
		System.out.println(toStringHex("4733304C33EFBBBF423031").length());
		System.out.println("================================================");
		System.out.println(toStringHex("4733304C33EFBBBF423031".replaceAll("EFBBBF", "")));
		System.out.println(toStringHex("4733304C33EFBBBF423031".replaceAll("EFBBBF", "")).length());
		
		System.out.println("G30L3 B01".equals("G30L3B01"));
		
		System.out.println(toStringHex("4733304C33EFBBBF423031".replaceAll("EFBBBF", "")).equals("G30L3B01"));
	}

在代碼中使用上述代碼也可以實(shí)現(xiàn)目標(biāo)字符的替換，將不可見字符進(jìn)行替換掉。歡迎在實(shí)踐中進(jìn)行測(cè)試使用。

四、總結(jié)

以上就是本文的主要內(nèi)容，本文以數(shù)據(jù)庫中不可見字符處理為例，講解在MySQL中，為什么會(huì)有這種“看”起來一致，但實(shí)際上不一樣的問題，通過現(xiàn)象找本質(zhì)，通過一步一步的排查，找到問題的根源，最后在尋根溯源后，找到解決的辦法；從應(yīng)用代碼編程的角度和底層數(shù)據(jù)庫的角度來解決上述問題。

使用建議，如果您也有這方面的問題，尤其是從Excel或者其它的模板中導(dǎo)入數(shù)據(jù)的，很有可能會(huì)遇到這個(gè)問題。如果碰到看起來一致，但實(shí)際內(nèi)容不一樣的情況，可以試試看是不是碰到了不可見字符，本文即分享了一種在MySQL中的不可見字符的解決方案，同時(shí)分享了使用JAVA語言進(jìn)行上述問題的解決。

到此這篇關(guān)于MySQL中不可見字符詳解的文章就介紹到這了,更多相關(guān)MySQL不可見字符內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論