F.W.知識瑣記: 資料庫

顯示具有 資料庫 標籤的文章。顯示所有文章

2023年1月15日

如何使用Excel讀取mySQL資料庫(含環境安裝)

作者: Fred F.M. Wang (FW知識瑣記) 日期:2023-1-15

筆者用Excel讀取過Access或SQL Server資料庫，最近嘗試使用Excel讀取mySQL資料庫，查了許多方法，經過幾次失敗的安裝，終於正確的安裝完成，並讓Excel可以讀取到mySQL資料庫，分享給需要的人。下面是我安裝的步驟與一些要注意的地方 :

我的電腦環境是:
. Windows 7 家用進階版
. Office 365專業增強版
. mySQL資料庫(使用UTF-8編碼) : 安裝在同一台個人電腦。

步驟一下載並安裝 mysql ODBC Connector

1.1 首先，要了解您的Office版本是32位元或64位元，安裝的mysql ODBC Connector必須與Office是相同位元的版本，因為我電腦的Office是32位元版本，因此安裝32位元的mysql ODBC Connector。如何知道您Office版本是32位元或64位元的呢? 以Office 365為例，在Excel選單 : 檔案 > 帳戶的畫面中，點擊"關於Excel"就可以看到您Excel版本了，如下畫面。

1.2 下載 mysql ODBC Connector

下載網址 : https://dev.mysql.com/downloads/file/?id=513647
下載與執行的檔案為 : mysql-connector-odbc-8.0.31-win32.msi

步驟二設定ODBC來源

       此步驟要設定一個mySQL資料庫的ODBC來源，因為步驟一安裝的mySQL ODBC Connector 32位元版本，因此必須在32 位元版本ODBC資料來源管理員內設定。此處要注意的是作業系統如果是Windows 7 64位元，就不能使用控制台ODBC資料來源管理員，因為它是64位元版本的。
       32位元版本ODBC資料來源管理員(Odbcad32.exe)位於 %systemdrive%\Windows\SysWoW64 資料夾(如: C:\Windows\SysWOW64)中。必須執行此程式來設定mySQL資料庫的ODBC來源才行。
       參考來源 ODBC 工具會顯示32 位元和64 位元- SQL Server

   如果使用錯誤版本的ODBC資料來源管理員，Excel在連接資料庫時會產生下面的錯誤訊息。

安裝步驟 :

在 ODBC資料來源管理員視窗的"使用者資料來源名稱" > 新增
選擇"MySQL ODBC 8.0 Unicode Driver”, 點擊[完成]

說明 : 因為資料庫建立時我採用Unicode編碼，因此要使用Unicode Driver。

3. 設定新的資料來源。
點擊[完成]後，會顯示出mySQL connector設定對話窗，如下。

填入個欄位 :

Database Source Name : 自訂一個好辨識的資料庫來源名稱
勾選 TCP/IP Server
hostname : localhost (因為裝在同一台電腦)
User : <mySQL資料庫管理帳號>
Password : <mySQL資料庫管理密碼>
Database : <選一個資料庫>

註 : mySQL資料庫管理帳號在資料庫建立時，已經建立至少一個資料庫管理員帳號，或可以到資料庫建立一個新的資料庫管理帳號，並設定適當的存取權限提供連線存取。

步驟三使用Excel讀取mySQL資料庫中的資料

選單 : 資料>取得資料>從其他來源>從 ODBC

在”從ODBC” 對話方塊的”資料來源名稱(DSN)”，選擇步驟二中建立的資料來源名稱(如mysql-xampp8.1.2 )。

如果您想要設定資料庫查詢條件，查詢並匯入資料，請在對話框"進階選項"的"SQL陳述句" 中下SQL命令。然後點擊 [確定]。

顯示出查詢結果後，點擊[載入]，就會將這些查詢結果匯入Excel工作表中了。

參考影片 : Excel Import MySQL Table with ODBC Database Query

整理三大容易發生錯誤的地方，要特別注意 :

安裝的mySQL ODBC Connector必須與Office是相同位元的版本。
安裝的mySQL ODBC Connector 32位元版本，因此必須在32位元版本ODBC資料來源管理員內設定。(注意64位元Windows 7 控制台的ODBC資料來源管理員是64位元版本的)
資料庫建立時採用Unicode編碼，因此資料來源設定時就要選擇MySQL ODBC Unicode Driver(不是ANSI Driver)

2021年5月18日

MySQL Community 版本與MySQL企業版有甚麼不同? 有甚麼限制?

作者: Fred F.M. Wang (FW知識瑣記) 日期:2021-5-18

最近聽說 MySQL Community 版本因為免費所以會有一些限制，因此在網路上找到一些資料整理一下MySQL Community 版本與MySQL企業版的差異。

一 MySQL Community vs Enterprise版本功能的差異

1 只有Enterprise版本才支援 Enterprise plugins 如Thread Pool, PAM, Audit等。

2 Enterprise版本提供企業的保證與客戶技術支援服務。

3 Enterprise版本提供熱備份，Policy-based auditing，MySQL Enterprise monitor，

MySQL advisor, mySQL Router等工具以及較多的安全性功能。

Enterprise版本更多的特色與功能請見 MySQL Products

來源 :

1 MySQL Community Server vs. Oracle MySQL databases

2 Oracle MySQL Community vs Oracle MySQL Enterprise

3 What are the differences between MySQL Community and MySQL Enterprise Server 5.7

二 MySQL Community vs Enterprise版本效能的差別

同時有大於128個資料庫連線時，Enterperise版本比Community版本效能好，大於500個連線效

能明顯差異更大。

來源: MySQL Enterprise Scalability

三 MySQL Community版本是否有資料表格數的限制?

MySQL Community與Enterprise版本的限制都是相同的，如下 :

InnoDB類型的資料表格存在同一個tablespace，數量限制為 4 billion筆, 最大64TB。

MyISAM類型的資料表格是檔案形態，只有跟作業系統可儲存多少檔案有關。

來源 :

1 MySQL community edition capacity

2 Limits on Number of Databases and Tables

2019年11月1日

資料庫中的char(1)與varchar(1)有甚麼不同?

作者: Fred F.M. Wang (FW知識瑣記) 日期:2019/11/1

在設計資料庫表格時，常常猶豫要使用char或varchar，在此以char(1)與varchar(1)為例做比較，看看兩者的差異，使用哪一個比較好。

一長度顯示上的差異
1. 內容是空值時, char(1)長度為1. varchar(1)長度為零
2. 內容是空白時，char(1)長度為1. varchar(1)長度為1

二實際使用空間的差異
char(1)實際使用1 bytes, varchar除了原資料的長度外，會額外需要兩個bytes，因varchar(1)會使用到3 bytes

結論 : 如果您的資料欄位只需要一個字元，就直接使用char(1)，當資料量很大時，就可以節省許多儲存空間。

參考 varchar(1) VS char(1)

2019年4月12日

[中文編碼問題] 繁簡體中文字都可以輸入, 儲存SQL Server DB(2019以前的版本)並顯示在PHP網頁上 (尚無正確的方法)

作者: Fred F.M. Wang (FW知識瑣記) 日期: 20190412

花了一天的時間研究UTF-8網頁繁簡體並存的問題，怎樣讓繁簡體都可以輸入，儲存入資料庫並正確地顯示在網頁上。

有些人覺得很簡單阿，只要將網頁上輸入的UTF-8繁或簡體中文字串，直接儲存進以UTF-8編碼的資料庫資料欄位就可以了，反之，從資料庫讀取也很直接，沒問題的。

問題出在資料庫，我在mysql資料庫，用上面方式存取，繁體簡體字都沒問題。但是使用MS SQL Server就是不行。

如果要直接儲存UTF-8不要轉碼, 在SQL Server(2019以前的版本)中辦得到嗎? 答案是否定的。

找到一篇微軟官網的文章 "說明SQL Server 中儲存 UTF-8 資料", 提到"某些應用程式 (特別是 Web 應用程式) 必須處理以 UTF-8 編碼方法所編碼的 Unicode 資料。SQL Server 7.0 和 SQL Server 2000 使用不同的 Unicode 編碼方式 (UCS-2)，無法將 UTF-8 識別為有效的字元資料。" , "Microsoft Windows NT、SQL Server、Java、COM 和 SQL Server ODBC 驅動程式以及 OLEDB 提供者在內部都以 UCS-2 代表 Unicode 資料。"

SQL Server原來是不支援UTF-8的，直到SQL Server 2019才支援 (微軟終於覺醒了?!)

那麼網頁(UTF-8)儲存入SQL Server DB改用 mb_convert_encoding($data, "UCS-2","UTF-8"); 轉成UCS-2編碼儲存到DB中，從SQL Server DB讀出顯示到網頁(UTF-8)時, 用 mb_convert_encoding($data,"UTF-8", "UCS-2"); 轉成UTF-8顯示。
這樣是否就可行了呢? 結果還是失敗, 仍然無法正確轉換所有的繁簡中文。

感謝internet上許多網友無私分享許多的文章，讓自己找到不少線索，不過可能因為環境不同，需要花一些時間嘗試，找到合適的方法來解決這個問題。

我的系統環境

資料庫 : MS SQL Server 2008
資料庫編碼(SQL Server定序) : Chinese_Taiwan_Stroke_BIN

Web System : PHP + Codeigniter
Web System編碼 : UTF-8

方法 :
1 將會輸入中文字的欄位編碼由Chinese_Taiwan_Stroke_CI_AS改為Chinese_PRC_CI_AS (當然, 欄位型態必須是nchar, nvarchar, ntext這種帶n開頭的型態)

2 儲存時判斷轉入的字串是不是簡體字，如果是就用mb_convert_encoding($inputstr,"cp936", "UTF-8") 轉為cp936碼, 否則視為繁體字用mb_convert_encoding($inputstr,"BIG5", "UTF-8") 轉為BIG5碼再儲存到資料庫中
*** 簡體字判斷方法 : 參考 "PHP 判斷 UTF-8 字串是簡體或繁體中文"

3 由資料庫讀出時判斷是不是簡體字，如果是就用mb_convert_encoding($data, "UTF-8", "CP936")轉為UTF-8顯示，否則視為繁體字用mb_convert_encoding($data, "UTF-8", "BIG5")轉為UTF-8顯示
~~判斷方法 : 用mb_detect_encoding~~

function encode($str) {
 $codes = array("ASCII","GB2312","BIG5","UTF-8");
 $encode = mb_detect_encoding($str,$codes); 
 return $encode;
}

結果 :
1 繁簡體中文都可以儲存到DB中
2 "測試" 未判斷成繁體字, 轉成簡體字"代刚", "知識"也沒有正確判斷是BIG5, 變成亂碼

問題關鍵是如何判斷從資料庫讀出的文字是繁體字或簡體字? 用mb_detect_encoding顯然無法正確判斷

20190415
用不同編碼的字串長度來實驗, 如下 :

發現從判斷從資料庫讀出的字串，如果是繁體中文字串, mb_strlen($data, "UTF-8")與mb_strlen($data, "CP950")的結果不同, 而簡體中文字串則相同，因此, 可已根據這種規則判斷是簡體中文或繁體中文。

我找到方法了, 修改上面第三點，判斷字串長度

$len1 = mb_strlen($str, "UTF-8");
$len2 = mb_strlen($str, "CP950");
if($len1 === $len2) { // 簡體字  
 $code = "CP936";
} else {   // 繁體字
 $code = "BIG5";
}

如上面方式, 兩種長度相等表示是簡體字, 不相等表示是繁體字
判斷出來後再將資料轉成UTF-8 ==> mb_convert_encoding($data, "UTF-8", $code);
這樣就正確了 (限目前我測試的內容)

以上為目前測試的結果, 但是不表示所有的中文字都能100%的辨別, 筆者發現更長的字串，上面判斷法則就會失敗, 還需要更多的測試與修正, 另外, 繁簡夾雜的字串也會有問題

筆者最後決定資料庫改在mySQL，就不會有此傷腦筋的問題了。使用SQL Server 2008也可以升級為SQL Server 2019就沒這個問題了。-20190426 Fred

相關文章 :
1. [繁簡中文並存問題]PHP Web應用系統繁簡中文並存問題研究
2. [繁簡中文並存問題]網頁應用上傳檔案檔名變成亂碼的解決方法

2014年8月7日

SQL案例 - Full Outer Join 應用

作者: Fred Wang (FW知識瑣記) 日期: 2014/8/7

下圖類型的問題, 最好用甚麼Join方法來完成呢?
以筆者遇到的個案, 如何在一張表格中列出各個客戶的未結帳的銷貨金額, 應收帳款金額與應收票據未兌現金額，而三個種資訊分別在三個不同的資料庫表中。

最好的方式就是用Full Outer Join, 以上面個案 Key就是客戶代號, P表格代表銷貨單, P表中的FieldA為未結帳銷貨金額; Q表格代表結帳單, Q表中的FieldB為應收帳款金額(結帳未收款金額), 以下SQL就是解法 :

select ISNULL(P.Key, Q.Key), ISNULL(P.FieldA,0), ISNULL(Q.FieldB,0)
from Table1 as P
full outer join Table2 as Q
on P.Key = Q.Key

如果要Full Outer Join三個表呢? 就要用下面的SQL
select COALESCEL(P.Key, Q.Key, R.Key), ISNULL(P.FieldA,0), ISNULL(Q.FieldB,0), ISNULL(R.FieldC,0)
from Table1 as P
full outer join Table2 as Q
on P.Key = Q.Key
full outer join Table3 as R
on P.Key = R.Key

要注意的是 COALESCEL 這個函式，與ISNULL不同，COALESCEL()內的參數，可以有多個，傳回參數值由左到右第一個非NULL的參數值。

2012年2月1日

Google Tenzing 簡介

作者: Fred Wang (FW知識瑣記) 日期: 2012/2/1

Tenzing 是一個建構在MapReduce上的查詢引擎，用來作為Google資料特定的分析。Tenzing 支援最完整的 SQL實作(還包含一些擴充) 具備許多關鍵特色如異質性, 高效能, 可擴展性(scalability), 可靠性, metadata awareness, 低延遲, 支援柱狀儲存(columnar storage)與結構化資料, 與簡單的延伸性。 Tenzing目前用於Google內部超過一千名員工，服務每天超過一萬個queries，查詢超過1.5 petabytes的壓縮資料。

較詳細的解說 : 讓你的MapReduce SQL引擎加速 ---- Google-Tenzing下的那些Tricks

2012年1月13日

Data Warehouse重要書籍與SQL學習資源備忘

Data Warehouse重要書籍
1.Ralph Kimball and Margy Ross,"The Data Warehouse Toolkit:The Complete Guide to Dimensional Modeling 2nd Edition"
2.Ralph Kimball,Margy Ross,Warren Thornthwaite,"The Data Warehouse Lifecycle Toolkit"
3.Ralph Kimball and Joe Caserta,The Data Warehouse ETL Toolkit: Practical Techniques for Extracting,...", 2004
4.William H. Inmon,"Building the Data Warehouse", 2005
5.Oracle,
a."Data Warehousing Guide 10g Release 2"
b."Data Warehousing Guide 11g Release 1"

SQL學習資源

1.Guy Harrison,"Oracle Performance Survival Guide: A Systematic Approach to Database Optimization"
2.Joe Celko, "Joe Celko's SQL Puzzles and Answers, Second Edition"
3.Joe Celko, "Joe Celko's SQL Programming Style"
4.Joe Celko, "Joe Celko's SQL for Smarties, Fourth Edition: Advanced SQL Programming"
5.Joe Celko Blog : http://joecelkothesqlapprentice.blogspot.com/
6.Ask Tom Home - Oracle : http://asktom.oracle.com/

2011年12月19日

免費的資料庫軟體整理

作者：Fred Wang 日期： 2011/12/19
資料庫霸主Oracle有Express版本提供開發者自行下載，最新版本為11g Express R2 Edition
其他商用軟體也已經有Express版本，如

IBM DB2 Express-C (最新版： v9.7.5)
Microsoft SQL Server 2008 Express Edition
IBM Infomix Developer Edition
SAP Sybase SQL Anywhere Developer Edition (最新版：12.0.1)

其他知名(或不那麼知名)的商用軟體還有Sybase IQ, Teradata, Adabas, FileMaker, Pervasive PSQL，沒有找到釋出的免費版本
Open Source 資料庫軟體，較有名的有

其他Open Source DB還有TurboPower FlashFiler, TurboPower B-Tree Filer, FIS GT.M, MCObject Perst(OO Embeded DB), Drizzle
非關聯式資料庫，非SQL資料庫，NoSQL(Not Only SQL) DB有

訂閱：文章 (Atom)