[Joomla!] Database

[Linux Programming] 在 Ubuntu 中使用 C 語言連結 MySQL

首先.......必須安裝 MySQL server，一般來說安裝 "mysql-server" 都可以裝到目前最新版的套件

接著要安裝開發程式所需要的 library：

Ubuntu 8.04 => libmysqlclient15-dev

相關的 header 檔案都會放在 /usr/include/ 裡面，而實際運作的函式庫則會存在 /usr/lib/mysql 中

若是撰寫 mysql 相關程式時，編譯方式如下：

gcc <程式名稱> -I /usr/include/mysql -L /usr/lib/mysql -l mysqlclient -o <輸出檔案名稱>

解決 MySQL 與 .NET 間 DateTime 資料格式衝突的問題

如果有用 MySQL Connector for .NET platform 的人可能在將 DateTime 格式的資料寫入資料庫時，會出現以下錯誤訊息：(我用的是 5.1 版 for .NET 2.0)

Unable to convert MySQL date/time value to System.DateTime

此例外很明顯的說明要將 DateTime 的格式轉換為 MySQL 可以接受的 DateTime 格式!

有兩種方式：

1、SQL 語法為自己組合而成的字串

此時只要知道 .NET DateTime 之間與 MySQL DateTime 之間格式有何不同即可。

.NET DateTime：2007/01/01 12:00:00

MySQL DateTime：2007-01-01 12:00:00

只要將年月日中間的「/」符號換成「-」即可囉!


2、由程式所自動產生的 SQL 語法

若是用了 MySqlDataAdapter、MySqlCommandBuilder、DataTable 所搭配起來的組合，若是 DateTime 的欄位，就必須要傳入 MySqlDateTime 格式的資料!

注意喔! 不是 DateTime 而是 MySqlDateTime 喔!

而 MySqlDataTime 的 class 在 MySql.Data.Type namespace 中，必須在程式前面先 using 才行，這是必須要注意的!

轉換範例如下：

//這是會發生錯誤的寫法，因為 MySQL 不接受 .NET DateTime 格式的資料
myDataTable.Rows[0]["myDateTime"] = DateTime.Parse("2007/01/01 12:00:00");

//必須初始化一個 MySqlDateTime 的物件傳入才行
myDataTable.Rows[0]["myDateTime"] = new MySqlDateTime(2007, 1, 1, 12, 0, 0);

當然，MySqlDateTime 的 constructor 有很多種，甚至可以傳入 .NET 的 DateTime 做為參數，詳細資料可以參考 MySQL Connector for .NET 的檔案中的文件檔，再以自己方便的方式傳入即可。

MySqlParameter 的使用 in C#

今天在寫用 C# 連結 MySQL 的程式，使用的是 MySQL AB 所提供的 Connector/Net 5.1

不過使用到 MySqlParameter 的時候，發生了一些問題.....

原本使用 SqlParameter 的時候，在設定 SQL 語法時，都會類似以下這樣...

insert into table1(col1, col2, col3) values(@col1, @col2, @col3)

上面的語法在連結 SQL Server 後可以正確執行。但是到了 MySQL 上，卻無法正確的指定 MySqlParameter 的 value (全部都會變成 Null)

後來 google 了一下，發現原來是連結 MySQL 就不一樣了! 原本的符號「@」要改為符號「?」(新版的 connector 改掉了!)

因此語法會變成以下這樣：

insert into table1(col1, col2, col3) values(?col1, ?col2, ?col3)

所以設定 MySqlParameter 時，就必須類似以下寫法：

MySqlParameter para1 = new MySqlParameter("?col1", MySqlDbType.VarChar, 50);
MySqlParameter para2 = new MySqlParameter("?col2", MySqlDbType.VarChar, 50);
MySqlParameter para3 = new MySqlParameter("?col3", MySqlDbType.VarChar, 50);

如此一來資料就可以正確新增囉!

參考資料

• MySQL Forums :: .NET :: Error using MySqlParameter

解決外部網路無法連到 Ubuntu MySQL server 的問題

今天在 Ubuntu 上裝了 MySQL server....
當帳號密碼都設定完成後，卻發現沒辦法從外部透過 mysql 指令連入，於是下了以下指令檢查：

user@ubuntu:~$ netstat -utlnp
(No info could be read for "-p": geteuid()=1000 but you should be root.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN -
tcp6 0 0 :::22 :::* LISTEN -

原來只針對 127.0.0.1 作 listen，難怪外部連不進來...

而解決方法就是在 MySQL 的設定檔(在 Ubuntu 中為 /etc/mysql/my.cnf)中找到以下此行，並將其註解：

bind-address = 127.0.0.1

再次重新 MySQL 後，問題就解決囉!

MySQL 中錯誤訊息「ERROR 1005 (HY000)」的解決方式

今天某個大笨妹傳了 xxx.sql 檔給我，要我 dump 進 MySQL server，結果出現以下訊息：

ERROR 1005 (HY000) at line 53: Can't create table './tgc/Privilege.frm' (errno: 150)

這是啥訊息啊.........google 一下，發現原來是跟 key reference 有關，於是找到以下這篇文章 ，裡面說先把 foreign key check 用以下語法關掉：

mysql> SET FOREIGN_KEY_CHECKS = 0;

# 到時候記得重新開啟
mysql> SET FOREIGN_KEY_CHECKS = 1;

恩.......結果無效.....


由於 table engine 為 InnoDB，於是又找到這篇文章 ，看起來似乎可用，文章中提到用以下語法檢查 InnonDB 的狀態資訊：

mysql> SHOW ENGINE INNODB STATUS;

此時會出現一堆訊息阿.....於是慢慢看慢慢看....結果發現原來是 table name 打錯了，把小寫 L 打成大寫 L........(竟然花了半個小時來處理這種 bug....@_@)

恩....學乖了，以後還是看 log 比較清楚，畫面上給出的訊息真是不清不楚。

MySQL 基本操作入門

初始化管理者密碼

由於現在安裝 MySQL 真是太方便了，Linux 上面隨便 APT 或是 YUM 就可以拉下來裝，還是最新版的，所以我這邊就不說明安裝的部份了，而直接切入初始化管理者帳號的部份

MySQL 中預設的管理者帳號就是 root 啦!

用以下語法就可以進行設定：(兩種任選一種吧!)

shell> mysqladmin -u root password '<管理者密碼>'

shell> mysqladmin -h <主機位址或名稱> -u root password '<管理者密碼>'

登入 MySQL Server

登入方式如下：

shell> mysql -h <host> -u <user> -p
Enter password: ********

參數說明：

-h
指定要連線的主機

-u
進行連線的使用者名稱

-p
在下一個步驟時輸入使用者密碼

若要離開 MySQL 則是相當簡單，只要在命令提示字元下達 quit 即可離開。


參考連結：

• Connecting to and Disconnecting from the Server

下達指令前的注意事項

在 MySQL 的命令提示字元中下達指令，有以下幾點必須注意：

1. 每個指令必須以分號(;)作為結束
2. 輸入 Enter 不代表指令結束，若沒有加入分號，則會被視為同一個指令
3. 每個指令都有其代表的關鍵字，輸入的是大小寫並不會影響執行的結果
   

資料庫建立與相關資訊的擷取

1、建立、使用、查詢資料庫(Database)

要使用 MySQL，當然要懂得如何建立一個新的資料庫了! 假設這邊要建立的資料庫名稱為 MyDB，不需額外指定 character set 以及 collate，則建立的語法如下：

mysql> CREATE DATABASE MyDB;

接著可以查詢資料庫是否建立成功，可用下列指令：

mysql> SHOW DATABASES;

在上個步驟中，一共有出現三個資料庫，若要使用剛剛建立的資料庫 MyDB，應該要怎麼做呢? 只要下達以下指令即可：

mysql> USE MyDB;
Database changed

透過 USE 關鍵字，就可以指定要使用的資料庫為何。


參考連結：

• Creating and Selecting a Database
• Creating and Using a Database

2、資料表(Table)的建立與查詢

其實建立 Table 的語法介紹是很複雜的，column、index、reference.....etc，都有複雜的設定語法，不過在這邊以簡單為主，以下就來個建立 table 的範例：

mysql> CREATE TABLE pet (
-> name VARCHAR(20),
-> owner VARCHAR(20),
-> species VARCHAR(20),
-> sex CHAR(1),
-> birth DATE,
-> death DATE );

若是要查詢有哪些 table 在資料庫，以及 table 的詳細結構呢? 可以用以下的語法：

# 查詢資料庫有哪些 table
mysql> SHOW TABLES;

# 查詢 table 結構
mysql> DESCRIBE pet;

參考連結：

• CREATE TABLE Syntax
• Creating a Table

批次模式(Batch Mode)

何謂 batch mode? 就是一次執行大量的 SQL 命令!

而 batch mode 應該用在什麼地方? 又有什麼好處呢? 以下做個簡單說明：

1. 當一個例行性工作需要執行大量的 SQL 命令時，將這些命令寫成純文字檔，再開發 script 將其以 batch mode 進行，方便也不容易出錯
2. 當多台電腦要執行相同且大量的 SQL 命令時，將命令編輯成純文字檔，以 batch mode 進行，快速也不會出錯
3. 假設一次要建立 database 中的所有 table，可將命令先編輯為純文字檔，再以 batch mode 進行，比較不容易出錯
4. 如果查詢結果將會產生大量輸出時，也可以利用 batch mode 將結果輸出至其他檔案觀看(亦可搭配 more 或是 less 等指令分頁瀏覽)
5. 在某些情況下必須用 batch mode，例如執行例行性(cron)的資料庫相關工作，不允許使用互動模式。
   

基本上，batch mode 相當好用，不論是一次將所有 table 以及相關資料建立完成，或是將備份資料還原回資料庫，甚至是一些例行性的資料庫工作，都可能會使用到這個功能。

一般來說，若要進行 batch mode，必須將要執行的 SQL 命令編輯為一個純文字檔案，才可以正確執行。

【註】在備份時，用指令 dump 出來的也是純文字檔案

若要執行 batch mode 的 SQL 命令，必須使用「mysql」指令，另外還必須指定主機位址、使用者帳號密碼、儲存SQL命令的純文字檔....等資訊，以下用範例來介紹：

shell> mysql -h <主機位址> --user=<登入帳號> --password='<登入密碼>' <資料庫名稱> < <檔案名稱>

當然上述的語法是最詳細的方式，因為所有的參數都指定上去了，但是其實許多情況下，並沒有如此複雜，可以視以下情況進行修改：

1. 在本機執行，則 -h 參數就可以不需指定
2. 若想要後來再打密碼，也可以改用 -p 指令，就不需要在指令中就輸入密碼
3. 若檔案內容中有「use 資料庫名稱;」的內容，也不需再指定資料庫為何了
   

因此使用者可以根據自身的情形進行適當的修改。

然而，以 batch mode 執行大量 SQL 命令時，有可能會有許多訊息的輸出，若是想要捕捉這些訊息，可以搭配 more 或是 less 指令，或是以 redirect 的方式將訊息輸出到指定檔案中，以下是兩種輸出方式的語法介紹：

# 以 more 進行分頁瀏覽
shell> mysql < batch-file | more

# 將訊息全部輸出至檔案 mysql.out 中
shell> mysql < batch-file > mysql.out

參考連結：

• Using mysql in Batch Mode

帳號與權限的控管

1、建立帳號

在一台剛安裝完 MySQL 的機器中，使用者只有 root 一個，但總不能每次存取 database 都用 root 的權限吧.....

所以這裡介紹怎麼新增帳號，首先以下是新增帳號的語法：

CREATE USER <使用者帳號> [IDENTIFIED BY [PASSWORD] '<使用者密碼>']
[, <使用者帳號> [IDENTIFIED BY [PASSWORD] '<使用者密碼>']] ...

由上面的語法可以看出，可以一次增加多個帳號，不過以下只用個簡單範例來說明，建立一個帳號即可：

# 建立登入帳號為「testuser」，登入密碼為「thisispassword」
mysql> CREATE USER testuser IDENTIFIED BY 'thisispassword';

2、權限設定

新增了帳號之後，若沒有設定相關資料庫的權限，也只能用該帳號登入 MySQL 而已，卻不能做任何事情，因此要給帳號分配資料庫權限，而由於這邊因為只是簡單介紹，就用分配給某帳號某個資料庫的所有權限為例好了! 以下是簡單的範例：

# 分配資料庫 MyDB 所有的存取權限給使用者 testuser，並限制僅能本機存取，密碼為 thisispassword
mysql> GRANT ALL PRIVILEGES ON MyDB.* TO 'testuser'@'localhost' IDENTIFIED BY 'thisispassword';

# 在主機端的部份，也可以用萬用字元「%」
mysql> GRANT ALL PRIVILEGES ON MyDB.* TO 'testuser'@'%' IDENTIFIED BY 'thisispassword';

# 進行權限分配後，還要將資料 flush
mysql> FLUSH PRIVILEGES;

參考連結：

• Adding New User Accounts to MySQL

備份

備份對資料庫來說，是絕對不可或缺的，因此定期的備份工作是需要的；不過由於這裡僅是做個簡單介紹，因此就以最簡單的備份方式來說明，直接指定某個資料庫完整備份，以下是備份的指令：

# 需要輸入 root 的密碼
shell> mysqldump -u root <資料庫名稱> -p > <檔案名稱>
Enter password: ********

# 直接指定 root 密碼，此種方式適合用於撰寫自動化備份的 shell script 時用
shell> mysqldump -u root --password='<密碼>' <資料庫名稱> > <檔案名稱>

透過此種方式，會產生出一個可以用來還原 database 的純文字檔，只要透過上面說的 batch mode，就可以將資料庫還原了。(尚未說明使用者以及權限的相關備份)

參考連結：

• Database Backups
• mysqldump — A Database Backup Program