主題：MySQL資料庫操作實戰

http://www.javaeye.com/topic/251307 author：touya

昨天項目發布，要做資料移行，要實現的功能很變態，時間很緊迫，基本上是使出了全身解數，才能有快又准地完成工作，期間發現很多小技巧串聯起來使用，效果的確非常好。

武器：

1 mysqldump+mysql命令=>資料的匯入匯出，備份恢複

2 perl命令列=>很多時候，你有一個想法，它就能只用一句話，幫你實現它，省得編寫很多代碼的麻煩

3 shell命令=>組合拳，單獨的命令誰都會，組合起來使用，需要一些經驗的積累

4 強大的編輯器支援UltraEdit/EmEdit等=>UltraEdit最專業，但不支援字元集轉換；EmEdit支援字元集轉換，且最新版有很多外掛程式可供選擇，基本夠常見的操作了，對於WEB開發人員來說，常需要在GB2312、utf8或者其他國家語言euc-jp、shift-jis等等編碼中轉來轉去，EmEdit的確是首選，特別贊的還有EmEdit的錄製鍵盤動作並播放的功能

常見組合：

1 資料移行時：

mysqldump --opt -t -h locahost -P 23236 -p mydb table -w "id>27000" > table.sql

>輸入密碼

OK , mydb中的表table中id大於27000的所有資料列的INSERT語句就儲存到table.sql中了。-h、-P、-p就不多說了，--opt 是mysqldump的最常用組合選項，可以認為是加速語句；-t 則很有用，表示--no-create-info，就是你不需要建表語句，只需要資料（INSERT語句）；-w就是where條件，也非常有用，讓你有選擇的匯出資料。其他常見選項有：-d ： 只需要建表語句，不需要insert語句；-c ：給每個insert語句加上列名（field），預設時，insert語句是"insert into `table` values (1),(2),(3);"，而加上-c後，就變成了"insert into table(id) values (1),(2),(3);"。有什麼用？一會兒自會明白。

如果表中資料非常多，匯出insert語句就非常長（預設情況下，一個表的所有資料都在一個insert語句中，不換行），要查看這個檔案常常引起死機，無論是linux下用vi，less查看，還是windows用EmEdit等編輯器，因為他們都是以行為單位load資料的，一行的資料過大就會記憶體佔用過大，怎麼辦？

你發現，只需要在EmEdit裡尋找"),("這個字串，將它替換為"),\n("，也就是加個換行，就行了，於是就這麼做，結果發現--當資料量達到上萬時，EmEdit就像數羊一樣一個一個替換，等它換完，你都睡了一覺了！！

怎麼辦呢？你當然知道寫個指令碼就行了，很容易，但是花的時間多，還需要測試，有簡單辦法嗎？恩，那就輪到perl單命令列上場了：

perl -i.bak -pe 's/\),\(/\),\n\(/g' table.sql

OK，替換結束，且產生了一個備份檔案table.sql.bak，如果你發現寫錯了，效果不對，還有救：）

當然了，如果你會用sed、awk等強大的編輯工具，這個也是小case，這裡就不多說了。

2 資料匯入

匯出的資料經過處理，就要匯入目標資料庫，現在有一個表，欄位非常多，其中主鍵為id，匯入另一個資料庫的相同表裡，但是id不想直接插入，而是只要資料，id最好是自動產生的，跟在目標資料表最末一條資料之後即可，以免主鍵衝突。怎麼搞呢？

這時候就能用上剛才說到的-c選項了，加了-c，匯出的資料有field列，只需要把其中的id列都替換為空白即可：）

這個替換過程，用EmEdit或上面說到的perl命令列都可以，只是速度的區別了。

3 接上面的問題，老資料匯入了新的表，產生了一串新的連續id，但和老資料完全不一樣了，現在有其他幾張表中使用了老資料的id作為外鍵，要把它們統一改為新的id。例如：

老資料中table.id=10010，有一個表table2.table=10010,是外鍵關係，要把這個table2.table改為新資料表中自動產生的那個id，就比如說是6041.現在手頭上的資料只有老id列表，且知道插入新表中的id是自增的，每次加1，從6041開始，怎麼快速修改 table2，table3中的相應外鍵呢？

一個檔案：

10010

10201

11301

11499

……

要替換成：

update table2 set table=6041 where id=10010;

update table2 set table=6042 where id=10201;

update table2 set table=6043 where id=11301;

update table2 set table=6044 where id=11499;

……

我們可以這麼組合手頭的工具：

perl -i.bak -pe 'BEGIN {$x=6041} s/^(\d+)/update table2 set table=$x where id=$1;/ ; $x++' file.txt

OK，搞定

這個例子的特色其實就是perl命令列中的BEGIN的用法，有begin，當然有end，具體就自己查查文檔吧：）

4 還有EmEdit中的錄製鍵盤動作並播放也很常用，畢竟寫正則比較費腦子，用工具雖然處理速度慢一些，但是思考速度要快很多。今天碰到一件事，需要把伺服器上某個目錄中的bmp圖片都轉為jpg格式，也是要使用組合拳的，其中就用到了EmEdit的錄製播放功能。

已知某目錄下有N多bmp圖片，通過convert命令可以轉格式（安裝Image::Magick模組即可）

怎麼搞？

find ./ -name "*.bmp" > bmp.txt

先拿到檔案清單再說。

在EmEdit中開啟該列表，一行行檔案名稱。

你發現你想做的就是：

./dir1/dir2/file1.bmp

./dir1/dir2/file2.bmp

./dir1/dir2/file3.bmp

轉變為：

convert ./dir1/dir2/file1.bmp ./dir1/dir2/file1.jpg

convert ./dir1/dir2/file2.bmp ./dir1/dir2/file2.jpg

convert ./dir1/dir2/file3.bmp ./dir1/dir2/file3.jpg

這是一個非常有規律的動作，在行首，按shift+End（選擇），ctrl+c（複製），End（到行尾），空格，ctrl+v（粘貼），backspace三次，輸入jpg，HOME到行首，輸入convert空格，下一行，HOME到行首

只要把這個操作序列記錄下來，對每行都是一樣操作，這時就可以使用EmEdit的錄製+播放功能了，錄製完成後，按F4快速鍵，一爽到底~~yeah~~

運行之後要刪除原圖：

在shell下：

find ./ -name "*.bmp" | xargs -n1 rm -f

(找到本目錄下的bmp圖片檔案名稱列表，一個一個傳給rm -f，刪之，慎用)

5 資料庫恢複（從bin-log中恢複資料庫也是一個非常重要的技巧），出現誤操作或DB伺服器不幸意外遺失資料時，常常要查bin-log來試圖恢複資料，下次接著聊吧，累了……

呵呵，亂寫寫，好像不只是資料庫操作了，不過挺實在，歡迎各位拍磚吧