MYSQL數(shù)據(jù)查詢技巧

■ 山東 李瑞祥 劉偉

編者按：筆者在通過MYSQL數(shù)據(jù)做數(shù)據(jù)業(yè)務(wù)統(tǒng)計時，遇到了一些問題，但通過查詢資料，反復摸索，問題得以解決。下面本文將選擇有代表性的問題詳細說明解決方法。

最近一段時間我們一直通過MYSQL數(shù)據(jù)做一項數(shù)據(jù)業(yè)務(wù)統(tǒng)計工作，經(jīng)常需做一些諸如將EXCLE或CSV格式的文件導入MYSQL數(shù)據(jù)，然后進行諸如數(shù)據(jù)關(guān)聯(lián)等的操作，在這個過程中我們遇到了一些問題，但都通過查詢資料，反復摸索解決了，下面選取三個比較有代表性的問題說一下解決方法。

圖1 查看字符集

無法插入中文數(shù)據(jù)的解決方法

這個問題的發(fā)現(xiàn)是啟用一臺新的MYSQL服務(wù)器時發(fā)現(xiàn)的，之前我們在一臺老的MYSQL服務(wù)器進行數(shù)據(jù)導入時一切正常，只是當需要統(tǒng)計的數(shù)據(jù)量越來越大的時候，老的MYSQL服務(wù)器的硬件性能跟上不了，我們就啟用了一臺新的MYSQL服務(wù)器，這臺服務(wù)器的硬件性能要遠遠高于舊服務(wù)器，理論上運行數(shù)據(jù)統(tǒng)計的速度要遠遠高于舊服務(wù)器，但是在導入數(shù)據(jù)的時候就出現(xiàn)了故障，無論是導入EXCEL格式還是CSV格式的數(shù)據(jù)，發(fā)現(xiàn)導入到數(shù)據(jù)庫中的表中的數(shù)據(jù)都是空的。通過仔細看導入時生成的日志文件，發(fā)現(xiàn)都是導入一個名為“所屬OLT名稱”的那一列數(shù)據(jù)時出現(xiàn)了問題，再經(jīng)過反復比較，我們發(fā)現(xiàn)只有這一列的內(nèi)容是有漢字的，那么故障的原因很有可能就是新的MYSQL數(shù)據(jù)庫不支持中文。這個判斷通過登錄數(shù)據(jù)庫，運行查看字符集的編碼格式得到了驗證，如圖1所示。

可以看到charact er_set_database和character_set_server這兩個字符集的編碼格式都是“l(fā)atin1”，這時就需要更改這兩項的編碼格式，通過修改/etc下的my.cnf文件即可，在[mysqld]的內(nèi)容中添加如下一行：[mysqld]

#

c h a r a c t e r_s e t_server=utf8

然后再重啟mysqld進程，就會發(fā)現(xiàn)編碼格式改過來了，如圖2所示。

確定完MYSQL對漢字的支持以后，再進行導入數(shù)據(jù)的操作，不論是EXCEL格式還是CSV格式就都可以順利的完成了。

圖2 具體內(nèi)容

圖3 具體內(nèi)容

依次對數(shù)據(jù)表中的字符串進行替換操作

這是一個什么需求呢？即我們?yōu)榱嗽贛YSQL中進行不同表之間的數(shù)據(jù)關(guān)聯(lián)，就必然要對表格中的數(shù)據(jù)進行一些規(guī)范操作，比如在“網(wǎng)元名稱”這一列中，我們只需要保留數(shù)字和“/”這一個分隔符，其它的像“ONU ”、“（---）”這樣的符號都需要去掉，這個操作在WPS表格中也可以完成，但一項一項的去做太麻煩了，在MYSQL中利用REPLACE函數(shù)可以方便的完在，即寫多條REPLACE命令來實現(xiàn)，如圖3所示。

可以看到REPLACE命令是支持嵌套的，但是相當復雜，其實一條指令實現(xiàn)一個替換即可，注意每條指令的最后加一個分號，就可以了。

通過對比發(fā)現(xiàn)，特別針對大數(shù)據(jù)進行操作，MYSQL的速度要高于WPS表格或EXCEL表格，這也是我們選擇在MYSQL中進行數(shù)據(jù)項的規(guī)范性操作的另一個原因。

老是出現(xiàn)的ORDER BY錯誤提示的解決方法

當數(shù)據(jù)導入正常、各項數(shù)據(jù)操作也可以正常進行得到相應(yīng)結(jié)果的時候，卻發(fā)經(jīng)常出現(xiàn)一個報錯，如下所示：

[Err] 1055 -Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column"i n f o r m a t i o n_schema.PROFILING.S E Q" w h i c h i s not functionally dependent on columns in GROUP BY clause;this is incompatible with sql_mode=only_full_group_by

通過仔細分析這個錯誤提示，我們找到了解決辦法同樣是編輯/etc下面的my.cnf文件，添加如下一行

[mysqld]

#

character_set_server=utf8

sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后再重啟mysql服務(wù)即可。這樣操作以后，這種類型的錯誤就不出現(xiàn)了。