使用Mysql的小技巧總結(jié)

pkman110

使用Mysql的小技巧總結(jié)
2009年09月02日　作者： 大頭剛　
使用Mysql也很長一段時(shí)間了，在使用的過程中遇到過各種各樣的問題，有些看上去很小的問題，在解決的過程中卻花了很長時(shí)間，解決后才發(fā)現(xiàn)，原來這樣簡單。所以總結(jié)了一些經(jīng)驗(yàn)和技巧，把它寫出來，希望對讀者能有所幫助，少走彎路。
1. binary
BINARY不是函數(shù)，是類型轉(zhuǎn)換運(yùn)算符，它用來強(qiáng)制它后面的字符串為一個二進(jìn)制字符串，可以理解為在字符串比較的時(shí)候區(qū)分大小寫。舉例：
mysql> select * from tjob;
+-------+
| title |
+-------+
| 11    |
| 22    |
| 33    |
| aa    |
| bb    |
| cc    |
| AA    |
| BB    |
| CC    |
+-------+
9 rows in set (0.00 sec)

mysql> select title from tjob where title like 'a%';
+-------+
| title |
+-------+
| aa    |
| AA    |
+-------+
2 rows in set (0.00 sec)

mysql> select title from tjob where binary title like 'a%';
+-------+
| title |
+-------+
| aa    |
+-------+
1 row in set (0.00 sec)

mysql> select title from tjob where binary title like 'A%';
+-------+
| title |
+-------+
| AA    |
+-------+
1 row in set (0.00 sec)
2.group by with rollup
在group by結(jié)果的最后一行，列出所有行的總和，如下，多了最后一行
mysql> select count(*),title from tjob group by title with rollup;
+----------+-------+
| count(*) | title |
+----------+-------+
|        1 | 11    |
|        1 | 22    |
|        1 | 33    |
|        2 | aa    |
|        2 | bb    |
|        2 | cc    |
|        9 | NULL  |
+----------+-------+
7 rows in set (0.00 sec)
3.group_concat
group_concat合并行，例如把title字段的值打印在一行，逗號分隔。
mysql> select group_concat(title separator ',') from tjob;
+-----------------------------------+
| group_concat(title separator ',') |
+-----------------------------------+
| 11,22,33,aa,bb,cc,AA,BB,CC        |
+-----------------------------------+
1 row in set (0.00 sec)
4.order by if
排序時(shí)將某列中特定值排在最前，例如我在排序中要把a(bǔ)a放在最前面：
select title from tjob order by if(title='aa',0,1),title;
+-------+
| title |
+-------+
| aa    |
| AA    |
| 11    |
| 22    |
| 33    |
| bb    |
| BB    |
| cc    |
| CC    |
+-------+
5.prompt
如果有很多mysql數(shù)據(jù)庫管理，有時(shí)會發(fā)生自己忘記了在那臺服務(wù)器，那臺數(shù)據(jù)庫上，毋庸多說，誰都知道這有多危險(xiǎn)�？梢允褂胮rompt來重寫mysql客戶端提示符。
mysql>prompt \u@\h(\d) \r:\m:\s>
\u:連接用戶
\h:連接主機(jī)
\d:連接數(shù)據(jù)庫
\r:\m:\s:顯示當(dāng)前時(shí)間

如果嫌每次都在命令行修改麻煩，直接修改my.cnf參數(shù)
[mysql]    #這是不是[mysqld]   
prompt=\\u@\\d \\R:\\m>
6.pager
如果select出來的結(jié)果集超過幾個屏幕，那么前面的結(jié)果一晃而過無法看到。使用pager可以設(shè)置調(diào)用os的more或者less等顯示查詢結(jié)果，和在os中使用more或者less查看大文件的效果一樣。
mysql> pager more
PAGER set to 'more'
mysql> \P more
PAGER set to 'more'

mysql> pager less
PAGER set to 'less'
mysql> \P less
PAGER set to 'less'

mysql> nopager
PAGER set to stdout
7.delimiter
delimiter就是告訴MySQL解釋器,命令的結(jié)束符是什么。
默認(rèn)情況下MySQL命令結(jié)束是以分號(;)，在寫過程或者函數(shù)等情況下，這會產(chǎn)生不少問題，因?yàn)榇鎯�過程中有許多語句，所以每一個都需要一個分號。因此你需要選擇一個不太可能出現(xiàn)在你的語句或程序中的字符串作為分隔符。
mysql> delimiter $
mysql> select count(*) from tjob$
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

想改回到分號，只需
mysql> delimiter ;
8.load data local infile
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS      //字段
        [TERMINATED BY 'string']   //字段分割符
        [[OPTIONALLY] ENCLOSED BY 'char']//字段的包含符，默認(rèn)是''（空）
        [ESCAPED BY 'char' ]       //轉(zhuǎn)義符，如'\\'
    ]
    [LINES
        [STARTING BY 'string']     //行的開始字符串，從string后面開始
        [TERMINATED BY 'string']   //行分隔符,默認(rèn)是'\n'(空行)
    ]
    [IGNORE number LINES]          //跳過前面number行
    [(col_name_or_user_var,...)]   //字段名稱
    [SET col_name = expr,...)]
使用load data導(dǎo)入數(shù)據(jù)，看起來是很簡單，但是卻經(jīng)常容易出錯。比如，需要導(dǎo)入的文本有時(shí)候并不是以’\n’(空行)結(jié)尾，不細(xì)心的話很容易出錯。有時(shí)候?qū)�入的�?shù)據(jù)有列的信息，則可以使用IGNORE number LINES跳過前面指定行數(shù)等。
9.unix_timestamp and from_unixtime
unix_timestamp將時(shí)間轉(zhuǎn)化成時(shí)間戳格式。
from_unixtime將時(shí)間戳轉(zhuǎn)化成時(shí)間格式。
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1251884321 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1251884321);
+---------------------------+
| from_unixtime(1251884321) |
+---------------------------+
| 2009-09-02 17:38:41       |
+---------------------------+
1 row in set (0.00 sec)
很多時(shí)候mysql的應(yīng)用都是使用時(shí)間戳格式來存儲時(shí)間，這兩個函數(shù)應(yīng)用的場合很多，需要牢記。
上面列舉了本人在使用mysql的時(shí)候遇到的一些容易出錯的問題和自己的一些經(jīng)驗(yàn)技巧，目前想到的就是上面的這些，以后將不斷補(bǔ)充，歡迎大家給我留言補(bǔ)充。


本文來自ChinaUnix博客，如果查看原文請點(diǎn)：http://blog.chinaunix.net/u3/111930/showart_2184975.html