MySQL 实用技巧

概述：

　　MySQL有许多实用的技巧，利用这些技巧能提高工作的效率，减少一些不必要的麻烦。以下是几个我在MySQL日常维护从常用的技巧。

一、prompt 命令

功能：设置mysql客户端提示符
说明：默认使用mysql命令行登录MySQL服务器后只会有[mysql>]的提示符，利用prompt命令则可以修改默认提示符，有利于数据库的维护，也能有效的防止误操作的发生。
用法：可在配置文件中配置，也可在命令行手动设定，写法有些许不同，详细参数说明如下，也可访问官方文档查看细节，链接如下：
http://dev.mysql.com/doc/refman/5.7/en/mysql-commands.html
详细选项说明：(红色为常用选项)

Option	Description
​​\C​​	The current connection identifier (MySQL 5.7.6 and up)
​​\c​​	A counter that increments for each statement you issue
​​\D​​	The full current date
​​\d​​	The default database
​​\h​​	The server host
​​\l​​	The current delimiter
​​\m​​	Minutes of the current time
​​\n​​	A newline character
​​\O​​	The current month in three-letter format (Jan, Feb, …)
​​\o​​	The current month in numeric format
​​\P​​	am/pm
​​\p​​	The current TCP/IP port or socket file
​​\R​​	The current time, in 24-hour military time (0–23)
​​\r​​	The current time, standard 12-hour time (1–12)
​​\S​​	Semicolon
​​\s​​	Seconds of the current time
​​\t​​	A tab character
​​\U​​	Your full ​​​​user_name@host_name​​​​ account name
​​\u​​	Your user name
​​\v​​	The server version
​​\w​​	The current day of the week in three-letter format (Mon, Tue, …)
​​\Y​​	The current year, four digits
​​\y​​	The current year, two digits
​​\_​​	A space
​​\ ​​	A space (a space follows the backslash)
​​\'​​	Single quote
​​\"​​	Double quote
​​\\​​	A literal “​​\​​”
​​​\x​​​	​​x​​, for any “​​x​​”

以下为配置示例：
1、在my.cnf配置文件[client]部分中增加以下配置
prompt="[\\r:\\m:\\s](\\U)[\\d] > "     -- 在配置文件中配置都需要用\对配置的参数进行转义

[root@manager ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear[03:11:57](root@localhost)[(none)] >　　-- 显示时间 + 登录用户 + 当前使用的数据库(未使用则为none)
[03:15:40](root@localhost)[test] >

2、在mysql客户端内直接配置

mysql>prompt [\r:\m:\s](\U)[\d] >　　　　
PROMPT set to '[\r:\m:\s](\U)[\d] >'
[03:22:31](root@localhost)[(none)] >   -- 显示时间 + 登录用户 + 当前使用的数据库

[03:22:31](root@localhost)[(none)] >prompt (\U)[\d] >
PROMPT set to '(\U)[\d] >'
(root@localhost)[(none)] >             -- 只显示 登录用户 + 当前使用的数据库

二、tee/notee命令

功能：tee实现将命令行中的输入输出结果保存到文本文件中,使用该命令能方便的将命令进行日志记录。 
用法：tee /tmp/command.log

以下为使用示例：

[03:43:49](root@localhost)[(none)] > tee /tmp/command.log    -- 设置将输出的结果保存到文件中
Logging to file '/tmp/command.log'
[03:44:02](root@localhost)[(none)] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| paralleldb         |
| performance_schema |
| web                |
+--------------------+
10 rows in set (0.00 sec)

[03:44:18](root@localhost)[(none)] > system cat /tmp/command.log    -- 通过system 命令查看系统文件内容与数据库中内容一致03:44:02](root@localhost)[(none)] > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| paralleldb         |
| performance_schema |
| web                |
+--------------------+
10 rows in set (0.00 sec)

[03:46:55](root@localhost)[(none)] > notee         -- 使用notee可结束文件的输出
Outfile disabled.

三、pager less/pager命令

功能：实现返回结果页面的分页显示，类似于Linux less 命令，可对分页进行上下翻页，搜索等操作，对返回结果过长很方便，如使用show engine innodb status命令时。
用法：pager less -i   表示设置页面分页显示，-i表示不区分大小写，在搜索时十分实用。
pager　　　　　　 直接输入pager表示恢复默认值，也就是不分页显示。
示例：略，可自行测试效果

四、concat函数

功能：concat函数能对查询返回的结果进行拼接
用法：concat(s1,s2,...sn)
常用场景：批量生成对数据库的相关修改语句或导出语句
以下为具体示例：

1、批量生成将test数据库中所有表导出为.txt或csv文本的命令
-- 注意需要对单引号用反斜杠(\)进行转义

[04:06:57](root@localhost)[(none)] > select concat('select * into outfile \'/tmp/',table_name,'.csv\' from ',table_schema,'.',table_name,';') 
from information_schema.tables where  table_schema='test';
+---------------------------------------------------------------------------------------------------+
| concat('select * into outfile \'/tmp/',table_name,'.csv\' from ',table_schema,'.',table_name,';') |
+---------------------------------------------------------------------------------------------------+
| select * into outfile '/tmp/address.csv' from test.address;                                       |
| select * into outfile '/tmp/address1.csv' from test.address1;                                     |
| select * into outfile '/tmp/clone_t.csv' from test.clone_t;                                       |
| select * into outfile '/tmp/lock_test.csv' from test.lock_test;                                   |
| select * into outfile '/tmp/query_history.csv' from test.query_history;                           |
| select * into outfile '/tmp/query_review.csv' from test.query_review;                             |
| select * into outfile '/tmp/ram.csv' from test.ram;                                               |
| select * into outfile '/tmp/t.csv' from test.t;                                                   |
| select * into outfile '/tmp/t1.csv' from test.t1;                                                 |
| select * into outfile '/tmp/t2.csv' from test.t2;                                                 |
| select * into outfile '/tmp/t3.csv' from test.t3;                                                 |
| select * into outfile '/tmp/z.csv' from test.z;                                                   |
+---------------------------------------------------------------------------------------------------+

当需要对数据库的表进行批量操作时，通过这种方式批量生成语句能大大提高效率

2、去除结果的虚线框并将结果输出到文件中-- 通常我们需要将生成的语句保存到一个文件中，并且运行，这时就需要配合tee命令以及mysql客户端的参数了
mysql --skip-column-names --silent  (简写为 mysql -ss)能以静默方式登录MySQL服务器并不显示输出结果的虚线框
tee  /tmp/export.sql　　　　 将执行的结果保存到文件中

[root@manager ~]# mysql -uroot -p -ss      -- 使用静默方式登录数据库
Enter password: 
[04:18:55](root@localhost)[(none)] > tee /tmp/export.sql     -- 将结果保存到外部文件中
Logging to file '/tmp/export.sql'
[04:19:04](root@localhost)[(none)] > select concat('select * into outfile \'/tmp/',table_name,'.csv\' from ',table_schema,'.',table_name,';') 
from information_schema.tables where  table_schema='test';
-- 可以看到输出的结果没有虚线框
select * into outfile '/tmp/address.csv' from test.address;
select * into outfile '/tmp/address1.csv' from test.address1;
select * into outfile '/tmp/clone_t.csv' from test.clone_t;
select * into outfile '/tmp/lock_test.csv' from test.lock_test;
select * into outfile '/tmp/query_history.csv' from test.query_history;
select * into outfile '/tmp/query_review.csv' from test.query_review;
select * into outfile '/tmp/ram.csv' from test.ram;
select * into outfile '/tmp/t.csv' from test.t;
select * into outfile '/tmp/t1.csv' from test.t1;
select * into outfile '/tmp/t2.csv' from test.t2;
select * into outfile '/tmp/t3.csv' from test.t3;
select * into outfile '/tmp/z.csv' from test.z;
[04:19:08](root@localhost)[(none)] > exit

-- 查看tee输出文件中的内容
[root@manager ~]# cat /tmp/export.sql 
[04:19:04](root@localhost)[(none)] > select concat('select * into outfile \'/tmp/',table_name,'.csv\' from ',table_schema,'.',table_name,';') from information_schema.tables where  table_schema='test';
select * into outfile '/tmp/address.csv' from test.address;
select * into outfile '/tmp/address1.csv' from test.address1;
select * into outfile '/tmp/clone_t.csv' from test.clone_t;
select * into outfile '/tmp/lock_test.csv' from test.lock_test;
select * into outfile '/tmp/query_history.csv' from test.query_history;
select * into outfile '/tmp/query_review.csv' from test.query_review;
select * into outfile '/tmp/ram.csv' from test.ram;
select * into outfile '/tmp/t.csv' from test.t;
select * into outfile '/tmp/t1.csv' from test.t1;
select * into outfile '/tmp/t2.csv' from test.t2;
select * into outfile '/tmp/t3.csv' from test.t3;
select * into outfile '/tmp/z.csv' from test.z;
[04:19:08](root@localhost)[(none)] > exit
-- 文件内容记录了所有的操作及输出的结果，可以编辑文件，将文件头的查询语句及文件尾的退出语句删除，则完成了一个将数据库导出成文本文件的的脚本。