【MySQL】常用拼接语句

前言：在MySQL中 CONCAT ()函数用于将多个字符串连接成一个字符串，利用此函数我们可以将原来一步无法得到的sql拼接出来，在工作中也许会方便很多，下面主要介绍下几个常用的场景。

注：适用于5.7版本 低版本可能稍许不同。

1.拼接查询所有用户

mysql">SELECT DISTINCT
    CONCAT(
        'User: \'',
        USER,
        '\'@\'',
        HOST,
        '\';'
    ) AS QUERY
FROM
    mysql.USER;
# 当拼接字符串中出现'时 需使用\转义符

2.拼接DROP table

SELECT
    CONCAT(
        'DROP table ',
        TABLE_NAME,
        ';'
    )
FROM
    information_schema. TABLES
WHERE
    TABLE_SCHEMA = 'test';

3.拼接kill连接

SELECT
    concat('KILL ', id, ';')
FROM
    information_schema. PROCESSLIST
WHERE
    STATE LIKE 'Creating sort index';

4.拼接创建数据库语句

SELECT
    CONCAT(
        'create database ',
        '`',
    SCHEMA_NAME,
    '`',
    ' DEFAULT CHARACTER SET ',
    DEFAULT_CHARACTER_SET_NAME,
        ';'
    ) AS CreateDatabaseQuery
FROM
    information_schema.SCHEMATA
WHERE
    SCHEMA_NAME NOT IN (
        'information_schema',
        'performance_schema',
        'mysql',
        'sys'
    );

5.拼接创建用户的语句

SELECT
    CONCAT(
        'create user \'',
    user,
    '\'@\'',
    Host,
    '\''
    ' IDENTIFIED BY PASSWORD \'',
    authentication_string,
        '\';'
    ) AS CreateUserQuery
FROM
    mysql.`user`
WHERE
    `User` NOT IN (
        'root',
        'mysql.session',
        'mysql.sys'
    );
#有密码字符串哦 在其他实例执行 可直接创建出与本实例相同密码的用户

6.导出权限脚本 这个shell脚本也用到了拼接

#!/bin/bash
#Function export user privileges  pwd=yourpass
expgrants()
{
  mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}  expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql

7.查找表碎片

SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME,
       t.TABLE_ROWS,
       concat(round(t.DATA_LENGTH / 1024 / 1024, 2), 'M') AS size,
       t.INDEX_LENGTH,
       concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = 'test' order by DATA_LENGTH desc;

8.查找无主键表 这个没用到拼接 也分享出来吧

#查找某一个库无主键表
SELECT
table_schema,
table_name
FROM
    information_schema.TABLES
WHERE
    table_schema = 'test'
AND TABLE_NAME NOT IN (
    SELECT
        table_name
    FROM
        information_schema.table_constraints t
    JOIN information_schema.key_column_usage k USING (
        constraint_name,
        table_schema,
        table_name
    )
    WHERE
        t.constraint_type = 'PRIMARY KEY'
    AND t.table_schema = 'test'
);#查找除系统库外 无主键表
SELECT
    t1.table_schema,
    t1.table_name
FROM
    information_schema. TABLES t1
LEFT OUTER JOIN information_schema.TABLE_CONSTRAINTS t2 ON t1.table_schema = t2.TABLE_SCHEMA
AND t1.table_name = t2.TABLE_NAME
AND t2.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
    t2.table_name IS NULL
AND t1.TABLE_SCHEMA NOT IN (
    'information_schema',
    'performance_schema',
    'mysql',
    'sys'
) ;

#########PS:

mysql 查询后不显示标题
2018年10月22日 15:48:00 weixin_33872566 阅读数 2
mysql -h 146.240.54.1 -P 60028 -u dba -p123456 -Dtestdb

直接进入命令行，select，是有|显示的格式的。

mysql -h 146.240.54.1 -P 60028 -u dba -p123456 -Dtestdb<1.sql

这样使用批处理的方式执行，显示是没有|的。

mysql -h 146.240.54.1 -P 60028 -u dba -p123456 -Dtestdb -t<1.sql

如果增加了-t命令项，则会显示|格式

如果使用-vvv选项，则会回显命令。

mysql -h 146.240.54.1 -P 60028 -u dba -p123456 -Dtestdb -N<1.sql

如果使用-N选项，则不显示标题。

在mysql命令行下，可以直接执行批处理文件。

mysql>. 1.sql
作者：mydriverc2
来源：CSDN
原文：https://blog.csdn.net/mydriverc2/article/details/38895395

###3

https://www.cnblogs.com/wangkangluo1/archive/2012/04/27/2472898.html

shell 下执行mysql 命令

原文链接

在shell开发中，很多时候我们需要操作mysql数据库（比如：查询数据、导出数据等），但是我们又无法进入mysql命令行的环境，就需要在shell环境中模拟mysql的环境，使用mysql相关命令，本文总结几种shell操作mysql的方法，供大家参考。

方案1

1. mysql -uuser -ppasswd -e”insert LogTable values(…)”

优点：语句简单缺点：支持的sql相对简单 

方案2

准备一个sql脚本，名字为update.sql，例如：

1. CREATE TABLE `user` (
2. `id` varchar(36) NOT NULL COMMENT ‘主键’,
3. `username` varchar(50) NOT NULL COMMENT ‘用户名’,
4. `password` varchar(50) NOT NULL COMMENT ‘用户密码’,
5. `createdate` date NOT NULL COMMENT ‘创建时间’,
6. `age` int(11) NOT NULL COMMENT ‘年龄’,
7. PRIMARY KEY  (`id`)
8. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=’用户信息表’;
9. DROP TABLE IF EXISTS `visit_log`;
10. CREATE TABLE `visit_log` (
11. `id` varchar(36) character set utf8 NOT NULL,
12. `type` int(11) NOT NULL,
13. `content` text character set utf8 NOT NULL,
14. `createdate` date NOT NULL,
15. PRIMARY KEY  (`id`)
16. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT=’访问日志’;

 新建一个update_mysql.sh，内容如下：

1. use chbdb;
2. source update.sql

 然后执行如下命令：

1. cat update_mysql.sh | mysql –user=root -ppassword

优点：支持复杂的sql脚本缺点：1> 需要两个文件：update.sql和update_mysql.sh2> 一旦中间出错，之后脚本就不会执行，例如：如果第一张表已经存在，则会报出如下异常：ERROR 1050 (42S01) at line 1 in file: ‘update.sql’: Table ‘user’ already exists然后脚本退出，第二张表也就无法创建。

方案3

    新建一个shell脚本，格式如下：

1. #!/bin/bash
2. mysql -u* -h* -p* <<EOF
3. Your SQL script.
4. EOF

例如：

1. #!/bin/bash
2. mysql -uroot  -ppassword <<EOF
3. use chbdb;
4. CREATE TABLE user (
5. id varchar(36) NOT NULL COMMENT ‘主键’,
6. username varchar(50) NOT NULL COMMENT ‘用户名’,
7. password varchar(50) NOT NULL COMMENT ‘用户密码’,
8. createdate date NOT NULL COMMENT ‘创建时间’,
9. age int(11) NOT NULL COMMENT ‘年龄’,
10. PRIMARY KEY  (`id`)
11. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=’用户信息表’;

优点：1>支持复杂的sql脚本2>无需其它额外文件缺点：1> 表名、字段不能使用单引号，需要修改原有sql语句2> 一旦中间出错，之后脚本就不会执行，例如：如果第一张表已经存在，则会报出如下异常：ERROR 1050 (42S01) at line 1 in file: ‘update.sql’: Table ‘user’ already exists然后脚本退出，第二张表也就无法创建。

方案4

准备一个sql脚本，如update.sql，然后执行如下命令：

1. mysql -uroot -ppassword < update.sql

优点：支持复杂的sql脚本缺点：1> 一旦中间出错，之后脚本就不会执行，例如：如果第一张表已经存在，则会报出如下异常：ERROR 1050 (42S01) at line 1 in file: ‘update.sql’: Table ‘user’ already exists然后脚本退出，第二张表也就无法创建。      大家知道在mysql命令行中使用source命令，即使中间出错，后续脚本也会继续执行，但是如上几种方式，均无法解决该问题，如果大家有好的建议，请回复，谢谢。