MySQL使用tips

用mysql內建函數轉換ip地址和數字

利用兩個內建函數

inet_aton:將ip地址轉換成數字型

inet_ntoa:將數字型轉換成ip地址

充分利用mysql內建的format函數

尤其是在處理字元格式設定的時候,例如將12345轉換成12,345這樣的,只要用:format(12345,0)即可,如果用format(12345,2)則顯示的是12,345.00了...

利用mysql的內建函數處理時間戳記問題

eg : select FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');

結果: 2004 3rd August 03:35:48 2004

利用mysql_convert_table_format轉換表類型

需要DBI和DBD的mysql相關模組支援才能用,例子:

mysql_convert_table_format --user=root --password='xx' --type=myisam test yejr

修改mysql表中的欄位名

alter table tb_name change old_col new_col definition...

利用臨時變數

select @var1:=a1+a2 as a_sum,@var2:=b1+b2 as b_sum,@var1+@var2 as total_sum from test_table xxx;

用int類型儲存ip地址

原先錯誤的認為必須用bigint才夠，後來發現使用int unsigned類型就足夠了。 :)

利用IF函數快速修改ENUM欄位值

一個例子：

update rule set enable = if('0' = enable,'1','0') where xxx;

enable 類型：enum('0','1') not null default '0'

事務無法嵌套

避免長時間的sleep串連造成的串連數超出問題

設定全域變數 wait_timeout 和 interactive_timeout 為比較小的值，例如

10(s)，就能使每個sleep串連在10s之後如果還沒有查詢的話自動斷開。

設定mysql用戶端的提示符(prompt)

export MYSQL_PS1="(\\u:\\h:)\\d> "

則用 mysql -hlocalhost -uroot -pxx db_name 登入後，提示符變成：

(root:localhost:)db_name>

好用吧 :), 時時刻刻提醒你在哪個伺服器上，盡量避免誤操作的發生

更詳細的請查看MySQL手冊

整理MyISAM片段

1.) 定期運行 OPTIMIZE TABLE 命令即可

2.) 用mysqldump出資料，然後重新import回去，這對大表來說顯然第一種方法比較方便

整理Innodb片段

1.) 運行NULL命令， ALTER TABLE XXX ENGINE=Innodb;

2.) 同上的mysqldump方法

MySQL如果認為檢索的記錄數量超過總記錄數的30%，則選擇全表掃描，而非使用索引

MySQL 5.0.3之後,VARCHAR欄位後面的空格就不再刪除

升級到4.1.1或更高後，就很難降級回到4.0或4.1了，因為 InnoDB 使用了多個資料表空間的緣故

MySQL 4.1之後，MySQL把字串類型欄位的長度定義理解為字元長度而不是位元組長度

MySQL 4.1=>5.0時，增加了一個新的啟動選項 innodb_table_locks，它導致 LOCK TABLE 時也可以請求 InnoDB

表鎖。這個選項預設開啟，不過可能在 AUTOCOMMIT=1 和 LOCK TABLES 應用中會導致死結

5.0.3開始，在計算 DECIMAL 值和舍入精確值的時候採用精確數學，DECIMAL 用更有效格式來儲存

從5.0.12開始，自然串連和使用 USING

的串連，包括外部串連的衍生形式，都按照SQL:2003標準來處理了；這個變化導致減少了自然串連和使用 USING

的串連產生的結果欄位數，並且還將按照更合理的順序顯示這些欄位，逗號比較符的優先順序和 JOIN, LEFT JOIN 中的一樣了

在以前，等待逾時的鎖會導致 InnoDB 復原當前全部事務，從5.0.13開始，就只復原最近的SQL語句了

InnoDB 和 MyISAM 表中空格結尾的 TEXT 欄位索引順序改變了。因此需要運行 "CHECK TABLE" 語句修複資料表，如果出現錯誤，就運行

"OPTIMIZE TABLE" 或 "REPAIR TABLE" 語句修複，甚至重新轉儲(用mysqldump)

MySQL 5.0.3到5.0.5之間版本的 MyISAM 和 InnoDB 表中建立的 DECIMAL 欄位升級到5.0.6之後會發生崩潰