Mysql使用技巧汇总

使用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的应用都是使用时间戳格式来存储时间,这两个函数应用的场合很多,需要牢记。

10. 使用Limit的时候获得总行数

A SELECT语句可能包括一个 LIMIT 子句,用来限制服务器返回客户端的行数。在有些情况下,需要不用再次运行该语句而得知在没有LIMIT 时到底该语句返回了多少行。为了知道这个行数, 包括在SELECT 语句中选择 SQL_CALC_FOUND_ROWS ,随后调用 FOUND_ROWS() :

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name

    -> WHERE id > 100 LIMIT 10;

mysql> SELECT FOUND_ROWS();

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

发表评论

返回顶部