使用Mysql的小技巧总结

2009年09月02日　作者： 大头刚　

使用Mysql也很长一段时间了，在使用的过程中遇到过各种各样的问题，有些看上去很小的问题，在解决的过程中却花了很长时间，解决后才发现，原来这样简单。所以总结了一些经验和技巧，把它写出来，希望对读者能有所帮助，少走弯路。

1. binary
BINARY不是函数，是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写。举例：

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结果的最后一行，列出所有行的总和，如下，多了最后一行

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
排序时将某列中特定值排在最前，例如我在排序中要把aa放在最前面：

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数据库管理，有时会发生自己忘记了在那台服务器，那台数据库上，毋庸多说，谁都知道这有多危险。可以使用prompt来重写mysql客户端提示符。

mysql>prompt \u@\h(\d) \r:\m:\s> 
\u:连接用户 
\h:连接主机 
\d:连接数据库 
\r:\m:\s:显示当前时间 
 
如果嫌每次都在命令行修改麻烦，直接修改my.cnf参数
[mysql]    #这是不是[mysqld]   
prompt=\\u@\\d \\R:\\m>

6.pager
如果select出来的结果集超过几个屏幕，那么前面的结果一晃而过无法看到。使用pager可以设置调用os的more或者less等显示查询结果，和在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解释器,命令的结束符是什么。
默认情况下MySQL命令结束是以分号(;)，在写过程或者函数等情况下，这会产生不少问题，因为存储过程中有许多语句，所以每一个都需要一个分号。因此你需要选择一个不太可能出现在你的语句或程序中的字符串作为分隔符。

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']//字段的包含符，默认是''（空）
        [ESCAPED BY 'char' ]       //转义符，如'\\'
    ]
    [LINES
        [STARTING BY 'string']     //行的开始字符串，从string后面开始
        [TERMINATED BY 'string']   //行分隔符,默认是'\n'(空行)
    ]
    [IGNORE number LINES]          //跳过前面number行
    [(col_name_or_user_var,...)]   //字段名称
    [SET col_name = expr,...)]

使用load data导入数据，看起来是很简单，但是却经常容易出错。比如，需要导入的文本有时候并不是以’\n’(空行)结尾，不细心的话很容易出错。有时候导入的数据有列的信息，则可以使用IGNORE number LINES跳过前面指定行数等。

9.unix_timestamp and from_unixtime
unix_timestamp将时间转化成时间戳格式。
from_unixtime将时间戳转化成时间格式。

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)

很多时候mysql的应用都是使用时间戳格式来存储时间，这两个函数应用的场合很多，需要牢记。

上面列举了本人在使用mysql的时候遇到的一些容易出错的问题和自己的一些经验技巧，目前想到的就是上面的这些，以后将不断补充，欢迎大家给我留言补充。